May 14, 2009 at 11:08 am
Hello Everyone!
I know I'm probaly making this much more difficult than it really is...Just can't get past it. I'm hoping to get some help.
I'm dealing with employee attendance data. An employee may receive amnesty for infractions that have occurred in the past (like unapproved absences etc.) I have an amnesty table that tracks the infraction(s) for which the amnesty was given. So in the amnesty table (tblAmnestyParentChild), the guidAttendanceCodeChildrenID points back to the original infraction in the attendance codes table (tblAssociateAttendanceCodes).
Although each attendance item in the tblAssociateAttendanceCodes (approved, unapproved, amnesty, etc) has its own associated date, I need to return the date of the original infraction (say the unapproved attendance date) with the row that indicates the amnesty and not the date that the amnesty code was given.
Sample data is attached and illustrates well what I need in the result.
I was thinking that I could use a CTE to result all Amnesty rows with the required date (the date of the original infraction) and then join that to the attendance codes table and that does works except when there are multiple amnesty rows, I get the subquery returned more than 1 value warning. So I'm really not gaining anything from the CTE as I thought.
I hope I've explained this good enough. Any help is greatly appreciated.
Thanks.
WITH InfractionDates (guidAssociateAttendanceCodeID, dteAttendanceCode, strAssociateID)
AS
(SELECT ac.guidAssociateAttendanceCodeID, ac.dteAttendanceCode, ad.strAssociateID
FROM dbo.tblAssociateDemographics ad
INNER JOIN dbo.tblAssociateAttendanceCodes ac
ON ad.guidAssociateID=ac.guidAssociateID
WHERE ac.guidAssociateAttendanceCodeID in
(SELECT apc.guidAttendanceCodeChildrenID
FROM dbo.tblAmnestyParentChild apc)
)
SELECT
apc.guidAttendanceCodeChildrenID
, ac.guidAssociateAttendanceCodeID
, ad.strAssociateID
, ac.dblScore
, aci.strAttendanceItem
, CASEWHEN guidAttendanceCodeChildrenID IS NOT NULL
THEN (SELECT ID.dteAttendanceCode
FROM InfractionDates ID
JOIN dbo.tblAssociateAttendanceCodes ac
ON ID.guidAssociateAttendanceCodeID = ac.guidAssociateAttendanceCodeID
)
ELSEac.dteAttendanceCode
ENDASdteAttendanceCode
, ad.strLastName
, ad.strFirstName
, ad.strDetailsStatus
, ad.dteDetailsHireDate
, ac.dblAdjScore
, ac.strAdjType
, d.guidDepartmentID
, f.strName
, d.strName
, ad.dblAttendanceScore
, ac.dteInsertedOn
, ac.strAdjComment
FROM
dbo.tblAssociateDemographics ad
INNER JOIN dbo.tblAssociateAttendanceCodes ac
ON ad.guidAssociateID=ac.guidAssociateID
INNER JOIN dbo.tblDepartments d
ON ad.guidDetailsHomeDepartment=d.guidDepartmentID
INNER JOIN dbo.tblFacilities f
ON ad.guidDetailsHomeFacility=f.guidFacilityID
LEFT OUTER JOIN dbo.tblAttendanceCodeItems aci
ON ac.intAttendanceItemCode=aci.intAttendanceItemCode
LEFT JOIN dbo.tblAmnestyParentChild apc
ON apc.guidAttendanceCodeID = ac.guidAssociateAttendanceCodeID
May 14, 2009 at 11:55 am
Here's the Sample Data:
Current Result Set
guidAttendanceCodeChildrenID guidAssociateAttendanceCodeID AttendanceCode Date
NULL 7A0A970A5CD4 Approved 2008-06-05
NULL 9BF2F1986ED9 Unapproved 2008-06-05
NULL 27058A22E167 Approved 2008-06-06
NULL 9C77054505CE Unapproved 2008-06-09
NULL DDC7ED8C2284 Approved 2008-06-09
NULL 975628B59CC3 Approved 2008-06-10
NULL 64A11AB65DB9 Unapproved 2008-06-11
NULL 837146E30E7B Unapproved 2008-06-12
837146E30E7B 648EE650E22E Amnesty 2008-09-11
64A11AB65DB9 C3F5D3C29599 Amnesty 2008-12-11
What I want to return for the last two rows (The Amnesty rows)
837146E30E7B648EE650E22EAmnesty2008-06-12
64A11AB65DB9C3F5D3C29599Amnesty2008-06-11
All Other Rows would be as they are.
May 14, 2009 at 12:14 pm
Does the following test code help you?
/*
guidAttendanceCodeChildrenID guidAssociateAttendanceCodeID AttendanceCode Date
NULL 7A0A970A5CD4 Approved 2008-06-05
NULL 9BF2F1986ED9 Unapproved 2008-06-05
NULL 27058A22E167 Approved 2008-06-06
NULL 9C77054505CE Unapproved 2008-06-09
NULL DDC7ED8C2284 Approved 2008-06-09
NULL 975628B59CC3 Approved 2008-06-10
NULL 64A11AB65DB9 Unapproved 2008-06-11
NULL 837146E30E7B Unapproved 2008-06-12
837146E30E7B 648EE650E22E Amnesty 2008-09-11
64A11AB65DB9 C3F5D3C29599 Amnesty 2008-12-11
What I want to return for the last two rows (The Amnesty rows)
837146E30E7B 648EE650E22E Amnesty 2008-06-12
64A11AB65DB9 C3F5D3C29599 Amnesty 2008-06-11
*/
create table #TempData (
guidAttendanceCodeChildrenID char(12),
guidAssociateAttendanceCodeID char(12),
AttendanceCode varchar(32),
AttendanceDate datetime
);
insert into #TempData
select NULL,'7A0A970A5CD4','Approved','2008-06-05' union all
select NULL,'9BF2F1986ED9','Unapproved','2008-06-05' union all
select NULL,'27058A22E167','Approved','2008-06-06' union all
select NULL,'9C77054505CE','Unapproved','2008-06-09' union all
select NULL,'DDC7ED8C2284','Approved','2008-06-09' union all
select NULL,'975628B59CC3','Approved','2008-06-10' union all
select NULL,'64A11AB65DB9','Unapproved','2008-06-11' union all
select NULL,'837146E30E7B','Unapproved','2008-06-12' union all
select '837146E30E7B','648EE650E22E','Amnesty','2008-09-11' union all
select '64A11AB65DB9','C3F5D3C29599','Amnesty','2008-12-11'
;
select
coalesce(td2.guidAttendanceCodeChildrenID,td1.guidAttendanceCodeChildrenID) as guidAttendanceCodeChildrenID,
td1.guidAssociateAttendanceCodeID,
case when td2.guidAttendanceCodeChildrenID is not null then td2.AttendanceCode else td1.AttendanceCode end as AttendanceCode,
td1.AttendanceDate
from
#TempData td1
left outer join #TempData td2
on (td1.guidAssociateAttendanceCodeID = td2.guidAttendanceCodeChildrenID)
where
td1.guidAttendanceCodeChildrenID is null;
drop table #TempData;
May 14, 2009 at 2:16 pm
Thanks a bunch Lynn. 😀
May 15, 2009 at 9:11 am
This works fine for attendance dates (Infractions) that are within my specified date range, but retains the Amnesty attendance date for Infraction Dates that fall outside that specified range.
How can I incorporate all attendance dates so that no matter when the infraction occurred the amnesty that was given can include the date for that associated infraction?
This seems like a good example of when to use a tally/numbers table but I'm just not sure how to execute it.
;WITH INFRACTIONDATES
( guidAttendanceCodeChildrenID
, guidAssociateAttendanceCodeID
, strAssociateID
, dblScore
, strAttendanceItem
, dteAttendanceCode
, strLastName
, strFirstName
, strDetailsStatus
, dteDetailsHireDate
, dblAdjScore
, strAdjType
, guidDepartmentID
, strFName
, strDName
, dblAttendanceScore
, dteInsertedOn
, strAdjComment
)
AS
(
SELECT
apc.guidAttendanceCodeChildrenID
, ac.guidAssociateAttendanceCodeID
, ad.strAssociateID
, ac.dblScore
, aci.strAttendanceItem
, ac.dteAttendanceCode
, ad.strLastName
, ad.strFirstName
, ad.strDetailsStatus
, ad.dteDetailsHireDate
, ac.dblAdjScore
, ac.strAdjType
, d.guidDepartmentID
, f.strName
, d.strName
, ad.dblAttendanceScore
, ac.dteInsertedOn
, ac.strAdjComment
FROM
dbo.tblAssociateDemographics ad
INNER JOIN dbo.tblAssociateAttendanceCodes ac
ON ad.guidAssociateID=ac.guidAssociateID
INNER JOIN dbo.tblDepartments d
ON ad.guidDetailsHomeDepartment=d.guidDepartmentID
INNER JOIN dbo.tblFacilities f
ON ad.guidDetailsHomeFacility=f.guidFacilityID
LEFT OUTER JOIN dbo.tblAttendanceCodeItems aci
ON ac.intAttendanceItemCode=aci.intAttendanceItemCode
LEFT JOIN FHIQDB.TRINIDAD.dbo.tblAmnestyParentChild apc
ON apc.guidAttendanceCodeID = ac.guidAssociateAttendanceCodeID
WHERE (ac.dteAttendanceCode>={ts '2008-05-08 00:00:00'}
AND ac.dteAttendanceCode<{ts '2009-05-08 00:00:00'})
AND ad.strDetailsStatus IN ('Contract','Leave','Light Duty','Regular','Road Crew','Training')
AND aci.strAttendanceItem IN('Amnesty','Early Departure','No Call No Show','Tardy','Unapproved')
AND (ac.dblScore0 OR aci.strAttendanceItem='Discipline Warning'
OR ac.strAdjType IN('Insert','Remove'))
AND f.strName='Atlanta'
--AND ad.strAssociateID = '11276'
)
SELECT
ida.guidAttendanceCodeChildrenID
,ida.guidAssociateAttendanceCodeID
,ida.strAssociateID
,ida.dblScore
,ida.strAttendanceItem
,ida.dteAttendanceCode
,coalesce(idb.dteAttendanceCode, ida.dteAttendanceCode) InfractionDate
,ida.strLastName
,ida.strFirstName
,ida.strDetailsStatus
,ida.dteDetailsHireDate
,ida.dblAdjScore
,ida.strAdjType
,ida.guidDepartmentID
,ida.strFName
,ida.strDName
,ida.dblAttendanceScore
,ida.dteInsertedOn
,ida.strAdjComment
FROM InfractionDates ida
LEFT JOIN InfractionDates idb
ON ida.guidAttendanceCodeChildrenID = idb.guidAssociateAttendanceCodeID
So if my specified date range for this query is 5/8/2008 through 5/8/2009 and amnesty for employee #123 was given on 6/20/2008 but the original infraction was an unapproved absence from attendance date 3/20/2008 the date returned would be 6/20/2008 because 3/20/2008 falls outside the range of my specified dates.
Additional help is appreciated.
May 15, 2009 at 9:24 am
At this point we really need the DDL for your tables, sample data (in a readily consummable format), expected results, and the code you have already completed.
For assistance in this, please read the first article I reference below in my signature block.
May 15, 2009 at 10:52 am
Lynn, Thanks for the help and suggestions. I'm hoping what I provide here is adequate.
I have the following sample data. The current code in my previous thread does work as long as the strAttendanceItem for which the Amnesty has been applied exists in the current result set. The problem is for those that occurred prior to the min dteAttendanceCode filter for the query.
In the sample below, you can see that guidAttendanceCodeChildrenID '64A11AB65DB9' has a related guidAssociateAttendanceCodeID in the result. Its date is '06/11/2008' and so that date is replaces the Amnesty's dteAttendanceCode for the Amnesty row.
However, the guidAttendanceCodeChildrenID '837146E30E7B' has no related guidAssociateAttendanceCodeID in the result set and so the Amnesty's dteAttendanceCode remains in that column. I know that the related attendance code occurred prior to the date range of this result set. I need that date to replace 2008-9-11 on my amnesty row.
I was thinking that maybe it would be possible to somehow use a numbers table to get those related guids (guidAssociateAttendanceCodeID) and their dates (dteAttendanceCode) that are outside the current result set for those amnesty rows (guidAttendanceCodeChildrenID) with no max in the current result set?
Thanks again.
Declare @Temp Table
(
guidAttendanceCodeChildrenID VarChar(12)
,guidAssociateAttendanceCodeID VarChar(12)
,strAttendanceItem VarChar(20)
,dteAttendanceCode DateTime
)
Insert Into @Temp Values(NULL ,'7A0A970A5CD4','Approved' ,'2008-06-05')
Insert Into @Temp Values(NULL ,'9BF2F1986ED9','Unapproved','2008-06-05')
Insert Into @Temp Values(NULL ,'27058A22E167','Approved' ,'2008-06-06')
Insert Into @Temp Values(NULL ,'9C77054505CE','Unapproved','2008-06-09')
Insert Into @Temp Values(NULL ,'DDC7ED8C2284','Approved' ,'2008-06-09')
Insert Into @Temp Values(NULL ,'975628B59CC3','Approved' ,'2008-06-10')
Insert Into @Temp Values(NULL ,'64A11AB65DB9','Unapproved','2008-06-11')
Insert Into @Temp Values('837146E30E7B','648EE650E22E','Amnesty' ,'2008-09-11')
Insert Into @Temp Values('64A11AB65DB9','C3F5D3C29599','Amnesty' ,'2008-06-11')
SELECT * FROM @Temp
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply