无法生成我的报告

Question

我必须生成以下格式的报告：

工作人员姓名 dateintime dateouttime 持续时间

例如：

员工代码姓名 01-08-2010intime 01-08-20100uttime 01-08-2010duration

1001 阿米特 09:00 18:30 09:30

在 sql server management studio 中 5 天，我编写了这样的查询

Declare @intime table
(
Staffcode varchar(7),
Name varchar(100),
[01-08-2010 InTime] datetime,
[02-08-2010 InTime] datetime,
[03-08-2010 InTime] datetime,
[04-08-2010 InTime] datetime,
[05-08-2010 InTime] datetime
)

Declare @outtime table
(
Staffcode varchar(7),
Name varchar(100),
[01-08-2010 outTime] datetime,
[02-08-2010 outTime] datetime,
[03-08-2010 outTime] datetime,
[04-08-2010 outTime] datetime,
[05-08-2010 outTime] datetime
)

Declare @Duration table
(
Staffcode varchar(7),
Name varchar(100),
[01-08-2010 Duration] datetime,
[02-08-2010 Duration] datetime,
[03-08-2010 Duration] datetime,
[04-08-2010 Duration] datetime,
[05-08-2010 Duration] datetime
)

Insert into @Intime
SELECT
StaffCode,
Name,
[01-08-2010] as [01-08-2010],
[02-08-2010] as [02-08-2010],
[03-08-2010] as [03-08-2010],
[04-08-2010] as [04-08-2010],
[05-08-2010] as [05-08-2010]
FROM
(
SELECT
StaffCode,
Name,
CONVERT(VARCHAR(10),AttendanceDate,105) AttendanceDate,
CONVERT(VARCHAR(5),MIN(FirstPunch),108) InTime
FROM AttendanceLog
JOIN Staff on Staff.Id = AttendanceLog.StaffId
WHERE AttendanceDate BETWEEN '2010-08-01' And '2010-08-05'
AND Staffcode BETWEEN '10001' AND '10999'
AND name <>'' and workstatus = 'Is Working'
GROUP BY
name,StaffCode,AttendanceDate
)p

PIVOT
(
  MIN(InTime)
  FOR AttendanceDate
  IN
(
[01-08-2010],
[02-08-2010],
[03-08-2010],
[04-08-2010],
[05-08-2010]
)
)AS pvt


Insert into @Outtime

SELECT
StaffCode,
Name,
[01-08-2010] as [01-08-2010],
[02-08-2010] as [02-08-2010],
[03-08-2010] as [03-08-2010],
[04-08-2010] as [04-08-2010],
[05-08-2010] as [05-08-2010]
FROM
(
SELECT
StaffCode,
Name,
CONVERT(VARCHAR(10),AttendanceDate,105) AttendanceDate,
CONVERT(VARCHAR(5),MAX(LastPunch),108) OutTime
FROM AttendanceLog
JOIN Staff on Staff.Id = AttendanceLog.StaffId
WHERE AttendanceDate BETWEEN '2010-08-01' And '2010-08-05'
AND Staffcode BETWEEN '10001' AND '10999'
AND name <>'' and workstatus = 'Is Working'
GROUP BY
name,StaffCode,AttendanceDate
)p

PIVOT
(
  MAX(OutTime)
  FOR AttendanceDate
  IN
(
[01-08-2010],
[02-08-2010],
[03-08-2010],
[04-08-2010],
[05-08-2010]
)
)AS pvt

Insert into @Duration

SELECT
StaffCode,
Name,
[01-08-2010] as [01-08-2010],
[02-08-2010] as [02-08-2010],
[03-08-2010] as [03-08-2010],
[04-08-2010] as [04-08-2010],
[05-08-2010] as [05-08-2010]
FROM
(
SELECT
StaffCode,
Name,
CONVERT(VARCHAR(10),AttendanceDate,105) AttendanceDate,
CONVERT(VARCHAR(5),(MAX(LastPunch) - MIN(FirstPunch)),114) Duration
FROM AttendanceLog
JOIN Staff on Staff.Id = AttendanceLog.StaffId
WHERE AttendanceDate BETWEEN '2010-08-01' And '2010-08-05'
AND Staffcode BETWEEN '1001' AND '1999'
AND name <>'' and workstatus = 'Is Working'
GROUP BY
name,StaffCode,AttendanceDate
)p

