Results 1 to 5 of 5
  1. #1
    Join Date
    May 2017
    Posts
    3

    Unanswered: Week total alongside year to date total

    Hi,

    I was wondering if any one could help me with something (for some reason) I'm finding difficult to achieve.

    I'm querying data with joins that I've created and would be something along the lines of:

    PersonID, OrderCount, SalesTotal, WeekNo, Year

    The Tsql being:

    Select PersonID, PersonCatagory, count(tableB.OrderNo) as OrderCount, Sum(tableB.Revenue) as SalesTotal, Week(orderdate), Year(orderdate) from tableA
    Right Join tableB on personID = clientID
    Where PersonCategory = 'GG'
    Group By PersonID
    Having Week = '10' and Year = '2017'

    The above works fine (sorry if anything is slightly incorrect there, I'm not in front of my computer)

    However, I'm looking at adding in the sum of 2017-01-01 to current year and week number. I tried a sub query at the end but this didn't seem to work.

    Does anyone know how I could achieve this so the same grouping rules apply but the output would be

    PersonID, OrderCount, SalesTotalLastWeek, SalesTotalThisYear, WeekNo, Year

    Many thanks!

  2. #2
    Join Date
    Oct 2007
    Posts
    136
    Provided Answers: 7
    something along lines of:

    Code:
    Select PersonID, PersonCatagory, count(tableB.OrderNo) as OrderCount, Sum(tableB.Revenue) as SalesTotal, AA.SalesTotalThisYear, 
              Week(orderdate), Year(orderdate)
        from tableA a
    inner join (Select PersonID, Sum(tableB.Revenue) as SalesTotalThisYear
                       from tableA
                    Right Join tableB on personID = clientID
                    Where PersonCategory = 'GG'
                    Group By PersonID
                    Having Year = '2017') as AA
       on a.PersonID = aa.PersonID
     Right Join tableB on personID = clientID
     Where PersonCategory = 'GG'
     Group By PersonID
     Having Week = '10' and Year = '2017'

  3. #3
    Join Date
    May 2017
    Posts
    3
    Quote Originally Posted by DNance View Post
    something along lines of:

    Code:
    Select PersonID, PersonCatagory, count(tableB.OrderNo) as OrderCount, Sum(tableB.Revenue) as SalesTotal, AA.SalesTotalThisYear, 
              Week(orderdate), Year(orderdate)
        from tableA a
    inner join (Select PersonID, Sum(tableB.Revenue) as SalesTotalThisYear
                       from tableA
                    Right Join tableB on personID = clientID
                    Where PersonCategory = 'GG'
                    Group By PersonID
                    Having Year = '2017') as AA
       on a.PersonID = aa.PersonID
     Right Join tableB on personID = clientID
     Where PersonCategory = 'GG'
     Group By PersonID
     Having Week = '10' and Year = '2017'
    I was thinking of achieving this with a CASE statement and passing variables. Do you know if your solution would perform the routine quicker at all?
    Last edited by awap87; 05-18-17 at 14:26.

  4. #4
    Join Date
    Oct 2007
    Posts
    136
    Provided Answers: 7
    no, don't have your data or table/index structures. To find the faster solution, just run them

  5. #5
    Join Date
    May 2017
    Posts
    3
    Quote Originally Posted by DNance View Post
    no, don't have your data or table/index structures. To find the faster solution, just run them
    Wow, I thought I was fairly confident with tsql until I saw this!

    Is there a pm facility on these forums where I could send you what I've re-wrote to match my actual table structure? I still don't think I got it right if I'm honest. I had a look but couldn't find the PM function in the forum.

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •