CODEWITHSUNDEEP
sqlsql-server
Quacks101
Quacks101

Reputation: 293

create a variance column in MS SQL Server

hi im using Microsoft SQL Server Management Studio,

I have the below query which is doing as intended,

Select    
    EU.[Store No]
   ,EU.[Store Name]
   ,sum(case when SA.Fiscalyear = 2017 and EU.Country = 'UK' then SA.Salesexvat/SA.ExchangeRateEuro end ) AS '2017 UK'    
   ,sum(case when SA.Fiscalyear = 2016 and EU.Country = 'UK' then SA.Salesexvat/SA.ExchangeRateEuro end ) AS '2016 UK'
From 
    dbo.EUACTIVESTORES EU    
Join
    EUUKSQL01.dashboard.dbo.SalesAggregateWeek SA On SA.BranchNo = EU.[Store No]
where EU.[Upload Type]='Main' and SA.fiscalweek <=19    
group by EU.[Store No], EU.[Store Name]
order by EU.[Store No]

I need to create a new column that will show the result of: (2017 UK / 2016 UK)- 1

For each StoreNo, to show variance between the two years,

Any suggestions ?

Upvotes: 0

Views: 854

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93734

Put the code inside CTE and do the calc in CTE select query. This makes the query more readable.

;With CTE As
(
Select    
    EU.[Store No]
   ,EU.[Store Name]
   ,sum(case when SA.Fiscalyear = 2017 and EU.Country = 'UK' then SA.Salesexvat/NULLIF(SA.ExchangeRateEuro,0) end ) AS '2017 UK'    
   ,sum(case when SA.Fiscalyear = 2016 and EU.Country = 'UK' then SA.Salesexvat/NULLIF(SA.ExchangeRateEuro,0) end ) AS '2016 UK'
From 
    dbo.EUACTIVESTORES EU    
Join
    EUUKSQL01.dashboard.dbo.SalesAggregateWeek SA On SA.BranchNo = EU.[Store No]
where EU.[Upload Type]='Main' and SA.fiscalweek <=19    
group by EU.[Store No], EU.[Store Name]
)
Select *,([2017 UK] / NULLIF([2016 UK],0))- 1
order by [Store No]

Note : Used NULLIF function in denominator to avoid divided by zero error

Upvotes: 1

Jester
Jester

Reputation: 3329

Fastest solution:

SELECT *, ([2017 UK]/[2016 UK])-1 as [index]
FROM (
Select    
    EU.[Store No]
   ,EU.[Store Name]
   ,sum(case when SA.Fiscalyear = 2017 and EU.Country = 'UK' then SA.Salesexvat/SA.ExchangeRateEuro end ) AS '2017 UK'    
   ,sum(case when SA.Fiscalyear = 2016 and EU.Country = 'UK' then SA.Salesexvat/SA.ExchangeRateEuro end ) AS '2016 UK'
From 
    dbo.EUACTIVESTORES EU    
Join
    EUUKSQL01.dashboard.dbo.SalesAggregateWeek SA On SA.BranchNo = EU.[Store No]
where EU.[Upload Type]='Main' and SA.fiscalweek <=19    
group by EU.[Store No], EU.[Store Name]
)
order by [Store No]

Upvotes: 0

Shadow
Shadow

Reputation: 34255

You can repeat the 2 sums in a calculated field to achieve the expected outcome:

Select    
    EU.[Store No]
   ,EU.[Store Name]
   ,sum(case when SA.Fiscalyear = 2017 and EU.Country = 'UK' then SA.Salesexvat/SA.ExchangeRateEuro end ) AS '2017 UK'    
   ,sum(case when SA.Fiscalyear = 2016 and EU.Country = 'UK' then SA.Salesexvat/SA.ExchangeRateEuro end ) AS '2016 UK'
   ,sum(case when SA.Fiscalyear = 2017 and EU.Country = 'UK' then SA.Salesexvat/SA.ExchangeRateEuro end ) / sum(case when SA.Fiscalyear = 2016 and EU.Country = 'UK' then SA.Salesexvat/SA.ExchangeRateEuro end ) - 1 as variance
From 
    dbo.EUACTIVESTORES EU    
Join
    EUUKSQL01.dashboard.dbo.SalesAggregateWeek SA On SA.BranchNo = EU.[Store No]
where EU.[Upload Type]='Main' and SA.fiscalweek <=19    
group by EU.[Store No], EU.[Store Name]
order by EU.[Store No]

Upvotes: 1

Related Questions