Post reply

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

  • May 28, 2014 at 6:47 pm

    #1717207

    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)
    Intro to Tally Tables and Functions

  • May 29, 2014 at 1:59 am

    #1717253

    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

    SELECT t1.StaffID,

    t1.WorkedHours,

    t2.ALHours,

    t3.SLHours

    FROM (

    SELECT t1.StaffID,

    SUM(t1.WorkedHours) AS [WorkedHours]

    FROM table1 t1

    WHERE t1.StaffID = @StaffID

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

    GROUP BY t1.StaffID

    ) t1

    CROSS JOIN (

    SELECT SUM(t2.ALHours) AS ALHours

    FROM table2 t2

    WHERE t2.StaffID = @StaffID

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

    ) t2

    CROSS JOIN (

    SELECT SUM(t3.SLHours) AS SLHours

    FROM table3 t3

    WHERE t3.StaffID = @StaffID

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

    ) t3

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

  • June 1, 2014 at 7:58 pm

    #1718098

    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?

  • June 1, 2014 at 8:02 pm

    #1718099

    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.

  • June 3, 2014 at 5:02 pm

    #1718715

    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

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

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