CODEWITHSUNDEEP
sqlsql-serversubquerycalculated-columns
Amit Singh Parihar
Amit Singh Parihar

Reputation: 557

How to create a calculated column on the fly in SQL Server?

Here's the query that I am using.

    Select  Product, Number,
    (Select 
        CASE 
        WHEN Product = 'WATCH'
        THEN (DaysRemYr-125)
        ELSE DaysRemYr
        END  
        from report) DaysRem,
        ((dailysales_YTD*DaysRem)+(SumYTDRev)) as ProjRev
From report
Group by Product,Number

Its all messed up now, but what I am trying to achieve is a calculated column say Projected_Revenue, which is calculated by 

   DailySales_YTD*DaysRemYr

However, I want to create a new "DaysRemYr" column call it DaysRem, where the logic is 

        WHEN Product = 'WATCH'
        THEN (DaysRemYr-125)
        ELSE DaysRemYr

So that I can use this new column to calculate another column as given below,

        ((dailysales_YTD*DaysRem)+(SumYTDRev)) as ProjRev

To clear any confusion and summarize, I am trying to create a Select statement with one column being calculated by multiplying an existing column with a new calculated column which is conditional.

So, the idea is to say

           Select 
                 Product, Number, 
                 a calculated column which is based on the CASE condition as shown before lets call it DaysRem,
                (SUM(DailySales_YTD*DaysRem)+SUM(SUMYTDRev)) as ProjRev
          from report

I am wondering if its even possible.

Here's some sample data to help. 

  Product   Number  SUMYTDRev   dailySales_YTD  DaysRemYR
  WHEEL     2533    9370.58     112.90          170
  WHEEL     8213    3206.08     38.63           170
  WHEEL     6475    22770       274.34          170
  WHEEL     6475    80608.01     971.18         170
  WATCH     8213    146007.2    1759.12         170
  WATCH     1177    11500.9      138.57         170
  WATCH     1177    39006        469.95         170
  WATCH     2371    446990      5385.42         170
  WATCH     2371    69005.4      831.39         170
  WATCH      204    260008      3132.63         170
  BIKE       441    60000        722.89         170
  BIKE      6815    18300        220.48         170
  BIKE      8918    39900        480.72         170

Upvotes: 1

Views: 2811

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

You can use the case expression in calculation.

Select Product, Number, 
CASE WHEN Product = 'WATCH' THEN DaysRemYr-125 ELSE DaysRemYr END as DaysRem,
SUM(DailySales_YTD*CASE WHEN Product = 'WATCH' THEN DaysRemYr-125 ELSE DaysRemYr END)+SUM(SUMYTDRev) as ProjRev
From report  
Group by Product,Number,CASE WHEN Product = 'WATCH' THEN DaysRemYr-125 ELSE DaysRemYr END

If you don't want to specify the calculation multiple times, use a derived table with the calculation.

Select Product, Number, DaysRem,
SUM(DailySales_YTD*DaysRem)+SUM(SUMYTDRev) as ProjRev
From (select r.*,CASE WHEN Product = 'WATCH' THEN DaysRemYr-125 ELSE DaysRemYr END as DaysRem
      from report r) r
Group by Product,Number,DaysRem

Upvotes: 2

Related Questions