Update row using different steptype within same order

Question

Post reply

Update row using different steptype within same order

Sezam

Ten Centuries

Points: 1240

March 3, 2023 at 3:40 am

#4156282

Hi All, been a while since I posted.

Facing weird calculation, I cant wrap my head around on how to google it.

I have a data that looks like this (simplified):

sql

Need to calculate Lead Time, rules as follow:

For StepType 1:

If Actual is empty: Today minus Steptype 10 "schedule" date

Actual is not empty: Actual date minus Steptype 10 "schedule" date

For other steptypes (2 to 12):

if Actual is empty: Today minus Steptype 11 "scheduled date"

Actual is not empty: Actual date minus Steptype 11 "schedule" date

I should mention, above calculation should be applied within same [Order]. And I suppose you can ignore Plan date, as its not used for this calculation

here is code to generate sample data and table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sample](
[Oid] [uniqueidentifier] NOT NULL,
[Order] [uniqueidentifier] NOT NULL,
[StepType] [int] NULL,
[Plan] [datetime] NULL,
[Schedule] [datetime] NULL,
[Actual] [datetime] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[sample] ([Oid], [Order], [StepType], [Plan], [Schedule], [Actual]) VALUES (N'45383efd-00af-4fe7-8a41-419f14aa95e1', N'b62e33cc-19b5-46e3-916f-6ef248cc53ae', 4, CAST(N'2018-08-30 00:00:00.000' AS DateTime), CAST(N'2018-08-29 00:00:00.000' AS DateTime), CAST(N'2018-08-29 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[sample] ([Oid], [Order], [StepType], [Plan], [Schedule], [Actual]) VALUES (N'cd5c72b5-7a4d-4886-a27d-70dd3699f543', N'b62e33cc-19b5-46e3-916f-6ef248cc53ae', 11, CAST(N'2018-07-31 00:00:00.000' AS DateTime), CAST(N'2018-07-31 00:00:00.000' AS DateTime), CAST(N'2018-07-31 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[sample] ([Oid], [Order], [StepType], [Plan], [Schedule], [Actual]) VALUES (N'0ac5108b-71c3-448a-a254-7452145de02d', N'b62e33cc-19b5-46e3-916f-6ef248cc53ae', 12, CAST(N'2018-10-07 00:00:00.000' AS DateTime), CAST(N'2018-11-30 00:00:00.000' AS DateTime), CAST(N'2018-11-30 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[sample] ([Oid], [Order], [StepType], [Plan], [Schedule], [Actual]) VALUES (N'cc6bf06c-1a6e-4c9a-90cb-bd7f562f251e', N'b62e33cc-19b5-46e3-916f-6ef248cc53ae', 7, CAST(N'2018-09-04 00:00:00.000' AS DateTime), CAST(N'2018-10-08 00:00:00.000' AS DateTime), CAST(N'2018-10-08 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[sample] ([Oid], [Order], [StepType], [Plan], [Schedule], [Actual]) VALUES (N'122b9eed-e955-4837-83cf-c230d51edff6', N'b62e33cc-19b5-46e3-916f-6ef248cc53ae', 6, CAST(N'2018-08-31 00:00:00.000' AS DateTime), CAST(N'2018-10-02 00:00:00.000' AS DateTime), CAST(N'2018-10-02 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[sample] ([Oid], [Order], [StepType], [Plan], [Schedule], [Actual]) VALUES (N'259ca6a1-7a8a-4b9a-8292-d56bb8117a17', N'b62e33cc-19b5-46e3-916f-6ef248cc53ae', 8, CAST(N'2018-09-11 00:00:00.000' AS DateTime), CAST(N'2018-11-29 00:00:00.000' AS DateTime), CAST(N'2018-11-23 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[sample] ([Oid], [Order], [StepType], [Plan], [Schedule], [Actual]) VALUES (N'c40308b5-8ba5-45f2-906d-df67eadff478', N'b62e33cc-19b5-46e3-916f-6ef248cc53ae', 1, CAST(N'2018-09-03 00:00:00.000' AS DateTime), CAST(N'2018-10-01 00:00:00.000' AS DateTime), CAST(N'2018-10-01 00:00:00.000' AS DateTime))
GO
INSERT [dbo].[sample] ([Oid], [Order], [StepType], [Plan], [Schedule], [Actual]) VALUES (N'751d9ee9-ad8e-46a0-90ae-e759bfa1a2bf', N'b62e33cc-19b5-46e3-916f-6ef248cc53ae', 10, CAST(N'2018-07-24 00:00:00.000' AS DateTime), CAST(N'2018-07-24 00:00:00.000' AS DateTime), NULL)
GO

This topic was modified 1 year, 8 months ago by Sezam.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

It could be two similar queries. For the first one maybe you could try something like this. It uses OUTER APPLY but LEFT JOIN would probably also work

select s.*, s10.Schedule as s10_Schedule, 
       iif(s.Actual is null, 
           datediff(second, s10.Schedule, getdate()), 
           datediff(second, s10.Schedule, s.Actual)) lead_time
from #sample s
     outer apply (select ss.Schedule
                  from #sample ss
                  where ss.[Order]=s.[Order]
                        and ss.StepType=10) s10(Schedule)
where StepType=1;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Emperor100 SSCrazy Points: 2173 More actions · Answer 2

You can use s10, s11 and update if that is required. Otherwise it will provide the days difference between actual and schedule times.

declare @Today date = Getdate()

select @Today

;with cte 
as 
(select t1.*,
        ISNULL(t1.actual,@Today) NotEmpty,
        (select t3.Schedule from sample t3 where t3.[order] = t1.[order] and t3.steptype=10) s10, 
        (select t3.Schedule from sample t3 where t3.[order] = t1.[order] and t3.steptype=11) s11            
from sample t1
)
select *,
         case when tt.steptype = 1 then
                abs(datediff(dd,NotEmpty,s10))
         else
                abs(datediff(dd,NotEmpty,s11))
         end NoOfDays
from cte tt

=======================================================================

Sezam Ten Centuries Points: 1240 More actions · Answer 3

Thanks folks for the great answers, both seems to work correct as I checked with data. Just second query looks more compact. Although, my "sample" is actually 7 complex select statement with unions. Would it be possible to turn them into another subquery within second solution given by @Empreror100. I'm eventually loading this data into fact table, I just want to avoid to create staging table just to do this calculation, if possible. Again, thanks a lot for the answer, I wouldn't be able to come up myself in this short period, if ever.

One more question, out of curiosity. why did you have to delcare today into variable, is using getdate directly into equery have some side effects or something?

Emperor100 SSCrazy Points: 2173 More actions · Answer 4

Ahh.. @Today is just declared while testing. You can replace it with getdate().

Sezam wrote:

Would it be possible to turn them into another subquery within second solution

Can you elaborate more on the above quote? I will try to help if I am not busy... others can chip in please.

=======================================================================

Sezam Ten Centuries Points: 1240 More actions · Answer 5

Here is what my actual select statement looks like (simplified) instead of #sample provided above:

select ps.[Oid]
      ,o.Oid [Order]  
      ,[StepType]
       ,coalesce( [InitialPlannedDate], CurrentplannedDate)[Plan]
      ,[CurrentPlannedDate] Schedule
      ,[CurrentConfirmedDate] Actual
 from [Pl].[Order] o 
join   [Pl].[ProcessStep] ps   on ps.[order]=o.oid  

union 
select brr2.[Oid]
      ,o.Oid [Order]
      ,[StepType]
      ,coalesce( [InitialPlannedDate], CurrentplannedDate)[Plan]
      ,[CurrentPlannedDate] Schedule
      ,[CurrentConfirmedDate] Actual
 from [Pl].[Order] o 
join   [Pl].[ProcessStep] brr2   on brr2.oid=o.DspBrr2 
...
--5 more select statements

I need to join to same table with different keys and union them to get data for different steps in row format. May be there is better sql to do that, but thats beyond my current skills.

Reading through cte document by microsoft it looks like we cant have subquery within cte, or can we?

Emperor100 SSCrazy Points: 2173 More actions · Answer 6

See if this helps...

I just provided for two scenarious using UNION, you can add the rest.. with actual columns and their table names

declare @Today date = getdate()

-- Linking steptype 1 and steptype 10
select  a.*, NoOfDays
from sample  a
outer apply
            (select ISNULL(abs(datediff(dd,coalesce(t1.actual, @Today),t1.Schedule)),0) NoOfDays         
              from sample t1
              where a.[Order] = t1.[Order] and t1.StepType=10 
            )b
where a.StepType=1
union
---- Linking steptype 8 and steptype 11
select  a.*, NoOfDays
from sample  a
outer apply
            (select ISNULL(abs(datediff(dd,coalesce(t1.actual, @Today),t1.Schedule)),0) NoOfDays         
              from sample t1
              where a.[Order] = t1.[Order] and t1.StepType=11 
            )b
where a.StepType=8
       .....
       .....

=======================================================================

Sezam Ten Centuries Points: 1240 More actions · Answer 7

I see, so it has to be in every select statement. I think I'll stick with your original solution, because every select is kind of retrieves different step types. But calculation should be done across all steptypes, so logically it should run after union is done.

I'll use staging table to do this calculation and update back fact table. I wanted to bake in whole solution in one script not to increase ETL runtime, but data is not that big. As they say, as long as it works 🙂

Thank you again, also for the additional fix with using ABS, I have not thought that was needed.