INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Max 2 Dates by Employee

Max 2 Dates by Employee

(OP)
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:

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:

CODE -->

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)

RE: Max 2 Dates by Employee

try this:

CODE

; 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

RE: Max 2 Dates by Employee

(OP)
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)

RE: Max 2 Dates by Employee

"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.

RE: Max 2 Dates by Employee

try this:

CODE

; 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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close