How to Create CheckIn / Out report ?

Question

5.00/5 (1 vote)

See more:

SQL-Server-2005

, +

Hi!

Can you help me to create CheckIn / Out report In Crystal Reports

sql Table Structure is

UserID	DateTime	CheckingType
888	1/1/2011 08:30:00	1
889	1/1/2011 08:32:00	1
885	1/1/2011 08:35:00	1
888	1/1/2011 16:30:00	2
889	1/1/2011 16:42:00	2
885	1/1/2011 16:55:00	2

Report Should be

UserID	Date	CheckIn Time	CheckOut Time
888	1/1/2011	08:30	16:30
889	1/1/2011	08:32	16:42
885	1/1/2011	08:35	16:55

--- EDIT --
Edited once again. In revision 2 i show expected output in wrong way. See revision 1 (oryginal post).

Posted 29-Jul-11 6:00am

Shanuka Fernando

Updated 31-Jul-11 2:25am

Maciej Los

v3

Add a Solution

Comments

Maciej Los 30-Jul-11 9:10am

Edited - using html tags for tables - losmac

2 solutions

Solution 1

I would use an inner join[^] for that.

SQL

SELECT tblA.[DateTime] AS [CheckIn Time], tblB.[DateTime] AS [CheckOut Time] FROM [YourTable] tblA INNER JOIN [YourTable] tblB ON tblA.UserID = tblB.UserID AND tblA.CheckingType <> tblB.CheckingType

Posted 30-Jul-11 7:27am

walterhevedeich

Updated 30-Jul-11 7:29am

v2

Comments

Maciej Los 31-Jul-11 4:58am

I'm not sure what you mean. I think the records are in the one table and i'm really sure Shanuka Fernando should use pivot tables to view results.
This is not solution.

walterhevedeich 31-Jul-11 5:10am

I have provided this as I'm not familiar with the PIVOT solution. I'm pretty sure though that you can do a join on the same table. You might want to post the PIVOT solution for OP's reference. I'm sure it will be helpful as well.

Maciej Los 31-Jul-11 6:35am

OK, i'll try to show solution using JOIN on the same table and using PIVOT table.

Shanuka Fernando 1-Aug-11 2:02am

Thanks my friend !!! can you help me to create a View out of this

walterhevedeich 1-Aug-11 2:23am

I believe the other solution was correct. Your question was edited by someone and have not done it correctly. Fortunately, he managed to modify it again to the correct result.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2011-07-31T01:13:00

OK, as i wrote, i try to show how to do the same using two different methods.

My database name: A_TEST
My data in the table Checkings looks like:

UserID	chDateTime	CheckingType
888	2011-01-01 08:30:00.000	1
889	2011-01-01 08:32:00.000	1
885	2011-01-01 08:35:00.000	1
888	2011-01-01 16:30:00.000	2
889	2011-01-01 00:42:00.000	2
885	2011-01-01 00:55:00.000	2
888	2011-01-02 08:30:00.000	1
889	2011-01-02 08:32:00.000	1
885	2011-01-02 08:35:00.000	1
888	2011-01-02 16:30:00.000	2
889	2011-01-02 00:42:00.000	2
885	2011-01-02 00:55:00.000	2

--- METHOD 1: JOIN ---

SQL

USE [A_TEST];

DECLARE @sqry NVARCHAR(2000)
DECLARE @fqry NVARCHAR(2000)
DECLARE @mqry NVARCHAR(2000)

--create subquery 
SET @sqry = 'SELECT UserID, CONVERT(DATETIME,CONVERT(NVARCHAR, YEAR([chDateTime])) + ''-'' + CONVERT(NVARCHAR, MONTH([chDateTime])) + ''-'' + CONVERT(NVARCHAR, DAY([chDateTime]))) AS [chDate], RIGHT([chDateTime],8) AS CheckInTime ' +
			'FROM [dbo].[Checkings]' +
			'WHERE CheckingType = 1'
--EXECUTE(@sqry)

--create subquery 
SET @fqry = 'SELECT UserID, CONVERT(DATETIME,CONVERT(NVARCHAR, YEAR([chDateTime])) + ''-'' + CONVERT(NVARCHAR, MONTH([chDateTime])) + ''-'' + CONVERT(NVARCHAR, DAY([chDateTime]))) AS [chDate], RIGHT([chDateTime],8) AS CheckOutTime ' +
			'FROM [dbo].[Checkings]' +
			'WHERE CheckingType = 2'
--EXECUTE(@fqry)

--show results from two subqueries on UserID for equals chDate
SET @mqry = 'SELECT tblA.[UserID], tblA.[chDate], tblA.[CheckInTime], tblB.[CheckOutTime] ' +
			'FROM (' + @sqry + ') AS tblA RIGHT OUTER JOIN (' + @fqry + ') AS tblB ON tblA.[UserID] = tblB.[UserID] ' +
			'WHERE tblA.chDate = tblB.chDate ' +
			'ORDER BY tblA.chDate'
