help query in sql server

Question

Post reply

help query in sql server

baladevu

SSC Veteran

Points: 298
More actions
May 14, 2017 at 10:30 pm

#321408

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63428 More actions · Answer 1

pietlinden

SSC Guru

Points: 63428

May 14, 2017 at 10:58 pm

#1942472

What do you mean by "missing days"?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

SELECT d.[Date], OrderID = ISNULL(o.orderid, y.orderid), MissingDays = ISNULL(DATEDIFF(DAY,x.LastOrd,d.[Date]),0), Cost = ISNULL(o.cost,y.cost) FROM #DateInfo d LEFT JOIN #Orders o ON o.orderdate = d.[Date] OUTER APPLY ( SELECT LastOrd = MAX(orderdate) FROM #Orders oi WHERE oi.orderdate < d.[Date] AND o.orderdate IS NULL ) x OUTER APPLY ( SELECT oi.orderid, oi.cost FROM #Orders oi WHERE oi.orderdate = x.LastOrd AND o.orderdate IS NULL ) y WHERE d.[date] BETWEEN '2016-06-01' AND '2016-06-09'

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

Duplicate post.
https://www.sqlservercentral.com/Forums/1876106/get-missing-dates-differencedays-and-fill-previous-dates-data-in-sql-server

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/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Kingston Dhasian - Monday, May 15, 2017 2:44 AM
Duplicate post.
https://www.sqlservercentral.com/Forums/1876106/get-missing-dates-differencedays-and-fill-previous-dates-data-in-sql-server

And a much nicer job 😉

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 5

ChrisM@Work - Monday, May 15, 2017 3:19 AM
Kingston Dhasian - Monday, May 15, 2017 2:44 AM
Duplicate post.
https://www.sqlservercentral.com/Forums/1876106/get-missing-dates-differencedays-and-fill-previous-dates-data-in-sql-server
And a much nicer job 😉

🙂 Thanks Chris.

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/

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

This sounds like a gaps and islands problem. Search Itzik Ben-Gan gaps and island sql server for some solutions.

BTW, my guess is that any solution that involves a date < or > comparison would not be scalable. Quadradic performance degradation IIRC.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service