Current Order Date and Average Count of Orders for Three Prior Days

Question

Post reply

Current Order Date and Average Count of Orders for Three Prior Days

Viewing 9 posts - 16 through 23 (of 23 total)

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

David-250683 Hall of Fame Points: 3452 More actions · Answer 1

Hey Jeff, thanks again. I had more time to look at it and made two minor changes. The core query is the same, but I

1) used a TABLE variable instead of a temp table, and

2) Directly retrieved the desired result set rather than doing an UPDATE first.

DECLARE @Results TABLE (

ID2 int IDENTITY (1,1) PRIMARY KEY,

SomeDate smalldatetime,

DailyCount int

)

INSERT @Results

(SomeDate, DailyCount)

SELECT SomeDate, COUNT(SomeDate)

FROM #TestData

GROUP BY SomeDate

ORDER BY SomeDate

SELECT a.SomeDate,

CAST(AVG(b.DailyCount*1.0) as DECIMAL(9,2))

FROM @Results a

JOIN @Results b

ON b.ID2 BETWEEN a.ID2-2 AND a.ID2

WHERE a.ID2 >= 3

GROUP BY SomeDate

ORDER BY 1 DESC

--Note. I used Jeff's test data. Data from original post works by swapping table Shipping.Orders for #TestData, and column RequiredDate for SomeDate.

THANK YOU

Jack Corbett SSC Guru Points: 184393 More actions · Answer 2

I had a solution I was going to post but Jeff's was faster by magnitudes. I'd go with his solution.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Sergiy SSC Guru Points: 110206 More actions · Answer 3

I believe this topic is a perfect illustration for the article "Why programmers cannot program?".

They take care about everything - formatting, performance, style, but ignore building correct algorithm absolutely.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 4

Sergiy (10/6/2007)
What about days having no orders?
What if nobody made any order on Tuesday?
Jeff, don't you think the Thursday calculation should include zero orders for Tuesday, not whatever number for Monday?
Sorry, there are no smart answers on stupid questions.

Personally, yeah... I think dates with no orders should be included in the mix and possibly execluding weekends as a separate category. Not what the OP asked for, though. 😛

--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: 1003869 More actions · Answer 5

Sergiy (10/8/2007)
I believe this topic is a perfect illustration for the article "Why programmers cannot program?".
They take care about everything - formatting, performance, style, but ignore building correct algorithm absolutely.

Absolutely agree with that, ol' friend... but different slant... they're just tired of telling folks that did the designing that they probably designed it wrong. That's why I did it with a table... gives them the opportunity to change their mind and makes my life easier when they do 😀

--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: 1003869 More actions · Answer 6

Jack Corbett (10/8/2007)
I had a solution I was going to post but Jeff's was faster by magnitudes. I'd go with his solution.

Heh... :blush: Thanks for the Kudo, Jack... But Serqiy is correct and his observation is really why I used a table for this... gives the opportunity to incorporate "zero" days which is what I actually think is necessary but didn't take the time to explain. 😉

--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: 1003869 More actions · Answer 7

David (10/6/2007)
Thanks Jeff! I was hoping you would respond. You always have such brilliant responses!!
Many thanks!!

Heh... thanks David :blush:

But, Serqiy's observations are spot on and I was actually being a bit lazy... I should have asked you why you think it's necessary to not include "zero" days before I posted a lick of code... but did write the code so it's pretty much possible to include them.

Also, be just a bit careful when you use Table Variable solutions... in particular, see Q3/A3 and Q4/A4 in the following URL... also remember that you may get a recompile out of Table Variable code, anyway, if the data has changed enough...

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

--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)

Sergiy SSC Guru Points: 110206 More actions · Answer 8

Jeff Moden (10/8/2007)
Personally, yeah... I think dates with no orders should be included in the mix and possibly execluding weekends as a separate category. Not what the OP asked for, though. 😛

Eventually OP changed his request.

Originally it was

2) the daily average of total Orders for that day and two previous days

Now it says:

2) the daily average of total Orders for that day and two previous days where there are orders.

😉

But it's still not good enough.

Because days without orders could be not only weekends or public holidays.

He'll discover it one day after it's deployed to production.

:w00t:

_____________
Code for TallyGenerator

David-250683 Hall of Fame Points: 3452 More actions · Answer 9

Jeff,

I don't see where the lazy is in your code. After a while, I was able to produce the desired result set on my own. My post then transformed into seeking a more efficient way. You provided that. (And no one else)