Query to select latest date for subset of data

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

Transact-SQL (2000)

Query to select latest date for subset of data

Author

Topic

EarlEBurba
Starting Member

3 Posts

Posted - 2005-07-14 : 15:57:08

I'm trying to generate reports to display the latest dates for id's. The data comes from two different tables. Given the following data

id year month Count Volume
----- ----- ----- ------ ----------
5559 2005 3 60110 8541303509
5559 2005 2 58367 8196839165
5559 2005 1 57333 8009625406
5559 2004 12 56376 7825335831
5558 2005 3 5756 1032721607
5558 2005 2 5405 961665922
5558 2005 1 5186 921096301
5558 2004 12 4956 875296137
5554 2005 3 48 5114861
5554 2005 2 45 4868861
5554 2005 1 43 4753861
5554 2004 12 45 4866861
5554 2004 11 43 4616861
5553 2005 3 20 854757
5553 2005 2 18 779980

Does anyone know how to create a report of id's having the latest month/year combination.

The resultant report would look like:

id year month Count Volume
----- ----- ----- ------ ----------
5559 2005 3 60110 8541303509
5559 2004 12 56376 7825335831
5558 2005 3 5756 1032721607
5558 2004 12 4956 875296137
5554 2005 3 48 5114861
5554 2004 12 45 4866861
5553 2005 3 20 854757

The closest I've come is

SELECT distinct
TreatyID,
Year,
Month,
Count,
Volume
FROM
dbo.tbl1 INNER JOIN
dbo.tbl2 ON dbo.tbl_db.1.ID = dbo.AccountSummary.TreatyID AND
dbo.tbl1.Year = dbo.tbl2.Year AND
dbo.tbl1.Month = dbo.tbl2.Month
where
dbo.tbl1.ID = dbo.tbl2.ID
GROUP BY
dbo.tbl1.ID,
dbo.tbl1.Year,
dbo.tbl1.Month,
dbo.tbl1.Count,
dbo.tbl1.Volume
ORDER BY dbo.tbl1.ID DESC,
dbo.tbl1.Year DESC,
dbo.tbl1.Month DESC;

Thanks for any help, I've been working on this problem for the last 3 days.

Earl

jhermiz

3564 Posts

Posted - 2005-07-14 : 16:06:39

Why are you using distinct?

Do you understand the differences between distinct and group by?
You will need to use aggregate functions such as MAX and then use a GROUP BY
clause to get what you want.

