T-SQL Calculation

• PSB

  SSChampion

  Points: 13195

  More actions

  May 8, 2014 at 8:24 am

  #307150

  Hi,

  I have a table where I need to calculate Trend based on Week Type and Unit .

  CREATE TABLE #REport

  (

  TYPE VARCHAR(20),

  Unit VARCHAR(20),

  Forecast INT,

  Week INT,

  Trend NUMERIC(9,2)

  )

  INSERT INTO #REport( TYPE, Unit, Forecast, Week, Trend )

  SELECT 'Net Revenue','XXX',0,1,0

  UNION

  SELECT 'Net Revenue','XXX',0,2,0

  UNION

  SELECT 'Net Revenue','XXX',0,3,0

  UNION

  SELECT 'Net Revenue','XXX',0,4,0

  UNION

  SELECT 'Net Revenue','XXX',105000,5,0

  UNION

  SELECT 'Invest','XXX',0,1,0

  UNION

  SELECT 'Invest','XXX',0,2,0

  UNION

  SELECT 'Invest','XXX',0,3,0

  UNION

  SELECT 'Invest','XXX',0,4,0

  UNION

  SELECT 'Invest','XXX', 45951 ,5,0

  SELECT * FROM #REport

  DROP TABLE #REport

  --FOR every TYPE AND Unit, the trend should be calculated AS follows :

  --FOR Week 1 OF TYPE = 'Invest' and Unit = 'XXX'

  --Trend = 45951/MAX week number = 45951/5 = 9190.2

  --FOR Week 2 OF TYPE = 'Invest' and Unit = 'XXX'

  --Trend = 9190.2 + 45951/5 = 18380.4-- Trend of Week 1 + 45951/5

  --FOR Week 3 OF TYPE = 'Invest' and Unit = 'XXX'

  --Trend = 18380.4 + 45951/5 = 27570.6-- Trend of Week 2 + 45951/5

  --FOR Week 4 OF TYPE = 'Invest' and Unit = 'XXX'

  --Trend = 27570.6 + 45951/5 = 36760.8-- Trend of Week 3 + 45951/5

  --FOR Week 5 OF TYPE = 'Invest' and Unit = 'XXX'

  --Trend = 36760.8 + 45951/5 = 45951-- Trend of Week 4 + 45951/5

  Thanks,

  PSB

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  May 8, 2014 at 8:41 am

  #1711893

  just to be sure.....can you please provide the actual results you require based on your sample data......and not the calcs.

  ta.

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• PSB

  SSChampion

  Points: 13195

  More actions

  May 8, 2014 at 8:50 am

  #1711897

  Desired result ..

  SELECT 'Invest' AS TYPE,'XXX' AS Unit,0 AS ForeCast,1 AS Week,9190.2 AS Trend

  UNION

  SELECT 'Invest' AS Type,'XXX' AS Unit,0 AS ForeCast,2 AS Week,18380.4 AS Trend

  UNION

  SELECT 'Invest' AS TYPE,'XXX' AS Unit,0 AS ForeCast,3 AS Week,27570.6 AS Trend

  UNION

  SELECT 'Invest' AS Type,'XXX' AS Unit,0 AS ForeCast,4 AS Week,36760.8 AS Trend

  UNION

  SELECT 'Invest' AS Type,'XXX' AS Unit, 45951 AS ForeCast,5 AS Week,45951 AS Trend

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  May 8, 2014 at 8:50 am

  #1711900

  Otherwise you will get answers like this:

  SELECT *, Trend = ([Week]/5.0) * MAX(Forecast) OVER(PARTITION BY [TYPE])

  FROM #REport

  Which exactly meets your requirements (apart from the trivial matter of rounding) but is almost certainly incorrect.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  May 8, 2014 at 9:04 am

  #1711905

  ChrisM@Work (5/8/2014)

  ---

  Otherwise you will get answers like this:

  SELECT *, Trend = ([Week]/5.0) * MAX(Forecast) OVER(PARTITION BY [TYPE])

  FROM #REport

  Which exactly meets your requirements (apart from the trivial matter of rounding) but is almost certainly incorrect.

  Chris ...your mind reading skills are quite extraordinary 🙂

  but have a suspicion that OP has oversimplified........we can wait and see

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  May 8, 2014 at 9:22 am

  #1711923

  J Livingston SQL (5/8/2014)

  ---

  ChrisM@Work (5/8/2014)

  ---

  Otherwise you will get answers like this:

  SELECT *, Trend = ([Week]/5.0) * MAX(Forecast) OVER(PARTITION BY [TYPE])

  FROM #REport

  Which exactly meets your requirements (apart from the trivial matter of rounding) but is almost certainly incorrect.

  Chris ...your mind reading skills are quite extraordinary 🙂

  but have a suspicion that OP has oversimplified........we can wait and see

  J Livingstone - you give me way too much credit for a Wild Guess 😀

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• PSB

  SSChampion

  Points: 13195

  More actions

  May 8, 2014 at 11:17 am

  #1711972

  Thanks for the solution! it works perfectly .