• Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

SQL challenge

although i think a couple of the previous posts have covered it, here is my take :)

if the nr of possible events are know, ie yuo know there will be evt1,evt2 and evt3, you can do this quite simple using setbased (and standard) sql.

select person,
sum(case when event_id = 1 then 1 else 0 end) as cnt_event_1,
sum(case when event_id = 2 then 1 else 0 end) as cnt_event_2,
sum(case when event_id = 3 then 1 else 0 end) as cnt_event_3
from mytable
group by person

if it is not, and you are not using ms sql 2005 (which implements crosstab), or ms sql 2000 with an additional 3rd party crosstab xp, then you can either:

a) build a dynamic sql t obe executed via exec(@sql), or sp_executesql (ms specific). no need for cursors even, d oa coalesce with distinct to pick up the exact nr of events to match...

b) use a cursor (which almost always sucks, and tells you u probably did somethin wrong)

either way, the db will sort it out faster then taking the data out of there and process in some other language (i dont care what u guys say, datacentric processing win 9 out of 10 times).

need specifics, let me know :)

my 2 cents only,

FGE
 
Run it through Crystal Reports. Then it's a piece of piss. 2 minute job. Nah. 1 minute.

But then again, I doubt anyone would know how to even do that on here.
 
Last edited: 22:35
only ever use cr if the client wants it used. used it a few weeks a go for similar crosstab and other queries but not my weapon of choice i have to say. lack of control over format etc etc
 
MarillionFan said:
Run it through Crystal Reports. Then it's a piece of piss. 2 minute job. Nah. 1 minute.

But then again, I doubt anyone would know how to even do that on here.

You must be gay.
 
Crosstabs. No ability to format.
Amateurs.

As I am now a certified Trainer in CR as well as a Consultant anyone who would actually like training or training notes on how to use the thing properly as opposed to the hacking the rest of you do then drop me a line.

Yep. Gay for £400 a day thanks.
 
ok arkwright, but do you do chintz drapes as well?
 
MarillionFan said:
Crosstabs. No ability to format.
Amateurs.

As I am now a certified Trainer in CR as well as a Consultant anyone who would actually like training or training notes on how to use the thing properly as opposed to the hacking the rest of you do then drop me a line.
Ahem, are you sure you're really investing your talents in the leading edge here?
 
MarillionFan said:
Crosstabs. No ability to format.
Amateurs.

As I am now a certified Trainer in CR as well as a Consultant anyone who would actually like training or training notes on how to use the thing properly as opposed to the hacking the rest of you do then drop me a line.

Yep. Gay for £400 a day thanks.

Well Interior designers earns lots of money too, but they're still gay. CR is a gay technology, full stop, not for real men.
 
I assume by 'Real Men' your discussing David Beckham
 
If you're that good why have you now had 4 contracts already this year and we're just halfway through February? :wave:

How's the daytime TV gig going, btw?
 

Advertisers

Back
Top