Date sequence missing values

• kiril.lazarov.77

  Hall of Fame

  Points: 3742

  More actions

  April 27, 2015 at 12:18 pm

  #317798

  Good morning,

  If someone could help to write the query that produces the below results that would be appreciated. I'm not ale to join the two sets in a way so that it displays NULLs if no purchase was made on a given day for a particular product. I need NULLs or s so that it shows up correctly on my SSRS report. Thanks.

  -- declare @from DATE='2015-1-5',@to DATE='2015-1-10'

  -- test data

  ;with testdata as(

  SELECT 1 AS Id,'1/6/2014' AS Date, 21 As Amount UNION ALL

  SELECT 1 ,'1/8/2014', 25 UNION ALL

  SELECT 1 ,'1/9/2014', 30 UNION ALL

  SELECT 1 ,'1/10/2014', 60 UNION ALL

  SELECT 1 ,'1/5/2015', 3800 UNION ALL

  SELECT 1 ,'1/6/2015', 7120 UNION ALL

  SELECT 1 ,'1/7/2015', 525 UNION ALL

  SELECT 1 ,'1/8/2015', 935 UNION ALL

  SELECT 1 ,'1/9/2015', 424 UNION ALL

  SELECT 1 ,'1/10/2015', 400 UNION ALL

  SELECT 2 ,'1/6/2015', 6880 UNION ALL

  SELECT 3 ,'1/5/2015', 3500 UNION ALL

  SELECT 3 ,'1/8/2015', 500 UNION ALL

  SELECT 4 ,'1/5/2015', 125 UNION ALL

  SELECT 4 ,'1/6/2015', 150 UNION ALL

  SELECT 4 ,'1/7/2015', 175 UNION ALL

  SELECT 4 ,'1/8/2015', 275 UNION ALL

  SELECT 4 ,'1/9/2015', 175 UNION ALL

  SELECT 4 ,'1/10/2015', 300 UNION ALL

  SELECT 5 ,'1/5/2015', 50 UNION ALL

  SELECT 5 ,'1/6/2015', 90 UNION ALL

  SELECT 5 ,'1/7/2015', 100 UNION ALL

  SELECT 5 ,'1/8/2015', 110 UNION ALL

  SELECT 5 ,'1/9/2015', 249 UNION ALL

  SELECT 5 ,'1/10/2015', 100 UNION ALL

  SELECT 6 ,'1/7/2015', 250

  )

  select id, cast(date as date) as date, amount from testdata

  -- generated from numbers table based on input parameters

  ;with testdates as(

  SELECT'1/5/2015' as date UNION ALL

  SELECT'1/6/2015' UNION ALL

  SELECT'1/7/2015' UNION ALL

  SELECT'1/8/2015' UNION ALL

  SELECT'1/9/2015' UNION ALL

  SELECT'1/10/2015' UNION ALL

  SELECT'1/5/2014' UNION ALL

  SELECT'1/6/2014' UNION ALL

  SELECT'1/7/2014' UNION ALL

  SELECT'1/8/2014' UNION ALL

  SELECT'1/9/2014' UNION ALL

  SELECT'1/10/2014'

  )

  select cast(date as date) as date from testdates

  ;with expectedresults as(

  SELECT 1 AS Id,'1/5/2015' AS date, NULL As Amount UNION ALL

  SELECT 1 ,'1/6/2015', 21 UNION ALL

  SELECT 1 ,'1/7/2015', NULL UNION ALL

  SELECT 1 ,'1/8/2015', 25 UNION ALL

  SELECT 1 ,'1/9/2015', 30 UNION ALL

  SELECT 1 ,'1/10/2015', 60 UNION ALL

  SELECT 1 ,'1/5/2014', 3800 UNION ALL

  SELECT 1 ,'1/6/2014', 7120 UNION ALL

  SELECT 1 ,'1/7/2014', 525 UNION ALL

  SELECT 1 ,'1/8/2014', 935 UNION ALL

  SELECT 1 ,'1/9/2014', 424 UNION ALL

  SELECT 1 ,'1/10/2014', 400 UNION ALL

  SELECT 2 ,'1/5/2015', NULL UNION ALL

  SELECT 2 ,'1/6/2015', 6880 UNION ALL

  SELECT 2 ,'1/7/2015', NULL UNION ALL

  SELECT 2 ,'1/8/2015', NULL UNION ALL

  SELECT 2 ,'1/9/2015', NULL UNION ALL

  SELECT 2 ,'1/10/2015', NULL UNION ALL

  SELECT 2 ,'1/5/2014', NULL UNION ALL

  SELECT 2 ,'1/6/2014', NULL UNION ALL

  SELECT 2 ,'1/7/2014', NULL UNION ALL

  SELECT 2 ,'1/8/2014', NULL UNION ALL

  SELECT 2 ,'1/9/2014', NULL UNION ALL

  SELECT 2 ,'1/10/2014', NULL UNION ALL

  SELECT 3 ,'1/5/2015', 3500 UNION ALL

  SELECT 3 ,'1/6/2015', NULL UNION ALL

  SELECT 3 ,'1/7/2015', NULL UNION ALL

  SELECT 3 ,'1/8/2015', 500 UNION ALL

  SELECT 3 ,'1/9/2015', NULL UNION ALL

  SELECT 3 ,'1/10/2015', NULL UNION ALL

  SELECT 3 ,'1/5/2014', NULL UNION ALL

  SELECT 3 ,'1/6/2014', NULL UNION ALL

  SELECT 3 ,'1/7/2014', NULL UNION ALL

  SELECT 3 ,'1/8/2014', NULL UNION ALL

  SELECT 3 ,'1/9/2014', NULL UNION ALL

  SELECT 3 ,'1/10/2014', NULL UNION ALL

  SELECT 4 ,'1/5/2015', 125 UNION ALL

  SELECT 4 ,'1/6/2015', 150 UNION ALL

  SELECT 4 ,'1/7/2015', 175 UNION ALL

  SELECT 4 ,'1/8/2015', 275 UNION ALL

  SELECT 4 ,'1/9/2015', 175 UNION ALL

  SELECT 4 ,'1/10/2015', 300 UNION ALL

  SELECT 4 ,'1/5/2014', NULL UNION ALL

  SELECT 4 ,'1/6/2014', NULL UNION ALL

  SELECT 4 ,'1/7/2014', NULL UNION ALL

  SELECT 4 ,'1/8/2014', NULL UNION ALL

  SELECT 4 ,'1/9/2014', NULL UNION ALL

  SELECT 4 ,'1/10/2014', NULL UNION ALL

  SELECT 5 ,'1/5/2015', 50 UNION ALL

  SELECT 5 ,'1/6/2015', 90 UNION ALL

  SELECT 5 ,'1/7/2015', 100 UNION ALL

  SELECT 5 ,'1/8/2015', 110 UNION ALL

  SELECT 5 ,'1/9/2015', 249 UNION ALL

  SELECT 5 ,'1/10/2015', 100 UNION ALL

  SELECT 5 ,'1/5/2014', NULL UNION ALL

  SELECT 5 ,'1/6/2014', NULL UNION ALL

  SELECT 5 ,'1/7/2014', NULL UNION ALL

  SELECT 5 ,'1/8/2014', NULL UNION ALL

  SELECT 5 ,'1/9/2014', NULL UNION ALL

  SELECT 5 ,'1/10/2014', NULL UNION ALL

  SELECT 6 ,'1/5/2015', NULL UNION ALL

  SELECT 6 ,'1/6/2015', NULL UNION ALL

  SELECT 6 ,'1/7/2015', 250 UNION ALL

  SELECT 6 ,'1/8/2015', NULL UNION ALL

  SELECT 6 ,'1/9/2015', NULL UNION ALL

  SELECT 6 ,'1/10/2015', NULL UNION ALL

  SELECT 6 ,'1/5/2014', NULL UNION ALL

  SELECT 6 ,'1/6/2014', NULL UNION ALL

  SELECT 6 ,'1/7/2014', NULL UNION ALL

  SELECT 6 ,'1/8/2014', NULL UNION ALL

  SELECT 6 ,'1/9/2014', NULL UNION ALL

  SELECT 6 ,'1/10/2014', NULL

  )

  select id, cast(date as date) as date, amount

  from expectedresults

