Fill missing dates related data with previous date related data and productid should be unique in SQL Server

• asrinu13

  Say Hey Kid

  Points: 709

  More actions

  May 13, 2017 at 8:46 am

  #337837

  How to fill missing dates related data with previous date related data and productid should be unique in SQL Server?

  Table: product

  CREATE TABLE [dbo].[product]
  (
    [productid] [int] NULL,
    [productdate] [date] NULL,
    [grandtotal] [money] NULL
  )
  GO

  INSERT [dbo].[product] ([productid], [productdate], [grandtotal]) 
  VALUES (1, CAST(N'2017-03-01' AS Date), 100.0000)
  GO
  INSERT [dbo].[product] ([productid], [productdate], [grandtotal])
  VALUES (2, CAST(N'2017-03-02' AS Date), 200.0000)
  GO
  INSERT [dbo].[product] ([productid], [productdate], [grandtotal])
  VALUES (3, CAST(N'2017-03-05' AS Date), 300.0000)
  GO
  INSERT [dbo].[product] ([productid], [productdate], [grandtotal])
  VALUES (4, CAST(N'2017-03-10' AS Date), 400.0000)
  GO
  Table: productdetails

  CREATE TABLE [dbo].[productdetails]
  (
    [productid] [int] NULL,
    [priceperunit] [int] NULL,
    [quantityorders] [int] NULL
  )
  GO

  INSERT [dbo].[productdetails] ([productid], [priceperunit], [quantityorders])
  VALUES (1, 10, 2)

  INSERT [dbo].[productdetails] ([productid], [priceperunit], [quantityorders])
  VALUES (2, 20, 3)

  INSERT [dbo].[productdetails] ([productid], [priceperunit], [quantityorders]) 
  VALUES (3, 6, 5)

  INSERT [dbo].[productdetails] ([productid], [priceperunit], [quantityorders])
  VALUES (4, 15, 10)
  Here both table common column is productid. Missing dates filled with previous dates data in both table and productid should be show unique in both table unique productid will give any values but should show unique when fill missing dates values.

  Example: productdate between two dates 2017-03-02 to 2017-03-05 missed two dates are 2017-03-04 and 2017-03-05 so need to fill with previous dates data and productid should be unique.

  Get productdetail information like below

  select pd.productid, pd.priceperunit, pd.quantityorders
  from product p
  join productdetails pd on p.productid = pd.productid
  where p.productid = 2
  Based on above two tables data I want two tables data like below :

  Product output:

  productid|productdate |grandtotal
  1 |2017-03-01 |100.00
  2 |2017-03-02 |200.00
  2a |2017-03-03 |200.00 ---here missing dates and productid show unique value
  2b |2017-03-04 |200.00 ---here missing dates and productid show unique value
  3 |2017-03-05 |300.00
  3a |2017-03-06 |300.00 ---here missing dates and productid show unique value
  3b |2017-03-07 |300.00 ---here missing dates and productid show unique value
  3c |2017-03-08 |300.00---here missing dates and productid show unique value
  3d |2017-03-09 |300.00 ---here missing dates and productid show unique value
  4 |2017-03-10 |400.00
  Productdetails:

  productid|priceperunit |quantityorders
  1 |10   |2
  2 |20   |3
  2a |20   |3--missing record show unique
  2b |20   |3--missing record show unique
  3 |6    |5
  3a |6    |5--missing record show unique
  3b |6    |5--missing record show unique
  3c |6    |5--missing record show unique
  3d |6    |5--missing record show unique
  4 |15   |10
  Please tell me how to write a query to achieve this task in SQL Server

• drew.allen

  SSC Guru

  Points: 76998

  More actions

  May 13, 2017 at 12:54 pm

  #1942439

  This works best if you have a calendar table.  I've created a CTE calendar table to demonstrate how it would work.

  I used LEAD to find the ranges for each of the records and then joined to the calendar table to fill in those ranges.

  DROP TABLE #product

  DECLARE @base_date DATE = '2017-03-01'
;
CREATE TABLE #product
(
[productid] [int] NULL,
[productdate] [date] NULL,
[grandtotal] [money] NULL
)

  INSERT #product ([productid], [productdate], [grandtotal])
VALUES (1, CAST(N'2017-03-01' AS Date), 100.0000)
,    (2, CAST(N'2017-03-02' AS Date), 200.0000)
,    (3, CAST(N'2017-03-05' AS Date), 300.0000)
,    (4, CAST(N'2017-03-10' AS Date), 400.0000)
;
WITH CTE AS
(
    SELECT *
    FROM
    (
        VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
    ) t(n)
),
Tally AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY @@VERSION) - 1 AS n
    FROM CTE AS a
),
Cal AS
(
    SELECT DATEADD(DAY, n, @base_date) AS dt
    FROM Tally
),
product_ranges AS
(
    SELECT productid, grandtotal, productdate AS start_dt, LEAD(productdate, 1, DATEADD(DAY, 1, productdate)) OVER(ORDER BY productdate) AS end_dt
    FROM #product
)
SELECT productid, dt AS productdate, grandtotal
FROM product_ranges pr
INNER JOIN Cal
    ON pr.start_dt <= Cal.dt
        AND pr.end_dt > Cal.dt
;


  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA