Author 
Topic 

forwheeler
Starting Member
44 Posts 
Posted  01/03/2014 : 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

Edited by  forwheeler on 01/03/2014 21:31:31


visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts 
Posted  01/04/2014 : 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  01/04/2014 : 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  01/06/2014 : 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
USA
550 Posts 
Posted  01/06/2014 : 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
India
52326 Posts 

forwheeler
Starting Member
44 Posts 
Posted  01/07/2014 : 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)
Singapore
17689 Posts 
Posted  01/07/2014 : 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 Time is always against us



forwheeler
Starting Member
44 Posts 
Posted  01/07/2014 : 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 Time is always against us
Yes that fixed it. Thanks. 


forwheeler
Starting Member
44 Posts 
Posted  01/08/2014 : 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
USA
550 Posts 
Posted  01/08/2014 : 10:05:26

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



Topic 
