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.

 

[Dolph]

If the attached query is all the information you want, I'd replace the table in the from clause of the query with a subquery to get the user and server path by hour and sum it together afterwards. Should be quicker than using distinct as well.

I'm on my tablet at the moment so writing code sucks, I'll post a full solution later when I am at a proper pc.

 

[Dolph]

Ok, on proper PC now. I would use something like this:

Select
    tb1.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,  
        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) tb1
Group by
    tb1.TheTime

I've dropped out what looks to be an extraneous join (it's not used in the select or where clauses in the code provided), and also changed the column names of 'Time' and 'All', as they are both reserved keywords in either TSQL or ODBC and hence probably best avoided. I've also removed the 'Top 100 Percent' as it's unnecessary (Top 100% just returns everything, but with a small amount of overhead to process the TOP statement.

It works by creating a list of by hour and user showing the server location as UK or US (which accounts for users who may appear on both locations in a given period, and also multiple servers for each group) within the subquery. The main query then sums up the values based on the distinct list returned by the subquery.

 

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

 

[Dolph]

I can, I left the literal constant in the group by clause in the subquery. Sorry, my bad.

Remove the + ':00' from the group by and it should be good.

 

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