Help with crosstab query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Help with crosstab query

Help with crosstab query

Rate Topic

Display Mode

Topic Options

Author

Message

goodguy

Posted Wednesday, December 15, 2010 1:20 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, October 01, 2012 3:30 PM
Points: 292, Visits: 1,028

Hi, all. This is not a SQL Server issue but Access 2007.

I have a table CHECKINOUT which contains three main columns, USERID (number), CHECKTIME (date/time), and CHECKTYPE (text), which contains sample data as below (CSV format):
1, 2010-12-01 08:12:11, I.
2, 2010-12-01 08:33:25, I.
1. 2010-12-01 09:25:45, 0.
2. 2010-12-01 11:15:15, 0.
1. 2010-12-01 14:33:55, 1.
2. 2010-12-01 15:11:22, 1.
2, 2010-12-01 15:35:44, 0.
2. 2010-12-01 16:22:33, 1.
1, 2010-12-01 17:44:01, O.
2, 2010-12-01 18:02:37, O.

CHECKTYPE 'I' and 'O' indicate normal clocking in and out respectively, while '0' and '1' indicate clocking out and back in for field jobs.

I want to write a report based on a crosstab query to return records for field jobs and the time taken as follows:
Date: 2010-12-01
USERID OUT IN DURATION
1 09:25:45 14:33:55 05:08:10
2 11:15:15 15:11:22 03:56:07.
2 15:35:44 16:22:33 00:56:49.

How can I write a crosstab query that will:
1. Filter for CHECKTYPES '0' and '1'.
2. Group records by date only (not date/time).
3. Pair step-out records with step-in records by user and date.

Any help will be appreciated.

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

Post #1035441

« Prev Topic | Next Topic »

Permissions