# Over partition range query

• ### 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 Sunday, January 7, 2018 8:51 PM
Sunday, January 7, 2018 8:47 PM

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

Sunday, January 7, 2018 8:59 PM

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

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

Sunday, January 7, 2018 9:00 PM
• 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 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