SQL Server query

Question

Hi,

I have a SQL Server DB containing a registrations table that I need to plot on a graph over time. The issue is that I need to break this down by where the user registered from (e.g. website, wap site, or a mobile application).

the resulting output data should look like this...

[date] [num_reg_website] [num_reg_wap_site] [num_reg_mobileapp]
1 FEB 2010,24,35,64
2 FEB 2010,23,85,48
3 FEB 2010,29,37,79

etc...

The source table is as follows...

UUID(int), signupdate(datetime), requestsource(varchar(50))

some smple data in this table looks like this...

1001,2010-02-2:00:12:12,'website'
1002,2010-02-2:00:10:17,'app'
1003,2010-02-3:00:14:19,'website'
1004,2010-02-4:00:16:18,'wap'
1005,2010-02-4:00:18:16,'website'

Running the following query returns one data column 'total registrations' for the website registrations but I'm not sure how to do this for multiple columns unfortunatly....

select  CAST(FLOOR(CAST([signupdate]AS FLOAT ))AS DATETIME) as [signupdate], count(UUID)  as 'total registrations' FROM [UserRegistrationRequests] WHERE requestsource = 'website'
group by CAST(FLOOR(CAST([signupdate]AS FLOAT ))AS DATETIME)

Answer 1

Try using PIVOT

Answer 2

SELECT  CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME),
        SUM(CASE WHEN requestsource = 'website' THEN 1 ELSE 0 END),
        SUM(CASE WHEN requestsource = 'wap' THEN 1 ELSE 0 END),
        SUM(CASE WHEN requestsource = 'app' THEN 1 ELSE 0 END)
FROM    mytable
GROUP BY
        CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

Answer 3

It sounds like there are two problems you are having. The first is how to convert the DateTime into a Date only value. Since you did not specify which version of SQL Server, I'm assuming it is SQL Server 2005 and prior. For that I typically use the DateDiff trick where I find the number of days from day zero and then cast that as a DateTime. The other trick is creating a crosstab like output.

Select Cast(DateDiff(d, 0, [signupdate]) As DateTime) As [Date]
    , Sum( Case When requestsource = 'website' Then 1 Else 0 End ) As num_reg_website
    , Sum( Case When requestsource = 'wap' Then 1 Else 0 End ) As num_reg_wap_site
    , Sum( Case When requestsource = 'mobileapp' Then 1 Else 0 End ) As num_reg_mobileapp
From [UserRegistrationRequests]
Group By Cast(DateDiff(d, 0, [signupdate]) As DateTime)

If you are using SQL Server 2008, you can take advantage of its Date data type and do something like:

Select Cast([signupdate]) As Date) As [Date]
    , Sum( Case When requestsource = 'website' Then 1 Else 0 End ) As num_reg_website
    , Sum( Case When requestsource = 'wap' Then 1 Else 0 End ) As num_reg_wap_site
    , Sum( Case When requestsource = 'mobileapp' Then 1 Else 0 End ) As num_reg_mobileapp
From [UserRegistrationRequests]
Group By Cast([signupdate]) As Date)

Answer 4

You could GROUP BY the two values; first, the date (as you're doing, although you could use a CONVERT(varchar(50), signupdate, 102) or some other style, to avoid all that flooring :) ), and then the mode of signup. GROUP BY can take multiple parameters!

So, something like:

SELECT requestsource, count(*), CONVERT(varchar(50), signupdate, 102)
FROM userregistrationrequest
GROUP BY CONVERT(varchar(50), signupdate, 102), requestsource

... with your own style rather than 102 to suit.

See here for a list of suitable CONVERT styles.