February 14, 2012 at 11:50 pm
Following are the table details
1.
name: time_tbl
columns:
time_id
employee_id
time_date (date)
time_start(time)
time_end(time)
time_details(text)
2.
name: employee_tbl
columns:
employee_id
employee_name(varchar)
employee_join_date(datetime)
Everyday employees enter their time entries and their task details. I want to have list of employees that did not fill their time entries between a given date range.
If the date range is more than one day and the employee has not entered time details for more than one day in between the range, then the employee name should appear more than once with respect to date.
Required output
date | employee_id | employee_name
February 15, 2012 at 4:07 am
--Build some sample data and DDL script
CREATE TABLE time_tbl (time_id INT IDENTITY(1,1), employee_id INT, time_date DATETIME,
time_start DATETIME, time_end DATETIME, time_details VARCHAR(MAX))
CREATE TABLE employee_tbl (employee_id INT IDENTITY(1,1), employee_name VARCHAR(200),
employee_join_date DATETIME)
--Generate 500 rows of random-ish data for time_tbl table
INSERT INTO time_tbl
SELECT TOP 500
employee_id, time_date,
DATEADD(DAY,-DATEDIFF(DAY,'1900-01-01 00:00:00',MIN(time_start)),MIN(time_start)) AS time_start,
DATEADD(HOUR,5,DATEADD(DAY,-DATEDIFF(DAY,'1900-01-01 00:00:00',MIN(time_start)),MIN(time_start))) AS time_end,
MAX(b.alpha) AS time_details
FROM (SELECT TOP 1000000
(ABS(CHECKSUM(NEWID())) % 10) + 1 AS employee_id,
ABS (CHECKSUM(NEWID())) % DATEDIFF(dd,'2011','2012') + CAST('2011' AS DATETIME) AS time_date,
RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2011','2012') + CAST('2011' AS DATETIME) AS time_start,
(ABS(CHECKSUM(NEWID())) % 26) + 1 AS seed
FROM master.dbo.syscolumns sc1) a
LEFT OUTER JOIN (SELECT MAX(SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',t1.N,t2.N-t1.N+1)) AS alpha, t1.N
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),
(9),(10),(11),(12),(13),(14),(15),
(16),(17),(18),(19),(20),(21),(22),
(23),(24),(25),(26)) t1(N)
CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),
(9),(10),(11),(12),(13),(14),(15),
(16),(17),(18),(19),(20),(21),(22),
(23),(24),(25),(26)) t2(N)
WHERE t1.N <= t2.N
GROUP BY t1.N) b ON a.seed = b.N
GROUP BY employee_id, time_date
ORDER BY time_date, employee_id
--Generate 10 rows of random-ish data from employee_tbl
INSERT INTO employee_tbl
SELECT employee_name, employee_join_date
FROM (SELECT TOP 10
alpha AS employee_name, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
FROM (SELECT MAX(SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',t1.N,t2.N-t1.N+1)) AS alpha, t1.N
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),
(9),(10),(11),(12),(13),(14),(15),
(16),(17),(18),(19),(20),(21),(22),
(23),(24),(25),(26)) t1(N)
CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),
(9),(10),(11),(12),(13),(14),(15),
(16),(17),(18),(19),(20),(21),(22),
(23),(24),(25),(26)) t2(N)
WHERE t1.N <= t2.N
GROUP BY t1.N) names) a
CROSS APPLY (SELECT MIN(time_date) FROM time_tbl WHERE employee_id = a.rn) b(employee_join_date)
Using the sample data above, here is my best guess: -
DECLARE @startRange DATETIME, @endRange DATETIME
SET @startRange = '2011-01-05'
SET @endRange = '2011-01-20'
;WITH t1(N) AS (SELECT 1 UNION ALL SELECT 1),
t2(N) AS (SELECT 1 FROM t1 x, t1 y),
t3(N) AS (SELECT 1 FROM t2 x, t2 y),
t4(N) AS (SELECT 1 FROM t3 x, t3 y),
Tally(N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM t4 x, t4 y),
Calendar(N) AS (SELECT DATEADD(DAY,N,startDate)
FROM Tally
CROSS APPLY (SELECT MIN(time_date), MAX(time_date) FROM time_tbl) b(startDate, endDate)
WHERE DATEADD(DAY,N,startDate) >= startDate AND DATEADD(DAY,N,startDate) <= endDate)
SELECT [date], employee_id, employee_name
FROM (SELECT N AS [date], empCalendar.employee_id, empCalendar.employee_name
FROM (SELECT employee_id, N, employee_name
FROM Calendar cal
CROSS APPLY employee_tbl emp) empCalendar
LEFT OUTER JOIN time_tbl timings ON empCalendar.employee_id = timings.employee_id AND empCalendar.N = timings.time_date
WHERE timings.time_date IS NULL) innerQuery
WHERE [date] >= @startRange AND [date] <= @endRange
One of my executions produced this result: -
date employee_id employee_name
----------------------- ----------- -----------------------------
2011-01-14 00:00:00.000 1 ABCDEFGHIJKLMNOPQRSTUVWXYZ
2011-01-15 00:00:00.000 2 BCDEFGHIJKLMNOPQRSTUVWXYZ
2011-01-15 00:00:00.000 5 EFGHIJKLMNOPQRSTUVWXYZ
2011-01-16 00:00:00.000 4 DEFGHIJKLMNOPQRSTUVWXYZ
2011-01-16 00:00:00.000 9 IJKLMNOPQRSTUVWXYZ
2011-01-18 00:00:00.000 10 JKLMNOPQRSTUVWXYZ
2011-01-20 00:00:00.000 8 HIJKLMNOPQRSTUVWXYZ
2011-01-20 00:00:00.000 9 IJKLMNOPQRSTUVWXYZ
The data I've created is randomised for each execution.
February 15, 2012 at 10:13 pm
I will try and let you know the result. Thanks
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply