如何按员工每15分钟工时分组进行计数 [英] How to count every 15 Mins duration group by employees
问题描述
我要形成查询.
我的员工人数众多，他们每天进行的交易更多.
我想要每个使用分组依据的员工的第一次交易时间(开始时间).
我想使用group by查询每15分钟从"08:00"到"17:15"开始的交易.
我有点困惑如何形成此查询.
任何人都可以帮助如何形成查询..?
查看我的样本表数据:
-------------------------
EmpID名称TransactionDate
101 Abdul 2012-05-2012 08:40:30 0000
101 Abdul 2012-05-2012 09:40:30 0000
101 Abdul 2012-05-2012 12:20:30 0000
102 Raju 2012-05-2012 08:15:30 0000
102 Raju 2012-05-2012 12:40:30 0000
102 Raju 2012-05-2012 16:20:30 0000
103艾伯特2012-05-2012 08:40:30 0000
103艾伯特2012-05-2012 10:40:30 0000
103艾伯特2012-05-2012 12:20:30 0000
103艾伯特2012-05-2012 14:40:30 0000
103艾伯特2012-05-2012 15:40:30 0000
103艾伯特2012-05-2012 17:10:30 0000
103艾伯特2012-05-2012 17:15:30 0000
104拉格2012-05-2012 08:00:30 0000
104 Raghu 2012-05-2012 09:40:30 0000
104 Raghu 2012-05-2012 12:20:30 0000
102约翰逊2012-05-2012 08:00:30 0000
102约翰逊2012-05-2012 12:40:30 0000
102约翰逊2012-05-2012 16:20:30 0000
预期产量
---------------
I want to form the query .
I am having number of employees and they made more transaction per day.
I want First transaction time (Start time) for the for each employee using group by.
This i want to query with every 15 mins transaction starting from "08:00" to "17:15" using group by.
I am little bit confused how to form this query.
can anyone help how to form the query..?
See my sample table data:
-------------------------
EmpID Name TransactionDate
101 Abdul 2012-05-2012 08:40:30 0000
101 Abdul 2012-05-2012 09:40:30 0000
101 Abdul 2012-05-2012 12:20:30 0000
102 Raju 2012-05-2012 08:15:30 0000
102 Raju 2012-05-2012 12:40:30 0000
102 Raju 2012-05-2012 16:20:30 0000
103 Albert 2012-05-2012 08:40:30 0000
103 Albert 2012-05-2012 10:40:30 0000
103 Albert 2012-05-2012 12:20:30 0000
103 Albert 2012-05-2012 14:40:30 0000
103 Albert 2012-05-2012 15:40:30 0000
103 Albert 2012-05-2012 17:10:30 0000
103 Albert 2012-05-2012 17:15:30 0000
104 Raghu 2012-05-2012 08:00:30 0000
104 Raghu 2012-05-2012 09:40:30 0000
104 Raghu 2012-05-2012 12:20:30 0000
102 Johnson 2012-05-2012 08:00:30 0000
102 Johnson 2012-05-2012 12:40:30 0000
102 Johnson 2012-05-2012 16:20:30 0000
Expected Output
---------------
TransDate StartTime End Time "Count (No. Of Transaction)
Group by employee"
2012-05-2012 8:00 8:15 2 Raghu and Johnson
2012-05-2012 8:15 8:30 1 Raju
8:30 8:45 2 Albert and Abdul
请从"08:00"到"17:15"每15分钟给我正确的查询.
pls. give me the correct query for every 15 mins starting from "08:00" to "17:15"
推荐答案
加尼，
我们可以使用CTE获取所需的结果集，以获取过滤后的结果，并在其上使用STUFF函数来连接Employee名称.
注意:您的测试数据包含无效的日期(即 2012 -5- 2012 ..)
仔细阅读以下代码.
-创建温度表
Hi Gani,
We can get the desired result set using the CTEs to get the filtered result and STUFF function on it to concatenate the Employee names.
Note : Your test data contains invalid Date (ie2012-5-2012..)
Go through the following code.
--Creating Temp table
CREATE TABLE [dbo].[Temp](
[EmpId] [int] NULL,
[EmpName] [varchar](30) NULL,
[StartTime] [datetime] NULL
)
-将数据插入临时表
-- Insert Data into Temp table
INSERT INTO [Temp]([EmpId],[EmpName],[StartTime])VALUES
(101,'Abdul','2012-05-01 08:40:30.000'),
(101,'Abdul','2012-05-01 09:40:30.000'),
(101,'Abdul','2012-05-01 12:20:30.000'),
(102,'Raju','2012-05-01 08:15:30.000'),
(102,'Raju','2012-05-01 12:40:30.000'),
(102,'Raju','2012-05-01 16:20:30.000'),
(103,'Albert','2012-05-01 08:40:30.000'),
(103,'Albert','2012-05-01 10:40:30.000'),
(103,'Albert','2012-05-01 12:20:30.000'),
(103,'Albert','2012-05-01 14:40:30.000'),
(103,'Albert','2012-05-01 15:40:30.000'),
(103,'Albert','2012-05-01 17:10:30.000'),
(103,'Albert','2012-05-01 17:15:30.000'),
(104,'Raghu','2012-05-01 08:00:30.000'),
(104,'Raghu','2012-05-01 09:40:30.000'),
(104,'Raghu','2012-05-01 12:20:30.000'),
(102,'Johnson','2012-05-01 08:00:30.000'),
(102,'Johnson','2012-05-01 12:40:30.000'),
(102,'Johnson','2012-05-01 16:20:30.000')
DECLARE @FromDate DATETIME ='2012-05-01';
-- Common Table Expressions
WITH TimeSheetCte as (
SELECT DATEADD(hh,8,@FromDate) DateValue
UNION ALL select DATEADD(mi,15,DateValue)
FROM TimeSheetCte where DATEADD(mi,15,DateValue) < DATEADD(hh,17,@FromDate)
)
,DateRanges AS(
SELECT DateValue StartTime,DATEADD(mi,15,DateValue) EndTime FROM TimeSheetCte
)
,UsersWithTimeSlot As(
SELECT CONVERT(VARCHAR,DateRanges.StartTime,108) StatRange,CONVERT(VARCHAR,DateRanges.EndTime,108) EndRange
,ISNULL(Temp.EmpName,'-') EmpName,
ISNULL(CONVERT(VARCHAR,Temp.StartTime,108),'-') UserStartTime
,ISNULL(CONVERT(VARCHAR,Temp.StartTime,111),'-') StartDate
FROM DateRanges LEFT JOIN Temp ON Temp.StartTime >= DateRanges.StartTime
AND Temp.StartTime <=DateRanges.EndTime
)
--Query to Display the desired result set
SELECT COUNT(*) [No.Transactions], Info.StartDate,StatRange As StartRange,EndRange
,REPLACE(STUFF( (
SELECT ','+ EmpName FROM UsersWithTimeSlot
WHERE StatRange = Info.StatRange AND EndRange = Info.EndRange
FOR XML PATH('')),1,1,''
),',',' and ') EmployeeName
FROM (SELECT * fROM UsersWithTimeSlot WHERE EmpName <> '-' AND UserStartTime <> '-') Info
Group By Info.StatRange,Info.EndRange,Info.StartDate
这篇关于如何按员工每15分钟工时分组进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
