Complex Windowed Sum

Question

Post reply

Complex Windowed Sum

j-1064772

SSCertifiable

Points: 5316

April 11, 2018 at 12:57 pm

#344601

Greetings,
Your assistance is kindly requested for the following problem

Description :
A series of transactions issuing (I) or redeeming (R) bonds.
A customer may be issued bonds one or more times. The bonds are entered in an "account" called UnitNo.
A customer may have more than one UnitNo.
A customer may have bonds redeemed. The withdrawals are made from a specific UnitNo.
The ordering of the transactions is critical, otherwise the percentages will mean nothing.

The requirements are :
1) A running total of bond quantities issues and redeemed
2) The total quantities of each customer combing all of the customer's UnitNo's
3) The percentage of the customers' quantities w/r to the running total of bonds in circulation

I think I have managed to do it.

The specific question :
1) I need to add two more columns showing for one specific customer (a) the current total of that customer and (b) the percentage of his bond quantities w/r to the total running count.
2) And while I got your attention, I also need to add a third column showing in which fiscal year the transactions took place - Considering an April 30 fiscal year end. (I always get confused with this accounting complication.)

Thanks in advance for your help

Table creation
-------------------

CREATE TABLE [dbo].[TestData]

(

[TrxNo] [int] NOT NULL Primary Key,

[TrxType] [varchar](1) NOT NULL,

[UnitNo] [varchar](255) NOT NULL,

[Customer] [varchar](10) NOT NULL,

[TrxDate] [date] NOT NULL,

[TrxQty] [int] NOT NULL

);

CREATE TABLE [dbo].[TestResults]

(

[TrxNo] [int] NOT NULL Primary Key,

[TrxType] [varchar](1) NOT NULL,

[UnitNo] [varchar](255) NOT NULL,

[Customer] [varchar](10) NOT NULL,

[TrxDate] [date] NOT NULL,

[TrxQty] [int] NOT NULL,

[CustomerTotal] [int] NOT NULL DEFAULT 0.0,

[RunningTotal] [int] NOT NULL DEFAULT 0.0,

[CustomerPercentage] [money] NOT NULL DEFAULT 0.0,

[C0004TotalQty] [int] NOT NULL DEFAULT 0.0,

[C0004Percentage] [money] NOT NULL DEFAULT 0.0,

[FiscalYear] [int] NULL

);

Test Data
---------

INSERT

INTO [TestData]

(

[TrxNo],

[TrxType],

[UnitNo],

[Customer],

[TrxDate],

[TrxQty]

)

SELECT 1, 'I', 'XBR-64 ', 'C0001' , '2004-07-06', 50000 UNION

SELECT 2, 'I', 'XBR-63', 'C0002' , '2004-07-10', 125000 UNION

SELECT 3, 'I', 'XBR-65', 'C0003' , '2004-07-19', 5000 UNION

SELECT 4, 'I', 'XBR-66', 'C0004' , '2004-07-19', 70000 UNION

SELECT 5, 'I', 'XBR-67', 'C0004' , '2004-07-19', 50000 UNION

SELECT 6, 'I', 'XBR-68', 'C0005' , '2004-07-19', 50000 UNION

SELECT 7, 'I', 'XBR-69', 'C0004' , '2004-09-25', 200000 UNION

SELECT 8, 'I', 'XBR-70', 'C0004' , '2004-09-25', 100000 UNION

SELECT 9, 'I', 'XBR-71 ', 'C0001', '2004-11-15', 80000 UNION

SELECT 10, 'I', 'XBR-72', 'C0003' , '2004-12-10', 5000 UNION

SELECT 11, 'I', 'XBR-75', 'C0007' , '2005-03-14', 15000 UNION

SELECT 12, 'I', 'XBR-57', 'C0009' , '2005-04-01', 30000 UNION

SELECT 13, 'I', 'XBR-37', 'C0007' , '2005-07-01', 25000 UNION

SELECT 14, 'I', 'XBR-76', 'C0003' , '2005-07-26', 10000 UNION

SELECT 15, 'I', 'XBR-77', 'C0010' , '2005-09-21', 25000 UNION

SELECT 16, 'I', 'XBR-80', 'C0004' , '2005-10-21', 700000 UNION

SELECT 17, 'I', 'XBR-78', 'C0003' , '2005-10-24', 10000 UNION

SELECT 18, 'R', 'XBR-79', 'C0007' , '2005-10-24', -30000 UNION

SELECT 19, 'I', 'XBR-83', 'C0004' , '2006-03-13', 600000 UNION

SELECT 20, 'R', 'XBR-57', 'C0009' , '2006-04-01', -30000 UNION

SELECT 21, 'I', 'XBR-92', 'C0004' , '2008-02-21', 500000 UNION

SELECT

22, 'I', 'XBR-93', 'C0004' , '2008-02-21'

