Query advice

• dopydb

  SSCertifiable

  Points: 6687

  More actions

  November 29, 2012 at 6:15 am

  #393700

  hi all

  i have a table in a format

  StockCode supplydate AvailableQty

  a 01/12/12 10

  a 08/12/12 2

  a 28/12/12 6

  a 5/01/13 15

  a 14/01/12 22

  what i want to do is create a view with the earliest record for each month (current and the following 11 months)

  eg

  a 01/12/12 10

  a 5/01/13 15

  a 3/02/13 2

  so far i am playing with using row numbers and partitioning by stockcode and date in ascending order

  but is just seems inefficient

  does anyone have a tip for me? 🙂

  mal

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  November 29, 2012 at 6:23 am

  #1563732

  -- sample data

  ;WITH SampleData (StockCode, supplydate, AvailableQty) AS (

  SELECT 'a', '01/12/12', 10 UNION ALL

  SELECT 'a', '08/12/12', 2 UNION ALL

  SELECT 'a', '28/12/12', 6 UNION ALL

  SELECT 'a', '5/01/13', 15 UNION ALL

  SELECT 'a', '14/01/12', 22

  )

  -- solution

  SELECT

  StockCode,

  supplydate,

  AvailableQty

  FROM (

  SELECT

  StockCode,

  supplydate,

  AvailableQty,

  FirstRowOfMonth = MIN(supplydate) OVER(PARTITION BY YEAR(supplydate), MONTH(supplydate) )

  FROM SampleData

  ) d

  WHERE supplydate = FirstRowOfMonth

  ^{“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