Max date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Max date

Author

Topic

Sep410
Posting Yak Master

117 Posts

Posted - 2008-06-10 : 11:24:31

I need to have greatest date in a view :
SELECT DISTINCT PERSONNUM, MAX(EFFECTIVEDATE) AS EDate, PERSONFULLNAME
FROM tkcsdb2.dbo.VP_ACCRUAL
WHERE (ACCRUALCODENAME = N'Personal')
GROUP BY PERSONNUM, ACCRUALTRANAMOUNT, PERSONFULLNAME
HAVING (PERSONNUM = N'10002')

but it returns:
1002 4/1/2007 k
1002 10/7/2007 k
1002 1/26/2008 k

I just need the last row.
What shoudl I do?

Sep

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-06-10 : 11:26:52

SELECT ~~DISTINCT~~ PERSONNUM, MAX(EFFECTIVEDATE) AS EDate, PERSONFULLNAME
FROM tkcsdb2.dbo.VP_ACCRUAL
WHERE (ACCRUALCODENAME = N'Personal')
and HAVING (PERSONNUM = N'10002')

GROUP BY PERSONNUM, ~~ACCRUALTRANAMOUNT,~~ PERSONFULLNAME

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-06-10 : 11:28:44

oops! I copied and pasted the HAVING

SELECT ~~DISTINCT~~ PERSONNUM, MAX(EFFECTIVEDATE) AS EDate, PERSONFULLNAME
FROM tkcsdb2.dbo.VP_ACCRUAL
WHERE (ACCRUALCODENAME = N'Personal')
and (PERSONNUM = N'10002')

GROUP BY PERSONNUM, ~~ACCRUALTRANAMOUNT,~~ PERSONFULLNAME

Sep410
Posting Yak Master

117 Posts

Posted - 2008-06-10 : 11:34:03

Thank you so much.

Sep

Sep410
Posting Yak Master

117 Posts

Posted - 2008-06-10 : 11:37:05

Now the problem is when I add AccrualTransAmount to the reporting fields again it shows me 3 date but I only need that filed for that date.
SELECT PERSONNUM, MAX(EFFECTIVEDATE) AS EDate, PERSONFULLNAME, ACCRUALTRANAMOUNT
FROM tkcsdb2.dbo.VP_ACCRUAL
WHERE (ACCRUALCODENAME = N'Personal')
GROUP BY PERSONNUM, PERSONFULLNAME, ACCRUALTRANAMOUNT
HAVING (PERSONNUM = N'10002')

this is what I get
10002 1/26/2008 k 0
10002 10/7/2007 k 144000
10002 4/1/2007 k -144000

And I only need the first row.

Sep

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 12:01:56

quote:
Originally posted by Sep410

Now the problem is when I add AccrualTransAmount to the reporting fields again it shows me 3 date but I only need that filed for that date.
SELECT PERSONNUM, MAX(EFFECTIVEDATE) AS EDate, PERSONFULLNAME, ACCRUALTRANAMOUNT
FROM tkcsdb2.dbo.VP_ACCRUAL
WHERE (ACCRUALCODENAME = N'Personal')
GROUP BY PERSONNUM, PERSONFULLNAME, ACCRUALTRANAMOUNT
HAVING (PERSONNUM = N'10002')

this is what I get
10002 1/26/2008 k 0
10002 10/7/2007 k 144000
10002 4/1/2007 k -144000

And I only need the first row.

Sep

remove ACCRUALTRANAMOUNT from group by and use MIN or MAX. B/w are you concerned about the value to be retrieved for ACCRUALTRANAMOUNT field or a random value is enough?

Sep410
Posting Yak Master

117 Posts

Posted - 2008-06-10 : 12:05:32

No the ACCRUALTRANAMOUNT should be the one which is in the max record.I mean
it should be 0 in the this example
10002 1/26/2008 k 0<----------- I need this amount which is for the greatest date
10002 10/7/2007 k 144000
10002 4/1/2007 k -144000

Sep

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 12:37:10

[code]SELECT t.PERSONNUM, t.EFFECTIVEDATE AS EDate, t.PERSONFULLNAME, t.ACCRUALTRANAMOUNT
FROM tkcsdb2.dbo.VP_ACCRUAL t
INNER JOIN (SELECT PERSONNUM, MAX(EFFECTIVEDATE) AS MaxDate
FROM tkcsdb2.dbo.VP_ACCRUAL
GROUP BY PERSONNUM)m
ON m.PERSONNUM=t.PERSONNUM
AND m.MaxDate=t.EFFECTIVEDATE
WHERE (ACCRUALCODENAME = N'Personal')
AND (PERSONNUM = N'10002')[/code]

Sep410
Posting Yak Master

117 Posts

Posted - 2008-06-10 : 13:19:39

it is not reyurning right value for ACCRUALTRANAMOUNT!!!

Sep

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-10 : 13:22:36

quote:
Originally posted by Sep410

it is not reyurning right value for ACCRUALTRANAMOUNT!!!

Sep

Only reason i suspect you didnt get the correct value is datatype of
EFFECTIVEDATE. Are you sure its datetime?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources