Date Difference contingent on other column values SQL
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.
Best Answer
-
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.
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Answers
-
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.
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
xx
0 -
We got it to work with that ! - Thank you
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 293 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive