set date value based on other row values

• carsten 97262

  Newbie

  Points: 2

  More actions

  July 8, 2014 at 5:43 am

  #293070

  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 257217907 2014-07-08 18:04:30.973634 4 NULL

  2 257187910 2014-07-08 18:04:30.973634 4 NULL

  3 257187910 2014-07-08 18:04:30.97365 2 NULL

  4 257197908 2014-07-08 18:04:30.97365 2 NULL

  5 257197908 2014-07-08 18:04:30.973574 4 NULL

  6 257197908 2014-07-08 18:04:30.973528 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 257217907 2014-07-08 18:04:30.973634 4 2014-07-08

  2 257187910 2014-07-08 18:04:30.973634 4 2014-07-08

  3 257187910 2014-07-08 18:04:30.97365 2 2014-07-12

  4 257197908 2014-07-08 18:04:30.97365 2 2014-07-12

  5 257197908 2014-07-08 18:04:30.973574 4 2014-07-08

  6 257197908 2014-07-08 18:04:30.973528 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 ...

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 8, 2014 at 6:09 am

  #1727733

  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

• carsten 97262

  Newbie

  Points: 2

  More actions

  July 8, 2014 at 6:43 am

  #1727749

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