August 1, 2010 at 6:16 am
I have a table of unique ID's for employees called timekeep and a table of time records called timecard where employees enter their time. Timecard would have a date field for the day and the empID for employee and the number of hours worked on that day. I need to find which empID did not enter any time for a date between two date ranges say 7/1/2010 and 7/31/2010. Any help would be appreciated. We do not have a calendar table in the database.
August 1, 2010 at 7:58 am
june.troxel
In order to get a tested solution to your question, please post table definitions, sample data and required results. To assist you in doing that please post as outlined in the article which can be accessed by clicking on the first link in my signature block.
August 2, 2010 at 3:35 am
Hello,
I suppose this code could be the code you want,
SELECT * FROM timekeep
LEFT JOIN timecard
ON timekeep.empID = timecard.empID AND timeField BETWEEN @beginDate AND @endDate
WHERE timecard.empID IS NULL
Regards,
Francesc
August 3, 2010 at 4:59 am
I hope I did this right. I am pretty new to sql. So the idea is that I would query who had no time entry in timecard from 7/1 to 7/7 for an org of 100. The result would be:
7/3/2010 040677
7/5/2010 040677
7/2/2010 056789
7/6/2010 056789
7/7/2010 056789
The two ID's in the result set have an org of 100 and no record for the dates listed in timecard.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#timekeep','U') IS NOT NULL
DROP TABLE #timekeep
IF OBJECT_ID('TempDB..#timecard','U') IS NOT NULL
DROP TABLE #timecard
--===== Create the test table with
CREATE TABLE #timekeep
(
ID VARCHAR(10),
Org VARCHAR(4),
)
--===== Create the test table with
CREATE TABLE #timecard
(
Date DATETIME,
ID VARCHAR(10),
Hours DECIMAL(10,2)
)
INSERT INTO #timekeep
(ID,Org)
SELECT '040677', '100' UNION ALL
SELECT '056789', '100' UNION ALL
SELECT '006777', '200'
INSERT INTO #timecard
(Date,ID,Hours)
SELECT 'Jul 1 2010 12:00AM' '040677', '8' UNION ALL
SELECT 'Jul 2 2010 12:00AM' '040677', '3' UNION ALL
SELECT 'Jul 4 2010 12:00AM' '040677', '8' UNION ALL
SELECT 'Jul 6 2010 12:00AM' '040677', '5' UNION ALL
SELECT 'Jul 7 2010 12:00AM' '040677', '8' UNION ALL
SELECT 'Jul 1 2010 12:00AM' '056789', '8' UNION ALL
SELECT 'Jul 3 2010 12:00AM' '056789', '6' UNION ALL
SELECT 'Jul 4 2010 12:00AM' '056789', '8' UNION ALL
SELECT 'Jul 5 2010 12:00AM' '056789', '8' UNION ALL
SELECT 'Jul 1 2010 12:00AM' '006777', '8' UNION ALL
SELECT 'Jul 2 2010 12:00AM' '006777', '8' UNION ALL
SELECT 'Jul 3 2010 12:00AM' '006777', '8' UNION ALL
SELECT 'Jul 5 2010 12:00AM' '006777', '8' UNION ALL
SELECT 'Jul 7 2010 12:00AM' '006777', '8'
--===== Set the identity insert back to normal SET IDENTITY_INSERT #timekeep OFF
IDENTITY_INSERT #timecard OFF
August 3, 2010 at 6:41 am
with corrected setup...
-- setup
/*
CREATE TABLE #timekeep
(
ID VARCHAR(10),
Org VARCHAR(4),
)
CREATE TABLE #timecard
(
Date DATETIME,
ID VARCHAR(10),
Hours DECIMAL(10,2)
)
INSERT INTO #timekeep
(ID,Org)
SELECT '040677', '100' UNION ALL
SELECT '056789', '100' UNION ALL
SELECT '006777', '200'
INSERT INTO #timecard
(Date,ID,Hours)
SELECT 'Jul 1 2010 12:00AM', '040677', '8' UNION ALL
SELECT 'Jul 2 2010 12:00AM', '040677', '3' UNION ALL
SELECT 'Jul 4 2010 12:00AM', '040677', '8' UNION ALL
SELECT 'Jul 6 2010 12:00AM', '040677', '5' UNION ALL
SELECT 'Jul 7 2010 12:00AM', '040677', '8' UNION ALL
SELECT 'Jul 1 2010 12:00AM', '056789', '8' UNION ALL
SELECT 'Jul 3 2010 12:00AM', '056789', '6' UNION ALL
SELECT 'Jul 4 2010 12:00AM', '056789', '8' UNION ALL
SELECT 'Jul 5 2010 12:00AM', '056789', '8' UNION ALL
SELECT 'Jul 1 2010 12:00AM', '006777', '8' UNION ALL
SELECT 'Jul 2 2010 12:00AM', '006777', '8' UNION ALL
SELECT 'Jul 3 2010 12:00AM', '006777', '8' UNION ALL
SELECT 'Jul 5 2010 12:00AM', '006777', '8' UNION ALL
SELECT 'Jul 7 2010 12:00AM', '006777', '8'
*/
declare @beginDate datetime, @endDate datetime
set @beginDate = 'Jul 3 2010 00:00:00'
set @endDate = 'Jul 3 2010 23:59:59'
SELECT tk.*
FROM #timekeep tk
LEFT JOIN #timecard tc
ON tk.ID = tc.ID and tc.Date BETWEEN @beginDate AND @endDate
WHERE tc.ID IS NULL
August 3, 2010 at 7:45 am
Eugene Elutin (8/3/2010)
with corrected setup...
After executing your code, I get: -
ID Org
---------- ----
040677 100
Whilst the OPs requirement was: -
7/3/2010 040677
7/5/2010 040677
7/2/2010 056789
7/6/2010 056789
7/7/2010 056789
I was thinking maybe a calender table?
August 3, 2010 at 8:34 am
Yep, OP's wording was clear enough for me... 😀
After looking closer into expected results I believe I do understand what he wants:
declare @beginDate datetime, @endDate datetime, @Org int
set @beginDate = 'Jul 1 2010'
set @endDate = 'Jul 7 2010'
set @Org = 100
SELECT DR.Dt, tk.ID
FROM (SELECT DATEADD(D,rn,@beginDate) Dt
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 rn
FROM sys.columns sc) tt
WHERE tt.rn <= DATEDIFF(D,@beginDate, @endDate)
) DR
CROSS JOIN #timekeep tk
LEFT JOIN #timecard tc
ON tc.ID = tk.ID
AND CAST(CONVERT(varchar(8),tc.Date,112) AS datetime) = DR.Dt
WHERE tk.Org = @Org
AND tc.ID IS NULL
Please note: As sys.columns is used for Tally-On-The-Fly table, this query will only work for the number of days no greater than number of recors in sys.columns. Tnis can be replaced by dedicated Tally table, or, even better, the whole DR subquery should be replaced by dedicated Calendar table. However, as I have no idea of OP limits I can't insist on creating specialised Tally and/or Calendar tables...
August 3, 2010 at 8:57 am
Eugene Elutin (8/3/2010)
Yep, OP's wording was clear enough for me... 😀
:hehe:
Eugene Elutin (8/3/2010)
Please note: As sys.columns is used for Tally-On-The-Fly table, this query will only work for the number of days no greater than number of recors in sys.columns. Tnis can be replaced by dedicated Tally table, or, even better, the whole DR subquery should be replaced by dedicated Calendar table. However, as I have no idea of OP limits I can't insist on creating specialised Tally and/or Calendar tables...
Could fix that potential problem by using a CTE to generate your tally-on-the-fly
;WITH numbers
AS (SELECT 0 AS rn
UNION ALL
SELECT rn + 1
FROM numbers
WHERE rn < (SELECT Datediff(dd, @beginDate, @endDate)))
SELECT dr.dt,
tk.id
FROM (SELECT Dateadd(d, rn, @beginDate) dt
FROM (SELECT rn
FROM numbers sc) tt
WHERE tt.rn <= Datediff(d, @beginDate, @endDate)) dr
CROSS JOIN #timekeep tk
LEFT JOIN #timecard tc
ON tc.id = tk.id
AND CAST(CONVERT(VARCHAR(8), tc.DATE, 112) AS DATETIME) =
dr.dt
WHERE tk.org = @Org
AND tc.id IS NULL
August 3, 2010 at 9:22 am
That whould work for maximum of 100 days period.
The maximum recursion of CTE...
You could extend it by specifying the MAXRECURSION hint, however you would still have a limit of approx 32,000.
Plus, recursive CTE would be even worse from performance prospective than using Tally-On-The-Fly based on sys.columns or other table...
August 3, 2010 at 3:33 pm
Thanks to all for the sugestions. I will give it a try. I don't see a need to go beyond 100 days. Most of the time they want a 30 day look. You guys are great!
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply