Query to show max date per account num

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)

Query to show max date per account num

Author

Topic

plawrenz
Starting Member

15 Posts

Posted - 2007-10-21 : 21:33:22

I have 2 tables Acct and Transactions and in my current query it shows:

AcctNum Date Amount
--------- --------- ------
1 9/1/2007 10.00
2 9/3/2007 10.00
2 9/7/2007 15.00
3 9/1/2007 20.00
3 9/10/2007 30.00
3 9/15/2007 50.00

I just want it to show only the last date for each AcctNum

AcctNum Date Amount
--------- --------- ------
1 9/1/2007 10.00
2 9/7/2007 15.00
3 9/15/2007 50.00

How would i write a query to show this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-21 : 21:39:51

[code]DECLARE @sample TABLE
(
AcctNum int,
[Date] datetime,
Amount decimal(10,2)
)
INSERT INTO @sample
SELECT 1, '9/1/2007', 10.00 UNION ALL
SELECT 2, '9/3/2007', 10.00 UNION ALL
SELECT 2, '9/7/2007', 15.00 UNION ALL
SELECT 3, '9/1/2007', 20.00 UNION ALL
SELECT 3, '9/10/2007', 30.00 UNION ALL
SELECT 3, '9/15/2007', 50.00

SELECT s.*
FROM @sample s
INNER JOIN
(
SELECT AcctNum, [Date] = MAX([Date])
FROM @sample
GROUP BY AcctNum
) m ON s.AcctNum = m.AcctNum
AND s.[Date] = m.[Date]
ORDER BY s.AcctNum

/*
AcctNum Date Amount
----------- ------------------------------------------------------ ------------
1 2007-09-01 00:00:00.000 10.00
2 2007-09-07 00:00:00.000 15.00
3 2007-09-15 00:00:00.000 50.00
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

plawrenz
Starting Member

15 Posts

Posted - 2007-10-21 : 22:22:08

I guess I was hoping for an easier solution so I simplified my orignal question. Here is the real query and I am hoping you can help since I am not good with inner joins:

Here is my real query I only want 13 and 16 with the 9/25 date and not the 9/16 date since these are the only AcctNum that show up more than once and i only want the max date per acctnum:
SELECT
DE.PortCde,
BT.EffDte,
DE.AcctNum,
BT.FuncCde,
FL.TypeCde,
FL.LineCde,
FL.TransAmt
FROM
FinancialLines FL,
Derivative DE,
BusinessTrans BT
WHERE
BT.BusinessTransNum = FL.BusinessTransNum AND
FL.SubjectNum = DE.AcctNum AND
FL.TypeCde = 48 AND
FL.LineCde = 104 AND
BT.FuncCde LIKE 'CLSFUTUR' AND
BT.EffDte >= '09/01/2007' AND
BT.EffDte <= '09/30/2007'

PortCde EffDte AcctNum FuncCde TypeCde BusTrnCde TransAmt
55055099 Sep 17 2007 1 CLSFUTUR 48 104 475.00
55055099 Sep 17 2007 3 CLSFUTUR 48 104 250.00
55055099 Sep 17 2007 5 CLSFUTUR 48 104 500.00
55055099 Sep 19 2007 2 CLSFUTUR 48 104 475.00
55055099 Sep 19 2007 4 CLSFUTUR 48 104 250.00
55055099 Sep 19 2007 6 CLSFUTUR 48 104 500.00
55055099 Sep 19 2007 13 CLSFUTUR 48 104 8.00
55055099 Sep 19 2007 16 CLSFUTUR 48 104 99.00
55055099 Sep 25 2007 13 CLSFUTUR 48 104 400.00
55055099 Sep 25 2007 16 CLSFUTUR 48 104 300.00
55055099 Sep 25 2007 22 CLSFUTUR 48 104 200.00
55055099 Sep 25 2007 23 CLSFUTUR 48 104 500.00
55055099 Sep 25 2007 24 CLSFUTUR 48 104 100.00

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-21 : 22:41:44

[code]SELECT
DE.PortCde,
BT.EffDte,
DE.AcctNum,
BT.FuncCde,
FL.TypeCde,
FL.LineCde,
FL.TransAmt
FROM
FinancialLines FL
INNER JOIN Derivative DE ON FL.SubjectNum = DE.AcctNum
INNER JOIN BusinessTrans BT ON BT.BusinessTransNum = FL.BusinessTransNum
INNER JOIN
(
SELECT
DE.AcctNum, EffDte = MAX(BT.EffDte)
FROM
FinancialLines FL
INNER JOIN Derivative DE ON FL.SubjectNum = DE.AcctNum
INNER JOIN BusinessTrans BT ON BT.BusinessTransNum = FL.BusinessTransNum
WHERE
FL.TypeCde = 48 AND
FL.LineCde = 104 AND
BT.FuncCde LIKE 'CLSFUTUR' AND
BT.EffDte >= '09/01/2007' AND
BT.EffDte <= '09/30/2007'
GROUP BY
DE.AcctNum
) M ON DE.AcctNum = M.AcctNum
AND BT.EffDte = M.EffDte
WHERE
FL.TypeCde = 48 AND
FL.LineCde = 104 AND
BT.FuncCde LIKE 'CLSFUTUR' AND
BT.EffDte >= '09/01/2007' AND
BT.EffDte <= '09/30/2007'[/code]

KH
[spoiler]Time is always against us[/spoiler]

plawrenz
Starting Member

15 Posts

Posted - 2007-10-22 : 13:09:25

Thank you that worked and now I understand!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources