Do recursive call without a ParentID column
-
I have the following tables
[Order]
ID
[Schedule]
OrderID
Start
End
I have the following query that gets the schedule details for a specific OrderID. It also gets the next Order.ID for the next order in the schedule for the date of the passed in OrderID
DECLARE @OrderID int = 57339;with orders as (
SELECT [Order].ID AS OrderID, Schedule.Start, Schedule.ScheduleEnd
FROM [Order] INNER JOIN
Schedule ON [Order].ID = Schedule.OrderID
WHERE ([Order].ID = @OrderID)
),
orderWithNextID AS (
SELECT o.*,
(
SELECT
TOP 1 OrderID
FROM Schedule ss
WHERE
ss.Start > o.Start AND CONVERT(DATE,ss.Start) < DATEADD(DAY, 1,CONVERT(DATE, o.Start))
AND ss.OrderID <> o.OrderID
ORDER BY Start ASC) as NextOrderID
FROM orders o
)
SELECT * FROM orderWithNextID
A schedule of the day may well have multiple orders, so I want to pass in the orderID of the first one in the morning and return all the orders for the day - ie the child orders based on this parent orderID.
I have removed a number of columns for clarity and I'm not able to change the table structure eg. I cannot put a partentID column on th Order table.
What is the best approach for doing this. Would it be a loop? I've looked at a way of doing it via a CTE but I think I would need the parentID column.
Any suggestions would be great.
-
Hi and welcome to the forums. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Hi Thanks for the reply, here is a script that build and populates the tables
GOSET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Schedule](
[OrderID] [int] NOT NULL,
[Start] [datetime] NOT NULL,
[End] [datetime] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Schedule] ([OrderID], [Start], [End]) VALUES (1, CAST(0x0000A37600DD6C5B AS DateTime), CAST(0x0000A37600EDE71B AS DateTime))
INSERT [dbo].[Schedule] ([OrderID], [Start], [End]) VALUES (2, CAST(0x0000A376010EDDD0 AS DateTime), CAST(0x0000A376011F5890 AS DateTime))
INSERT [dbo].[Schedule] ([OrderID], [Start], [End]) VALUES (3, CAST(0x0000A37600FE6D75 AS DateTime), CAST(0x0000A376010EE835 AS DateTime))
INSERT [dbo].[Schedule] ([OrderID], [Start], [End]) VALUES (4, CAST(0x0000A376012FDEA8 AS DateTime), CAST(0x0000A37601405968 AS DateTime))
INSERT [dbo].[Schedule] ([OrderID], [Start], [End]) VALUES (5, CAST(0x0000A37601615001 AS DateTime), CAST(0x0000A3760171CAC1 AS DateTime))
INSERT [dbo].[Schedule] ([OrderID], [Start], [End]) VALUES (6, CAST(0x0000A3760150D61A AS DateTime), CAST(0x0000A376016150DA AS DateTime))
INSERT [dbo].[Schedule] ([OrderID], [Start], [End]) VALUES (7, CAST(0x0000A376018246FB AS DateTime), CAST(0x0000A37700073FBB AS DateTime))
INSERT [dbo].[Schedule] ([OrderID], [Start], [End]) VALUES (8, CAST(0x0000A3750038B09C AS DateTime), CAST(0x0000A37500492B5C AS DateTime))
INSERT [dbo].[Schedule] ([OrderID], [Start], [End]) VALUES (9, CAST(0x0000A376008B1737 AS DateTime), CAST(0x0000A376009B91F7 AS DateTime))
INSERT [dbo].[Schedule] ([OrderID], [Start], [End]) VALUES (10, CAST(0x0000A37600BC8ACA AS DateTime), CAST(0x0000A37600CD058A AS DateTime))
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Order](
[ID] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Order] ([ID]) VALUES (1)
INSERT [dbo].[Order] ([ID]) VALUES (2)
INSERT [dbo].[Order] ([ID]) VALUES (3)
INSERT [dbo].[Order] ([ID]) VALUES (4)
INSERT [dbo].[Order] ([ID]) VALUES (5)
INSERT [dbo].[Order] ([ID]) VALUES (6)
INSERT [dbo].[Order] ([ID]) VALUES (7)
INSERT [dbo].[Order] ([ID]) VALUES (8)
INSERT [dbo].[Order] ([ID]) VALUES (9)
INSERT [dbo].[Order] ([ID]) VALUES (10)
ALTER TABLE [dbo].[Schedule] ADD CONSTRAINT [DF_Schedule_Start] DEFAULT (getdate()) FOR [Start]
GO
ALTER TABLE [dbo].[Schedule] ADD CONSTRAINT [DF_Schedule_End] DEFAULT (getdate()) FOR [End]
GO
Ideally my expected results would look something like:
OrderID | Start | End | NextOrderID
Many thanks
-
OK I got the columns you want to see but what the actual values for the sample data you posted? I am not understanding what you are trying to do here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
This may help kick you along the right path, but if you are dealing with a large dataset this won't be good for performance...
declare @ID int; -- the first order number
set @ID=9;
select x.[OrderID], x.[Start], x.[End], y.[OrderID] as NextOrderId
from [Order] AS O
join [Schedule] AS S
on S.orderid = O.id
outer apply (
-- Find all scheduled orders that start on/after the selected order's start time
select *
from schedule AS sc
where sc.[Start]>= S.[Start]
and sc.[Start]<dateadd(day,datediff(day,0,S.[Start])+1,0)
) x
outer apply (
-- Find the next order in the schedule (SQL 2012 would make this easier)
select top 1 OrderID
from schedule AS sn
where sn.[Start]> x.[Start]
and sn.[Start]<dateadd(day,datediff(day,0,x.[Start])+1,0)
order by sn.[Start]
) y
where O.ID=@ID
order by x.[Start];
Results:
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);- Forum Etiquette: How to post Reporting Services problems
[/url]- Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]- How to Post Performance Problems - by Gail Shaw
[/url] -
Thanks all for your help, had to tweak it a little but works great.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply