CODEWITHSUNDEEP
sqlsql-server
Sharmistha Sinha
Sharmistha Sinha

Reputation: 335

Complex Group BY query in SQL Server

Below is my SQL query. I need to change it so that I can get the sum or all workload per wkld_date wise. This code works perfectly when I give condition ssawd.wkld_date = 201803 but I want it to return sum of workload grouped per wkld_date. Please help. Thanks a lot.

select 
    ISNULL(SUM(ssawd.WORKLOAD), 0) w3  
from   
    AGMT_MAN_SCH_SS ssams 
inner join 
    AGMT_WKLD_SUMMARY_SS ssaws on ssams.SCHEDULE_ID = ssaws.SCHEDULE_ID 
                               and ssams.SNAP_DATE = ssaws.SNAP_DATE 
inner join 
    AGMT_WKLD_DETAIL_SS ssawd on ssaws.summary_id = ssawd.summary_id 
                              and ssaws.SNAP_DATE = ssawd.SNAP_DATE 
inner join 
    AGREEMENT ag on ssams.agmt_id = ag.AGMT_ID 
where 
    ssaws.STAFF_ID = 4331
    and ssawd.wkld_date = 201803 
    and ((ag.AGMT_ACTCPLTDATE is not null 
          and ag.AGMT_ACTCPLTDATE > '2018-01-01') 
          or ag.AGMT_ACTCPLTDATE is null) 
    and ((ag.AGMT_TERMDATE is not null 
          and ag.AGMT_TERMDATE > '2018-01-01') 
          or ag.AGMT_TERMDATE is null) 
    and dateadd(year, 10, ag.AGMT_ACTCOMMENTDATE) > '2018-01-01' 
    and UPPER(ssaws.POST_TYPE)  NOT LIKE UPPER('Unnamed')           
    and ssams.agmt_id != 140 
    and (ssams.INTM_SNAP_DATE in (select max(INTM_SNAP_DATE) 
                                  from AGMT_MAN_SCH ams 
                                  where ams.AGMT_ID = ag.AGMT_ID 
                                    and ams.SNAP_TYPE = 'I')  
         or ssams.LATEST_SNAP_DATE in (select max(LATEST_SNAP_DATE) 
                                       from  AGMT_MAN_SCH ams 
                                       where ams.AGMT_ID = ag.AGMT_ID 
                                         and ams.SNAP_TYPE = 'F')
        )

Upvotes: 0

Views: 43

Answers (1)

Anton Petrosyuk
Anton Petrosyuk

Reputation: 51

Sounds like you want a simple GROUP BY statement. In your case it will look like this:

SELECT ssawd.wkld_date, sum(ssawd.WORKLOAD) w3  
from AGMT_MAN_SCH_SS ssams 
inner join AGMT_WKLD_SUMMARY_SS ssaws on ssams.SCHEDULE_ID = ssaws.SCHEDULE_ID and ssams.SNAP_DATE = ssaws.SNAP_DATE 
inner join AGMT_WKLD_DETAIL_SS ssawd on  ssaws.summary_id = ssawd.summary_id and ssaws.SNAP_DATE = ssawd.SNAP_DATE 
inner join AGREEMENT ag on ssams.agmt_id = ag.AGMT_ID 
where ssaws.STAFF_ID = 4331
and  ((ag.AGMT_ACTCPLTDATE is not null and  ag.AGMT_ACTCPLTDATE > '2018-01-01') or ag.AGMT_ACTCPLTDATE is null) 
and  ((ag.AGMT_TERMDATE is not null and ag.AGMT_TERMDATE > '2018-01-01') or ag.AGMT_TERMDATE is null) 
and  dateadd(year, 10, ag.AGMT_ACTCOMMENTDATE) > '2018-01-01' 
and UPPER(ssaws.POST_TYPE)  NOT LIKE UPPER('Unnamed')           
and ssams.agmt_id != 140 
and (ssams.INTM_SNAP_DATE in 
            (select max(INTM_SNAP_DATE) from AGMT_MAN_SCH ams where ams.AGMT_ID = ag.AGMT_ID and ams.SNAP_TYPE = 'I' )  
    or ssams.LATEST_SNAP_DATE in 
            (select max(LATEST_SNAP_DATE) from AGMT_MAN_SCH ams where ams.AGMT_ID = ag.AGMT_ID and ams.SNAP_TYPE = 'F'))
GROUP BY  ssawd.wkld_date

Note that I've removed ssawd.wkld_date from the WHERE statement, added it to the returned fields and using it in the GROUP BY statement at the very end.

Upvotes: 2

Related Questions