I just can't get my head around this query 1

[hoggle]

I have a table that contains a start and end time.
ID start end
1 1/15/2004 12:00:00 PM 1/15/2004 2:00:00 PM
2 1/15/2004 10:30:00 PM 1/15/2004 11:00:00 PM
3 1/15/2004 11:00:00 PM 1/16/2004 12:30:00 AM

what I need is the total minutes between the 2 times for a date I pass in. But since id 3 rolls over into the next day I only want the total minutes up until 11:59, for the 15th and then I want the remaining minutes to be associated with the 16th

so it would end up something like this
date total minutes
1/15/2004 209
because
1 120
2 30
3 59

1/16/2004 30
because
3 30


hopefully that makes enough sense.

 

[JayKusch]

Your not going to be able to aquire that with a simple query. This query will give you the minutes for the ones where the start/end dates are w/in the same day ...

SELECT DateDiff(mm,Start,End) as TotalMinutes
FROM MyTable



Thanks

J. Kusch

 

[nigelrivett]

select datediff(mi,case when startdte < @d then @d else startdte end, case when enddte > @d+1 then @d+1 else enddte end)
from tbl
where @d between convert(varchar(8),startdte,112) and convert(varchar(8),enddte,112)

not sure why 3 = 59 but you may need
dateadd(mi,-1,@d+1) on the enddte bit to get that.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[hoggle]

nigelrivett that query works! Thanks for the help, for some reason I couldn't get the case selection working correctly when I was trying to create it...
thanks again