Choosing by year

Question

Post reply

Choosing by year

krypto69

SSChampion

Points: 13604
More actions
June 4, 2012 at 10:34 am

#392424

Hi,
Sorry kinda new to ssrs.
I have report that is working fine.
However, they now want to be able to pick a year (instead of all years coming back).
I have this query, what do I cnage so my users can pick (or type in ) a year?
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')
ORDER BY E.eecDateOfLastHire DESC

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Geoff A SSChampion Points: 11511 More actions · Answer 1

add something like this to your WHERE statement.

and DATEPART(YEAR, e.eecDateOfLastHire) = @yearpicked

you could populate the @yearpicked variable in SSRS by defining that varialbe with another select statement.

select distinct DATEPART(YEAR, e.eecDateOfLastHire) from YourTable

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Here are two options depending on how you send the date to the query:

DECLARE @SelectYear DATETIME;

SET @SelectYear = '2012-02-01'; -- With this date, select all records in 2012

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 e.eecDateOfLastHire >= DATEADD(yy, DATEDIFF(yy, 0, @SelectYear), 0)

AND e.eecDateOfLastHire < DATEADD(yy, DATEDIFF(yy, 0, @SelectYear) + 1, 0)

ORDER BY

E.eecDateOfLastHire DESC

--OR, if only passed a year

DECLARE @SelectYear INT;

SET @SelectYear = 2012; -- select all records in 2012