none
Over partition range query RRS feed

  • Question

  • Consider the following table with 4 columns.  The columns are branchid, clientid, dateYYYYMMMkey, amount.  Each branch has  many clients.  Amount represents dollar activity for that month for that client.  1) I need to create a table which would contain running total as of balance for each client.  2) I need to summarize based on branchid-dateYYYYMMKey.  The item 1)  appears to be easy to do by using over partition range query.  The item 2)  is more complicated and was wondering if someone could offer an advice.  The problem is when for a specific branchid-clientid-dateYYYYMMKey, there is no entry.  Hence, the amount for that key will not be included in amount summary based on branchid-dateYYYYMMKey.  Is there an elegant solution to this? or a better solution to this problem.

     

    1)

    SELECT distinct 
           branchid,

           clientid,

           dateYYYYMMMkey,
           SUM(amount) OVER(PARTITION BY 
                                         branchid, clientid ORDER BY 

                                         branchid, clientid, dateYYYYMMMkey range UNBOUNDED PRECEDING) amount

    into tab1

    FROM
    (
        SELECT 
               branchid,

                clientid,           
               dateYYYYMMMkey,
               SUM(credit-debit) amount
        FROM tab
        GROUP BY 
                 branchid,

                 clientid,           
                 dateYYYYMMMkey
    ) res

    2)

    select branchid, dateYYYYMM, sum(amount) from tab1

    group by branchid, dateYYYYMM


    • Edited by rgelfand Sunday, January 7, 2018 8:51 PM
    Sunday, January 7, 2018 8:47 PM

Answers

  • If I understand the second point correctly, you need to show value for all combinations of branch and month, even if there is no row for, say, branch 35 for month 201607.

    The solution to that problem is:

    SELECT B.branchid, M.dateYYYYMM, isnull(SUM(tab1.amount), 0)
    FROM   Branches B
    CROSS  JOIN Months M
    LEFT   JOIN tab1 ON B.branchid = tab1.branchid
                    AND M.dateYYYYMM = tab1.dateYYYYMM
    GROUP  BY B.branchid, M.dateYYYYMM

    The CROSS JOIN spans the full dimension you want values for, and then you left join to your fact table and replace NULLs for missing data with 0.

    That is, I assuming that there is a table defining all possible branches, and likewise that there is a table defining the months. In lieu of such tables, you can replace Branches with
       (SELECT DISTINCT branchid FROM tab1)

    and the same for month. But if there are no sales at all some month, that month is lost, so you are better of with having tables for branches, and months. (And clients.)

    • Marked as answer by rgelfand Tuesday, January 9, 2018 5:45 PM
    Sunday, January 7, 2018 9:10 PM

All replies

  • Sorry didnt understand the issue. If there's no entry for a  branchid-clientid-dateYYYYMMKey combinaion, then why do you need to consider it for the branchwise summary? Can you illustrate with an example?

    IF it has no entry then there would be no amount right? Then what is there to summarize?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, January 7, 2018 8:59 PM
  • please post:

    1) Queries to CREATE your table(s) including indexes
    2) Queries  to INSERT sample data.
    3) The desired result given the sample, as text or image of excel for example.
    4) A short description of the business rules, and how you got 1-2 of the results
    5) Which version of SQL Server you are using (this will help to fit the query to your version).

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Sunday, January 7, 2018 9:00 PM
    Moderator
  • If I understand the second point correctly, you need to show value for all combinations of branch and month, even if there is no row for, say, branch 35 for month 201607.

    The solution to that problem is:

    SELECT B.branchid, M.dateYYYYMM, isnull(SUM(tab1.amount), 0)
    FROM   Branches B
    CROSS  JOIN Months M
    LEFT   JOIN tab1 ON B.branchid = tab1.branchid
                    AND M.dateYYYYMM = tab1.dateYYYYMM
    GROUP  BY B.branchid, M.dateYYYYMM

    The CROSS JOIN spans the full dimension you want values for, and then you left join to your fact table and replace NULLs for missing data with 0.

    That is, I assuming that there is a table defining all possible branches, and likewise that there is a table defining the months. In lieu of such tables, you can replace Branches with
       (SELECT DISTINCT branchid FROM tab1)

    and the same for month. But if there are no sales at all some month, that month is lost, so you are better of with having tables for branches, and months. (And clients.)

    • Marked as answer by rgelfand Tuesday, January 9, 2018 5:45 PM
    Sunday, January 7, 2018 9:10 PM
  • Hi rgelfand,

    Thanks for posting here.

    From your post, I find it hard to get useful information. Could you please make a more detailed description? As you know, helping us always means to help you. If you could follow Ronen' advice, we could provide clearer solution rather than keep on guessing your description.

    Thanks for your understanding and support.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 8, 2018 2:41 AM
    Moderator