Another SQL problem!

[GzArIa]

Well, well well it's me again, and this SQL stuff is doing my head, been learning a lot in here, but there is somehting I can't solve,...

Container_ID StatusChange Date
ABCD Not in Use 10/07/2004
ABCD I In Use 12/07/2004
ABCD Not in Use 20/07/2004
ABCD In Use 25/07/2004
ABCD Not in Use 30/07/2004
ABCD In Use 05/08/2004
ABCD Not in Use 09/08/2004

I am running a query and want to check how many days the container ABCD was in use between the dates 11/07/2004 and 07/08/2004.
To make it easier for the first step how can I actually find out how many days the container is in use in this entire list. If I do it manually it would look like this:
8 days between 12/07 and 20/07
5 days between 25/07 and 30/07
4 days between 05/08 and 09/08
all together 17 days,...

but how would the SQL look like,...

I appreciate any help, and "NO" I am not lazy :-) I just can't figure out how to do this,...cause I somehow have to find the one's with the status change "In use" and kind of calculate back to the last "Not in use",...

thanks a lot
GzArIa

[GzArIa]

Here the list again, in better format

Container_ID - StatusChange - Date
ABCD ---------Not in Use -----10/07/2004
ABCD ---------In Use ---------12/07/2004
ABCD ---------Not in Use -----20/07/2004
ABCD ---------In Use ---------25/07/2004
ABCD ---------Not in Use -----30/07/2004
ABCD ---------In Use ---------05/08/2004
ABCD ---------Not in Use -----09/08/2004

[Roelf]

now, that was a challenge.

what you need to do the next time, is describe the query in detail to yourself, like:
i want to get each record where the status is "In Use" and then find the first appearing record where the status is "Not In Use". Then calculate the difference in days between these two dates.
To do that, the records must be in ascending order, ordered by the date column.

Then start building the query, bit by bit.
first i did:
SELECT *
FROM test t1
ORDER BY t1.[Date]
as expected, this gave me all fields and all records
now we need in one record, the date where the thing came in use and the date where the thing cam out of use. so we have to join this table to itself
SELECT * fromt test t1
INNER JOIN test t2
ORDER BY t1.[Date]
this gives a resultset where each record in the table is joined with each record in the table, so we have to make some criteria to limit these results
what we can do is eliminate each record where the two displayed statuses are equal:
SELECT * from test t1
INNER JOIN test t2
ON t2.Status <> t1.Status
ORDER BY t1.[Date]
now we still see too many records, lets limit these results to those where the first date is before the second date
SELECT * FROM test t1
INNER JOIN test t2
ON t1.Status <> t2.Status AND t1.[Date] < t2.[Date]
ORDER BY t1.[Date]

now we can limit further by selecting only those records where the first status is "In Use"
SELECT * FROM test t1
INNER JOIN test t2
ON t1.Status <> t2.Status AND t1.[Date] < t2.[Date]
WHERE t1.Status='In Use'
ORDER BY t1.[Date]
now we have all the records, where the first status is in use, and the second is not in use, but with each date in use, we get to much records where it is out of use. That is something we will take care of later. for now we want to calculate the difference between the two dates, we can do that using the datediff function:
SELECT *, DATEDIFF(Day, t1.[Date], t2.[Date]) As DaysInUse
FROM test t1
INNER JOIN test t2
ON t1.Status <> t2.Status AND t1.[Date] < t2.[Date]
WHERE t1.Status='In Use'
ORDER BY t1.[Date]
what we see now is almost what we want, we only want the records for each first date where the DaysInUse is the minimum value, so we select the minimum value for this column. and group by firstdate. Using the group by, we have to use aggregate functions on all returned fields, or use the other fields in the group by clause, so now we have to decide which fields we need. As far as i can tell, this is the query you need:
SELECT t1.Cont_ID,
t1.[Date] AS StartDate,
MIN(DATEDIFF(Day, t1.[Date], t2.[Date])) AS DaysInUse
FROM test t1
INNER JOIN test t2 ON t1.Status <> t2.Status AND t1.[Date] < t2.[Date]
WHERE (t1.Status = 'In Use') AND (DATEDIFF(Day, t1.[Date], t2.[Date]) > 0)
GROUP BY t1.Cont_ID, t1.[Date]
ORDER BY t1.[Date]

in the where clause, you can add a startdate (t1.[Date]) and an end date (t2.[Date]) to select the number of days in use in a specific timeframe or select the values for a specific Cont_ID