CODEWITHSUNDEEP
sqlsql-server
dot
dot

Reputation: 15670

How to roll up SQL data

I have the following sql statement:

 SELECT
    Distinct UserId, A.TId, Branch ,Operation, FormId, A.ReportRefreshDate, UserType=Case When U.Designer=1 Then 'Designer' When U.Respondent=1 Then 'Respondent' END
FROM Activities A
INNER JOIN Users 
    ON LOWER(Users.UserPName) = LOWER(A.UserId)
INNER JOIN ActiviesUserTypes U
    ON Operation = U.ActionName
WHERE 
A.TId=4
AND
Users.Branch='LLA'
AND
(U.Designer=1 OR U.Respondent=1)
AND
A.ReportRefreshDate between DateAdd(DD,-200,GETDATE() ) and GETDATE()

This returns the following dummy / sample data:

UserId  TId Branch  Operation      FormId   ReportRefreshDate       UserType
jja     4   LLA     CreateResponse x21      2021-08-25 07:18:06.000 Respondent
asdf    4   LLA     ExportForm     f22      2021-08-25 07:18:06.000 Designer
38fa    4   LLA     ViewForm       2ay      2021-08-25 07:18:06.000 Designer
arb     4   LLA     CreateResponse x21      2021-08-29 07:18:06.000 Respondent
rtbs    4   LLA     CreateResponse f22      2021-08-29 07:18:06.000 Respondent
vfaa    4   LLA     ViewForm       2ay      2021-08-29 07:18:06.000 Designer

This is a good start. But ultimately what I need to get back is totals for each user type per day. so something like this:

ReportRefreshDate.       TotalDesigners.  TotalRespondents
2021-08-25 07:18:06.000  2                1
2021-08-29 07:18:06.000  1                2

Can someone point me in the right direction? Thanks!

Upvotes: 0

Views: 78

Answers (2)

Ali Fidanli
Ali Fidanli

Reputation: 1372

I used Pivot. Update: I removed Distinct on your query after Charlieface feedback.

  SELECT 'count' AS user_type,   
      [Respondent], [Designer] 
    FROM  
    ( 
       SELECT
     UserId, A.TId, Branch ,Operation, FormId, A.ReportRefreshDate, UserType=Case When U.Designer=1 Then 'Designer' When U.Respondent=1 Then 'Respondent' END
FROM Activities A
INNER JOIN Users 
    ON LOWER(Users.UserPName) = LOWER(A.UserId)
INNER JOIN ActiviesUserTypes U
    ON Operation = U.ActionName
WHERE 
A.TId=4
AND
Users.Branch='LLA'
AND
(U.Designer=1 OR U.Respondent=1)
AND
A.ReportRefreshDate between DateAdd(DD,-200,GETDATE() ) and GETDATE()
    
    ) AS SourceTable  
    PIVOT  
    (  
      COUNT(UserType)  
      FOR UserType IN ([Respondent], [Designer])  
    ) AS PivotTable;

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can do :

with cte as (
    <query here>
)
select ReportRefreshDate,
       sum(case when UserType = 'Designer' then 1 else 0 end) as TotalDesigner,
       sum(case when UserType = 'Respondent' then 1 else 0 end) as TotalRespondents
from cte c
group by ReportRefreshDate;

Upvotes: 1

Related Questions