Minus the time problem?

Using SQL Sever 2005

From the below Query Am getting total worked time means Outtime - Intime, Suppose when I minus the Next day time means showing wrong time

Query

Select ID, Normal_Intime, Normal_Outtime, Date, Intime, outtime, CONVERT(char(8), CASE WHEN InTime < Outtime THEN CASE WHEN OutTime > Normal_Outtime THEN CAST(Normal_Outtime AS datetime) ELSE CAST(Outtime AS datetime) END - CASE WHEN InTime < Normal_Intime THEN CAST(Normal_Intime AS datetime) ELSE CAST(Intime AS datetime) END ELSE CASE WHEN InTime < Normal_Intime THEN CAST(Normal_Intime AS datetime) ELSE CAST(Intime AS datetime) END - CASE WHEN OutTime > Normal_Outtime THEN CAST(Normal_Outtim开发者_运维问答e AS datetime) ELSE CAST(Outtime AS datetime) END END, 8) AS workedtime from table1

Output

ID Normal_Intime, Normal_Outtime, Date Intime, Outtime, Worktime

01  10:00:00 19:00:00 01/09/2009 08:20:56 15:40:15  05:40:15
01  18:00:00 05:00:00 02/09/2009 15:00:59 08:20:16  13:00:00

First One is Correct, Second Worktime should give 11:00:00 Hours only because Intime 18:00:00 and Outtime is 05:00:00 (next day 05:00:00 am), Now It is working as 18:00:00 - 05:00:00 giving as 13:00:00. It should give only 11:00:00

How to minus the next day time.

Need Query Help

in case out time is less than in time then add 24 to it before minus

Don't use substraction, use the datediff function instead.