Is there another way to solve this "group in 5 day spans" problem?

Question

Is there another way to solve this "group in 5 day spans" problem?

Viewing 6 posts - 31 through 35 (of 35 total)

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

@Frederico,

Ok. I'm not sure which part of my brain was missing when it came to that index but that did the trick. It won't touch the QU when it comes to the number of reads but it gives the QU a really good run for the money for CPU and Duration... and I mean REALLY good run. The index I used was, as you suggested, on the ActivationTime column. Adding the Score column as an INCLUDE knocked out one of the sets of lookups, to boot.

Since that index was basically a duplication of data for the entire table, I changed it to a UNIQUE CLUSTERED INDEX and that made it a little faster still.

Like I said, really interesting code. Thank you and everyone else for your contributions.

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

Posting again because the first post of pages > 1 never shows up until a second post on the page is made.

Thanks again, everyone. This has been really interesting and I appreciate all the submittals and discussions.

And, nope... I don't consider this to be the "end"... if you have some different thoughts or code, don't hesitate to post back. Despite the size of my belly, "I'm all ears". 😀

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

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

Is this solution is only good if there are no dates falling on weekends?

frederico_fonseca SSCoach Points: 16154 More actions · Answer 4

Jonathan AC Roberts wrote:

Is this solution is only good if there are no dates falling on weekends?

what mention of weekends? there is nothing on the original OP post that mentions it neither on the code posted by the multiple members here?

if talking about my own code, it is agnostic of what day of the week it is (and so are the others as far as I can see).

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 5

frederico_fonseca wrote:

Jonathan AC Roberts wrote:
Is this solution is only good if there are no dates falling on weekends?
what mention of weekends? there is nothing on the original OP post that mentions it neither on the code posted by the multiple members here?
if talking about my own code, it is agnostic of what day of the week it is (and so are the others as far as I can see).

Sorry, I was looking Ken McKelvey's solution

frederico_fonseca SSCoach Points: 16154 More actions · Answer 6

Jonathan AC Roberts wrote:

frederico_fonseca wrote:
Jonathan AC Roberts wrote:
Is this solution is only good if there are no dates falling on weekends?
what mention of weekends? there is nothing on the original OP post that mentions it neither on the code posted by the multiple members here?
if talking about my own code, it is agnostic of what day of the week it is (and so are the others as far as I can see).
Sorry, I was looking Ken McKelvey's solution

ahh. that would not work anyway as isoweek plus year would not group by consecutive days anyway on cross year cases - not to mention week 53 which contains the first days of a year so was sorted higher than the remaining days of the same year.