Max 2 Dates by Employee

[FireGeek21]

I need the max 2 check dates for each employee. I was using a temp table for the most recent check date but now I need the check just before it but keep getting ALL checks before the max check. Here is my code:

SELECT   PAY.COMPANY as tCO, PAY.EMPLOYEE as tEE, MAX(PAY.CHECK_DATE) as tCHKDT
INTO     #t
FROM     db.PAYTABLE PAY
GROUP BY PAY.COMPANY, PAY.EMPLOYEE
ORDER BY PAY.EMPLOYEE

SELECT   db.PAYTABLE.EMPLOYEE, Max(db.PAYTABLE.CHECK_DATE) AS PREVIOUS_CHECK, #t.tCHKDT AS MAX_CHECK
FROM     db.PAYTABLE, #t
WHERE    db.PAYTABLE.EMPLOYEE = #t.tEE
  and    db.PAYTABLE.CHECK_DATE < 
			(SELECT MAX(#t.tCHKDT)
			 FROM   #t
			 WHERE  #t.tCO = db.PAYTABLE.COMPANY
			   and  #t.tEE = db.PAYTABLE.EMPLOYEE)
GROUP BY db.PAYTABLE.EMPLOYEE, db.PAYTABLE.CHECK_DATE, #t.tCHKDT
ORDER BY db.PAYTABLE.EMPLOYEE, prod.dbo.PAYMASTR.CHECK_DATE

drop table #t

Attached is a pic of what I am getting. I need something like the following:

EMPLOYEE   PREVIOUS_CHECK            MAX_CHECK
91         2011-05-13 00:00:00:000   2011-11-10 00:00:00:000
93         2016-06-03 00:00:00:000   2016-08-12 00:00:00:000
101        2016-07-01 00:00:00:000   2016-08-12 00:00:00:000

Thanks!

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[gmmastros]

try this:

; With Data As
(
	SELECT   PAY.COMPANY as tCO, 
			PAY.EMPLOYEE as tEE, 
			PAY.CHECK_DATE,
			Row_Number() Over (Partition By Pay.Company, Pay.Employee Order By Pay.Check_Date DESC) As RowId
	FROM     db.PAYTABLE PAY
)
Select	A.tCO, A.tEE, B.CHECK_Date As Previous, A.CHECK_DATE As Last
From	Data As A
		Left Join Data As B
			On A.tCO = B.tCO
			And A.tEE = B.tEE
			And A.RowId = 1
			And B.RowId = 2

If this works, and you would like me to explain, let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[FireGeek21]

Thank you for your assistance George. See the attached image as to what I am seeing now. Lots of Nulls. Need to get rid of those.

I did add at the end a Where A.CHECK_DATE <> NULL but that put be back to where I was originally.

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[Andrzejek]

Where A.CHECK_DATE <> NULL" looks weird to me.
How about "Where A.CHECK_DATE IS NOT NULL"

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[gmmastros]

try this:

; With Data As
(
  SELECT  PAY.COMPANY as tCO, 
          PAY.EMPLOYEE as tEE, 
          PAY.CHECK_DATE,
          Row_Number() Over (Partition By Pay.Company, Pay.Employee Order By Pay.Check_Date DESC) As RowId
  FROM    db.PAYTABLE PAY
)
Select  A.tCO, A.tEE, B.CHECK_Date As Previous, A.CHECK_DATE As Last
From    Data As A
        Left Join Data As B
           On A.tCO = B.tCO
           And A.tEE = B.tEE
           And B.RowId = 2 
Where   A.RowId = 1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[FireGeek21]