SQL - Search table for last day of each month in date range and output data?

Question

I have 2 tables:

Ticket_Report
Ticket_Report_Snapshot

The Ticket_Report_Snapshot table is an exact copy of the Ticket_Report table, but has 1 extra column:

Snapshot_Date

A snap shot of the Ticket report table is taken everyday, with the Snapshot_date being the date that the snapshot was taken.

The columns that both Tables have that I am working with are:

Project_group, Ticket_Status

I need to create a stored procedure that takes 2 Date parameters. From these 2 dates, I need to print the count of all open tickets for each project on the last day of each month in between the 2 dates passed (the last day of each month is to be searched for in the Snapshot_Date column of the Ticket_report_snapshot table).

This is what I have so far:

--This query gives me the last day of any particular month
DECLARE @dtDate DATETIME
SET @dtDate = '1/6/2016'
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
-- ouput: 2016-01-31 23:59:59.000


SELECT Project_Group as Project_Name,  count(ticket_status) as Open_Tickets 
FROM Ticket_Report_SnapShot
WHERE ticket_status != 'closed' AND ticket_status != 'cancelled'
AND snapshot_date = '2016-01-06'      
GROUP BY Project_Group

--Right now, the output is perfect for this 1 date, hard coded in
--OutPut:

Project_Name    Open_Tickets
Project 1       77
Project 2       5
Project 3       118
Project 4       22     --I need this kind of output, but for the last
Project 5       1      --day of each month between the 2 parameters
Project 6       2      --instead of just 1 date
Project 7       1

So I have 2 queries so far, 1 to give me the last day of any particular month, and 1 to show me the open tickets for 1 particular hard coded date.

How can I edit/combine these queries to use 2 date parameters, and give me the open tickets for each project for the last day of every month in between 2 date ranges?

Ex. 1/1/2016 and 3/3/2016 (1/31, 2/29, 3/31, these 3 dates would be searched for in the ticket_report_snapshot table, in the snapshot_date column)

Do you still need a Zero result for months (last days) with NO data? Or only those dates with data... — Grantly, Jan 07 '16 at 14:34
@Grantly If there is no data, then I dont need to put a zero result, but if it would be a simple addition, it would be nice to have. — Reeggiie, Jan 07 '16 at 14:38
@Reggie It makes it more complex if you want to list every end-of-month date (between your parameters) even if some have no data...It means you really need a calendar table to start with (which produces only your end dates of each month) and then join this calendar table to your other tables (LEFT JOIN) so that every date is listed - even if the resultant data is NULL (or converted to zero) — Grantly, Jan 07 '16 at 14:40
The trick is really in producing a calendar table - not as easy as it sounds even though the output is simple... Then the joins are easy after that...I'll scout around for you — Grantly, Jan 07 '16 at 14:42
@Grantly ahh ok, printing zero results is not a requirement, so I could avoid this extra work. — Reeggiie, Jan 07 '16 at 14:44
Way over the top, but looks very good: https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/ — Grantly, Jan 07 '16 at 14:56
This is helpful, and simple... http://stackoverflow.com/questions/6125504/how-can-i-generate-a-temporary-table-filled-with-dates-in-sql-server-2000 — Grantly, Jan 07 '16 at 15:00

JamieD77 · Accepted Answer · 2016-01-07T15:15:13.323

You can use a recursive cte to get your dates and join your snapshot table to the cte

DECLARE @StartDate DATETIME = '2016-01-01',
    @EndDate DATETIME = '2016-03-03';

WITH DateCTE AS 
(
    SELECT    EOMONTH(@StartDate) snapshot_date
    UNION ALL
    SELECT    EOMONTH(DATEADD(MONTH,1,snapshot_date))
    FROM      DateCTE
    WHERE     EOMONTH(DATEADD(MONTH,1,snapshot_date)) <= EOMONTH(@EndDate)
)
SELECT  Project_Group AS Project_Name,
        trs.snapshot_date,
        COUNT(ticket_status) AS Open_Tickets
FROM    Ticket_Report_SnapShot trs
        INNER JOIN DateCTE cte ON trs.snapshot_date = cte.snapshot_date
WHERE   ticket_status != 'closed'
        AND ticket_status != 'cancelled'
GROUP BY Project_Group,
        trs.snapshot_date

You can use this if you are still using SQL Server 2008

DECLARE @StartDate DATETIME = '2016-01-01',
        @EndDate DATETIME = '2016-03-03';

WITH DateCTE AS 
(
    SELECT    DATEADD(dd,-1,DATEADD(mm,DATEDIFF(m,0,@StartDate) + 1,0)) snapshot_date
    UNION ALL
    SELECT    DATEADD(dd,-1,DATEADD(mm,DATEDIFF(m,0,DATEADD(MONTH,1,snapshot_date)) + 1,0))
    FROM      DateCTE
    WHERE     DATEADD(dd,-1,DATEADD(mm,DATEDIFF(m,0,DATEADD(MONTH,1,snapshot_date)) + 1,0)) <= DATEADD(dd,-1,DATEADD(mm,DATEDIFF(m,0,@EndDate) + 1,0))
)
SELECT  Project_Group AS Project_Name,
        trs.snapshot_date,
        COUNT(ticket_status) AS Open_Tickets
FROM    Ticket_Report_SnapShot trs
        INNER JOIN DateCTE cte ON trs.snapshot_date = cte.snapshot_date
WHERE   ticket_status != 'closed'
        AND ticket_status != 'cancelled'
GROUP BY Project_Group,
        trs.snapshot_date

If the OP is using less than SQL 2012 this may be handy: ´SELECT DATEADD(dd, -DAY(DATEADD(mm, 1, @today)), DATEADD(mm, 1, @today))´ to replace EOMONTH — Grantly, Jan 07 '16 at 15:06
@Grantly I am getting a message that EOMONTH is not a recognized built in function, so I am inputting your line, and getting an error on Today saying 'Must declare the scalar variable Today. Can I just declare this, or do I need to replace Today with something — Reeggiie, Jan 07 '16 at 15:14
@JamieD77 I am getting back columns with nothing in them, im trying to figure out if its because of my table data, or something in the query — Reeggiie, Jan 07 '16 at 15:22
@JamieD77 Thanks this is perfect, I added some new data and its working perfectly. — Reeggiie, Jan 07 '16 at 15:35

SQL - Search table for last day of each month in date range and output data?

1 Answers1