A lot of people mix distinct with group by hoping to get the right results, only to
find that their results work sometimes (it's very unexpected results).

For your query you will need to
Group By ID, Year and Month. You will not need to group by count.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-07-14 : 16:07:26

[code]select d.id,d.year,d.month,t.count,t.volume
from
(
select id,year,max(month) month
from t
group by id,year
) d
join t t on t.id = d.id and t.year = d.year and t.month = d.month[/code]

EarlEBurba
Starting Member

3 Posts

Posted - 2005-07-15 : 08:27:41

Thanks I'll try your suggestions. I appreciate your help since I'm trying to learn as I go.

EarlEBurba
Starting Member

3 Posts

Posted - 2005-07-17 : 13:17:56

I'm still struggling, and tried your suggestions, but still can't quite get it.

--- This works for getting the correct TreatyID and max_month for each period year
SELECT TOP 100 PERCENT dbo.AccountSummary.TreatyID, dbo.AccountSummary.PeriodYear, MAX(dbo.AccountSummary.PeriodMonth) AS Max_Month
FROM dbo.AccountSummary INNER JOIN
dbo.tbl_vwAMRPolicyExhibit ON dbo.AccountSummary.PeriodMonth = dbo.tbl_vwAMRPolicyExhibit.PeriodMonth AND
dbo.AccountSummary.PeriodYear = dbo.tbl_vwAMRPolicyExhibit.PeriodYear AND
dbo.AccountSummary.TreatyID = dbo.tbl_vwAMRPolicyExhibit.TreatyID
GROUP BY dbo.AccountSummary.TreatyID, dbo.AccountSummary.PeriodYear
HAVING dbo.AccountSummary.PeriodYear=2005
ORDER BY dbo.AccountSummary.TreatyID DESC, dbo.AccountSummary.PeriodYear DESC, Max_Month DESC

-- Actual Results

TreatyID PeriodYear PeriodMonth
-------- ---------- -----------
5559 2005 3
5558 2005 3
5554 2005 3
5553 2005 3
5552 2005 3
5551 2005 2
5550 2005 2
5548 2005 3
5547 2005 2
5542 2005 3
5541 2005 2
5538 2005 2
5537 2005 3
5532 2005 2
5531 2005 1
5522 2005 3
5521 2005 3
5518 2005 1
5517 2005 3
5516 2005 2
5514 2005 2
5513 2005 3

-- Actual results after adding in additional table entries
SELECT TOP 100 PERCENT dbo.AccountSummary.TreatyID, dbo.AccountSummary.PeriodYear, MAX(dbo.AccountSummary.PeriodMonth) AS Max_Month,
dbo.tbl_vwAMRPolicyExhibit.EndingCount, dbo.tbl_vwAMRPolicyExhibit.EndingVolume
FROM dbo.AccountSummary INNER JOIN
dbo.tbl_vwAMRPolicyExhibit ON dbo.AccountSummary.PeriodMonth = dbo.tbl_vwAMRPolicyExhibit.PeriodMonth AND
dbo.AccountSummary.PeriodYear = dbo.tbl_vwAMRPolicyExhibit.PeriodYear AND
dbo.AccountSummary.TreatyID = dbo.tbl_vwAMRPolicyExhibit.TreatyID
GROUP BY dbo.AccountSummary.TreatyID, dbo.AccountSummary.PeriodYear, dbo.tbl_vwAMRPolicyExhibit.EndingCount,
dbo.tbl_vwAMRPolicyExhibit.EndingVolume
HAVING (dbo.AccountSummary.PeriodYear = 2005)
ORDER BY dbo.AccountSummary.TreatyID DESC, dbo.AccountSummary.PeriodYear DESC, Max_Month DESC

TreatyID PeriodYear PeriodMonth EndintCount EndingVolume
-------- ---------- ----------- ----------- ------------
5559 2005 3 60110 8541303509
5559 2005 2 58367 8196839165
5559 2005 1 57333 8009625406
5558 2005 3 5756 1032721607
5558 2005 2 5405 961665922
5558 2005 1 5186 921096301
5554 2005 3 48 5114861
5554 2005 2 45 4868861
5554 2005 1 43 4753861
5553 2005 3 20 854757
5553 2005 2 18 779980
5553 2005 1 10 558149
5552 2005 3 35 3325813
5552 2005 2 28 2732244
5552 2005 1 23 1418567
5551 2005 2 367 41415417
5551 2005 1 352 40611876
5550 2005 2 2646 367676169
5550 2005 1 2400 334803705
5548 2005 3 7 595393
5547 2005 2 2 202800
5542 2005 3 63 6491250
5542 2005 2 8 483750
5542 2005 1 1 22500
5541 2005 2 93 3221880
5538 2005 2 39 676820
5538 2005 1 29 541572
5537 2005 3 2076 182394066
5537 2005 2 306 25539975
5537 2005 1 7 360675
5532 2005 2 26721 409647305
5532 2005 1 21995 334658515
5531 2005 1 4185 336686930
5522 2005 3 83 4934875
5522 2005 2 74 4529748
5522 2005 1 59 3267883
5521 2005 3 130 15216613
5521 2005 2 119 12663003
5521 2005 1 115 12281388
5518 2005 1 0 0
5517 2005 3 75 7923942
5517 2005 2 64 7122473
5517 2005 1 34 3441723
5516 2005 2 1352 49892865
5516 2005 1 78 5069670
5514 2005 2 104 12433331
5514 2005 1 89 10625725
5513 2005 3 294 31487225
5513 2005 2 266 27889475
5513 2005 1 233 24897600

-- Desired/Expected Results
TreatyID PeriodYear PeriodMonth EndintCount EndingVolume
-------- ---------- ----------- ----------- ------------
5559 2005 3 60110 8541303509
5558 2005 3 5756 1032721607
5554 2005 3 48 5114861
5553 2005 3 20 854757
5552 2005 3 35 3325813
5551 2005 2 367 41415417
5550 2005 2 2646 367676169
5548 2005 3 7 595393
5547 2005 2 2 202800
5542 2005 3 63 6491250
5541 2005 2 93 3221880
5538 2005 2 39 676820
5537 2005 3 2076 182394066
5532 2005 2 26721 409647305
5531 2005 1 4185 336686930
5522 2005 3 83 4934875
5521 2005 3 130 15216613
5518 2005 1 0 0
5517 2005 3 75 7923942
5516 2005 2 1352 49892865
5514 2005 2 104 12433331
5513 2005 3 294 31487225

Any help is greatly appreciated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources