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.
| Author |
Topic |
|
swims01
Yak Posting Veteran
59 Posts |
 Posted - 2009-05-06 : 17:29:46
|
| I have a table (tblContracts) that tracks cancellations. For simplicity sake we'll say it has five fields: ContractID, FacilityID, CancelDate, MemberID, ContractNumber.I'd like to show the total number of member cancels for today grouped by each facility.ContractID is a unique record IDFacilityID is one of three results.CancelDate is the day the member cancelled.MemberID is the id of the member.ContractNumber is the number of the contract for the member.There can be multiple entries for the member. The highest contractNumber is the most recent contract to look at.The highest contractID is the most recent record for the most recent contractNumber.My current query can return the total number for all facilities but it doesn't break it down by facility. I think I've got the hard part done...SELECT COUNT(members) AS [# of Cancels]FROM (SELECT MAX(ContractNumber) AS members FROM tblContracts WHERE (CancelDate = '5/5/2009') GROUP BY MemberId) AS subThis looks to be similar to txverm's example here... http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=125217 |
|
|
Tapalotapus
Starting Member
22 Posts |
Posted - 2009-05-06 : 18:34:07
|
| This should give you what your looking for.SELECT count(MemberID) as 'members', FacilityID FROM tblContracts WHERE (CancelDate = '1/1/2009') GROUP BY FacilityID |
 |
|
|
swims01
Yak Posting Veteran
59 Posts |
Posted - 2009-05-07 : 11:20:01
|
| Thanks Tap..The query needs to take into consideration the highest contractNumber for each person. |
|
|
|
|
|
|
Aggregate Function / Group by similar to txverm's