SQL Statement to combine 2 records into 1 row.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Red jack - 18 Jul 2008 04:27 GMT

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

Reply to this Message

Plamen@sqlstudio.com - 18 Jul 2008 05:12 GMT

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

Reply to this Message

Rahul - 18 Jul 2008 06:28 GMT

On Jul 18, 9:12 am, Pla...@sqlstudio.com wrote:

> Assuming you have one pair of dates per day you can pivot like this:
>
[quoted text clipped - 9 lines]
>
> 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

Reply to this Message

Rahul - 18 Jul 2008 06:37 GMT

> On Jul 18, 9:12 am, Pla...@sqlstudio.com wrote:
>
[quoted text clipped - 49 lines]
>
> 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

Reply to this Message