EXECUTE(@mqry)

--- METHOD 2: PIVOT ---

SQL

USE [A_TEST];

DECLARE @sqry NVARCHAR(2000)
DECLARE @mqry NVARCHAR(2000)
DECLARE @cols NVARCHAR(2000)

-- STEP 1 - get pivoted columns names:
-- returns:
--   [1] - for CheckInTime
--   [2] - for CheckOutTime
SET @cols = STUFF((SELECT DISTINCT '],[' + CONVERT(NVARCHAR, [CheckingType])
					FROM [dbo].[Checkings]
					ORDER BY '],[' + CONVERT(NVARCHAR, [CheckingType])
			FOR XML PATH('')), 1, 2, '') + ']'
--Use: 'SELECT @cols AS [my_cols];' to see returned data

-- STEP 2 - get data for pivot table
SET @sqry = 'SELECT [UserID], CONVERT(DATETIME,CONVERT(NVARCHAR, YEAR([chDateTime])) + ''-'' + CONVERT(NVARCHAR, MONTH([chDateTime])) + ''-'' + CONVERT(NVARCHAR, DAY([chDateTime]))) AS [chDate],  RIGHT([chDateTime],8) AS chTime, CheckingType ' + 
			'FROM [dbo].[Checkings]'
--EXECUTE(@sqry)

-- STEP 3 - show results in the pivot table
-- 
SET @mqry = 'SELECT [UserId], [chDate], ' + @cols + ' ' + 
			'FROM (' + @sqry + ') AS DT ' +
			'PIVOT (MAX(DT.[chTime]) FOR DT.[CheckingType] IN ( ' + @cols  + ' )) AS PT ' +
			'ORDER BY PT.[chDate]'
EXECUTE(@mqry)

--- RESULTS: ---
results are the same in both examples, differ only column headers
--- JOIN ---

UserID	chDate	CheckInTime	CheckOutTime
885	2011-01-01 00:00:00.000	8:35AM	4:55PM
888	2011-01-01 00:00:00.000	8:30AM	4:35PM
889	2011-01-01 00:00:00.000	8:32AM	4:42PM
885	2011-01-02 00:00:00.000	8:35AM	4:55PM
888	2011-01-02 00:00:00.000	8:30AM	4:30PM
889	2011-01-03 00:00:00.000	8:32AM	4:42PM

--- PIVOT ---

UserID	chDate	1	2
885	2011-01-01 00:00:00.000	8:35AM	4:55PM
888	2011-01-01 00:00:00.000	8:30AM	4:35PM
889	2011-01-01 00:00:00.000	8:32AM	4:42PM
885	2011-01-02 00:00:00.000	8:35AM	4:55PM
888	2011-01-02 00:00:00.000	8:30AM	4:30PM
889	2011-01-03 00:00:00.000	8:32AM	4:42PM

I don't know how to use it in Crystal Reports but i'm sure the solution will be helpful. Probably, we need to create a stored procedure (i.e.: pvtCheckings) and then use it as a source of Crystal Report (how? i don't know!):

SQL

USE [A_TEST];
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE pvtCheckings 
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @sqry NVARCHAR(2000)
	DECLARE @mqry NVARCHAR(2000)
	DECLARE @cols NVARCHAR(2000)
	SET @cols = STUFF((SELECT DISTINCT '],[' + CONVERT(NVARCHAR, [CheckingType])
						FROM [dbo].[Checkings]
						ORDER BY '],[' + CONVERT(NVARCHAR, [CheckingType])
				FOR XML PATH('')), 1, 2, '') + ']'
	SET @sqry = 'SELECT [UserID], CONVERT(DATETIME,CONVERT(NVARCHAR, YEAR([chDateTime])) + ''-'' + CONVERT(NVARCHAR, MONTH([chDateTime])) + ''-'' + CONVERT(NVARCHAR, DAY([chDateTime]))) AS [chDate],  RIGHT([chDateTime],8) AS chTime, CheckingType ' + 
				'FROM [dbo].[Checkings]'
	SET @mqry = 'SELECT [UserId], [chDate], ' + @cols + ' ' + 
				'FROM (' + @sqry + ') AS DT ' +
				'PIVOT (MAX(DT.[chTime]) FOR DT.[CheckingType] IN ( ' + @cols  + ' )) AS PT ' +
				'ORDER BY PT.[chDate]'
	EXECUTE(@mqry)
END
GO

USAGE (in MS SQL Server Management Studio):

SQL

DECLARE @RC int
EXECUTE @RC = [A_TEST].[dbo].[pvtCheckings]

In both examples we need to extract part of date and time, becouse DateTime field includes date and time. Until time part is differ we can't equal results.