您的位置: 首页  >  技术问答  >  如何创建签入/签出报告?

如何创建签入/签出报告?

分类: 技术问答 • 2022-05-03 10:23:06
问题描述：

嗨！

您能帮我在Crystal Reports中创建签入/签出报告吗?

sql表结构为

用户ID 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


报告应为

tbody>
用户名 日期 签入时间 2011年1月1日 16:30
889 2011年1月1日 08:32 16:42
885 1/1/2011 08:35 16:55


---编辑-
再次编辑.在修订版2中，我以错误的方式显示了预期的输出.请参阅修订版1(原始帖子).

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).

答

我会使用>内部加入 [ ^ ].

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

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


好吧，正如我所写的，我试图展示如何使用两种不同的方法来做同样的事情.
我的数据库名称:A_TEST
我在表Checkings中的数据如下:

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


---方法1:加入---
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 ---
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)




---方法2:PIVOT ---




--- METHOD 2: PIVOT ---

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)




---结果:---
两个示例的结果相同，仅列标题不同
---加入---

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


--- PIVOT ---

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



我不知道如何在Crystal Reports中使用它，但是我敢肯定该解决方案会有所帮助.可能我们需要创建一个存储过程(即:pvtCheckings)，然后将其用作Crystal Report的来源(怎么办?我不知道！):




--- 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!):
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


用法(在MS SQL Server Management Studio中):


USAGE (in MS SQL Server Management Studio):

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


在两个示例中，我们都需要提取日期和时间的一部分，因为DateTime字段包含日期和时间.在时间部分不同之前，我们无法等于结果.


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.


相关推荐