October 29, 2014 at 2:38 am
hi all,
i am working on time attendance application. i need to show records for whole month and if there is no data for a particular day then it should consider it as absent. how do i achieve this.
data
id date timein timeout status
1 1/1/2002 07:00 14:00
1 2/1/2002 07:10 14:20
1 3/1/2002 null null absent
1 4/1/2002 07:00 14:00
1 5/1/2002 null null absent
any help is highly appreciated
October 29, 2014 at 3:05 am
LEFT JOIN your data table to a calendar table.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 29, 2014 at 3:27 am
Use a calendar table, left join from the calendar table to your data table (because you want all the rows from the calendar and matching rows from the data table)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2014 at 4:06 am
using calendar table is the only way???
October 29, 2014 at 4:10 am
Well you need something that has all dates in it that you can join to.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2014 at 8:48 am
imfairozkhan (10/29/2014)
using calendar table is the only way???
you could dynamically create calendar table with recursive cte but I am not sure it would scale well on a large data set.
CREATE TABLE #temp(id INT, [date] DATE, timein TIME, [timeout] TIME, [status] VARCHAR(10));
INSERT INTO #temp
VALUES(1,'2002-1-1','07:00','14:00',''),
(1,'2002-1-2','07:10','14:20',''),
(1,'2002-1-4','07:00','14:00','');
WITH cal(dt) AS(
SELECT CAST('2002-01-01' AS DATE) AS dt
UNION ALL
SELECT CAST(DATEADD(dd,1,dt) AS DATE)FROM cal WHERE dt < '2002-01-05')
SELECT a.dt,b.*FROM cal a
LEFT OUTER JOIN #temp b
ON a.dt = b.[date]
--OPTION(MAXRECURSION 365);
recursive CTEs have a max recursion of 100 so if you time range is large than that you will need to use the maxrecursion option
October 29, 2014 at 1:28 pm
Robert klimes (10/29/2014)
imfairozkhan (10/29/2014)
using calendar table is the only way???you could dynamically create calendar table with recursive cte but I am not sure it would scale well on a large data set.
...
recursive CTEs have a max recursion of 100 so if you time range is large than that you will need to use the maxrecursion option
Carefull now... rCTEs that count are bad even for something so small. Please see the following article for more information
http://www.sqlservercentral.com/articles/T-SQL/74118/
--Jeff Moden
Change is inevitable... Change for the better is not.
October 30, 2014 at 9:36 am
imfairozkhan (10/29/2014)
using calendar table is the only way???
Here's a quick and dirty calendar generator using a tally table (you can find plenty of example here of how to build one).
declare @startDate datetime = '2014-01-01', -- example date
@endDate datetime = '2015-12-31', -- example date
@startDateKey int,
@totalNumberOfDays int
select @startDateKey = cast(@startDate as int)
select @totalNumberOfDays = cast(@endDate as int) - @startDateKey
declare @calendar table (dateKey int, date_time datetime, [Date] date)
;with tt (dateKey, date_time, [Date])
as (select TallyTable.i + @startDateKey,
cast(TallyTable.i + @startDateKey as datetime),
cast(cast(TallyTable.i + @startDateKey as datetime) as date)
from dbo.TalleyTable(@totalNumberOfDays) TallyTable)
insert @calendar
(dateKey, date_time, [Date])
select tt.dateKey, tt.date_time, tt.[Date] from tt
As noted above, left join your data to something like this (@calendar).
Don Simpson
October 30, 2014 at 10:13 am
Robert klimes (10/29/2014)
imfairozkhan (10/29/2014)
using calendar table is the only way???you could dynamically create calendar table with recursive cte but I am not sure it would scale well on a large data set.
CREATE TABLE #temp(id INT, [date] DATE, timein TIME, [timeout] TIME, [status] VARCHAR(10));INSERT INTO #temp
VALUES(1,'2002-1-1','07:00','14:00',''),
(1,'2002-1-2','07:10','14:20',''),
(1,'2002-1-4','07:00','14:00','');
WITH cal(dt) AS(
SELECT CAST('2002-01-01' AS DATE) AS dt
UNION ALL
SELECT CAST(DATEADD(dd,1,dt) AS DATE)FROM cal WHERE dt < '2002-01-05')
SELECT a.dt,b.*FROM cal a
LEFT OUTER JOIN #temp b
ON a.dt = b.[date]
--OPTION(MAXRECURSION 365);
recursive CTEs have a max recursion of 100 so if you time range is large than that you will need to use the maxrecursion option
Great article. thanks. I knew my solution was not great but I was trying to provide something that didn't require materializing a table as sometime that isn't an option
I have adjusted my query to just use row_number() to build the calendar. For the same result set it reduces reads from 31 to 2. For the full result set of sys.allobjects(2180) the difference is much greater (13081 to 29).
WITH cal(dt)
AS (SELECT TOP 5 CAST(DATEADD(dd, ROW_NUMBER()OVER(ORDER BY(SELECT NULL)), '2001-12-31')AS date)
FROM sys.all_objects)
SELECT a.dt,
b.*
FROM
cal a
LEFT OUTER JOIN #temp b
ON a.dt = b.[date];
Thanks Jeff, I'm always happy to improve my skills.
October 30, 2014 at 11:28 am
What I'll sometimes use as a poor man's tally table (we don't have one) is to combine ROW_NUMBER() and TOP from some existing table that has enough records to do what I want.
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY KEY_COLUMN)
FROM SOME_TABLE
You can combine that with DATEADD in this situation. A hundred thousand days covers 273 years worth of days.
with TallyTable as (
-- Subtract 1 so you start counting from 0
select TOP 100000 ROW_NUMBER() OVER (ORDER BY KEY_COLUMN) - 1 as Row_Num
from SOME_TABLE
order by Row_Num
)
select dateadd(day,Row_Num,cast('1900-01-01' as date)) as Day
from TallyTable
This code sample covers all days from 1900-01-01 to 2173-10-15. Increase your TOP number if you need more.
October 31, 2014 at 6:11 pm
The CTE has a default max recusrion of 100. I can easily create a on the fly set of 100 consecutive days from a start date with something like :
(using my ol favorite Northwind database)
declare @startdate datetime='1996-07-01';
WITH SET1 AS(
SELECT clm1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS rs(clm1) /* TEN ELEMENTS */
),
SET2 AS(
SELECT a.clm1 FROM SET1 a, SET1 b /* 10 rows x 10 rows = 100 rows */
)
,
SET3 as
(
SELECT DATEADD( d, ROW_NUMBER() OVER( ORDER BY ( SELECT '1')) - 1, @startdate) AS [Date]
fromset2
)
selects3.[date],
count(orderID) numOrders
FROMSET3 as s3
left join Orders as o ON s3.[date] = o.orderDate
GRoup
Bys3.[date]
Order by [Date];
Here you can just join to your attendance table instead of the orders table. If you need more days create another set that takes a cross of the previous CTE (set2 in this example). Hope this makes sense.
----------------------------------------------------
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply