Another SQL with CASE when using DISTINCT

[darkgen]

Another query similar to the other where I want to SUM CASE but only count where unique;

Here's the code:

SELECT        TOP (100) PERCENT dbo.vw_ctrx_archive_session_perf.time_stamp, CONVERT(varchar(2), DATEADD(day, 0, dbo.vw_ctrx_archive_session_perf.time_stamp), 105) 
                         + '-' + CONVERT(varchar(2), DATEADD(hh, 0, dbo.vw_ctrx_archive_session_perf.time_stamp), 108) + ':00' AS Time, COUNT(DISTINCT dbo.vw_ctrx_archive_session_perf.[user]) 
                         AS [All], SUM(CASE WHEN dbo.vw_ctrx_archive_session_perf.dept_path LIKE 'All\XenApp Farms\GSK_XA65\SomeServers\UK%' THEN 1 ELSE 0 END) AS UK, 
                         SUM(CASE WHEN dbo.vw_ctrx_archive_session_perf.dept_path LIKE 'All\XenApp Farms\GSK_XA65\SomeServers\US%' THEN 1 ELSE 0 END) AS US
FROM            dbo.vw_ctrx_archive_session_perf LEFT OUTER JOIN
                         dbo.vw_ctrx_archive_published_app_event ON dbo.vw_ctrx_archive_session_perf.sessid = dbo.vw_ctrx_archive_published_app_event.sessid
WHERE        (dbo.vw_ctrx_archive_session_perf.dept_path LIKE 'All\XenApp Farms\SomeServers%') AND (dbo.vw_ctrx_archive_session_perf.time_stamp BETWEEN 
                         GETDATE() - 3 AND GETDATE() - 0.25)
GROUP BY dbo.vw_ctrx_archive_session_perf.time_stamp, CONVERT(varchar(2), DATEADD(hh, 0, dbo.vw_ctrx_archive_session_perf.time_stamp), 108)
ORDER BY dbo.vw_ctrx_archive_session_perf.time_stamp

This returns 5 columns of data (time_stamp, Time, All, UK, US), however the SUM CASES will simply return a count where the conditions are met in the WHERE clause, where as I want to only SUM based on the unique user in that grouped hour. Ergo All should be the sum of UK and US for that grouped hour.

It's another subquery but again where I'm using the COUNT (DISTINCT in this instance) it's throwing me.

Any pointers much appreciated.

Cheers, Paul.

 

[darkgen]

Thanks; I knew a subquery would be the way to go here. So I'm looking at the query as is and it currently throws up "Each GROUP BY expression must contain at least one column that is not an outer reference". I removed the erroneous ',' here also "... ELSE 'Other' END as ServerLocation," and defined the USER subquery as AllServers.

SELECT        TheTime, COUNT(USER) AS AllServers, SUM(CASE WHEN ServerLocation = 'UK' THEN 1 ELSE 0 END) AS UK, 
                         SUM(CASE WHEN ServerLocation = 'US' THEN 1 ELSE 0 END) AS US
FROM            (SELECT        CONVERT(varchar(2), DATEADD(day, 0, time_stamp), 105) + '-' + CONVERT(varchar(2), DATEADD(hh, 0, time_stamp), 108) + ':00' AS TheTime, 
                                                    USER AS AllServers, 
                                                    CASE WHEN dept_path LIKE 'All\XenApp Farms\GSK_XA65\SomeServers\UK%' THEN 'UK' WHEN dept_path LIKE 'All\XenApp Farms\GSK_XA65\SomeServers\US%' THEN
                                                     'US' ELSE 'Other' END AS ServerLocation
                          FROM            dbo.vw_ctrx_archive_session_perf
                          WHERE        (dept_path LIKE 'All\XenApp Farms\SomeServers%') AND (time_stamp BETWEEN GETDATE() - 3 AND GETDATE() - 0.25)
                          GROUP BY CONVERT(varchar(2), DATEADD(day, 0, time_stamp), 105) + '-' + CONVERT(varchar(2), DATEADD(hh, 0, time_stamp), 108) + ':00', USER, 
                                                    CASE WHEN dept_path LIKE 'All\XenApp Farms\GSK_XA65\SomeServers\UK%' THEN 'UK' WHEN dept_path LIKE 'All\XenApp Farms\GSK_XA65\SomeServers\US%' THEN
                                                     'US' ELSE 'Other' END) AS tb1
GROUP BY TheTime

Hmm, I can't see what's wrong with it.

 

[darkgen]

Hmm, Still throws the same error.

SELECT        TheTime, COUNT(USER) AS AllServers, SUM(CASE WHEN ServerLocation = 'UK' THEN 1 ELSE 0 END) AS UK, 
                         SUM(CASE WHEN ServerLocation = 'US' THEN 1 ELSE 0 END) AS US
FROM            (SELECT        CONVERT(varchar(2), DATEADD(day, 0, time_stamp), 105) + '-' + CONVERT(varchar(2), DATEADD(hh, 0, time_stamp), 108) + ':00' AS TheTime, 
                                                    USER AS AllServers, 
                                                    CASE WHEN dept_path LIKE 'All\XenApp Farms\GSK_XA65\SomeServers\UK%' THEN 'UK' WHEN dept_path LIKE 'All\XenApp Farms\GSK_XA65\SomeServers\US%' THEN
                                                     'US' ELSE 'Other' END AS ServerLocation
                          FROM            dbo.vw_ctrx_archive_session_perf
                          WHERE        (dept_path LIKE 'All\XenApp Farms\SomeServers%') AND (time_stamp BETWEEN GETDATE() - 3 AND GETDATE() - 0.25)
                          GROUP BY CONVERT(varchar(2), DATEADD(hh, 0, time_stamp), 108)', 
                                                    CASE WHEN dept_path LIKE 'All\XenApp Farms\GSK_XA65\SomeServers\UK%' THEN 'UK' WHEN dept_path LIKE 'All\XenApp Farms\GSK_XA65\SomeServers\US%' THEN
                                                     'US' ELSE 'Other' END) AS tb1
GROUP BY TheTime

So I have actually stripped out the initial group by condition as simply "CONVERT(varchar(2), DATEADD(hh, 0, time_stamp), 108)". Actually I'm a little unclear why USER would also be included in the subquery group by clause? Removed and error persists.

So looking back at the query; It is not immediately clear to me how the regional counts are based on a unique user record in that hour (time_stamp is an hour segment). That and no matter what I do I can't seem to get the query to run in sql2008r2.

Hmmm.