SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Find attended date when I only have absent date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

forwheeler
Starting Member

44 Posts

Posted - 01/03/2014 :  17:38:31  Show Profile  Reply with Quote
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
52325 Posts

Posted - 01/04/2014 :  05:09:07  Show Profile  Reply with Quote
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
Go to Top of Page

forwheeler
Starting Member

44 Posts

Posted - 01/04/2014 :  11:58:09  Show Profile  Reply with Quote
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)
Go to Top of Page

forwheeler
Starting Member

44 Posts

Posted - 01/06/2014 :  11:13:20  Show Profile  Reply with Quote
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.
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
437 Posts

Posted - 01/06/2014 :  17:28:42  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/07/2014 :  01:04:16  Show Profile  Reply with Quote
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
Go to Top of Page

forwheeler
Starting Member

44 Posts

Posted - 01/07/2014 :  20:39:38  Show Profile  Reply with Quote
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.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 01/07/2014 :  20:50:47  Show Profile  Reply with Quote
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

Go to Top of Page

forwheeler
Starting Member

44 Posts

Posted - 01/07/2014 :  23:47:23  Show Profile  Reply with Quote
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.
Go to Top of Page

forwheeler
Starting Member

44 Posts

Posted - 01/08/2014 :  00:00:29  Show Profile  Reply with Quote
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!
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
437 Posts

Posted - 01/08/2014 :  10:05:26  Show Profile  Reply with Quote
You're welcome. Sorry about the oversight on the column naming, was busy at work too!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000