Query Help

Question

Query Help

Stephen crocker

SSCrazy

Points: 2512
More actions
June 4, 2010 at 10:30 am

#221863

I have a table that contains a customer key, event code
There is multiple customer key's and events because the customers register for the event every year, the event code has the name of the event
What i need to do is isolate the customers that did not sign up for the event prior to the last event
Example cst_id 1 registered for evnt1, evt2, evt5, and evt10
cst_id 2 registered for evt10
Table looks like this
CstID evtcode
1 evt1
1 evt2
1 evt5
1 evt10
2 evt10
3 evt9
3 evt10
4 evt10
What i need is to know how to exclude cstID1 and 3 because 2 and 4 are the only ones who did now have prior events.
Could I get someone to help on this. I currently have all of the information in a view just need to know how to to the grouping counting or logic on it

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Stephen crocker (6/4/2010)
I have a table that contains a customer key, event code
There is multiple customer key's and events because the customers register for the event every year, the event code has the name of the event
What i need to do is isolate the customers that did not sign up for the event prior to the last event
Example cst_id 1 registered for evnt1, evt2, evt5, and evt10
cst_id 2 registered for evt10
Table looks like this
CstID evtcode
1 evt1
1 evt2
1 evt5
1 evt10
2 evt10
3 evt9
3 evt10
4 evt10
What i need is to know how to exclude cstID1 and 3 because 2 and 4 are the only ones who did now have prior events.
Could I get someone to help on this. I currently have all of the information in a view just need to know how to to the grouping counting or logic on it

select

CstID

from

dbo.MyTable

group by

CstID

having

count(evtcode) = 1;

Does this help?

Ben Teraberry SSChampion Points: 11845 More actions · Answer 2

The request isn't altogether clear, but the way I'm reading it is that you're looking for the people who went to the last event as their first event. If this is the case there is an issue with the evtCode field in that it is a char/nchar field and getting the correct order of return is going to be problematic. For instance, for SQL Server, 'evt2' > 'evt10' with a standard collating order. You can force the values into the correct order but generally I would hope you have a lookup table with an int field that establishes the correct order on a numerical basis.

create table #temp

(

CstIDint,

evtCodevarchar(10)

)

insert into #temp

select 1, 'evt01' union

select 1, 'evt02' union

select 1, 'evt05' union

select 1, 'evt10' union

select 2, 'evt10' union

select 3, 'evt09' union

select 3, 'evt10' union

select 4, 'evt10';

This is one way you can go about obtaining those ID's that came for the first time to the most recent event:

with cteTemp(CstID, evtCode, evtOrder)

as

(

select CstID,

evtCode,

ROW_NUMBER() OVER (PARTITION BY CstID ORDER BY evtCode)

from #temp

)

select CstID

from cteTemp

where evtOrder = 1

and evtCode = (select MAX(evtCode) from #temp);

drop table #temp

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Stephen crocker SSCrazy Points: 2512 More actions · Answer 3

I also have the cst_id which is not a number i did that for simplicity it's actually a guid

does that help any.