Find attended date when I only have absent date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Find attended date when I only have absent date

Author

Topic

forwheeler
Starting Member

44 Posts

Posted - 2014-01-03 : 17:38:31

I want to find the person's last attended date when my table only contains the absent dates. The dates will only be saturdays.

There is a person table with a PersonID etc. and an Attendance table which contains the PersonID, Date.
I have this query which results in their latest absent date.
It seems I would need a recursive query to check all Saturdays to get the MAX Saturday which they were not absent.

SELECT p.LName, p.FName, lastabsent 
FROM dbo.Person p 
INNER JOIN(             		
	SELECT PersonID, MAX(date) AS lastabsent
	FROM dbo.Attendance
	GROUP BY PersonID)sub 
ON p.PersonID = sub.PersonID

Results
mouse mickey 12/28/2013
duck donald 12/28/2013
one thing 12/28/2013
two thing 12/28/2013
three thing 10/26/2013
four thing 12/7/2013

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-04 : 05:09:07

Do you've a calendar table? Or any other table which will details on dates when persons were present?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

forwheeler
Starting Member

44 Posts

Posted - 2014-01-04 : 11:58:09

No I don't track when they are present since that will add many more records to the table which technically isn't necessary. I enter a reason for absent which is why I track absences.
I found some code and modified to to list all Saturdays for a year so I thought I could use this instead of a calendar table.

WITH CTE(dt)
AS
(
Select DATEADD(week,-52, GETDATE())
Union All
Select DATEADD(d,1,dt)FROM CTE
Where dt < GETDATE()
)
select dt from CTE
where DATENAME(dw,dt)='Saturday'
OPTION (MAXRECURSION 400)

forwheeler
Starting Member

44 Posts

Posted - 2014-01-06 : 11:13:20

I know this would be easier if I had a calendar table or had the attended dates but I know it is possible to do otherwise. I know you experts have an answer.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-01-06 : 17:28:42

Can't test, don't have sample data.



;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [10s].digit * 10 + [1s].digit AS tally
    FROM cteDigits [1s]
    INNER JOIN cteDigits [10s] ON [10s].digit BETWEEN 0 AND 5
    WHERE [10s].digit * 10 + [1s].digit <= 53
)
SELECT p.LName, p.FName, MAX(DATEADD(DAY, -week# * 7, mostRecentSaturday)) AS lastAttended
FROM dbo.Person p
CROSS APPLY (
    SELECT DATEADD(DAY, DATEDIFF(DAY, 5, GETDATE()) / 7 * 7, 5) AS mostRecentSaturday
) AS mostRecentSaturday
CROSS JOIN cteTally AS week#
WHERE NOT EXISTS(SELECT 1 FROM dbo.Attendance a WHERE a.PersonID = p.PersonID AND a.date = DATEADD(DAY, -week# * 7, mostRecentSaturday))
GROUP BY p.LName, p.FName

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-07 : 01:04:16

quote:
Originally posted by forwheeler

I know this would be easier if I had a calendar table or had the attended dates but I know it is possible to do otherwise. I know you experts have an answer.

Even otherwise you need to generate one on the fly and then use it

like this

http://visakhm.blogspot.in/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

forwheeler
Starting Member

44 Posts

Posted - 2014-01-07 : 20:39:38

quote:

Originally posted by ScottPletcher

Can't test, don't have sample data.



;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [10s].digit * 10 + [1s].digit AS tally
    FROM cteDigits [1s]
    INNER JOIN cteDigits [10s] ON [10s].digit BETWEEN 0 AND 5
    WHERE [10s].digit * 10 + [1s].digit <= 53
)
SELECT p.LName, p.FName, MAX(DATEADD(DAY, -week# * 7, mostRecentSaturday)) AS lastAttended
FROM dbo.Person p
CROSS APPLY (
    SELECT DATEADD(DAY, DATEDIFF(DAY, 5, GETDATE()) / 7 * 7, 5) AS mostRecentSaturday
) AS mostRecentSaturday
CROSS JOIN cteTally AS week#
WHERE NOT EXISTS(SELECT 1 FROM dbo.Attendance a WHERE a.PersonID = p.PersonID AND a.date = DATEADD(DAY, -week# * 7, mostRecentSaturday))
GROUP BY p.LName, p.FName

I get invalid column name week#.
I'm not sure if you meant this to be a variable that I enter a value for.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-07 : 20:50:47

change this part

cteTally AS (
    SELECT [10s].digit * 10 + [1s].digit AS tallyweek#
    FROM cteDigits [1s]
    INNER JOIN cteDigits [10s] ON [10s].digit BETWEEN 0 AND 5
    WHERE [10s].digit * 10 + [1s].digit <= 53
)

KH
[spoiler]Time is always against us[/spoiler]

forwheeler
Starting Member

44 Posts

Posted - 2014-01-07 : 23:47:23

quote:

Originally posted by khtan

change this part

cteTally AS (
    SELECT [10s].digit * 10 + [1s].digit AS tallyweek#
    FROM cteDigits [1s]
    INNER JOIN cteDigits [10s] ON [10s].digit BETWEEN 0 AND 5
    WHERE [10s].digit * 10 + [1s].digit <= 53
)

KH
[spoiler]Time is always against us[/spoiler]

Yes that fixed it. Thanks.

forwheeler
Starting Member

44 Posts

Posted - 2014-01-08 : 00:00:29

quote:

Originally posted by ScottPletcher

Can't test, don't have sample data.



;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [10s].digit * 10 + [1s].digit AS tally
    FROM cteDigits [1s]
    INNER JOIN cteDigits [10s] ON [10s].digit BETWEEN 0 AND 5
    WHERE [10s].digit * 10 + [1s].digit <= 53
)
SELECT p.LName, p.FName, MAX(DATEADD(DAY, -week# * 7, mostRecentSaturday)) AS lastAttended
FROM dbo.Person p
CROSS APPLY (
    SELECT DATEADD(DAY, DATEDIFF(DAY, 5, GETDATE()) / 7 * 7, 5) AS mostRecentSaturday
) AS mostRecentSaturday
CROSS JOIN cteTally AS week#
WHERE NOT EXISTS(SELECT 1 FROM dbo.Attendance a WHERE a.PersonID = p.PersonID AND a.date = DATEADD(DAY, -week# * 7, mostRecentSaturday))
GROUP BY p.LName, p.FName

I am impressed. After the fix by khtan this works great. I checked a few records and it was accurate for all of them. Thanks!

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-01-08 : 10:05:26

You're welcome. Sorry about the oversight on the column naming, was busy at work too!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources