Customer Ranking

Question

Post reply

Customer Ranking

Viewing 5 posts - 16 through 19 (of 19 total)

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

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 1

Thanks for the explanation as well, as I now understand why the NTILE won't work in your case.

Fraggle

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

I don't know if it'll help performance at all, but there is one more "eyeball" optimization you could make...

Using the 2007 code (which would also apply to the equivalent 2008 code), you have the following...

update #Sales

Set [2007 Rank] = Case

When isNull(Temp.Total,0) <= @Dividor Then 'Low'

When (isNull(Temp.Total,0) > @Dividor) And (isNull(Temp.Total,0) <= @Dividor*2) then 'Medium'

When (isNull(Temp.Total,0) > @Dividor * 2) Then 'High'

End

From

(

Select

House_ID

,Total

From

#RunningTotal2007

)Temp

Where

#Sales.house_ID = temp.house_ID

Because CASE is listed-order-sensitive (top down), the case statement could be written much more simply (and probably with less processing/time) like this...

update #Sales

Set [2007 Rank] = Case

When isNull(Temp.Total,0) > @Dividor * 2 Then 'High'

When isNull(Temp.Total,0) > @Dividor) Then 'Medium'

Else 'Low'

End

From

(

Select

House_ID

,Total

From

#RunningTotal2007

)Temp

Where

#Sales.house_ID = temp.house_ID

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Hey Upperbognor!

This is an unusual method of "ranking"... From the looks of things, most folks haven't heard about it. You should write an article about it for this forum. Lemme know if you need help getting started. You could call it something like... Ranking: Who's Really Got the Money? 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

upperbognor Right there with Babe Points: 787 More actions · Answer 4

Thanks Jeff, I might have to take you up on that idea just because I LOVE the artical title so much!

-upperbognor

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Heh... yeah, it's an attention getter for sure.

Start by clicking on "Write for us" in the vertical column at the top left of this whole thread. Then, click on "(visit the contribution center)" at the top of the "instructions". Then, at the bottom of the next screen, click on the "Contribute Article" button... follow your nose from there. I'd be happy to do a review of the article before you actually submit it, if you'd like. You can, in fact, save articles as a draft without submitting them. You'd need to copy and paste from there for me to do a review. You can send it to my email but, again, that's only if you'd like me to do a prereview.

The way I normally do it is that I'll first write the article in MS Word to allow spell checking, etc, to come into play. Then, I'll copy and paste into the submittal form and tweek it. There's, unfortunately, no preview mode there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)