Apply multiple "Where" clauses in one Query SQL SERVER -
i want
i have query :
select sum(fact.cheques) totalchequeamount, sum(fact.revenusintermediationtodate + fact.cheques ) ytd , sum(fact.revenusintermediationtodate + fact.cheques ) ytdn1, sum(revenusintermediationtodate) totalrevenuintermediationtodate, acc.becompanycode codecompany, acc.country pays, acc.name name, acc.accounttypesec accounttype, acc.segmentation segmentation, acc.city city, acc.officialgroup officialgroup , acc.actualyearbudget budget2016, acc.segmentationressec segmentsecurities dbo.fact_profit_and_loss fact left outer join dbo.dim_accounts acc on ( acc.accounts_techkey = fact.fk_account) left outer join dbo.dimtemps time1 on (time1.temps_pk = fact.fk_time) year(time1.date) = 2016 group acc.becompanycode, acc.country , acc.name , acc.accounttypesec , acc.segmentation , acc.city , acc.officialgroup , acc.actualyearbudget, acc.segmentationressec
the problem want apply filter year = 2016 column ytd , 1 other filter year = 2015 column ytdn1.
is there possibility define 2 clauses 2 columns in 1 sql query ?
totalchequeamount ytd ytdn1 codecompany 0.00 6541.2826 6541.2826 2513 0.00 0.00 0.00 2541 0.00 7350.9433 7350.9433 2547
use conditional aggregation:
select sum(fact.cheques) totalchequeamount, sum(case when year(time1.date) = 2016 fact.revenusintermediationtodate + fact.cheques else 0 end) ytd , sum(case when year(time1.date) = 2015 fact.revenusintermediationtodate + fact.cheques else 0 end) ytdn1, sum(revenusintermediationtodate) totalrevenuintermediationtodate, acc.becompanycode codecompany, acc.country pays, acc.name name, acc.accounttypesec accounttype, acc.segmentation segmentation, acc.city city, acc.officialgroup officialgroup , acc.actualyearbudget budget2016, acc.segmentationressec segmentsecurities dbo.fact_profit_and_loss fact left outer join dbo.dim_accounts acc on ( acc.accounts_techkey = fact.fk_account) left outer join dbo.dimtemps time1 on (time1.temps_pk = fact.fk_time) year(time1.date) in (2015, 2016) group acc.becompanycode, acc.country , acc.name , acc.accounttypesec , acc.segmentation , acc.city , acc.officialgroup , acc.actualyearbudget, acc.segmentationressec
field ytd
of above query returns result year 2016, wheras field ytdn1
returns result year 2015. other fields sum
return total both years.
Comments
Post a Comment