How to count every 15 Mins duration group by employees(http://forums.asp.net/t/1808316.aspx/1?How+to+count+every+15+Mins+duration+group+by+employees)

I have seen such type of scenario. so i am providing the easiest way to handle it.

Table format:-

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
——————-

Sl.No TransDate StartTime End Time Count (No. Of Transaction)
Group by employee
1 2012-05-2012 8:00 8:15 2 Raghu and Johnson
2 2012-05-2012 8:15 8:30 1 Raju
3 2012-05-2012 8:30 8:45 2 Albert and Abdul

Solution:-

CREATE Table TranTbl (EmployeeID Int,[Name] Varchar(100),[Date] DateTime)

Insert Into TranTbl (EmployeeID, [Name], [Date])
Select 101, ‘Abdul’, ‘2012-05-12 08:40:30’ Union
Select 101, ‘Abdul’, ‘2012-05-12 09:40:30’ Union
Select 101, ‘Abdul’, ‘2012-05-12 12:20:30’ Union
Select 102, ‘Raju’, ‘2012-05-12 08:15:30’ Union
Select 102, ‘Raju’, ‘2012-05-12 12:40:30’ Union
Select 102, ‘Raju’, ‘2012-05-12 16:20:30’ Union
Select 103, ‘Albert’, ‘2012-05-12 08:40:30’ Union
Select 103, ‘Albert’, ‘2012-05-12 10:40:30’ Union
Select 103, ‘Albert’, ‘2012-05-12 12:20:30’ Union
Select 103, ‘Albert’, ‘2012-05-12 14:40:30’ Union
Select 103, ‘Albert’, ‘2012-05-12 15:40:30’ Union
Select 103, ‘Albert’, ‘2012-05-12 17:10:30’ Union
Select 103, ‘Albert’, ‘2012-05-12 17:15:30’ Union
Select 104, ‘Raghu’, ‘2012-05-12 08:00:30’ Union
Select 104, ‘Raghu’, ‘2012-05-12 09:40:30’ Union
Select 104, ‘Raghu’, ‘2012-05-12 12:20:30’ Union
Select 102, ‘Johnson’, ‘2012-05-12 08:00:30’ Union
Select 102, ‘Johnson’, ‘2012-05-12 12:40:30’ Union
Select 102, ‘Johnson’, ‘2012-05-12 16:20:30’

GO

CREATE PROCEDURE GetExpectedOutput
AS
BEGIN

DECLARE @startTime DATETIME = ‘8:00’,
@endTime DATETIME = ‘7:15’

IF @endTime< @startTime
BEGIN
SELECT @endTime= DATEADD(DAY, 1, @endTime)
END

; WITH rcte AS
(
SELECT StartTime = DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, 0, @startTime) / 15.0) * 15, 0)
,EndTime = DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, 0, @startTime) / 15.0) * 15, 0)

UNION ALL

SELECT StartTime = DATEADD(MINUTE, 0, EndTime)
,EndTime = DATEADD(MINUTE, 15, EndTime)
FROM rcte
WHERE DATEADD(MINUTE, 15, EndTime) <= @endTime
)

SELECT ROW_NUMBER() OVER(ORDER BY StartTime) AS Rno
,CONVERT(TIME,StartTime) StartTime
,CONVERT(TIME,EndTime) EndTime INTO #tmp
FROM rcte
WHERE StartTime<>EndTime
SELECT H1.*
,H2.Name INTO #tmp2 FROM
(SELECT * FROM #tmp A CROSS JOIN ( SELECT DISTINCT CONVERT(DATE,[date])TransDate FROM TranTbl) B )H1
JOIN
(SELECT * FROM TranTbl) H2
ON H1.TransDate=CONVERT(DATE,H2.[Date]) AND CONVERT(TIME,H2.[Date]) BETWEEN H1.StartTime AND H1.EndTime
ORDER BY H1.StartTime,H1.EndTime
SELECT ROW_NUMBER() OVER(ORDER BY StartTime) AS [Sl.No]
,TransDate
,CONVERT(VARCHAR(5), StartTime, 108)StartTime
,CONVERT(VARCHAR(5), EndTime, 108)EndTime
,COUNT(name) AS [No. Of Transaction]
,STUFF((SELECT ‘ and ‘ + CAST(t2.Name AS VARCHAR(MAX))
FROM #tmp2 t2 WHERE t1.Rno = t2.Rno AND t1.TransDate = t2.TransDate AND t1.StartTime = t2.StartTime AND t1.EndTime = t2.EndTime
FOR XML PATH(”)),1,5,”) [Group by employee]
FROM #tmp2 t1
GROUP BY Rno,TransDate,StartTime,EndTime
END
–EXEC GetExpectedOutput

Advertisements

About jeeveshfuloria

i am working in iSpace global solution pvt. ltd. in MSBI field and pursuing b.tech through AMIE.
This entry was posted in SQL SERVER. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s