• amurjani

  SSC Eights!

  Points: 862

  More actions

  April 27, 2015 at 1:23 pm

  #1793811

  Here is one solution. It solves your problem:

  create table #testdata(MyId int, DailyDt date, MyAmount money)

  create table #timeseries(SeriesDate date)

  declare @startDt date, @EndDt date, @workDt date

  select @startDt = '01/05/2014', @EndDt = '01/10/2015'

  select @workDt = @startDt

  while @workDt between @startDt and @EndDt

  begin

  insert #timeseries(SeriesDate) select @workDt

  select @workDt = dateadd(dd,1,@workDt)

  end

  insert #testdata(MyId,DailyDt,MyAmount) SELECT 1 AS Id,'1/6/2014' AS Date, 21 As Amount UNION ALL

  SELECT 1 ,'1/8/2014', 25 UNION ALL

  SELECT 1 ,'1/9/2014', 30 UNION ALL

  SELECT 1 ,'1/10/2014', 60 UNION ALL

  SELECT 1 ,'1/5/2015', 3800 UNION ALL

  SELECT 1 ,'1/6/2015', 7120 UNION ALL

  SELECT 1 ,'1/7/2015', 525 UNION ALL

  SELECT 1 ,'1/8/2015', 935 UNION ALL

  SELECT 1 ,'1/9/2015', 424 UNION ALL

  SELECT 1 ,'1/10/2015', 400 UNION ALL

  SELECT 2 ,'1/6/2015', 6880 UNION ALL

  SELECT 3 ,'1/5/2015', 3500 UNION ALL

  SELECT 3 ,'1/8/2015', 500 UNION ALL

  SELECT 4 ,'1/5/2015', 125 UNION ALL

  SELECT 4 ,'1/6/2015', 150 UNION ALL

  SELECT 4 ,'1/7/2015', 175 UNION ALL

  SELECT 4 ,'1/8/2015', 275 UNION ALL

  SELECT 4 ,'1/9/2015', 175 UNION ALL

  SELECT 4 ,'1/10/2015', 300 UNION ALL

  SELECT 5 ,'1/5/2015', 50 UNION ALL

  SELECT 5 ,'1/6/2015', 90 UNION ALL

  SELECT 5 ,'1/7/2015', 100 UNION ALL

  SELECT 5 ,'1/8/2015', 110 UNION ALL

  SELECT 5 ,'1/9/2015', 249 UNION ALL

  SELECT 5 ,'1/10/2015', 100 UNION ALL

  SELECT 6 ,'1/7/2015', 250

  select ISNULL(a.MyId,9999) 'MyId', isnull(a.DailyDt,b.SeriesDate) 'Date',a.MyAmount

  from #testdata a

  RIGHT JOIN #timeSeries b on a.DailyDt = b.SeriesDate

  order by 2

