CODEWITHSUNDEEP
sqlsql-serverdate
Matt VT
Matt VT

Reputation: 11

SQL - return all days of last month

I have a query that will return the total number of calls I received each day of last month for a queue. if no calls it will return zero for that day. However when running this report for for December of 2013 the results are blank. It appears to be due to the fact the query is returning the dates with the year of 2014 instead of 2013. How can I adjust the query so the date is 12/01/2013, etc.

DECLARE @pMnth int,@pYr int,@pQueue varchar
SET @pMnth = '12'
SET @pYr = '2013'
SET @pQueue = 'Queue Name'
;

WITH
CTE_Days AS
 (
 SELECT DATEADD(month, @pMnth, DATEADD(month, -MONTH(GETDATE()), DATEADD(day, -DAY(GETDATE()) + 1, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)))) as Dt
 UNION ALL
 SELECT DATEADD(day, 1, Dt)
 FROM CTE_Days
 WHERE Dt < DATEADD(day, -1, DATEADD(month, 1, DATEADD(month, @pMnth, DATEADD(month, -MONTH(GETDATE()), DATEADD(day, -DAY(GETDATE()) + 1, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))))))
 )
 SELECT tbl1.Dt,ISNULL(Calls,0) AS Calls
 FROM CTE_Days tbl1

 LEFT JOIN(
SELECT CAST(clmdate AS Date) AS ClDt,COUNT(*) AS Calls
FROM dbo.tblcalls
WHERE DATEPART(yyyy,clmdate) = @pYr
      AND DATEPART(mm,clmdate) = @pMnth
      AND clmqueue = @pQueue
GROUP BY CAST(clmdate AS Date) 
) tbl2 ON tbl1.Dt = tbl2.ClDt

Upvotes: 1

Views: 1277

Answers (1)

Hart CO
Hart CO

Reputation: 34774

Not really sure what you're trying to do with all the variables, but if you want the first day of last month:

SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE())-1, 0)   -- < SQL 2012
SELECT DATEADD(day,1,EOMONTH(GETDATE(),-2))              -- SQL 2012

Last day of last month:

SELECT DATEADD(day,-1,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))  -- < SQL 2012
SELECT EOMONTH(GETDATE(),-1)       -- SQL 2012

In your code (assuming you're not on 2012):

DECLARE @pMnth int,@pYr int,@pQueue varchar
SET @pMnth = '12'
SET @pYr = '2013'
SET @pQueue = 'Queue Name'
;
WITH
CTE_Days AS
 (
 SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE())-1, 0) as Dt
 UNION  ALL
 SELECT DATEADD(day, 1, Dt)
 FROM CTE_Days
 WHERE Dt < DATEADD(day,-1,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
 )
 SELECT tbl1.Dt,ISNULL(Calls,0) AS Calls
 FROM CTE_Days tbl1

 LEFT JOIN(
SELECT CAST(clmdate AS Date) AS ClDt,COUNT(*) AS Calls
FROM dbo.tblcalls
WHERE DATEPART(yyyy,clmdate) = @pYr
      AND DATEPART(mm,clmdate) = @pMnth
      AND clmqueue = @pQueue
GROUP BY CAST(clmdate AS Date) 
) tbl2 ON tbl1.Dt = tbl2.ClDt

Update:

To incorporate your variables, I would change the month and year variables to strings, and replace GETDATE() with a date variable:

DECLARE @pMnth CHAR(2)
       ,@pYr CHAR(4)
       ,@pQueue VARCHAR(MAX)
       ,@dt DATE

SET @pMnth = '12'
SET @pYr = '2013'
SET @pQueue = 'Queue Name'
SET @dt = CAST(@pYr+RIGHT('0'+@pMnth,2)+'01' AS DATE)

WITH
    CTE_Days AS
     (
     SELECT DATEADD(month, DATEDIFF(month, 0, @dt)-1, 0) as Dt
     UNION  ALL
     SELECT DATEADD(day, 1, Dt)
     FROM CTE_Days
     WHERE Dt < DATEADD(day,-1,DATEADD(month, DATEDIFF(month, 0, @dt), 0))
     )
     SELECT tbl1.Dt,ISNULL(Calls,0) AS Calls
     FROM CTE_Days tbl1

     LEFT JOIN(
    SELECT CAST(clmdate AS Date) AS ClDt,COUNT(*) AS Calls
    FROM dbo.tblcalls
    WHERE DATEPART(yyyy,clmdate) = @pYr
          AND DATEPART(mm,clmdate) = @pMnth
          AND clmqueue = @pQueue
    GROUP BY CAST(clmdate AS Date) 
    ) tbl2 ON tbl1.Dt = tbl2.ClDt

Upvotes: 3

Related Questions