Sum a Column by Client and Group by date 4 weeks apart!

Question

Sum a Column by Client and Group by date 4 weeks apart!

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

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

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

Tallboy (5/28/2014)
I hope that all makes sense, if not let me know.

And now for the killer question... what do you want to do for a year that has 53 Week Ending dates?

To answer the quote above, you wouldn't have had to explain so much if you had posted readily consumable data as requested numerous times. 😉

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

David Burrows SSC Guru Points: 65142 More actions · Answer 2

Tallboy (5/28/2014)
In a perfect world they will but in reality users a rushing and type a slightly different date, so I would prefer a kind if catch all between 2 dates.

Then you will have to sum the values separately first and then join them together like this

SELECTt1.StaffID,

t1.WorkedHours,

t2.ALHours,

t3.SLHours

FROM(

SELECTt1.StaffID,

SUM(t1.WorkedHours) AS [WorkedHours]

FROMtable1 t1

WHEREt1.StaffID = @StaffID

ANDt1.WeekendingDate BETWEEN DATEADD(day,-23,@StartDate) AND @StartDate

GROUPBY t1.StaffID

) t1

CROSS JOIN (

SELECTSUM(t2.ALHours) AS ALHours

FROMtable2 t2

WHEREt2.StaffID = @StaffID

ANDt2.WeekendingDate BETWEEN DATEADD(day,-23,@StartDate) AND @StartDate

) t2

CROSS JOIN (

SELECTSUM(t3.SLHours) AS SLHours

FROMtable3 t3

WHEREt3.StaffID = @StaffID

ANDt3.WeekendingDate BETWEEN DATEADD(day,-33,@StartDate) AND @StartDate

) t3

Far away is close at hand in the images of elsewhere.
Anon.

Tallboy SSCrazy Points: 2455 More actions · Answer 3

Hi,

I have my stored procedure working alright, thanks to everyone who contributed, but then I tried it a different way, I created a number of different views and joined them together, just as the code does and it works well too.

So now I'm wondering is a sp better than a view that can be queried. There's not much data in the database at the moment so there is no noticeable difference in speed, but as the data grows to the tens of thousands thing may changes.

Any opinion or advice?

pietlinden SSC Guru Points: 63503 More actions · Answer 4

I would put it all in the stored procedure, and if you can avoid using the view, do it. Think about what would happen if your view got dropped somehow... your stored procedure would break, and it would be a hassle to figure out why and fix it. Generally, the fewer dependencies you have on other objects, the better.

Tallboy SSCrazy Points: 2455 More actions · Answer 5

Hi,

Thanks for your reply, while I respect your opinion, the reasons you give not to use a View equally apply to Stored Proc. surely if a SP got dropped, it woudl be difficult to fix !

I was thinking more along hte lines of efficiency and speed when the database grows on size!

kind regards