Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

set date value based on other row values Expand / Collapse
Author
Message
Posted Tuesday, July 8, 2014 5:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 11:25 AM
Points: 2, Visits: 6
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 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 ... ;)
Post #1590308
Posted Tuesday, July 8, 2014 6:09 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:56 AM
Points: 7,137, Visits: 13,527
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
Post #1590320
Posted Tuesday, July 8, 2014 6:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 11:25 AM
Points: 2, Visits: 6
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 :) ...
Post #1590338
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse