distinct quey

I am using the below:
datediff(hour,b.[Time Finish], b.[Time Start]
to calculate the difference between the Finish time and start time. However,
I would like to do so only for distinct Time finish and start values.
Table: Schedule
S.No Time St. Time fin. SID# Date
1 10:00 11:00 1234 1-dec-04
2 10:00 11:00 1235 1-dec-04
3 01:00 02:00 1236 1-dec-04
The table design is very bad (the SIDs should have been in a separate table
referencing one distinct row of schedule from the schedule table) but I am
stuck with this.
Is there a way I can get it to return:
2 for 1-dec-04 (2 because 11-10 =1 and 2-1 =1)
distinct datediff(hour,b.[Time Finish], b.[Time Start]
returns only 1 because "1" is the distinct value.
I would like to be able to do something like:
datediff(hour,distinct b.[Time Finish],distinct b.[Time Start])
which is wrong.
Can somebody please throw some light? Hope this is clear.
Thanks a lot!
VinceYou could select from a derived table:
select datediff(hour,[Time Finish], [Time Start]
from (select distinct [Time Finish], [Time Start] from [table]) AS d
"Vince" wrote:

> I am using the below:
> datediff(hour,b.[Time Finish], b.[Time Start]
> to calculate the difference between the Finish time and start time. Howeve
r,
> I would like to do so only for distinct Time finish and start values.
> Table: Schedule
> S.No Time St. Time fin. SID# Date
> 1 10:00 11:00 1234 1-dec-04
> 2 10:00 11:00 1235 1-dec-04
> 3 01:00 02:00 1236 1-dec-04
> The table design is very bad (the SIDs should have been in a separate tabl
e
> referencing one distinct row of schedule from the schedule table) but I am
> stuck with this.
> Is there a way I can get it to return:
> 2 for 1-dec-04 (2 because 11-10 =1 and 2-1 =1)
> distinct datediff(hour,b.[Time Finish], b.[Time Start]
> returns only 1 because "1" is the distinct value.
> I would like to be able to do something like:
> datediff(hour,distinct b.[Time Finish],distinct b.[Time Start])
> which is wrong.
> Can somebody please throw some light? Hope this is clear.
> Thanks a lot!
> Vince
>
>|||Thanks! Didn't know you could do that.
"KH" <KH@.discussions.microsoft.com> wrote in message
news:66A5062F-5144-4364-9509-0294EAAFA985@.microsoft.com...
> You could select from a derived table:
> select datediff(hour,[Time Finish], [Time Start]
> from (select distinct [Time Finish], [Time Start] from [table]) AS d
>
> "Vince" wrote:
>
However,
table
am