SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


set date value based on other row values


set date value based on other row values

Author
Message
carsten 97262
carsten 97262
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 8
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 Smile ... 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 25721 7907 2014-07-08 18:04:30.973 63 4 4 NULL
2 25718 7910 2014-07-08 18:04:30.973 63 4 4 NULL
3 25718 7910 2014-07-08 18:04:30.973 6 5 2 NULL
4 25719 7908 2014-07-08 18:04:30.973 6 5 2 NULL
5 25719 7908 2014-07-08 18:04:30.973 57 4 4 NULL
6 25719 7908 2014-07-08 18:04:30.973 52 8 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 25721 7907 2014-07-08 18:04:30.973 63 4 4 2014-07-08

2 25718 7910 2014-07-08 18:04:30.973 63 4 4 2014-07-08
3 25718 7910 2014-07-08 18:04:30.973 6 5 2 2014-07-12

4 25719 7908 2014-07-08 18:04:30.973 6 5 2 2014-07-12
5 25719 7908 2014-07-08 18:04:30.973 57 4 4 2014-07-08
6 25719 7908 2014-07-08 18:04:30.973 52 8 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 ... Wink
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43249 Visits: 20015
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]



“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
Exploring Recursive CTEs by Example Dwain Camps
carsten 97262
carsten 97262
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 8
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 Smile ...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search