July 8, 2014 at 5:43 am
Sorry for the Header, I can't find the right way to describe what I want, possibly the reason why I can't get a solution by googling 🙂 ... Here my problem:
I've got a table with the following structure and some example data:
CREATE TABLE [dbo].[set_dates](
[Split3_ID] [int] IDENTITY(1,1) NOT NULL,
[CU_ID] [int] NULL,
[order_id] [int] NULL,
[st_date] [datetime] NULL,
[sku] [int] NULL,
[Priority] [int] NULL,
[Delay] [int] NULL,
[CourseDate] [datetime] NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[set_dates]
([CU_ID]
,[order_id]
,[st_date]
,[sku]
,[Priority]
,[Delay])
VALUES
(25721,7907,GETDATE(),63,4,4),
(25718,7910,GETDATE(),63,4,4),
(25718,7910,GETDATE(),6,5,2),
(25719,7908,GETDATE(),6,5,2),
(25719,7908,GETDATE(),57,4,4),
(25719,7908,GETDATE(),52,8,2)
GO
So my Table should look like this:
Split3_ID CU_ID order_id st_date sku Priority Delay CourseDate
1 257217907 2014-07-08 18:04:30.973634 4 NULL
2 257187910 2014-07-08 18:04:30.973634 4 NULL
3 257187910 2014-07-08 18:04:30.97365 2 NULL
4 257197908 2014-07-08 18:04:30.97365 2 NULL
5 257197908 2014-07-08 18:04:30.973574 4 NULL
6 257197908 2014-07-08 18:04:30.973528 2 NULL
What I would like to accomplish now is to set the CourseDate depending on three fields, order_id, Priority and Delay ... more exact:
If there is only one row with the same order_id, then CourseDate = st_date, if there is multiple rows with the same order_id, then the Dates should be entered depending on the Priority. Lowest Priority would be the first CourseDate = st_date, next bigger Priority would be st_date + Delay from previous entry ... and so on ...
In the above example, the outcome should look like this:
Split3_ID CU_ID order_id st_date sku Priority Delay CourseDate
1 257217907 2014-07-08 18:04:30.973634 4 2014-07-08
2 257187910 2014-07-08 18:04:30.973634 4 2014-07-08
3 257187910 2014-07-08 18:04:30.97365 2 2014-07-12
4 257197908 2014-07-08 18:04:30.97365 2 2014-07-12
5 257197908 2014-07-08 18:04:30.973574 4 2014-07-08
6 257197908 2014-07-08 18:04:30.973528 2 2014-07-14
[/code]
Is there a way to accomplish this? I've got up to ten orders with the same order_id with no presorting, so my first attempts with CASE Statements ended up in a lot of writing.
The second, but not so important problem is, that there may be orders with the same priority from time to time under the same order_id. In this case I would like those to be treated just like CourseDate = st_date
Any help is highly appreciated, as I got a major brainfart at the moment ... 😉
July 8, 2014 at 6:09 am
This should get you started:
;WITH SequencedData AS (
SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY [Priority])
FROM #set_dates
)
SELECT
[Split3_ID], [CU_ID], [order_id], [st_date], [sku], [Priority],
o.[Delay],
[CourseDate] = CASE WHEN o.rn = 1 THEN o.[st_date] ELSE DATEADD(DAY,x.[Delay],o.[st_date]) END
FROM SequencedData o
OUTER APPLY (
SELECT [Delay] = SUM([Delay])
FROM SequencedData i
WHERE i.order_id = o.order_id
AND i.rn < o.rn
) x
ORDER BY [Split3_ID]
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
July 8, 2014 at 6:43 am
Thanks for the answer!
It works fine - would just have to get it into an UPDATE Statement - got a very different and simpler solution though from stackexchange, which even fixes my problem when it comes to having multiple Courses with the same Priorities (for anyone interested):
update D1
set CourseDate = dateadd(day, (select isnull(sum(D2.Delay), 0)
from dbo.set_dates as D2
where D1.order_id = D2.order_id and
D1.Priority > D2.Priority), D1.st_date)
from set_dates as D1
Thanks again though and I'll mark your answer 🙂 ...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply