Count numbers per month with increasing value

• bjorn.gabrielsson

  SSC Enthusiast

  Points: 122

  More actions

  January 30, 2018 at 2:01 am

  #343167

  Hi.
   I have a table with date and a increasing value.
  There is not allways one reading per month, sometime there is two readings one in the beginning and one i near the end of the month.
   I wounld like to get a tabel with numbers per year/month.
  The value is the number added between 1st and last each month.
  Input looks like this:

  2016-06-01 3574
  2016-06-30 4178
  2016-09-01 4941
  2016-09-30 5569
  2016-11-03 6323
  2016-12-05 6967
  2017-01-04 7484
  2017-02-01 8126
  2017-03-06 8928
  2017-04-04 9699
  2017-05-02 10331
  2017-05-31 11005
  2017-07-04 11588

• Thomas Rushton

  SSC-Insane

  Points: 22649

  More actions

  January 30, 2018 at 2:05 am

  #1977812

  From that input data, what output are you expecting / wanting?

  Thomas Rushton
  blog: https://thelonedba.wordpress.com

• bjorn.gabrielsson

  SSC Enthusiast

  Points: 122

  More actions

  January 30, 2018 at 2:15 am

  #1977813

  I would like an output like....

  2017 07 800
  2017 08 500
  2017 09 600

  (The numbers is just made up...)
  I guess i need to split on number of days in between dates and then by the value added in between dates, or something like that.

• reinhardcilliers

  SSC Eights!

  Points: 891

  More actions

  January 30, 2018 at 2:59 am

  #1977817

  Is this what you are looking for:

  CREATE TABLE #Test(TestDate DATETIME, TestNumber INT)

  INSERT INTO #Test(TestDate,TestNumber)

  VALUES ('2016-06-01', 3574),
       ('2016-06-30', 4178) ,
       ('2016-09-01', 4941) ,
       ('2016-09-30', 5569) ,
       ('2016-11-03', 6323) ,
       ('2016-12-05', 6967) ,
       ('2017-01-04', 7484) ,
       ('2017-02-01', 8126) ,
       ('2017-03-06', 8928) ,
       ('2017-04-04', 9699) ,
       ('2017-05-02', 10331),
       ('2017-05-31', 11005),
       ('2017-07-04', 11588)
    
SELECT TestDate
  , TestNumber
  , TestNumber - LAG(TestNumber, 1, NULL) OVER(ORDER BY TestDate) AS Diff_In_Months
FROM #Test

  DROP TABLE #Test

  With output:

  

• bjorn.gabrielsson

  SSC Enthusiast

  Points: 122

  More actions

  January 30, 2018 at 3:19 am

  #1977819

  Nice,  i think i can work with this..