pls help - 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

SQL Server 2000 Forums

SQL Server Development (2000)

pls help

Author

Topic

pooh
Starting Member

4 Posts

Posted - 2004-06-17 : 04:21:11

This is my statement:

(SELECT MAX(Year)AS Year,Dev4.uf_FAMonthName(MAX(Dev4.uf_FAMonthValue(Month)))AS Month,Iden
FROM dev4.tb_FANewAsset
--p/s:cant add condition of latest year here.cause there will be
--diff years in my table
GROUP BY Year,Iden)

where i need to find the latest month(from the latest year),group by Iden.

the result i get:
------------------------------
Year Month Iden
2003/2004 DEC AA
2005/2006 JAN AA

which is incorrect, since what i want is 2005/2006 JAN AA.
anyone can help ?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-17 : 07:12:16

Do you only want 2005/2006 JAN AA?

If so then why not use a select top 1 ordering by the Year Month desc

Im not sure what your udf does, but Something like this might do it.

SELECT TOP 1 MAX(Year)AS Year,Dev4.uf_FAMonthName(MAX(Dev4.uf_FAMonthValue(Month)))AS Month,Iden
FROM dev4.tb_FANewAsset
--p/s:cant add condition of latest year here.cause there will be
--diff years in my table
GROUP BY Year,Iden
ORDER BY MAX(Year)AS Year,Dev4.uf_FAMonthName(MAX(Dev4.uf_FAMonthValue(Month))) DESC

Duane.

pooh
Starting Member

4 Posts

Posted - 2004-06-17 : 20:47:09

let me clarify my question.:O) my table might be:
Year Month Iden
2003/2004 DEC AA
2005/2006 JAN AA
2003/2004 AUG BB

so, i need to get the latest month and year from EACH Iden.

that's mean, my result would be:
Year Month Iden
2005/2006 JAN AA
2003/2004 AUG BB

do you know what's wrong with my coding? thankx for your help Duane.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-17 : 21:20:07

I don't like your functions :), but this is what you need to accomplish. I just put in numbers for the months.

DECLARE @weirdyear TABLE(
Year CHAR(9),
Month CHAR(3),
Iden CHAR(2))

INSERT @weirdyear(Year, Month, Iden)
SELECT '2003/2004',12,'AA'
UNION ALL
SELECT '2005/2006',1,'AA'
UNION ALL
SELECT '2003/2004',8,'BB'

SELECT wy2.Year, MAX(wy1.Month), wy2.Iden
FROM
@weirdyear wy1
INNER JOIN (
SELECT MAX(Year) AS Year, Iden
FROM @weirdyear
GROUP BY Iden) wy2 ON wy1.Year = wy2.Year
AND wy1.Iden = wy2.Iden
GROUP BY
wy2.Year,
wy2.Iden

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

pooh
Starting Member

4 Posts

Posted - 2004-06-17 : 21:38:07

:P anyway, i'd tried but i just took a portion of it cause i cant affort to do in your way(my table is with huge record)

SELECT wy2.Year, MAX(wy1.Month), wy2.Iden
FROM .tb_New wy1
INNER JOIN (
SELECT MAX(Year) AS Year, Iden
FROM .tb_New
GROUP BY Iden) wy2 ON wy1.Year = wy2.Year
AND wy1.Iden = wy2.Iden
GROUP BY
wy2.Year,
wy2.Iden

anyhow, i got the latest rec for AAA but not for others. y?
original table:
Year Month Iden
2003/2004 DEC AA
2005/2006 JAN AA
2003/2004 AUG BB
2003/2004 JUL BB

the result i get is:
Year Month Iden
2005/2006 JAN AA
2003/2004 JUL BB

(where BB should return the AUG record..)
thanks

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-17 : 23:42:21

Because it's not August, It's a word called 'AUG'. There's a big difference. You're going to have to convert those to a month and take the max of that.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

pooh
Starting Member

4 Posts

Posted - 2004-06-18 : 04:33:57

derrickleggett, thanks for your help ^O^

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources