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.
Author |
Topic |
pnasz
Posting Yak Master
101 Posts |
Posted - 2013-04-16 : 03:19:29
|
ID Date TimeIn TimeOut 123 15/04/2013 08:00 11:55 123 15/04/2013 08:10 11:58 123 15/04/2013 16:00 22:05 123 15/04/2013 16:03 22:10 What to transfer data from the above table in the formatGiven Below ID Date In[Shift1] Out[Shift1] In[Shift2] Out[Shift2]123 15/04/2013 08:00 11:58 16:00 22:10 |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-16 : 03:37:35
|
--This??SELECT ID,[Date], MIN(CASE WHEN TimeIn BETWEEN '08:00:00' AND '12:00:00' THEN TimeIn END) Shift1In, MAX(CASE WHEN TimeOut BETWEEN '08:00:00' AND '12:00:00' THEN [TimeOut] END) Shift1Out, MIN(CASE WHEN TimeIn BETWEEN '12:00:00' AND '23:59:59' THEN TimeIn END) Shift2In, MAX(CASE WHEN TimeOut BETWEEN '12:00:00' AND '23:59:59' THEN [TimeOut] END) Shift2OutFROM @ShiftsGROUP BY ID, [Date] --Chandu |
 |
|
pnasz
Posting Yak Master
101 Posts |
Posted - 2013-04-16 : 07:56:25
|
ID Date TimeIn123 15/04/2013 08:00123 15/04/2013 08:10123 15/04/2013 10:00123 15/04/2013 12:00123 15/04/2013 16:30123 15/04/2013 17:00123 15/04/2013 18:00123 15/04/2013 20:00123 15/04/2013 22:30What will be if the table in this format and there are two shiftShift 1=9:00-12:00SHift 2=16:30-22:30How do i tranfer the data from the above table in format given below.ID Date IN-SHIFT1 OUT-SHIFT1 IN-SHIFT2 OUT-SHIFT2123 15/04/2013 8:00 12:00 16:30 22:30 |
 |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-16 : 08:14:14
|
[code]DECLARE @Shifts TABLE(ID INT, [Date] DATE, TimeIn TIME)insert into @ShiftsSELECT 123, '15/04/2013', '08:00:00' union allSELECT 123, '15/04/2013', '08:10:00' union all SELECT 123, '15/04/2013', '10:00:00' union all SELECT 123, '15/04/2013', '12:00:00' union all SELECT 123, '15/04/2013', '16:30:00' union all SELECT 123, '15/04/2013', '17:00:00' union all SELECT 123, '15/04/2013', '18:00:00' union all SELECT 123, '15/04/2013', '20:00:00' union allSELECT 123, '15/04/2013', '22:30:00'/*What will be if the table in this format and there are two shiftShift 1=9:00-12:00SHift 2=16:30-22:30How do i tranfer the data from the above table in format given below.ID Date IN-SHIFT1 OUT-SHIFT1 IN-SHIFT2 OUT-SHIFT2123 15/04/2013 8:00 12:00 16:30 22:30 */SELECT ID,[Date], MIN(CASE WHEN TimeIn <= '12:00:00' THEN TimeIn END) Shift1In, MAX(CASE WHEN TimeIn <= '12:00:00' THEN TimeIn END) Shift1Out, MIN(CASE WHEN TimeIn BETWEEN '16:30:00' AND '22:30:00' THEN TimeIn END) Shift2In, MAX(CASE WHEN TimeIn BETWEEN '16:30:00' AND '22:30:00' THEN TimeIn END) Shift2OutFROM @ShiftsGROUP BY ID, [Date][/code]--Chandu |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-16 : 10:45:27
|
i think this is safer considering the first sample data tooSELECT ID,[Date], MIN(CASE WHEN TimeIn <= '12:00:00' THEN TimeIn END) Shift1In, MAX(CASE WHEN TimeOut <= '12:00:00' THEN TimeOut END) Shift1Out, MIN(CASE WHEN TimeIn > '12:00:00' THEN TimeIn END) Shift2In, MAX(CASE WHEN TimeOut > '12:00:00' THEN TimeOut END) Shift2OutFROM @ShiftsGROUP BY ID, [Date] ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
|
|
|
|