Join with conditions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Join with conditions

Author

Topic

newbie007
Starting Member

37 Posts

Posted - 2009-04-16 : 11:40:02

I have a table (T1) which has start and endtime entered as (eg) 10015 , 12528 etc. ie to represent 01:00:15 & 01:25:28. The data is entered as integer. How do I compute

1)The difference between end and start time in minutes. Wherever the output is negative , ( when start time > end time,ie, end time spilling over to next day. No separate column for start/end days.but it will spill over only till next day & not later than that ) how can the query correct that also ? Do I need to change the data type ?

2)My 2nd table T2 again has start time and end time in the same format as above but ‘ss’ will be zeros(data type integer). I need to retrieve a column from T1 subject to

a.Start time of T2 >= start time of T1 and
end time of T2 <=end time of T1 ( ie, start time to end time of T2 should lie between start time to end time of T1. endtime - start time for T2 cannot be greater than the duration computed in 1 above)

b)BUT if the start time of T2>= start time of T1 and end time of T2 is also > end time of T1, then for that row, the start time will be same as T2 start time but end time will be the end time of T1. The entire row needs to get repeated again with its start time as end time or previous row, end time as original T2 end time & retrieve column form T1 subject again to conditions a & b

The attached data table explains it better - the input tables T1,T2 and what I want as output

[URL=http://img21.imageshack.us/my.php?image=datao.png][IMG]http://img21.imageshack.us/img21/5

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 14:20:02

 SELECT CASE WHEN DATEDIFF(mi,STUFF(STUFF(RIGHT('0' + CAST(starttime AS varchar(6)),6),3,0,':'),6,0,':'),STUFF(STUFF(RIGHT('0' + CAST(endtime AS varchar(6)),6),3,0,':'),6,0,':')) > 0 THEN DATEDIFF(mi,STUFF(STUFF(RIGHT('0' + CAST(starttime AS varchar(6)),6),3,0,':'),6,0,':'),STUFF(STUFF(RIGHT('0' + CAST(endtime AS varchar(6)),6),3,0,':'),6,0,':')) ELSE 1440 - DATEDIFF(mi,STUFF(STUFF(RIGHT('0' + CAST(starttime AS varchar(6)),6),3,0,':'),6,0,':'),STUFF(STUFF(RIGHT('0' + CAST(endtime AS varchar(6)),6),3,0,':'),6,0,':')) END AS Duration FROM Table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources