CTE not effective / Subquery returned multiple values 1

[WoodyGuthrie]

Hello Everyone! I'll try a slightly different approach here at Tek-Tips.

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:
Current Result Set

guidAmnesty guidAttendanceCode 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 is like above except for the last two rows (the 2 Amnesty rows) with the date from their matching guidAttendanceCode row as follows:

837146E30E7B 648EE650E22E Amnesty 2008-06-12
64A11AB65DB9 C3F5D3C29599 Amnesty 2008-06-11

All Other Rows would be as they are.

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
, CASE  WHEN guidAttendanceCodeChildrenID IS NOT NULL
		THEN	(SELECT ID.dteAttendanceCode
				FROM InfractionDates ID
				JOIN dbo.tblAssociateAttendanceCodes ac
				ON ID.guidAssociateAttendanceCodeID = ac.guidAssociateAttendanceCodeID
				)
		ELSE    ac.dteAttendanceCode
		END AS  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 dbo.tblAmnestyParentChild apc
ON apc.guidAttendanceCodeID = ac.guidAssociateAttendanceCodeID

Woody

 

[gmmastros]

Maybe this will help:

Declare @Temp Table(guidAmnesty VarChar(12), guidAttendanceCode VarChar(12), AttendanceCode VarChar(20), Date 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(NULL          ,'837146E30E7B','Unapproved','2008-06-12')
Insert Into @Temp Values('837146E30E7B','648EE650E22E','Amnesty'   ,'2008-09-11')
Insert Into @Temp Values('64A11AB65DB9','C3F5D3C29599','Amnesty'   ,'2008-12-11')

Select A.guidAmnesty, A.guidAttendanceCode, A.AttendanceCode, Coalesce(B.Date, A.Date) As Date 
From   @Temp As A
       Left Join @Temp As B
         On A.guidAmnesty = b.guidAttendanceCode

Basically.... it's a left join and a coalesce on the date.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[WoodyGuthrie]

Someone tatto COALESCE on my forhead!
Thanks a bunch George.

Woody

 

[WoodyGuthrie]

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?

;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 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.dblScore<>0 OR aci.strAttendanceItem='Discipline Warning'
OR ac.strAdjType IN('Insert','Remove')) 


)
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.

Woody

 

[WoodyGuthrie]

This seems like a good example of when to use a tally/numbers table but I'm just not sure how to execute it.

Woody

 

[gmmastros]

Add that criteria to the join clause.... something like this....

LEFT JOIN dbo.tblAmnestyParentChild apc
ON apc.guidAttendanceCodeID = ac.guidAssociateAttendanceCodeID
And apc.DateColumn>={ts '2008-05-08 00:00:00'}
AND apc.DateColumn<{ts '2009-05-08 00:00:00'})

You MUST put this in the join clause. If you put the date filter stuff in the where clause, your left join will behave as though it's an inner join.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[WoodyGuthrie]

Thanks George.

But this table (apc) has no date column. It only has the guid back to the original infractions in the tblAssociateAttendanceCodes (ac) which has the date I need. It is then that row (guid, attendance code, date, etc.) that is out of my query date range.



Woody

 

[WoodyGuthrie]

Hoping this will help.

I have the following sample data. The current code above 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

Woody