February 23, 2006 at 10:28 am
Hello,
I am having a problem returning a set of data opposite of my original constraints to check my result set. I am creating a report of all personnel that used the MLK holiday. see code:
select t.RES_ID, e.SSN, convert(char(35),e.last_name +', ' + e.first_name) as Name, t.ACT_CODE, convert(numeric(4),t.DAY2) as Hours_Taken
from Time t join employee e on(t.RES_ID=e.RES_ID)
where t.PERIOD_YR = 2006
and t.PERIOD = 3
and t.ACT_CODE = 'HOL'
and t.RES_ID in
(AAAA,CCCC,XXXX,EEEE,VVVV,BBBB,CCCC,
IIII,OOOO,NNNN,HHHH,QQQQ,WWWW,HHYU)
--That "in" list should be much larger but I just shortened to save space! The filed ACT_CODE is the key here. If there is a value = 'HOL' then you know it was a holiday taken. But my problem is that in the tabLE Time you have multiple rows for various ACT_CODE which is valid for the week. So you could have user 'AAAA' with three valid rows with 3 different and valid ACT_CODE values. How do I reengineer the query to check each RES_ID to ensure that it does not contain a row in Time that contains a 'HOL' value for any RES_IDs? Sorry if this is overkill!
Thanks,
MJ
February 23, 2006 at 10:52 am
Your query returns AAAA as the only person that took the holiday and is in the list. CCCC did not take the holiday and XXXX is not in the list. Given this data, what would you expect the output to be?
declare @Employee table (Res_ID varchar(4), last_name varchar(20), first_name varchar(20), ssn varchar(9))
insert @Employee values('AAAA', 'Smith', 'Joe', '111223333')
insert @Employee values('CCCC', 'Doe', 'John', '222334444')
insert @Employee values('XXXX', 'Listed', 'Not', '555667777')
declare @Time table (Res_ID varchar(4), PERIOD_YR int, period int, Act_Code char(3), Day2 varchar(4))
insert @Time values('AAAA', 2006, 3, 'HOL', 1)
insert @Time values('AAAA', 2006, 3, 'REG', 2)
insert @Time values('AAAA', 2006, 3, 'OT1', 3)
insert @Time values('CCCC', 2006, 3, 'REG', 1)
insert @Time values('CCCC', 2006, 3, 'OT1', 2)
insert @Time values('CCCC', 2006, 3, 'OT2', 3)
insert @Time values('XXXX', 2006, 3, 'HOL', 1)
select t.RES_ID, e.SSN, convert(char(35),e.last_name +', ' + e.first_name) as Name, t.ACT_CODE, convert(numeric(4),t.DAY2) as Hours_Taken
from @Time t join @employee e on(t.RES_ID=e.RES_ID)
where t.PERIOD_YR = 2006
and t.PERIOD = 3
and t.ACT_CODE = 'HOL'
and t.RES_ID in
('AAAA','CCCC')
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply