Query that generates values by date range

• legeboka

  Mr or Mrs. 500

  Points: 588

  More actions

  December 12, 2014 at 7:46 am

  #292891

  Hi,

  I have a table that stores Terminal ID, Product Name, Cost and Effective Date. I need to generate query that will produce record set with start effective date and end date based on terminal and product. Table has over million records. In example below you could see table structure/data and desired outcome.

  SELECT fmc_terminal, fmc_date = CAST(d. fmc_date AS DATETIME)

  ,d.fmc_prodlnk, d. fmc_cost

  INTO #TestTable

  FROM (

  SELECT 1, '2014-12-03 00:04:00.000','A', 2.25 UNION ALL

  SELECT 1, '2014-12-03 11:33:00.000','A', 2.26 UNION ALL

  SELECT 1, '2014-12-04 00:04:00.000','A', 2.27 UNION ALL

  SELECT 1, '2014-12-05 00:04:00.000','A', 2.28 UNION ALL

  SELECT 1, '2014-12-05 11:04:00.000','A', 2.29 UNION ALL

  SELECT 2, '2014-12-01 15:04:00.000','A', 2.20 UNION ALL

  SELECT 2, '2014-12-02 18:08:00.000','A', 2.31 UNION ALL

  SELECT 2, '2014-12-03 13:13:00.000','A', 2.39 UNION ALL

  SELECT 1, '2014-12-01 00:04:00.000','B', 5.54 UNION ALL

  SELECT 1, '2014-12-01 12:31:00.000','B', 5.55 UNION ALL

  SELECT 1, '2014-12-02 14:04:00.000','B', 5.64 UNION ALL

  SELECT 2, '2014-12-03 12:12:00.000','B', 5.51 UNION ALL

  SELECT 1, '2014-12-01 00:04:00.000','C', 7.23 UNION ALL

  SELECT 1, '2014-12-04 00:04:00.000','C', 6.22 UNION ALL

  SELECT 1, '2014-12-05 00:04:00.000','C', 6.23 UNION ALL

  SELECT 1, '2014-12-06 00:04:00.000','C', 6.24 UNION ALL

  SELECT 1, '2014-12-12 10:04:00.000','C', 3.99

  ) d ( fmc_terminal, fmc_date, fmc_prodlnk, fmc_cost)

  ;

  Expected outcome

  fmc_terminal fmc_prodlnk fmc_cost StartDate EndDate

  1 A 2.25 NULL 2014-12-03 00:04:00.000

  1 A 2.26 2014-12-03 00:04:00.000 2014-12-03 11:33:00.000

  1 A 2.27 2014-12-03 11:33:00.000 2014-12-04 00:04:00.000

  1 A 2.28 2014-12-04 00:04:00.000 2014-12-05 00:04:00.000

  1 A 2.29 2014-12-04 00:04:00.000 2014-12-05 11:04:00.000

  2 A 2.20 NULL 2014-12-01 15:04:00.000

  2 A 2.31 2014-12-01 15:04:00.000 2014-12-02 18:08:00.000

  2 A 2.39 2014-12-02 18:08:00.000 2014-12-03 13:13:00.000

  1 B 5.54 NULL 2014-12-01 00:04:00.000

  And so on….

• g.britton

  SSChampion

  Points: 13863

  More actions

  December 12, 2014 at 8:50 am

  #1764456

  SELECT fmc_terminal

  , fmc_prodlnk

  , fmc_cost

  , lag(fmc_date)OVER(PARTITION BY fmc_terminal

  , fmc_prodlnk

  ORDER BY fmc_date) AS StartDate

  , fmc_date AS EndDate

  FROM #TestTable

  ORDER BY fmc_terminal, fmc_prodlnk;

  Gerald Britton, Pluralsight courses

• legeboka

  Mr or Mrs. 500

  Points: 588

  More actions

  December 12, 2014 at 8:53 am

  #1764459

  I have sql 2008, LAG wouldn't work

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  December 12, 2014 at 9:40 am

  #1764483

  This should work for 2008.

  WITH cteRows AS(

  SELECT fmc_terminal,

  fmc_date,

  fmc_prodlnk,

  fmc_cost,

  ROW_NUMBER() OVER(PARTITION BY fmc_terminal, fmc_prodlnk ORDER BY fmc_date) rn

  FROM #TestTable

  )

  SELECT a.fmc_terminal,

  a.fmc_prodlnk,

  a.fmc_cost,

  b.fmc_date StartDate,

  a.fmc_date EndDate

  FROM cteRows a

  LEFT

  JOIN cteRows b ON a.fmc_terminal = b.fmc_terminal

  AND a.fmc_prodlnk = b.fmc_prodlnk

  AND a.rn = b.rn + 1

  ORDER BY fmc_prodlnk,

  fmc_terminal

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2