How to create grouped daily,weekly and monthly reports including calculated fields in SQL Server

Question

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: ``` 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... For the reports I need to show: Dail…

Accepted Answer

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.

How to create grouped daily,weekly and monthly reports including calculated fields in SQL Server

Solution

Problem