Rank Based on Difference between Numbers 1

[Bonediggler1]

Hi-

I need to build a query that ranks customers based on the size of difference in sales from the current month to the last month - irrespective of whether the difference is positive or negative.

I.e. if the difference in sales for customer 1 is -100 and the difference in sales for customer 2 is 99, customer 1 is ranked #1 as the overall change is greater (even though the number is less).

Any ideas?

Thank you~

 

[drlex]

Yes, ABS() for one. An idea of how many tables you have and the version of SQL Server you are using would assist in specifics. Trial (fake) data makes life easier (and answers more likely).

soi là, soi carré

 

[Crowley16]

rank() is another...

--------------------
Procrastinate Now!

 

[Bonediggler1]

SQL Server 2005, a union query serving as a derived table with each section of the union containing 2 tables


Thanks for the help! I will try ABS() out now...

 

[Crowley16]

derived table?

why not a cte?

--------------------
Procrastinate Now!

 

[Bonediggler1]

Hmmm. Never used a CTE but after some research it seems like a good idea. Think I'll do this too...

 

[gmmastros]

cte?

why not a derived table?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bonediggler1]

By the way...I implemented 2 cte's instead of derived tables. The query now takes 17 minutes, whereas the sloppy subquery method takes 30 seconds.

Is there something I need to know about CTEs? I created two and did a left join...

 

[gmmastros]

So... the CTE was 34 times slower than the derived table method?

By the way, coded properly and formatted properly, I see absolutely nothing wrong with derived tables and would not consider them sloppy at all.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

I wish I remembered where did I see a discussion why CTE may be slower. I remember the basic idea, though, that CTE are fine as long as you don't join them together...

PluralSight Learning Library

 

[Crowley16]

there's no real difference between cte's and derived tables, essentially they are the same thing.

I find cte's better since they make the coding neater and are easier to work with. It's just as easy to make a derived table perform badly as it is for a CTE.

If you're joining on multiple cte's then it's the same as joining on multiple derived tables, which is often a performance killer, however, if properly constructed, there's no performance difference between either.

--------------------
Procrastinate Now!

 

[gmmastros]

Crowley,

Most of your recent post talks about performance. I absolutely agree that you can take ANY programming construct and kill the performance by not using it properly. This is universally true.

I find cte's better since they make the coding neater and are easier to work with.

This is where I will disagree with you. The syntax is different (of course), but I don't think they are neater or easier to work with. Quite the opposite, actually. When you use a derived table, it's because you need to retrieve data in a special/complicated way, and then join that data to another part of your query. CTE's have similar usefulness. With a CTE, you declare it above the query where you actually use it. With a derived table, you declare where you use it.

For me, it's a perspective thing. With a CTE query, you list the details about one part of the (larger) query before you actually see the query. With a derived table, you see the big picture first, and the details later.

For me, it's like opening up the manual for your car, and having the first several dozen pages explaining in excruciating detail how the radio works.

That being said, I do recognize their usefulness in certain queries, and I do use them where appropriate. Given a choice, I prefer derived tables because they are cleaner and makes the query easier to read.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Crowley16]

ahh, but I think of cte's differently from you.

in my mind, I treat a cte as a temp table, that's completely seperate from the query, and when I look at the statement, I'd look at the main query first, and if needed, I'd look at the cte.

that way, I can ignore the "first several dozen pages explaining in excruciating detail how the radio works" completely, well, until I need to actually use the radio anyway...

If I use a derived table, then it means when I look at the statement as a whole, I *have* to look at the complicated bit as well, and since requiring the use of cte/derived table means the complicated bit is usually actually complicated, this can make things much harder.

just because the compiler reads from top to bottom doesn't mean I can't skip the cte declaration, and come back to it later...
however, I do use both, since sometimes, there's just no need for a cte.

--------------------
Procrastinate Now!

 

[Crowley16]

oh, forgot to mention, you can't *re-use* a derived table in a query... doesn't happen often, and can kill performance but very useful when it does work...

--------------------
Procrastinate Now!

 

[markros]

I agree with Crowley16. IMHO, CTEs improve readability and maintenance of the query greatly.

Take a look at the first part of this blog

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/cte-and-hierarchical-queries

PluralSight Learning Library

 

[Bonediggler1]

This thread has been much more informative than i ever intended!

While we are on the subject of performance, can anyone tell me why this UD function considerably slows down the queries in question above?

Basically I have an "Accounting Period ID" which is in format yyyymm, from which I need to add and subtract and then come up with a subsequent valid accounting period ID.

I.e. if the input value is 201001 (January 2010) and I want to subtract one month (return December 2009) the result would be 200912.

My function works fine but as I said, really slows things down. Here it is:

Input is @ACCTGID, and @INCRMNT (how much i want to add or subtract from @ACCTGID)

DECLARE @CURPER AS VARCHAR(10)
DECLARE @TARGETPER AS VARCHAR(10)
DECLARE @FN_VALUE INT

SET @CURPER = RIGHT(@ACCTGID, 2) + '/01/' + LEFT(@ACCTGID, 4)
SET @TARGETPER = CONVERT(VARCHAR(10), DATEADD(M, @INCRMNT, @CURPER),101)
SET @FN_VALUE = RIGHT(@TARGETPER, 4) + LEFT(@TARGETPER, 2)


FINISH:
RETURN @FN_VALUE


Thanks again~

 

[Crowley16]

when you use a scalar udf in your select statement, it will be executed for each row in your data set, so if you're returning like 10 million rows, then this gets executed 10 million times.
if possible, always use a set based approach.

in your above example, skip the udf altogether, cast the column as date, use datediff to calculate the month, and then convert it back into yyyymm format, should be much faster...

--------------------
Procrastinate Now!