June 5, 2012 at 10:02 am
I have this code that drives my report. Works fine, but my users have to manually type in the four digit year into a text box.
I really would like for them to have a calendar control (do they make one for jusy year?) or a drop down list of the years.
How do I change my code/report to accomplish this?
--With Terminations
SELECT
DATEPART(YEAR, e.eecDateOfLastHire) as 'Year of Hire',
CmpCompanyCode as 'Company Code',
E.EecOrgLvl2 as 'Org Level',
cmpCompanyName as 'Company Name',
E.eecCoID as 'Co ID',
E.EecEEID as 'EE ID', --stays with person, if listed more then once
E.eecDateOfTermination as 'Term Date',
E.eecEmplStatus as 'Emp Status',
E.eecEmpNo as 'Emp Number',
E.eecLocation as 'Location',
E.eecFullTimeorPartTime as 'Full or Part',
E.eecJobCode as 'Job Code',
eepNameLast as 'Last Name',
eepNameSuffix as 'Suffix',
eepNameFirst as 'First Name',
EepNameMiddle as 'Middle Name',
E.EecJobtitle as 'Job Title',
E.EecAnnSalary as 'Annual Salary',
E.EecDateLastPayDatePaid as 'Last Pay Date',
E.EecTermReason as 'Term Code'
FROM EmpPers JOIN EmpComp E ON E.eecEEID = eepEEID
JOIN Company ON eecCoID = cmpCoID
WHERE EecDateOfTermination IS NOT NULL
and not exists (select 1 from EmpComp e2 where e2.EecEEID = E.EecEEID and e2.eecEmplStatus = 'A')-- this is to filter out anyone that was termed then re-hired
AND E.EecTermReason not in ('I01','I02','I03','I14','I22','V05','V07','V09','V12','V22','V13')
--AND DATEPART(YEAR, e.eecDateOfLastHire) = '2011'
and DATEPART(YEAR, e.eecDateOfLastHire) = @yearpicked
ORDER BY E.eecDateOfLastHire DESC
June 5, 2012 at 10:09 am
Other than changing this:
and DATEPART(YEAR, e.eecDateOfLastHire) = @yearpicked
to this:
and e.eecDateOfLastHire >= dateadd(yy, (@yearpicked - 1900), 0) and e.eecDateOfLastHire < dateadd(yy, (@yearpicked - 1900) + 1, 0)
Your code doesn't necessarily need to change just because of how you change how your users select a year (unless they end up passing a complete date such as 2012-01-31 for the year 2012). You could present them with a drop down list of years to select from.
June 5, 2012 at 10:21 am
wow interesting.
Thanks Lynn, I'll try and figure out how to add the drop down list.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply