March 1, 2010 at 4:18 pm
Ok, I have this stored procedure I am trying to run and it runs successfully but the data returned isnt right. I am trying to return the columns listed below they are Expired, Over Date, Emp Name, Swipe Number, Employee Number, Manager, and Secured.
I want the flag to be Yes or No for Secured as you can see in my CASE statement. Right now I have 5 gategroups with a total of 10 gatenames assigned to the access card for this employee and when I run this I am getting all gate access returned for the employee with Yes and No both showing under Secure column. For example I am getting this returned:
7Days, 3/8/2010, Joe Jackson, 112, S34534, John Jenkins, Yes
7Days, 3/8/2010, Joe Jackson, 112, S34534, John Jenkins, Yes
7Days, 3/8/2010, Joe Jackson, 112, S34534, John Jenkins, No
7Days, 3/8/2010, Joe Jackson, 112, S34534, John Jenkins, Yes
....
....
....
I am only wanting one row returned for each employee with the Yes or No flag if they have secured access or not, Id otn want 10 different entries just because they have 10 gatenames assigned to their access card. All of the Secured gates start with 'NE_'. So no matter how many gate names they have assigned to them if just one of the gatenames = 'NE_%' then only one entry will be returned with the Secured column flagged as 'Yes'. I have been trying to use the Select Exists at the bottom of the query. Any help is appreciated! I hope im clear enough if not let me know, I have my code listed below.
select CASE
WHEN DATEDIFF(day, getdate(), nhct.altinactivedate) = 0
THEN 'Today'
WHEN DATEDIFF(day, getdate(), nhct.altinactivedate) = 1
THEN '1 day'
WHEN DATEDIFF(day, getdate(), nhct.altinactivedate) = 2
THEN '2 days'
WHEN DATEDIFF(day, getdate(), nhct.altinactivedate) = 3
THEN '3 days'
WHEN DATEDIFF(day, getdate(), nhct.altinactivedate) = 4
THEN '4 days'
WHEN DATEDIFF(day, getdate(), nhct.altinactivedate) = 5
THEN '5 days'
WHEN DATEDIFF(day, getdate(), nhct.altinactivedate) = 6
THEN '6 days'
WHEN DATEDIFF(day, getdate(), nhct.altinactivedate) = 7
THEN '7 days'
WHEN DATEDIFF(day, getdate(), nhct.altinactivedate) = 8
THEN '8 days'
END as 'Expired',
CONVERT(nvarchar, nhct.altinactivedate, 101) as 'Over Date',
rpt.lastname + ', ' + rpt.firstname as 'Emp Name',
et.swipenumber as 'Swipe Number',
rpt.employeenumber as 'Employee Number',
ae.ManagerName as 'Manager',
CASE
WHEN GateName like 'NE_%' THEN 'Yes'
ELSE 'No'
END as 'Secured'
from TestServ1.nameholder.dbo.report rpt
inner join TestServ1.nameholder.dbo.nameholdercrosstable nhct
on rpt.cardid = nhct.cardid
inner join TestServ1.nameholder.dbo.employeetable et
on rpt.empid = et.empid
left outer join PSDatabase.dbo.AllEmployees ae
on rpt.EmployeeNumber = ae.Emplid
JOIN TestServ1.nameholder.dbo.GateSwipeGroupCrossTable gsgt
ON nhct.GateSwipeCrossId = gsgt.GateSwipeCrossID
JOIN TestServ1.nameholder.dbo.GateCrossTable gct
ON gsgt.gateSwipeId = gct.gateSwipeId
JOIN TestServ1.nameholder.dbo.Gates g
ON gct.gateId = g.gateId
where et.inactive = 0
and nhct.altinactivedate >= getdate()
and nhct.altinactivedate < getdate() + 10
I WAS TRYING TO USE THIS in the where clause
and EXISTS
(select gatename
from amagt1.multiMAX.dbo.ReaderTable
where gatename like 'NE_%')
March 2, 2010 at 1:07 am
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply