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

Open in new window

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

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

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
group by t1.column_changed        ,t1.key1_cd        ,t1.key1_value 

Open in new window

Mark WillsTopic AdvisorCommented:
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....

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
abarefootAuthor Commented:
Thanks for the help.  That is what I was looking for.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.