如何创建分组的每日，每周和每月报告，包括SQL Server中的计算字段

I'm using SQL Server (2012), and using the two (simplified) tables below, how do I create 3 separate reports (daily, weekly and monthly) and include the following calculated fields:

我正在使用SQL Server（2012），并使用下面的两个（简化）表，如何创建3个单独的报告（每日，每周和每月）并包含以下计算字段：

1. new users created in this period
2. total number of users at this time


**Users**
userID          int
name            varchar(80)
userCreated     datetime


**Orders**
orderID         int
userID          int
orderCreated    datetime

I've been messing around with this code:

我一直在搞乱这段代码：

SELECT CAST(DATEPART(dd,userCreated) as VARCHAR(2)) + '/' + CAST(DATEPART(mm,userCreated) AS VARCHAR(2)) + '/' + CAST(DATEPART(yyyy,userCreated) AS VARCHAR(4)) [Date],
    count(*) newSignUps,
    (select count(*) from users u2 WHERE u2.userCreated < u1.userCreated)
FROM users u1
WHERE userCreated BETWEEN '05/01/2014 00:00:00.000' and '05/31/2014 23:59:59.000'
GROUP BY DATEPART(dd,userCreated), DATEPART(mm,userCreated), DATEPART(yyyy,userCreated),userCreated

But to show anything, it needs the "userCreated" field added to the grouping...

但要显示任何内容，需要将“userCreated”字段添加到分组中...

For the reports I need to show:

对于我需要显示的报告：

Daily:

日常：

date          new sign ups        users in system
17/03/2013    10                  100
18/03/2013    4                   104
19/03/2013    8                   112

Weekly:

每周：

week
13             8                   40
14             2                   42
15             5                   47

Monthly:

每月：

Jan            3                   54
Feb            9                   63
Mar            2                   65

I hope this makes sense? Thank you...

我希望这是有道理的？谢谢...

2 个解决方案

#1

I'm not sure if I understood your question correctly, but this gives you all the users created per day:

我不确定我是否正确理解了您的问题，但这会为您提供每天创建的所有用户：

SELECT year(userCreated), month(userCreated), day(userCreated), count(*)
FROM Users
GROUP BY year(userCreated), month(userCreated), day(userCreated)

this one by month:

这一个月一个月：

SELECT year(userCreated), month(userCreated), count(*)
FROM Users
GROUP BY year(userCreated), month(userCreated)

and this one by week:

这一周一周：

SELECT year(userCreated), datepart(week, userCreated), count(*)
FROM Users
GROUP BY year(userCreated), datepart(week, userCreated)

Edit according to you the missing total field I give you here the example for the month query:

根据您编辑缺少的总字段我在这里为您提供月份查询的示例：

SELECT year(userCreated), month(userCreated), count(*) AS NewCount,
(SELECT COUNT(*) FROM Users u2 WHERE 
    CAST(CAST(year(u1.userCreated) AS VARCHAR(4)) + RIGHT('0' + CAST(month(u1.userCreated) AS VARCHAR(2)), 2) + '01' AS DATETIME) > u2.userCreated) AS TotalCount
FROM Users u1
GROUP BY year(userCreated), month(userCreated)

Hope this helps for the other two queries.

希望这有助于其他两个查询。

#2

DAILY

日常

select count(UserId),userCreated     
from User WHERE CONDITION group by CreatedOn

MONTHLY

每月

select count(UserId),
LEFT(CONVERT(varchar, userCreated ,112),6) from User 
WHERE CONDITION  group by LEFT(CONVERT(varchar, userCreated ,112),6)

要么

select count(UserId),
month(userCreated ) 
from User group by month(userCreated )

WEEKLY

每周

select count(UserId),
DATEPART( wk, userCreated) 
from User WHERE CONDITION  group by DATEPART( wk, userCreated     )

FOR BOTH NEW and EXISTING USER COUNT (i did for monthly)

对于新的和现有的用户计数（我每月做的）

select new,
(select count(UserId) from User where month(userCreated)<=monthwise) as total,
monthwise
FROM (

select count(UserId) as new,

month(userCreated)as monthwise from User group by month(userCreated) 
)tmp

#1