Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Sorry, SQL Server 2008 ... –  user2301990 Jul 8 at 11:35

1 Answer 1

up vote 1 down vote accepted

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
share|improve this answer
    
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? –  user2301990 Jul 8 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 :) –  user2301990 Jul 8 at 12:08
    
@user2301990 Ok, added one way to do the update. –  Mikael Eriksson Jul 8 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 ... –  user2301990 Jul 8 at 12:32
    
@user2301990 Not near Thailand right now but thanks and glad I could help. –  Mikael Eriksson Jul 8 at 12:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.