SQL Statement to combine 2 records into 1 row.
in [Programming SQL]
|
Prev: Classic ASP: Using parameters -- Help needed please!
Next: A job to script out Tables and prosedures.
From: Red jack on 17 Jul 2008 23:27 Not very sure how to explain this. but perhaps with my illustration, you guys will be able to understand. Record stored in table StaffID Date / Time Status 1234 16/07/2008 8:00am In 1234 16/07/2008 5:00pm Out 2345 16/07/2008 8:05am In 2345 16/07/2008 5:00pm Out Need to generate report 1234 16/07/2008 8:00am In 16/07/2008 5:00pm Out 2345 16/07/2008 8:05am In 16/07/2008 5:00pm Out
From: Plamen on 18 Jul 2008 00:12 Assuming you have one pair of dates per day you can pivot like this: SELECT staffid, MAX(CASE WHEN status = 'In' THEN date_time END) AS date_time_in, MAX(CASE WHEN status = 'Out' THEN date_time END) AS date_time_out FROM Foo GROUP BY staffid, DATEDIFF(DAY, 0, date_time); HTH, Plamen Ratchev http://www.SQLStudio.com
From: Rahul on 18 Jul 2008 01:28 On Jul 18, 9:12 am, Pla...(a)sqlstudio.com wrote: > Assuming you have one pair of dates per day you can pivot like this: > > SELECT staffid, > MAX(CASE WHEN status = 'In' THEN date_time END) AS > date_time_in, > MAX(CASE WHEN status = 'Out' THEN date_time END) AS > date_time_out > FROM Foo > GROUP BY staffid, DATEDIFF(DAY, 0, date_time); > > HTH, > > Plamen Ratchevhttp://www.SQLStudio.com Try this, Create Table dbo.OneLine ( StaffID Int, [Date/Time] DateTime, Status VarChar(3) ) Delete From OneLine Insert Into OneLine(StaffID,[Date/Time],Status) Select 1234, Cast('07/16/2008 08:00' As DateTime), 'In' Union Select 1234, Cast('07/16/2008 17:00' As DateTime), 'Out' Union Select 2345, Cast('07/16/2008 08:05' As DateTime), 'In' Union Select 2345, Cast('07/16/2008 13:00' As DateTime), 'Out' Union Select 1234, Cast('07/17/2008 08:00' As DateTime), 'In' Union Select 1234, Cast('07/17/2008 17:00' As DateTime), 'Out' Union Select 2345, Cast('07/17/2008 08:05' As DateTime), 'In' Union Select 2345, Cast('07/17/2008 13:00' As DateTime), 'Out' Select A.StaffID, A.[Date/Time], A.Status, B.[Date/Time], B.Status From (Select * From OneLine Where Status = 'In') A Inner Join (Select * From OneLine Where Status = 'Out') B On A.StaffID = B.StaffID And Convert(Char(8), A.[Date/Time], 112) = Convert(Char(8), B.[Date/Time], 112) Rahul
From: Rahul on 18 Jul 2008 01:37
On Jul 18, 10:28 am, Rahul <verma.car...(a)gmail.com> wrote: > On Jul 18, 9:12 am, Pla...(a)sqlstudio.com wrote: > > > Assuming you have one pair of dates per day you can pivot like this: > > > SELECT staffid, > > MAX(CASE WHEN status = 'In' THEN date_time END) AS > > date_time_in, > > MAX(CASE WHEN status = 'Out' THEN date_time END) AS > > date_time_out > > FROM Foo > > GROUP BY staffid, DATEDIFF(DAY, 0, date_time); > > > HTH, > > > Plamen Ratchevhttp://www.SQLStudio.com > > Try this, > > Create Table dbo.OneLine > ( > StaffID Int, > [Date/Time] DateTime, > Status VarChar(3) > ) > > Delete From OneLine > > Insert Into OneLine(StaffID,[Date/Time],Status) > Select 1234, Cast('07/16/2008 08:00' As DateTime), 'In' > Union > Select 1234, Cast('07/16/2008 17:00' As DateTime), 'Out' > Union > Select 2345, Cast('07/16/2008 08:05' As DateTime), 'In' > Union > Select 2345, Cast('07/16/2008 13:00' As DateTime), 'Out' > Union > Select 1234, Cast('07/17/2008 08:00' As DateTime), 'In' > Union > Select 1234, Cast('07/17/2008 17:00' As DateTime), 'Out' > Union > Select 2345, Cast('07/17/2008 08:05' As DateTime), 'In' > Union > Select 2345, Cast('07/17/2008 13:00' As DateTime), 'Out' > > Select A.StaffID, A.[Date/Time], A.Status, B.[Date/Time], B.Status > From > (Select * From OneLine Where Status = 'In') A > Inner Join > (Select * From OneLine Where Status = 'Out') B > On A.StaffID = B.StaffID And Convert(Char(8), A.[Date/Time], 112) = > Convert(Char(8), B.[Date/Time], 112) > > Rahul Some improvement Create Table dbo.OneLine ( StaffID Int, [Date/Time] DateTime, Status VarChar(3) ) Delete From OneLine Insert Into OneLine(StaffID,[Date/Time],Status) Select 1234, Cast('07/16/2008 08:00' As DateTime), 'In' Union Select 1234, Cast('07/16/2008 17:00' As DateTime), 'Out' Union Select 2345, Cast('07/16/2008 08:05' As DateTime), 'In' Union Select 2345, Cast('07/16/2008 13:00' As DateTime), 'Out' Union Select 1234, Cast('07/17/2008 11:00' As DateTime), 'In' Union Select 1234, Cast('07/18/2008 01:30' As DateTime), 'Out' Union Select 2345, Cast('07/17/2008 08:05' As DateTime), 'In' Union Select 2345, Cast('07/17/2008 13:00' As DateTime), 'Out' Select A.StaffID, A.[Date/Time], A.Status, B.[Date/Time], B.Status From (Select * From OneLine Where Status = 'In') A Inner Join (Select * From OneLine Where Status = 'Out') B On A.StaffID = B.StaffID And (Convert(Char(8), A.[Date/Time], 112) = Convert(Char(8), B.[Date/ Time], 112) Or DateDiff(Hour,A.[Date/Time], B.[Date/Time]) Between 0 And 24) Rahul |