get missing dates differencedays and fill previous dates data in sql server

• baladevu

  SSC Veteran

  Points: 298

  More actions

  May 14, 2017 at 3:25 am

  #330236

  How to fill missing dates related data with previous date related data and get days differnce b/w missing dates with previous data in SQL Server

  CREATE TABLE [dbo].[dateinfo](
      [date] [date] NULL
  )
  GO
  INSERT [dbo].[dateinfo] ([date]) VALUES (CAST(N'2016-06-01' AS Date))
  GO
  INSERT [dbo].[dateinfo] ([date]) VALUES (CAST(N'2016-06-02' AS Date))
  GO
  INSERT [dbo].[dateinfo] ([date]) VALUES (CAST(N'2016-06-03' AS Date))
  GO
  INSERT [dbo].[dateinfo] ([date]) VALUES (CAST(N'2016-06-04' AS Date))
  GO
  INSERT [dbo].[dateinfo] ([date]) VALUES (CAST(N'2016-06-05' AS Date))
  GO
  INSERT [dbo].[dateinfo] ([date]) VALUES (CAST(N'2016-06-06' AS Date))
  GO
  INSERT [dbo].[dateinfo] ([date]) VALUES (CAST(N'2016-06-07' AS Date))
  GO
  INSERT [dbo].[dateinfo] ([date]) VALUES (CAST(N'2016-06-08' AS Date))
  GO
  INSERT [dbo].[dateinfo] ([date]) VALUES (CAST(N'2016-06-09' AS Date))
  GO
  INSERT [dbo].[dateinfo] ([date]) VALUES (CAST(N'2016-06-10' AS Date))
  GO
  INSERT [dbo].[dateinfo] ([date]) VALUES (CAST(N'2016-06-11' AS Date))
  go
  CREATE TABLE [dbo].[orders](
      [orderid] [int] NULL,
      [orderdate] [date] NULL,
      [cost] [money] NULL
  )
  GO
  INSERT [dbo].[orders] ([orderid], [orderdate], [cost]) VALUES (10, CAST(N'2016-06-01' AS Date), 100.0000)
  GO
  INSERT [dbo].[orders] ([orderid], [orderdate], [cost]) VALUES (11, CAST(N'2016-06-02' AS Date), 200.0000)
  GO
  INSERT [dbo].[orders] ([orderid], [orderdate], [cost]) VALUES (12, CAST(N'2016-06-05' AS Date), 300.0000)
  GO
  INSERT [dbo].[orders] ([orderid], [orderdate], [cost]) VALUES (13, CAST(N'2016-06-09' AS Date), 400.0000)
  GO
  INSERT [dbo].[orders] ([orderid], [orderdate], [cost]) VALUES (14, CAST(N'2016-06-02' AS Date), 700.0000)
  GO
  INSERT [dbo].[orders] ([orderid], [orderdate], [cost]) VALUES (15, CAST(N'2016-06-09' AS Date), 700.0000)
  GO

  based on above data i want data like below
  date       |orderid|missingdays    cost
  2016-06-01  |10    |0      |100.00
  2016-06-02    |11    |0      |200.00
  2016-06-02    |14    |0      |700.00
  2016-06-03    |11    |1      |200.00
  2016-06-03    |14    |1      |700.00
  2016-06-04    |11    |2      |200.00
  2016-06-04    |14    |2      |700.00
  2016-06-05    |12    |0      |300.00
  2016-06-06    |12    |1      |300.00
  2016-06-07    |12    |2      |300.00
  2016-06-08    |12    |3      |300.00
  2016-06-09    |13    |0      |400.00
  2016-06-09    |15    |0      |700.00
  I treid like below
  SELECT d.date,
     o.orderid,
     datediff(DAY, o.orderdate, d.date) AS missingdays,
     o.cost
  FROM dateinfo d
  INNER JOIN
  (SELECT o.orderid,
      o.orderdate,
      o.cost
   FROM orders o) o ON o.orderdate <= d.date
  WHERE d.date BETWEEN '2016-06-01' AND '2016-06-09'

  but above query not given expected result. plese tell me how to write query to acive this task
  in sql server

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  May 15, 2017 at 1:44 am

  #1942477

  SELECT di.date, ord.orderid, DATEDIFF( DAY, ord.orderdate, di.date ) missingdays, ord.cost
FROM    [dbo].[dateinfo] di
CROSS APPLY (
              SELECT TOP 1 WITH TIES o.orderid, o.orderdate, o.cost
              FROM  [dbo].[orders] o
              WHERE o.orderdate <= di.date
              ORDER BY o.orderdate DESC
            ) ord

  ---

  Kingston Dhasian

  How to post data/code on a forum to get the best help - Jeff Moden
  http://www.sqlservercentral.com/articles/Best+Practices/61537/