Calculate DateDiff Challenge Via Pivot Operator

Cat265 · January 18, 2019, 3:59pm

I am trying to calculate the duration between two different times. Here is my Original query and its results:

SELECT 
  [DJ_NUMBER]
  ,[PROCESSING_ACTION]
  ,[TRANSACTION_QTY]
  ,[MES_MACHINE]
  ,[ACTION]
  ,[ACTION_TIME]

FROM [StagingDB].[dbo].[AFL_MES2O_Interface_Data_arch]
-- where ACTION in ('SetUp Start', 'Run Start','Run End')
WHERE PROCESSING_ACTION = 'Job Status'
Order by DJ_NUMBER asc, ACTION_TIME

|DJ_NUMBER|PROCESSING_ACTION|TRANSACTION_QTY|MES_MACHINE|ACTION|ACTION_TIME|

|---|---|---|---|---|---|
|19483257|Job Status|NULL|BL17|SetUp Start|2018-07-03 16:08:05.000|
|19483257|Job Status|NULL|BL17|Run Start|2018-07-03 16:08:08.000|
|19483257|Job Status|NULL|BL17|Run End|2018-07-03 16:59:05.000|
|19483258|Job Status|NULL|BL17|SetUp Start|2018-07-02 13:33:13.000|
|19483258|Job Status|NULL|BL17|Run Start|2018-07-02 13:33:17.000|

I tied to use DateDiff function but I am having conversion issue with it. My 'Run End' and 'Run Start' are string format:

DATEDIFF(Minute,'Run End','Run Start')

My goal is to get the following calculations via Pivot Operator:

Duration 1 = 'Run Start' - 'SetUp Start'
Duration 2 = 'Run End' - 'Run Start'

Final Output should be something like this:

DJ_NUMBER Duration1(Minutes) Duration2(Minutes)
19483257 0 50
19483258 0 4.00

Here is my Pivot table Operator. Here is my code:

   SELECT 	DJ_NUMBER 
		, 'SetUp Start' as [SetUp]
		,'Run Start' as [RunStart]
		,'Run End' as [RunEnd]

-- ,DATEDIFF(Minute,'Run End','Run Start')
FROM
(
SELECT DJ_NUMBER,ACTION,ACTION_TIME
FROM [StagingDB].[dbo].[AFL_MES2O_Interface_Data_arch]
) As SourceData
PIVOT
(
COUNT( ACTION_TIME)
FOR ACTION IN ([SetUp Start], [Run Start],[Run End])
) AS PIVOTTABLE
Order by DJ_NUMBER asc

Any help to calculate the duration in a pivot table will be greatly appreciated.

Thank you..

mike01 · January 18, 2019, 4:36pm

Your results don't match your data, but does this work for you?

Drop table if exists  #t
go


Create table #t(
DJ_NUMBER int,
PROCESSING_ACTION varchar(30),
TRANSACTION_QTY int,
MES_MACHINE varchar(5),
ACTION varchar(50),
ACTION_TIME varchar(50))


insert into #t values(
'19483257','Job Status',NULL,'BL17','SetUp Start','2018-07-03 16:08:05.000')
,('19483257','Job Status',NULL,'BL17','Run Start','2018-07-03 16:08:08.000')
,('19483257','Job Status',NULL,'BL17','Run End','2018-07-03 16:59:05.000')
,('19483258','Job Status',NULL,'BL17','Run Start','2018-07-02 13:29:13.000')
,('19483258','Job Status',NULL,'BL17','SetUp Start','2018-07-02 13:29:13.000')
,('19483258','Job Status',NULL,'BL17','Run End','2018-07-02 13:33:17.000')

select DJ_Number, 
	   DateDiff(minute, SetUpStart, RunStart) as Duration1,
	   DateDiff(minute, RunStart, RunEnd) as Duration2
from (
select DJ_Number, 
		max(Case when Action = 'SetUp Start' then cast(Action_Time as datetime) else null end) SetUpStart,
		max(Case when Action = 'Run Start' then cast(Action_Time as datetime) else null end) RunStart,
		max(Case when Action = 'Run End' then cast(Action_Time as datetime) else null end) RunEnd
 from #T
 group by DJ_Number) v

Cat265 · January 18, 2019, 6:15pm

Thank you mike01 - You are awesome. I appreciate your feedback.

Can you do this in Pivot table?

I have attempted to do it that way but had trouble doing it. can you tell me what am I doing wrong beside I am using Count as an aggregated function since I couldn't use Datediff.

