Sql-server – set date value based on other row values

sql serversql-server-2008t-sql

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 … 😉

Best Answer

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
Related Question