Date Difference contingent on other column values SQL

user03757

Hi I am trying to find the date difference between different rows with like IDs.   Please see screenshot of Excel to see data needs clearly.   Also screen shots attached from SQL transforms. 

 

SQL Does Validate: Transform 1

SELECT sp_palapprovals_workflow_history_dwb1. *,
Case when (`Primary Item ID`=`Primary Item ID`) and (`Workflow Association Name` like 'Approval') and (`Event Type` like '1') then `Created`
else ' '
end as 'Start Date of Approval'
from sp_palapprovals_workflow_history_dwb1

 

SQL Does Validate: Transform 2

SELECT start_of_approval. *,
Case when (`Primary Item ID`=`Primary Item ID`) and (`Workflow Association Name` like 'Approval') and (`Event Type` like '2') then `Created`
else ' '
end as 'End Date of Approval'
from start_of_approval

 

Not Validating........

SELECT end_of_approval.*,
Case when ((`Primary Item ID`=`Primary Item ID`) and (`Start Date of Approval` IS NOT NULL) and (`Start Date of Approval` !='') and (`Start Date of Approval` !=0) and (`End Date of Approval` IS NOT NULL) and (`End Date of Approval` !='') and (`End Date of Approval` !=0))
then (datediff(`End Date of Approval`,`Start Date of Approval`)) - ((week(`End Date of Approval`) - week(`Start Date of Approval`))*2)
else ''
end as 'Date Difference'
from end_of_approval

 

I did notice The Primary Item ID is not in order in the data set so they are not grouped up in order....  So I tried this first but could not get it to validate either.

Select sp_palapprovals_workflow_history_dwb1.*,

ORDER BY `Primary Item ID`
end

from sp_palapprovals_workflow_history_dwb1

 

I may be thinking about this problem incorrectly.....  Any help would be welcomed.  

 

AS

Your idea behind the first two transforms is good.  For every primary item id approval, find the start date and find the end date in different transforms.

Then you need to join the two tables together on the primary item id and calculate the date difference.  It looks like it's that second part that still needs attention.  It looks like you were trying to find the start of approval in the end of approval data but that column wasn't there.

 

Pseudo code:

start_of_approval

SELECT

`primary item id`

,a.`created` as start_date

FROM

data a

WHERE

a.`workflow` = 'Approval' and a.`event type` = 1

 

end_of_approval

SELECT

b.`primary item id`

,b.`created` as end_date

FROM

data b

WHERE

and b.`workflow` = 'Approval' and b.`event type` = 2

 

date diff calculation

SELECT

a.`primary item id`

,a.`start_date`

,b.`end_date`

,CASE WHEN b.`end_date` IS NOT NULL THEN DATEDIFF(b.`end_date`,a.`start_date`) ELSE NULL END as date_difference

FROM

start_of_approval a

LEFT OUTER JOIN end_of_approval b ON a.`primary item id` = b.`primary item id`

 

 

 

Start with something like that and see where it takes you.

AS

Your idea behind the first two transforms is good.  For every primary item id approval, find the start date and find the end date in different transforms.

Then you need to join the two tables together on the primary item id and calculate the date difference.  It looks like it's that second part that still needs attention.  It looks like you were trying to find the start of approval in the end of approval data but that column wasn't there.

 

Pseudo code:

start_of_approval

SELECT

`primary item id`

,a.`created` as start_date

FROM

data a

WHERE

a.`workflow` = 'Approval' and a.`event type` = 1

 

end_of_approval

SELECT

b.`primary item id`

,b.`created` as end_date

FROM

data b

WHERE

and b.`workflow` = 'Approval' and b.`event type` = 2

 

date diff calculation

SELECT

a.`primary item id`

,a.`start_date`

,b.`end_date`

,CASE WHEN b.`end_date` IS NOT NULL THEN DATEDIFF(b.`end_date`,a.`start_date`) ELSE NULL END as date_difference

FROM

start_of_approval a

LEFT OUTER JOIN end_of_approval b ON a.`primary item id` = b.`primary item id`

 

 

 

Start with something like that and see where it takes you.

Marc_H

 

 

xx

user03757

We got it to work with that !  - Thank you