CODEWITHSUNDEEP
sqlsql-serverdatabasewhere-clause
S.BM
S.BM

Reputation: 127

Apply multiple "Where" clauses in one Query SQL SERVER

I want to

I have this query : 

select 

SUM(fact.cheques) as TotalChequeAmount,
SUM(fact.revenusIntermediationToDate + fact.cheques ) as YTD ,
SUM(fact.revenusIntermediationToDate + fact.cheques ) as YTDN1,
SUM(revenusIntermediationToDate) as TotalRevenuIntermediationToDate,

acc.beCompanyCode as Codecompany,
acc.Country as Pays,
acc.Name as Name,
acc.AccountTypeSec as AccountType,
acc.Segmentation as Segmentation,
acc.City as City,
acc.OfficialGroup as OfficialGroup , 
acc.ActualYearBudget as Budget2016,
acc.SegmentationResSec as SegmentSecurities

from 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)
where YEAR(time1.Date) = 2016
group by acc.beCompanyCode, acc.Country , acc.Name , acc.AccountTypeSec , acc.Segmentation ,
acc.City , acc.OfficialGroup , acc.ActualYearBudget, acc.SegmentationResSec

the problem is that I want to apply the filter Year = 2016 for the Column YTD and one other filter Year = 2015 for the Column YTDN1.

Is there any possibility to define two where clauses for two columns in one 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

Upvotes: 0

Views: 875

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Use conditional aggregation:

select SUM(fact.cheques) as TotalChequeAmount,
       SUM(CASE 
              WHEN YEAR(time1.Date) = 2016 
                 THEN fact.revenusIntermediationToDate + fact.cheques 
              ELSE 0 
           END) as YTD ,
       SUM(CASE 
              WHEN YEAR(time1.Date) = 2015 
                 THEN fact.revenusIntermediationToDate + fact.cheques 
              ELSE 0 
           END) as YTDN1,
       SUM(revenusIntermediationToDate) as TotalRevenuIntermediationToDate,    
       acc.beCompanyCode as Codecompany,
       acc.Country as Pays,
       acc.Name as Name,
       acc.AccountTypeSec as AccountType,
       acc.Segmentation as Segmentation,
       acc.City as City,
       acc.OfficialGroup as OfficialGroup , 
       acc.ActualYearBudget as Budget2016,
       acc.SegmentationResSec as SegmentSecurities    
from 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)
where YEAR(time1.Date) IN (2015, 2016)
group by acc.beCompanyCode, acc.Country , 
         acc.Name , acc.AccountTypeSec , 
         acc.Segmentation , acc.City , 
         acc.OfficialGroup , acc.ActualYearBudget, 
         acc.SegmentationResSec

Field YTD of the above query returns the result for year 2016, wheras field YTDN1 returns the result for year 2015. The other fields with SUM return the total for both years.

Upvotes: 3

Related Questions