Query for work shift differential

Question

Query for work shift differential

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

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

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 1

technically there is no need, it was just for demonstration purposes 🙂

Did you have a look at the execution plans? It did look like it was more optimal to go the CTE/derived table route, I need to do some testing with larger datasets and a more intensive sub query though

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

Heh... understood and thanks...

Becareful, though, Sam... one of the things that an Execution Plan will do, is "lie"... frequently, it can't "see" past the first row of the correlated sub-query (whatever form it takes) and will report seeming remarkable low percentages... the best proof of the pudding is an actual run on something like a million rows with STATISTICS IO and STATISTICS TIME turned on.

For the record, I think you'll find that the actual performance of a single use CTE is no better than a Derived Table... in my (so far) limited experience with 2k5, I've found that they are basically the same thing... CTE just allows more multiple references in the SELECT than a derived table and can be self joined for recurrsion (my least favorite form of RBAR). 😛

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

wilkeyrd Grasshopper Points: 10 More actions · Answer 3

Is it possible to add up the results from the case statments? I am trying to get the total time for a person, plus double time for working the midnight shifts.

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

Absolutely. You can use CASE in just about any of the aggregate fuctions like SUM, etc.

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

wilkeyrd Grasshopper Points: 10 More actions · Answer 5

I haven't had much luck with the case statements.

I have renamed the cases at M1 and M2. when ever I try to use the case names I get an 'invalid column name'. I have changed the case from 'end as M1' to 'M1 = case' and still get the same results.

If any has any ideas, it would be a great help. I can see all the raw data is correct and just need to do a couple of sum statements to finish this project off.

TIA