, 500000

-- Solution attempt

INSERT

INTO [TestResults]

(

[TrxNo],

[TrxType],

[UnitNo],

[Customer],

[TrxDate],

[TrxQty],

[CustomerTotal],

[RunningTotal],

[CustomerPercentage]

)

SELECT

[TrxNo],

[TrxType],

[UnitNo],

[Customer],

[TrxDate],

[TrxQty],

SUM([TrxQty]) OVER (PARTITION BY [Customer] ORDER BY [TrxNo]),

SUM([TrxQty]) OVER (ORDER BY [TrxNo]),

(SUM([TrxQty]) OVER (PARTITION BY [Customer] ORDER BY [TrxNo]) * 100) / SUM([TrxQty]) OVER (ORDER BY [TrxNo])

FROM [TestData]

ORDER BY [TrxNo]

Expected Results

TrxNo	TrxType	UnitNo	Customer	TrxDate	TrxQty	CustomerTotal	RunningTotal	Customer Percentage	C0004 TotalQty	C0004 Percentage	Fiscal Year
1	I	XBR-64	C0001	2004-07-06	50,000	50,000	50,000	100.00	0	0.00
2	I	XBR-63	C0002	2004-07-10	125,000	125,000	175,000	71.00	0	0.00
3	I	XBR-65	C0003	2004-07-19	5,000	5,000	180,000	2.00	0	0.00
4	I	XBR-66	C0004	2004-07-19	70,000	70,000	250,000	28.00	70,000	28.00
5	I	XBR-67	C0004	2004-07-19	50,000	120,000	300,000	40.00	120,000	40.00
6	I	XBR-68	C0005	2004-07-19	50,000	50,000	350,000	14.00	120,000	34.29
7	I	XBR-69	C0004	2004-09-25	200,000	320,000	550,000	58.00	320,000	58.18
8	I	XBR-70	C0004	2004-09-25	100,000	420,000	650,000	64.00	420,000	64.62
9	I	XBR-71	C0001	2004-11-15	80,000	130,000	730,000	17.00	420,000	57.53
10	I	XBR-72	C0003	2004-12-10	5,000	10,000	735,000	1.00	420,000	57.14
11	I	XBR-75	C0007	2005-03-14	15,000	15,000	750,000	2.00	420,000	56.00
12	I	XBR-57	C0009	2005-04-01	30,000	30,000	780,000	3.00	420,000	53.85
13	I	XBR-37	C0007	2005-07-01	25,000	40,000	805,000	4.00	420,000	52.17
14	I	XBR-76	C0003	2005-07-26	10,000	20,000	815,000	2.00	420,000	51.53
15	I	XBR-77	C0010	2005-09-21	25,000	25,000	840,000	2.00	420,000	50.00
16	I	XBR-80	C0004	2005-10-21	700,000	1,120,000	1,540,000	72.00	1,120,000	72.73
17	I	XBR-78	C0003	2005-10-24	10,000	30,000	1,550,000	1.00	1,120,000	72.26
18	R	XBR-79	C0007	2005-10-24	-30,000	10,000	1,520,000	0.00	1,120,000	73.68
19	I	XBR-83	C0004	2006-03-13	600,000	1,720,000	2,120,000	81.00	1,720,000	81.13
20	R	XBR-57	C0009	2006-04-01	-30,000	0	2,090,000	0.00	1,720,000	82.30
21	I	XBR-92	C0004	2008-02-21	500,000	2,220,000	2,590,000	85.00	2,220,000	85.71
22	I	XBR-93	C0004	2008-02-21	500,000	2,720,000	3,090,000	88.00	2,720,000	88.03

Viewing 4 posts - 1 through 3 (of 3 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

It's fairly simple.
To get just a Customer's data, you can use a CASE statement.
To get the fiscal year ending on April 30th, you just substract 4 months.


 SUM(CASE WHEN Customer = 'C0004' THEN TrxQty ELSE 0 END) OVER (PARTITION BY [Customer] ORDER BY [TrxNo]),
  (SUM(CASE WHEN Customer = 'C0004' THEN TrxQty ELSE 0 END) OVER (PARTITION BY [Customer] ORDER BY [TrxNo]) * 100.) / SUM([TrxQty]) OVER (ORDER BY [TrxNo]),
  YEAR(DATEADD(MM,-4,trxDate))

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

j-1064772 SSCertifiable Points: 5316 More actions · Answer 2

Thank you for your prompt reply.

I just got harpooned in a staff meeting, so I will try it later tonight.

Regards

j-1064772 SSCertifiable Points: 5316 More actions · Answer 3

Finally out that meeting.

Works like a cham.

Thanks a million.

It's been quite a while since I used SQL Server Central - there used to be a way to flag an answer as having solved the problem, but I cannot find it in the different user interface I am seeing today for the first time.