PIVOT
(
  MAX(Duration)
  FOR AttendanceDate
  IN
(
[01-08-2010],
[02-08-2010],
[03-08-2010],
[04-08-2010],
[05-08-2010]
)
)AS pvt

Select

I.StaffCode,
I.Name,
CONVERT(VarCHAR(5),[01-08-2010 InTime],114)  [01-08-2010 InTime],
CONVERT(VarCHAR(5),[01-08-2010 OutTime],114) [01-08-2010 OutTime],
CONVERT(VarCHAR(5),[01-08-2010 Duration],114)[01-08-2010 Duration],
CONVERT(VarCHAR(5),[02-08-2010 InTime],114)     [02-08-2010 InTime],
CONVERT(VarCHAR(5),[02-08-2010 OutTime],114) [02-08-2010 OutTime],
CONVERT(VarCHAR(5),[02-08-2010 Duration],114)[02-08-2010 Duration],
CONVERT(VarCHAR(5),[03-08-2010 InTime],114)     [03-08-2010 InTime],
CONVERT(VarCHAR(5),[03-08-2010 OutTime],114) [03-08-2010 OutTime],
CONVERT(VarCHAR(5),[03-08-2010 Duration],114)[03-08-2010 Duration],
CONVERT(VarCHAR(5),[04-08-2010 InTime],114)     [04-08-2010 InTime],
CONVERT(VarCHAR(5),[04-08-2010 OutTime],114) [04-08-2010 OutTime],
CONVERT(VarCHAR(5),[04-08-2010 Duration],114)[04-08-2010 Duration],
CONVERT(VarCHAR(5),[05-08-2010 InTime],114)     [05-08-2010 InTime],
CONVERT(VarCHAR(5),[05-08-2010 OutTime],114) [05-08-2010 OutTime],
CONVERT(VarCHAR(5),[05-08-2010 Duration],114)[05-08-2010 Duration]
From @Intime I
JOIN @Outtime O on I.StaffCode=O.StaffCode
JOIN @Duration D on I.StaffCode=D.Staffcode
order by Staffcode

从这个查询我得到 intime，在我公司工作的所有员工的超时和持续时间..

我也生成了这 5 天的报告..

但现在我想生成 n 天的报告...

为此我需要做的。

但我只想要列中的所有日期..

请任何人告诉我该怎么做...

请告诉我我需要在查询中更改什么？？？

我正在使用 sql server 2005.. 我认为我们需要使用 while 循环的另一件事...... 但我很困惑如何使用以及在哪里使用.. 因为我是这个领域的新手..

谢谢&问候

Answer 1

首先，您不需要三个表来表示“入”、“出”时间和持续时间。您只需要一个能够将此数据合并到单个 CTE 中的工具。

然后，您需要为给定的时间跨度生成 varchar 格式的天数数组。

declare @from datetime = '20100901', @to datetime = '20100910'
;with Calendar as
(
  SELECT CAST(@from as datetime) AS [date]
  UNION ALL
  SELECT DATEADD(dd, 1, [date])
  FROM Calendar
  WHERE DATEADD(dd, 1, [date]) <= @to
)
SELECT substring(convert(varchar, [date], 121), 1, 10) Day
FROM Calendar c
OPTION (MAXRECURSION 0);

然后，您可以将这些日期组合成一个字符串，并将其插入到 SQL 代码中使用这些日期的位置。

然后，您将使用 exec 命令运行这个庞大的查询。

旁注。使用 SQL 来完成此任务非常酷，但就我个人而言，我会安装报表生成器并根据您拥有的平面数据构建此报表，并且有各种可用的聚合函数在那里，您无法轻松地仅使用 sql 来模拟而不使用任何辅助函数。

更新。
这是一个实际的动态枢轴的实现。