SELECT DJ_NUMBER
, 'SetUp Start' as [SetUp]
,'Run Start' as [RunStart]
,'Run End' as [RunEnd]
-- ,DATEDIFF(Minute,'Run End','Run Start')
FROM
(
SELECT DJ_NUMBER,ACTION,ACTION_TIME
FROM [StagingDB].[dbo].[AFL_MES2O_Interface_Data_arch]
) As SourceData
PIVOT
(
COUNT( ACTION_TIME)
FOR ACTION IN ([SetUp Start], [Run Start],[Run End])
) AS PIVOTTABLE
Order by DJ_NUMBER asc

mike01 · January 18, 2019, 8:51pm

I'm not sure what you want to pivot on or why. Can you supply what you are expecting for results?

Cat265 · January 18, 2019, 9:07pm

mike01,

In addition to the duration, I am trying to get the quantity of different stages of the production Line. See the headings of my cross table. I was able to get the data for these stages, I just wasn't able to do it for the Duration. I would like to replace the Red boxes in the diagram below with Duration1 and Duration2

Here is the code:
USE StagingDB;
GO
select
[LineNo]
,[TubeJobNo]
,[RampdownAccount Alias Fiber SetRamp Down] AS RampDownFiber
,[RampdownAccount AliasRamp Down] AS RampDownCompund
,[ReturnFiber RTSRTS] AS FiberReturned
,[StartJob StatusSetUp Start] AS SetupStartTime
,[JobFinishedWIP Issue Fiber Set] AS FibersConsumed
,[MRBOracleOp Completion]
,[ScrappedOp Completion]
,[JobFinishJob StatusRun End] AS RunTime
,[RunJob StatusRun Start] AS SetupTime
,[ScrapFiber ScrapScrap] AS FiberScrapped
,[StartUpAccount Alias Fiber SetStart Up] AS RampUpFiber
,[StartUpAccount AliasStart Up] AS RampUpCompound
,[StartUpJob StatusSetUp Start] AS SetupStartTime2
,[TubeScrapAccount AliasTube Scrap]
,[FinalDataOp Completion]
,[FinalDataWIP Issue]
,[JobFinishedWIP Move]
FROM
(
SELECT
[MES_MACHINE] As [LineNo]
,[DJ_NUMBER] AS [TubeJobNo]
,(isnull(SUBSTRING (BATCH_ID, CHARINDEX('',BATCH_ID)+1, 9)+ PROCESSING_ACTION + ACTION, RIGHT(BATCH_ID, Len (BATCH_ID)- CHARINDEX('',BATCH_ID))+PROCESSING_ACTION)) as JobStatus
,[TRANSACTION_QTY]
FROM
[StagingDB].[dbo].[AFL_MES2O_Interface_Data_arch]

) As SourceProductionData

PIVOT
(
SUM (TRANSACTION_QTY)
FOR JobStatus
IN
(
[RampdownAccount Alias Fiber SetRamp Down]
,[RampdownAccount AliasRamp Down]
,[ReturnFiber RTSRTS]
,[StartJob StatusSetUp Start]
,[JobFinishedWIP Issue Fiber Set]
,[MRBOracleOp Completion]
,[ScrappedOp Completion]
,[JobFinishJob StatusRun End]
,[RunJob StatusRun Start]
,[ScrapFiber ScrapScrap]
,[StartUpAccount Alias Fiber SetStart Up]
,[StartUpAccount AliasStart Up]
,[StartUpJob StatusSetUp Start]
,[TubeScrapAccount AliasTube Scrap]
,[FinalDataOp Completion]
,[FinalDataWIP Issue]
,[JobFinishedWIP Move]
)
) AS PivotTable
GO

Cat265 · January 20, 2019, 10:16am

I was able to figure it out. Here is my solution to my answer:
SELECT
A.MES_MACHINE AS LineNo
,A.DJ_NUMBER
,A.ACTION as SetupStarted
,A.ACTION_TIME AS SetupStart
,B.ACTION as RunStarted
,B.ACTION_TIME AS UpTimeStart
,C.ACTION as RunEnded
,C.ACTION_TIME AS UpTimeEnd
,FORMAT(DATEDIFF(SECOND, A.ACTION_TIME, B.ACTION_TIME)/60.0,'##.##') AS Setup
,FORMAT(DATEDIFF(SECOND, B.ACTION_TIME, C.ACTION_TIME)/60.0, '##.##') AS UpTime
FROM [StagingDB].[dbo].[AFL_MES2O_Interface_Data_arch] A, [StagingDB].[dbo].[AFL_MES2O_Interface_Data_arch] B, [StagingDB].[dbo].[AFL_MES2O_Interface_Data_arch] C
WHERE (B.DJ_NUMBER = A.DJ_NUMBER AND B.ACTION_TIME > A.ACTION_TIME) and (C.DJ_NUMBER = B.DJ_NUMBER AND C.ACTION_TIME > B.ACTION_TIME)

Cat265 · January 20, 2019, 10:26am