June 28, 2012 at 7:36 am
Hi,
I have this query, works fine:
SELECT MAX(EC.eecDateOfTermination) FROM EMPCOMP EC WHERE EC.EecEEID = E.EECEEID AND (eC.eecDateOfTermination <= '12/31/2011' AND (EC.eecDateOfTermination is not null OR EC.eecDateOfTermination >= '01/01/2011')))
This pulls what I need for the year. Now, they want this one week at a time (they only want the current weeks data)
This will be scheduled as a job to run every Friday - they want Monday thru Friday's terminated employees of the current week.
How would I write this?
June 28, 2012 at 7:48 am
I'm close but this isn't right....
(SELECT MAX(EC.eecDateOfTermination) FROM EMPCOMP EC WHERE EC.EecEEID = E.EECEEID AND (eC.eecDateOfTermination <= (SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)) AND (EC.eecDateOfTermination is not null OR EC.eecDateOfTermination >= (SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)))))
June 28, 2012 at 7:48 am
It might help if you posted your actual query formatted is a way that people can read.
SELECT
MAX(EC.eecDateOfTermination)
FROM
EMPCOMP EC
WHERE
-- no table with an alias of E in this query
EC.EecEEID = E.EECEEID AND
(eC.eecDateOfTermination <= '12/31/2011' AND
(
-- null check is unnecessary since you already checked for date LT end of year
EC.eecDateOfTermination is not null OR
EC.eecDateOfTermination >= '01/01/2011')
) -- unbalanced parenthesis )
Have they considerd that there might be employees terminated on Sat or Sun? Maybe the range should be from the prior Sat through Fri?
June 28, 2012 at 7:52 am
You could add these into your WHERE clause for the date range:
SELECT DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0) First Day of Last Week
SELECT DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0) + 6 -- Beginning of 6th day of previous week
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
June 28, 2012 at 8:04 am
Okay here's everything I have so far...runs, but not correct results...not filtering by week..
can't figure out why
--With Terminations
SELECT
----DATEPART(YEAR, e.eecDateOfLastHire) as 'Year of Hire',
--EEPDATEOFBIRTH,
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', 'TRO')
AND E.eecDateOfTermination in (SELECT MAX(EC.eecDateOfTermination) FROM EMPCOMP EC WHERE EC.EecEEID = E.EECEEID AND (eC.eecDateOfTermination <= (SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)) AND (EC.eecDateOfTermination is not null OR EC.eecDateOfTermination >= (SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)))))
AND DATEPART(wk, e.eecDateOfTermination) <= (SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)) AND (E.eecDateOfTermination is not null OR E.eecDateOfTermination >= (SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)))
June 28, 2012 at 8:11 am
I'm not at my laptop at the moment however does this work for you?
--With Terminations
SELECT
----DATEPART(YEAR, e.eecDateOfLastHire) as 'Year of Hire',
--EEPDATEOFBIRTH,
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', 'TRO')
AND E.eecDateOfTermination
IN (SELECT MAX(EC.eecDateOfTermination)
FROM EMPCOMP EC
WHERE EC.EecEEID = E.EECEEID
AND EC.eecDateOfTermination IS NOT NULL
AND eC.eecDateOfTermination >= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
AND EC.eecDateOfTermination <=DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)
AND e.eecDateOfTermination >= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
AND E.eecDateOfTermination <= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)
AND E.eecDateOfTermination IS NOT NULL )
Sorry its quite difficult to debug without any test data to see what you are trying to achieve, there is probably a much easier way
to accomplish this
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
June 28, 2012 at 8:33 am
I think that's it....checking now..
Thank you so much Andy!
June 28, 2012 at 8:39 am
No probs 🙂
The OR operator is a dangerous beast, and you also had your <= >=evaluations the wrong way round 😉
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
June 28, 2012 at 9:09 am
darn it....it's not right...not filtering by week...
I changed it (a little)..
SELECT
E.eecCoID AS 'Rec ID',
E.EecEEID AS 'Emp ID',
eepNameFirst AS 'First Name',
eepNameLast AS 'Last Name',
EecDateOfOriginalHire AS 'Service Date',
EecDateOfTermination
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')-- changed to <> ...this WAS/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', 'TRO')
AND E.eecDateOfTermination
IN (SELECT MAX(EC.eecDateOfTermination)
FROM EMPCOMP EC
WHERE EC.EecEEID = E.EECEEID
AND EC.eecDateOfTermination IS NOT NULL
AND eC.eecDateOfTermination >= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
AND EC.eecDateOfTermination <=DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)
AND e.eecDateOfTermination >= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
AND E.eecDateOfTermination >= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)
AND E.eecDateOfTermination IS NOT NULL ))
On date of termination I'm showing allot of years/records...should only show this week...
June 28, 2012 at 9:17 am
Does this work?
SELECT
E.eecCoID AS 'Rec ID',
E.EecEEID AS 'Emp ID',
eepNameFirst AS 'First Name',
eepNameLast AS 'Last Name',
EecDateOfOriginalHire AS 'Service Date',
EecDateOfTermination
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')-- changed to <> ...this WAS/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', 'TRO')
AND E.eecDateOfTermination
IN (SELECT MAX(EC.eecDateOfTermination)
FROM EMPCOMP EC
WHERE EC.EecEEID = E.EECEEID
AND EC.eecDateOfTermination IS NOT NULL
AND eC.eecDateOfTermination >= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
AND EC.eecDateOfTermination <=DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)
AND e.eecDateOfTermination >= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
AND E.eecDateOfTermination <= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)
AND E.eecDateOfTermination IS NOT NULL ))
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
June 28, 2012 at 9:27 am
Nope!
<bangs head against wall>
Sure doesn't (still shows years and years of terminations) and I can't figure out why if I run just this:
SELECT (eecDateOfTermination)
FROM EMPCOMP
WHERE
eecDateOfTermination IS NOT NULL
AND eecDateOfTermination >= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
AND eecDateOfTermination <=DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)
It works fine...
June 28, 2012 at 9:42 am
Sorry I've done the best I can on my phone and working blind!
If you could post some sample data and table defs I'm sure we can sort it but it is difficult as I can't see the structure..
Looking at the query as it is it looks way over complicated!
For example would the following not work for you why the MAX and Subqueries?
SELECT
E.eecCoID AS 'Rec ID',
E.EecEEID AS 'Emp ID',
eepNameFirst AS 'First Name',
eepNameLast AS 'Last Name',
EecDateOfOriginalHire AS 'Service Date',
EecDateOfTermination
FROM
EmpPers
JOIN EmpComp E
ON E.eecEEID = eepEEID
JOIN Company
ON eecCoID = cmpCoID
WHERE
EecDateOfTermination IS NOT NULL
AND E.EecEEID IN
(SELECT EecEEID
FROM EMPCOMP EC
WHERE
EC.eecDateOfTermination IS NOT NULL
AND eC.eecDateOfTermination >= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
AND EC.eecDateOfTermination <=DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)
)
OR
SELECT
E.eecCoID AS 'Rec ID',
E.EecEEID AS 'Emp ID',
eepNameFirst AS 'First Name',
eepNameLast AS 'Last Name',
EecDateOfOriginalHire AS 'Service Date',
EecDateOfTermination
FROM
EmpPers
JOIN EmpComp E
ON E.eecEEID = eepEEID
JOIN Company
ON eecCoID = cmpCoID
WHERE
E.EecDateOfTermination IS NOT NULL
AND e.eecDateOfTermination >= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
AND E.eecDateOfTermination <=DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
June 28, 2012 at 10:09 am
You are right!
That did the trick!
Thanks soooo much for all your help Andy!
June 28, 2012 at 10:36 am
No worries, got there in the end 😉
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply