Noob having troble with group by etc.

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)

Noob having troble with group by etc.

Author

Topic

damnhippie
Starting Member

2 Posts

Posted - 2006-09-06 : 12:05:15

Well i guess it is a bit more than just group by but here we go...

General description :( background info on the problem; may or may not be needed)

I am trying to get a list of active card holders for a particular month. A card holder is considered active if their status was active any time during that month. I have a card holder status in my CardHolder table but that will only give me their current status. I also have a CardHolderHistory table that saves any changes to a card holder including their status. Using the CardHolderHistory table i can find out who was active for any month in question by using the following algorithm:

A card holder is active if they meet one or both of the following conditions;

1) The last entry before the month in question has them as active in CardHolderHistory or

2) They were set to active any time during the month in question

The problem:

I create a temporary table and copy the card holders into the table with separate select into statements for each of the above conditions. For the first condition, I first gather all the CardHolderHistory entries before the month in question with the following select

select EmployerName, cardholderhistory.FirstName, cardholderhistory.Initial, cardholderhistory.LastName, ChangeDate, CardHolderHistory.CardState, FKEmployerID

into #MonthlyCards from cardholderhistory left join Employer on PKEmployerID = cardholderhistory.FKEmployerID

where changedate<@datespecified

This gives me ALL of the CardHolderHistory before the month in question (@dateSpecified).

It would look something like this

EmployerName First Initial Last Status ChangeDate EmployerID

--------------------------------------------------------------------------

.

.

.

Some Fake Company John P Doe A 2006-05-02 11:34:26.360 109

Some Fake Company John P Doe A 2006-03-28 11:14:10.520 109

Some Fake Company Jane T Doe S 2006-05-30 15:34:14.900 109

Some Fake Company Jane T Doe A 2006-03-28 12:20:03.670 109

Some Fake Company Jane T Doe A 2006-03-01 10:12:45.320 109

.

. (S = suspended; A = Active)

.

Now I need to make sure people are only listed once and remove everyone whose status = suspended for their last change date. That is my problem; I can’t figure out how to remove these people. The next step i took was to do a group by with EmployerName, FirstName, Initial, LastName, Status, Max(ChangeDate), FKEmployerID. This removes everyone listed multiple times except if their status changes. After the group by the list would be

EmployerName First Initial Last Status ChangeDate EmployerID

--------------------------------------------------------------------------

Some Fake Company John P Doe A 2006-05-02 11:34:26.360 109

Some Fake Company Jane T Doe S 2006-05-30 15:34:14.900 109

Some Fake Company Jane T Doe A 2006-03-28 12:20:03.670 109

Now I need to delete Jane completely, since her latest status is S. I could delete where Status = ‘S’ but that leaves her as active from 3/28. I can’t figure out how to completely remove Jane. The rest I can figure out I think. Please help me get rid of Jane for good, I don’t like her.

Thanks for any help,

Rico

WTB/WTS a little help

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-09-06 : 12:29:46

following is some code to get you moving in the right direction....I'm rushing for time so I'm not sure if I've got it 100% right....but play around with it and you'll get there.

you need a combination of an 'exists' clause and a delete statement.

concentrate on getting it right for 1 customer (jane)..and then remove the 'jane' bit to target all customers.

1st...you need the last applicable record for Jane. (ORDER BY DESC, TOP 1 gets 1 record ie the last 'according' to the SORT order)
2nd...if it has an 's' then you need to do something else...ie get all janes eligible data
3rd...turn the SELECT into a DELETE using the style of code
delete a
from table1 a
inner join table2 b on a.id = b.id

1.
select top 1 * from
cardholderhistory c left join Employer d on d.PKEmployerID = c.FKEmployerID
where c.changedate<@datespecified
and d.name = 'jane'
order by date desc

2.
select *
from cardholderhistory a
left join Employer b on b.PKEmployerID = a.FKEmployerID
where exists
(select * from
(select top 1 * from
cardholderhistory c left join Employer d on d.PKEmployerID = c.FKEmployerID
where c.changedate<@datespecified
and d.name = b.name
order by date desc) z
where z.active = 's')
where c.changedate<@datespecified
and c.name = 'jane'

I'm missing some ")" here and also some table aliases...but I think it'd be close to matching your requirements.

4...wrap this in a begin transaction...(so that you can rollback if it's wrong)
5...take a backup 1st.

damnhippie
Starting Member

2 Posts

Posted - 2006-09-06 : 14:22:23

Thank you Andrew Murphy,

Someone from the Microsoft forums helped me out before you had replied. He suggested a different approch and I was able to solve my problem. I will try your suggestions also as an exercise as i am very interested to see if i can get it to work the way i had first tried. i have a few more questions now that i solved my original problemm so if you, or anyone else could take a look that would be great. Also, if anyone else is interested in how i was able to solve the problem check out

[url]http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=702539&SiteID=17[/url]

thanks again

WTB/WTS a little help

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-09-07 : 05:23:00

If there are other problems....post them here...probably best to post them as seperate items, unless they are interlinked. If possible post some table DDL and sample data in the form of INSERT statements....so that we can re-create your environment 'EASILY'....plus expected results.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources