COUNT total by weekly and monthly SQL

Question

I have a table in which I have to count total rows assigned to each USER by daily, weekly and monthly.

Table BooksIssued

BOOKID    ISSUEDUSER    DATE
 1            A        20160708
 2            A        20160709
 3            A        20160708
 4            A        20150102
 5            B        20160709
 6            C        20160708
 7            C        20160708

Now I have to COUNT daily, weekly and monthly books issued to each user

Daily is today (20160709) Weekly is Sunday through Saturday Monthly is whole month

The result should be

ISSUEDUSER    DAILYBOOKS WEEKLYBOOKS  MONTHLYBOOKS
  A               1       3           3
  B               1       1           1
  C               0       2           2

I have done this SQL for daily issued

SELECT ISSUEDUSER, COUNT(BOOKID) AS DAILYBOOKS
FROM BOOKSISSUED
WHERE DATE = CONVERT(VARCHAR(11), SYSDATETIME(), 112)
GROUP BY ISSUEDUSER

Can someone please help me write a combined SQL for all three ?

Thanks

Aiden

Answer 1

you might need to add a WHERE clause to only retrieve current month's records

SELECT ISSUEDUSER, 
       SUM(CASE WHEN DATE = DATEADD(DAY, DATEDIFF(DAY, 0, SYSDATETIME()), 0))
                THEN 1 ELSE 0 END) AS DAILYBOOKS,
       SUM(CASE WHEN DATE >= DATEADD(WEEK, DATEDIFF(WEEK, 0, SYSDATETIME()), 0)
                AND  DATE <  DATEADD(WEEK, DATEDIFF(WEEK, 0, SYSDATETIME()) + 1, 0)
                THEN 1 ELSE 0 END) AS WEEKLYBOOKS,
       COUNT(*) AS MONTHLYBOOKS
FROM   BOOKSISSUED
WHERE  DATE >= DATEADD(MONTH, DATEDIFF(MONTH, 0, SYSDATETIME()), 0)
AND    DATE <  DATEADD(MONTH, DATEDIFF(MONTH, 0, SYSDATETIME()) + 1, 0)
GROUP BY ISSUEDUSER

EDIT : for [DATE] column is INT

SELECT ISSUEDUSER, 
       SUM(CASE WHEN DATE = CONVERT(INT, CONVERT(VARCHAR(8), SYSDATETIME(), 112))
                THEN 1 ELSE 0 END) AS DAILYBOOKS,
       SUM(CASE WHEN DATE >= CONVERT(INT, CONVERT(VARCHAR(8), DATEADD(WEEK, DATEDIFF(WEEK, 0, SYSDATETIME()), 0), 112))
                AND  DATE <  CONVERT(INT, CONVERT(VARCHAR(8), DATEADD(WEEK, DATEDIFF(WEEK, 0, SYSDATETIME()) + 1, 0), 112))
                THEN 1 ELSE 0 END) AS WEEKLYBOOKS,
       COUNT(*) AS MONTHLYBOOKS
FROM   BOOKSISSUED
WHERE  DATE >= CONVERT(INT, CONVERT(VARCHAR(6), SYSDATETIME(), 112) + '01')
AND    DATE <  CONVERT(INT, CONVERT(VARCHAR(6), DATEADD(MONTH, 1, SYSDATETIME()), 112) + '01')
GROUP BY ISSUEDUSER

Answer 2

• You should consider investing in a legitimate Date_Time table.

It makes comparing the official beginning and ending of the weeks MUCH easier and practical. And hey, you might even be able to use indexing!

However, there is another way. AS you shall see, DATEPART returns the ISO Month and Week we are looking for.

So provided our year is right, we now know where our boundaries are and can easily use an IIF(<boolean_expression>, <true_expression>, <false_expression>) statement inside of a COUNT(<column>). COUNT ignores NULLs, so we set TRUE to 1 and FALSE to NULL. :D

-- Note, I changed the column [Date] to [Dates]
DECLARE @Date INT
SET @Date = CAST(CAST(SYSDATETIME() AS VARCHAR(4) ) + '0101' AS INT)

SELECT  ISSUEDUSER--DATEPART(YYYY, CAST(Dates AS VARCHAR(10) ) )
      , COUNT( IIF(DATEDIFF(MM, CAST(Dates AS VARCHAR(10) ), GETDATE() ) = 0
           , 1
           , NULL) ) AS MONTHS
      , COUNT( IIF(DATEDIFF(WW, CAST(Dates AS VARCHAR(10) ), GETDATE() ) = 0
           , 1
           , NULL) ) AS Weeks
      , COUNT( IIF(DATEDIFF(DD, CAST(Dates AS VARCHAR(10) ), GETDATE() ) = 0
           , 1
           , NULL) ) AS Days
FROM    #BookReport
WHERE DATES >= @Date
GROUP BY ISSUEDUSER
--results    
ISSUEDUSER  MONTHS  Weeks   Days
A           3       3       1
B           1       1       1 
C           2       2       0

Note that you can expand the allowable date difference by adjusting the boolean statement! No extra coding required.

Also note that your examples actually only have one date that is not of the same Month, Week, or Day (within one day), although in my example I required Days to be of the same day as the query to make it look a bit different.

Cool Observations:

• DATE by definition has no formatting and DATEPART can guess from a well-formed Datetime string, so there was no reason to double cast your Date column. However, if your pattern changes, you may need to add a CONVERT.
• DATEPART gives you the standard (ISO) Month and Week recognized, which means no Date_Time table required here. :)
• DATEDIFF is the magic here, and makes your Boolean statement REALLY easy to work with.
• Pretty slick, no?
• MSDN's page on DATEPART is worth a quick glance.