• Y.B.

  SSChampion

  Points: 11572

  More actions

  April 27, 2015 at 2:02 pm

  #1793821

  This is how I did it

  DECLARE @testdata TABLE (id int, date datetime, Amount int);

  DECLARE @testdates TABLE (date datetime);

  INSERT INTO @testdata (Id, Date, Amount)

  SELECT 1, '1/6/2014' , 21 UNION ALL

  SELECT 1 ,'1/8/2014', 25 UNION ALL

  SELECT 1 ,'1/9/2014', 30 UNION ALL

  SELECT 1 ,'1/10/2014', 60 UNION ALL

  SELECT 1 ,'1/5/2015', 3800 UNION ALL

  SELECT 1 ,'1/6/2015', 7120 UNION ALL

  SELECT 1 ,'1/7/2015', 525 UNION ALL

  SELECT 1 ,'1/8/2015', 935 UNION ALL

  SELECT 1 ,'1/9/2015', 424 UNION ALL

  SELECT 1 ,'1/10/2015', 400 UNION ALL

  SELECT 2 ,'1/6/2015', 6880 UNION ALL

  SELECT 3 ,'1/5/2015', 3500 UNION ALL

  SELECT 3 ,'1/8/2015', 500 UNION ALL

  SELECT 4 ,'1/5/2015', 125 UNION ALL

  SELECT 4 ,'1/6/2015', 150 UNION ALL

  SELECT 4 ,'1/7/2015', 175 UNION ALL

  SELECT 4 ,'1/8/2015', 275 UNION ALL

  SELECT 4 ,'1/9/2015', 175 UNION ALL

  SELECT 4 ,'1/10/2015', 300 UNION ALL

  SELECT 5 ,'1/5/2015', 50 UNION ALL

  SELECT 5 ,'1/6/2015', 90 UNION ALL

  SELECT 5 ,'1/7/2015', 100 UNION ALL

  SELECT 5 ,'1/8/2015', 110 UNION ALL

  SELECT 5 ,'1/9/2015', 249 UNION ALL

  SELECT 5 ,'1/10/2015', 100 UNION ALL

  SELECT 6 ,'1/7/2015', 250

  -- generated from numbers table based on input parameters

  INSERT INTO @testdates (date)

  SELECT'1/5/2015' UNION ALL

  SELECT'1/6/2015' UNION ALL

  SELECT'1/7/2015' UNION ALL

  SELECT'1/8/2015' UNION ALL

  SELECT'1/9/2015' UNION ALL

  SELECT'1/10/2015' UNION ALL

  SELECT'1/5/2014' UNION ALL

  SELECT'1/6/2014' UNION ALL

  SELECT'1/7/2014' UNION ALL

  SELECT'1/8/2014' UNION ALL

  SELECT'1/9/2014' UNION ALL

  SELECT'1/10/2014'

  ;WITH newtestdates (id, date)

  AS (

  SELECT DISTINCT data.id, td.date

  FROM @testdata data

  CROSS JOIN @testdates td

  )

  SELECT td.id, td.date, data.amount

  FROM newtestdates td LEFT JOIN @testdata data ON td.date = data.date AND td.id = data.id

  ORDER BY td.id

  ---

  SELECT quote FROM brain WHERE original = 1
  0 rows returned