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:

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...

Solution 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.

Solution 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) 

OR

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

Comments

[andym0908]

Thank you, but this is missing the "total number of users at that time" field

[sumit]

i hope u can do it that by yourself :)

[andym0908]

Yes, this has grouping and includes the "new users" field, but is also missing the "total number of users at that time" field...

[andym0908]

Of course I can, I'm not a beginner ;) -- but my problem is incorporating this extra field into the grouping

[sumit]

seems complex .. i am figuring out

[andym0908]

Thank you - in essence it seems simple, but I get the "userCreated" is invalid because not contained in the group by clause. This is where I'm stuck...

[sumit]

use tmp table.. check my answer

[andym0908]

That's it, thank you - now to build the daily/weekly versions :)

[andym0908]

Yes this appears to be what I'm looking for, thank you.. and someone else got there just before you :)

[sumit]

its okay but using derived table and cte gives faster results ... u will realize when your table swells

[andym0908]

So if I wanted to include another field to pull the number of orders relating to a user, it seems straightforward, but needs to be part of the GROUP BY??

[sumit]

if user is created on today , it wont count it as total user because here '>' is used which consider total count from past date only

[andym0908]

Ah ok - well I need to include a field which shows active users (which is users who have made an order within 360 days of the period)...

[schlonzo]

@timus You are right. If the past users and the new users should be shown as result you just have to sum up NewCount and TotalCount to get the desired number of users.

[andym0908]

Summing up NewCount and TotalCount would give the up-to-date absolute total. By Active users I mean users who have a "last order date" of within 360 days of the current period...

[andym0908]

So if I add this line into the SELECT list: "(SELECT COUNT(*) FROM users u3 WHERE u3.lastOrderDate > DATEADD(dd,-10,u1.userCreated)) RecentOrders" - it requires this to be part of the GROUP BY clause, which then screws it up... why is that?

[schlonzo]

@user3306622 If you want to count the users which have ordered something in the last 360 days you could do something like (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) AND u2.userId IN (SELECT DISTINCT(userID) FROM Orders WHERE orderCreated >= dateadd(dd, -360, CAST(CAST(year(u1.userCreated) AS VARCHAR(4)) + RIGHT('0' + CAST(month(u1.userCreated) AS VARCHAR(2)), 2) + '01' AS DATETIME))) AS TotalCount for the inner select. Hope that gives you a hint.

[andym0908]

Thank you, that really helps. I'm just getting my head around these calculated fields NOT having to be a part of the group by clause.