1

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

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 ... ;)

Improve this question
1
  • Sorry, SQL Server 2008 ... Commented Jul 8, 2014 at 11:35

1 Answer 1

Reset to default
1

You can calculate the Delay in a cross apply and use dateadd() to add it to st_date in the main query.

select D1.Split3_ID, D1.CU_ID, D1.order_id, D1.st_date, D1.sku, D1.Priority, D1.Delay, dateadd(day, D2.Delay, cast(D1.st_date as date)) as CourseDate from dbo.set_dates as D1 cross apply ( select isnull(sum(D2.Delay), 0) from dbo.set_dates as D2 where D1.order_id = D2.order_id and D1.st_date = D2.st_date and D1.Priority > D2.Priority ) as D2(Delay)

SQL Fiddle

Update:

I am not really sure how you want to deal with different st_date within a order_id. The above query groups on order_id and st_date but I guess an alternative would be to use the lowest st_date per order_id in the dateadd function.

select D1.Split3_ID, D1.CU_ID, D1.order_id, D1.st_date, D1.sku, D1.Priority, D1.Delay, dateadd(day, D2.Delay, cast(isnull(D2.st_date, D1.st_date) as date)) as CourseDate from dbo.set_dates as D1 cross apply ( select isnull(sum(D2.Delay), 0), min(D2.st_date) from dbo.set_dates as D2 where D1.order_id = D2.order_id and D1.Priority > D2.Priority ) as D2(Delay, st_date) order by D1.Split3_ID

Update 2:

The update statement for the second query could look like this.

with C as ( select D1.CourseDate, dateadd(day, D2.Delay, cast(isnull(D2.st_date, D1.st_date) as date)) as NewCourseDate from dbo.set_dates as D1 cross apply ( select isnull(sum(D2.Delay), 0), min(D2.st_date) from dbo.set_dates as D2 where D1.order_id = D2.order_id and D1.Priority > D2.Priority ) as D2(Delay, st_date) ) update C set CourseDate = NewCourseDate

Update 3:

Or even like this if st_date is guaranteed to be the same within one order_id.

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
Improve this answer
5
  • Hi Mikael, thanks a lot, it works great in a Select Statement... still having trouble to use this in an Update Statement to actually write the CourseDate Value into the table ... could you provide me with some input? Commented Jul 8, 2014 at 12:02
  • oh ... the st_date per order_id is the same, it's basically the date the order is booked for, no matter how many items, so this shouldn't be a problem, just need to get it into an Update Statement now :) Commented Jul 8, 2014 at 12:08
  • @user2301990 Ok, added one way to do the update. Commented Jul 8, 2014 at 12:10
  • You're a star! The last one is the simple answer I was looking for - even with 3 different entries of which 2 have the same Priority, it's doing what's intended, delays both of them to the same day :) If you're in Thailand, I'll buy you a beer right now! Thx a mill ... Commented Jul 8, 2014 at 12:32
  • @user2301990 Not near Thailand right now but thanks and glad I could help. Commented Jul 8, 2014 at 12:42

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.