Rolling Months - Calculation

• koti.raavi

  SSCrazy

  Points: 2756

  More actions

  May 17, 2019 at 4:57 am

  #3644442

  Hello All,

  Below is the script and also provided required output as well. We need to split up data based on headers provided. It is coming in one excel file  (headers are fixed- no change) and loaded to SQL - then split need to be done as per output.

  One thing is we used to receive data 1 month advance - so if we are in may month then we will get data till jun ,

  Jan-jun consider in 2019 year,July-Dec consider in 2018 year.

  One more example - let say we are in March 2020-  May-Dec 2019 and Jan -April -2020

  Please let me know any questions. Thanks

  CREATE TABLE #TEMP
  (
  MID INT,
  Jan_S MONEY,
  Feb_S MONEY,
  Mar_S MONEY,
  Apr_S MONEY,
  May_S MONEY,
  Jun_S MONEY,
  Jul_S MONEY,
  Aug_S MONEY,
  Sep_S MONEY,
  Oct_S MONEY,
  Nov_S MONEY,
  Dec_S MONEY
  )
  
  
  INSERT INTO #TEMP
  SELECT 101,0,0,10,10,10,10,0,0,0,0,0,0
  UNION ALL
  SELECT 101,0,0,0,0,30,30,0,0,0,0,0,0
  
  
  SELECT * FROM #TEMP
  
  ---OUTPUT
  
  MID TotalSub   BilledMonth (MM/dd/yyyy)
  101 0          01/01/2019
  101 0          02/01/2019
  101 10         03/01/2019
  101 10         04/01/2019
  101 10         05/01/2019
  101 10         06/01/2019
  101 0          07/01/2018
  101 0          08/01/2018
  101 0          09/01/2018
  101 0          10/01/2018
  101 0          11/01/2018
  101 0          12/01/2018
  
  102 0          01/01/2019
  102 0          02/01/2019
  102 0          03/01/2019
  102 0          04/01/2019
  102 30         05/01/2019
  102 30         06/01/2019
  102 0          07/01/2018
  102 0          08/01/2018
  102 0          09/01/2018
  102 0          10/01/2018
  102 0          11/01/2018
  102 0          12/01/2018
  
  ?

   

   

  • This topic was modified 5 years, 7 months ago by  koti.raavi. Reason: Code is copied twice