每周和每月SQL的COUNT总计

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

我有一张表,我必须按每天,每周和每月计算分配给每个USER的总行数。

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

现在我必须为每个用户发送COUNT每日,每周和每月的书籍

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

每日是今天(20160709)每周是星期日到星期六每月是整月

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

我已经为每日发布了这个SQL

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 ?

有人可以帮我写这三个组合的SQL吗?

Thanks

Aiden

2 个解决方案

#1

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

您可能需要添加WHERE子句以仅检索当前月份的记录

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

编辑:对于[日期]列是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

#2

• You should consider investing in a legitimate Date_Time table.

您应该考虑投资合法的Date_Time表。

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.

但是,还有另一种方式。如您所见,DATEPART返回我们正在寻找的ISO月和周。

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

所以如果我们的年份是正确的,我们现在知道我们的边界在哪里,并且可以在COUNT( )内轻松使用IIF( , , )语句。 COUNT忽略NULL,因此我们将TRUE设置为1,将FALSE设置为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.

另请注意,您的示例实际上只有一个日期不是同一个月,周或日(在一天内),尽管在我的示例中,我要求Days与查询在同一天使其看起来有点不同。

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.

根据定义,DATE没有格式化,DATEPART可以从格式良好的Datetime字符串中猜出,因此没有理由对您的Date列进行双重转换。但是,如果您的模式发生更改,则可能需要添加CONVERT。

• DATEPART gives you the standard (ISO) Month and Week recognized, which means no Date_Time table required here. :)

DATEPART为您提供标准(ISO)月和周识别,这意味着此处不需要Date_Time表。 :)

• DATEDIFF is the magic here, and makes your Boolean statement REALLY easy to work with.

DATEDIFF在这里很神奇,使你的布尔语句真的很容易使用。

• Pretty slick, no?

很漂亮,不是吗?

• MSDN's page on DATEPART is worth a quick glance.

MSDN的DATEPART页面值得一看。