DateDiff in the same column

abarefoot
abarefoot used Ask the Experts™
on
I'm needing to find the DateDiff between two dates in the same column.  B is when an order goes on backorder.   So I'm basically look for how long an order was on backorder.  The difference between when (old_value is NULL and new_value = 'B') and (old_value = 'B' and new_value is NULL).

SELECT        column_changed, key1_cd, key1_value, old_value, new_value, date_created
FROM            p21_view_audit_trail_oe_hdr_1319
WHERE        (item_id = 'BWTREG-DF-1') AND (column_description = 'disposition') AND (key1_value = '2732473')
ORDER BY date_created DESC

Open in new window


results:
  1. column_changed        key1_cd        key1_value       old_value       new_value                  date_created
  2. disposition                     order_no      2732473              B                       NULL                    2018-01-18 18:58:31.633
  3. disposition                     order_no      2732473              D                       B                            2018-01-11 19:10:40.503
  4. disposition                     order_no      2732473              B                       D                            2018-01-11 19:07:57.397
  5. disposition                     order_no      2732473              NULL               B                            2018-01-11 19:07:42.213
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You could make it with join like the following:
select t1.column_changed        ,t1.key1_cd        ,t1.key1_value , datediff(s,t1.date_created , t2.date_created ) dd
from t1 t1 inner join t1 t2 on t1.column_changed = t2.column_changed and t1.key1_cd   = t2.key1_cd     and t1.key1_value = t2.key1_value 
where t1.old_value is NULL  and t1.new_value = 'B' and t2.old_value = 'B' and t2.new_value is null

Open in new window

or like the following
select t1.column_changed        ,t1.key1_cd        ,t1.key1_value , datediff(s,Min(t1.date_created) , Max(t1.date_created )) dd
FROM t1
group by t1.column_changed        ,t1.key1_cd        ,t1.key1_value 

Open in new window

Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
So, is it fair to say, you want the date difference between MIN(date_Created) where new_value = 'B' and MAX(date_created) where old_value is 'B' and new_value = NULL ?

OR, do you also want to take into account any transitional (from old_value to new_value)  days ?

And, what other values could there be in that transition / status ?

And of course, that might not happen (not all items go on backorder), so need to take that into account as well....

Have a look at :
SELECT        column_changed, key1_cd, key1_value, old_value, new_value, A.date_created, O.date_created as [On_BO_Date], N.date_created as [Off_BO_Date],
              case when o.date_created is not null and n.date_created is not null then datediff(day,o.date_created,n.date_created) else 0 end as [Days_On_Backorder]
FROM          p21_view_audit_trail_oe_hdr_1319 A
Outer apply (select min(date_created) from p21_view_audit_trail_oe_hdr_1319 B where A.key1_cd = B.key1_cd and A.key1_value = B.key1_value and B.new_value = 'B') O(date_created) 
Outer apply (select max(date_created) from p21_view_audit_trail_oe_hdr_1319 B where A.key1_cd = B.key1_cd and A.key1_value = B.key1_value and B.old_value = 'B' and B.new_value is NULL) N(date_created) 

WHERE        (item_id = 'BWTREG-DF-1') AND (column_description = 'disposition') AND (key1_value = '2732473')
ORDER BY date_created DESC

Open in new window

Those two outer apply clauses, could be combined into one (using case statements to help identify date_created for MIN() or MAX() qualification), and will help a little with performance. Depends a bit on transitional status values, and, not entirely needed to combine them unless there is a performance concern.Then again, might also be desirable to incorporate Item_Id - depending on (underlying) indexes....

Author

Commented:
Thanks for the help.  That is what I was looking for.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial