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