SQL Query asked by AMA

The problem statement:
 

 

I have following data (table). Each item i.e. A, B, C have multiple entries at different times with status.  For for each item/time where status = 'Cosumed',  I want the prior Available time for that item.

Item	Time	Status
A	8:01:03 AM	Available
B	8:02:43 AM	Available
C	8:02:56 AM	Available
B	8:05:43 AM	Consumed
A	8:07:32 AM	Consumed
C	8:11:42 AM	Consumed
C	8:12:26 AM	Available
A	8:15:16 AM	Available
B	8:21:48 AM	Available
C	8:24:21 AM	Consumed
A	8:30:15 AM	Consumed
B	8:32:32 AM	Consumed

For Each Item (A, B, C) - I need to find the time difference (in minutes) between item's (each) 'Available' Status and 'Consumed' Status.
For Item 'A': Time Difference1 = (8:07:32 - 8:01:03) = 6 minutes and 29 secs [i.e time difference between item A's first consumption and availability)
For Item 'A': Time Difference2 = (8:30:15 - 8:15:16) = 14 minutes and 59 secs [i.e time difference between item A's 2nd consumption and availability)
The output will be Item A: max (time diff): 14 minutes and 59 secs
The output will be Item A: min (time diff): 6 minutes and 29 sec
Same is applicable for items B and C.

 Solution by RAC

 

select A.item, A.time, A.status, isnull(cast(datediff(hh,B.time,A.time) as varchar)+':'+cast((datediff(mi,B.time,A.time))%60 as varchar)+':'+ cast(datediff(s,B.time,A.time)%60 as varchar),'NA') as ConAvaiTimeDiff

from BI as A

left join BI as B

on B.time = (select max(time) from [BI] where [time] < [A].time and item = [A].item and [A].status = 'Consumed') and B.time < A.time

order by A.time , B.time

The same solution in MS-Access by Mr. Mehdi.


SELECT A.*, (SELECT TOP 1 B.Time FROM Table1 AS B WHERE B.Item = A.Item AND B.Time < A.Time And B.Status <> 'Consumed' ORDER BY B.Time DESC) AS LastTime
FROM Table1 AS A
WHERE A.Status = 'Consumed';