# DateDiff in the same column

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
``````

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
LVL 1
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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
``````
0
Commented:
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
``````
0
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
``````
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....
0

Experts Exchange Solution brought to you by