September 19, 2016 at 9:55 am
I am using new features of SQL Server 2012 (SUM OVER, LEAD, LAG...) to refactor legacy SQL Statements and get rid of cursors and temporary tables. Things are going pretty well, thanks to the advices I got from this forum. I have a solution for the current case I am trying to resolve but I am wondering if there might be a better solution.
My source table contains transactions and my result dataset must return the percentage of holding as of a given day. Thus, I have to divide the total held by a holder as of a given date by the total held by all holders as of the same date. Let's examine a simple case:
[font="Courier New"]DECLARE @Transactions TABLE (EffectiveDate DATETIME, Holder VARCHAR(10), Quantity INT)
INSERT INTO @Transactions (EffectiveDate, Holder, Quantity) VALUES
( '2001-01-01', 'John', 10 ),
( '2001-01-02', 'Peter', 10 )
SELECT
EffectiveDate, Holder,
HQuantity, TQuantity, HQuantity * 100 / TQuantity AS Percentage
FROM (
SELECT
EffectiveDate, Holder, Quantity,
SUM(Quantity) OVER (PARTITION BY Holder ORDER BY EffectiveDate, Holder RANGE UNBOUNDED PRECEDING) AS HQuantity,
SUM(Quantity) OVER (ORDER BY EffectiveDate RANGE UNBOUNDED PRECEDING) AS TQuantity
FROM (
SELECT EffectiveDate, Holder, SUM(Quantity) AS Quantity
FROM @Transactions
GROUP BY EffectiveDate, Holder
) X
) Y
ORDER BY EffectiveDate, Holder
[/font]
Gives the result:
[font="Courier New"]EffectiveDate Holder HQuantity TQuantity Percentage
============= ====== ========= ========= ==========
2001-01-01 John 10 10 100
2001-01-02 Peter 10 20 50
[/font]
Those numbers are correct. However, the following row needs to be added as John only holds 50% of shares as of 2001-01-02
[font="Courier New"]EffectiveDate Holder HQuantity TQuantity Percentage
============= ====== ========= ========= ==========
2001-01-02 John 10 20 50
[/font]
My solution is to add "fake" transactions so every holder has an entry as of every date. I thus replace:
[font="Courier New"]FROM @Transaction[/font]
in the previous statement with:
[font="Courier New"]FROM (
SELECT EffectiveDate, Holder, Quantity FROM @Transactions
UNION ALL
SELECT D.*, H.*, 0
FROM (SELECT DISTINCT Holder FROM @Transactions) H
INNER JOIN (SELECT DISTINCT EffectiveDate FROM @Transactions) D ON 0 = 0
) Z
[/font]and I add a filter that removes holder that have no holdings. The final statement is:
[font="Courier New"]SELECT
EffectiveDate, Holder,
HQuantity, TQuantity, HQuantity * 100 / TQuantity AS Percentage
FROM (
SELECT
EffectiveDate, Holder, Quantity,
SUM(Quantity) OVER (PARTITION BY Holder ORDER BY EffectiveDate, Holder RANGE UNBOUNDED PRECEDING) AS HQuantity,
SUM(Quantity) OVER (ORDER BY EffectiveDate RANGE UNBOUNDED PRECEDING) AS TQuantity
FROM (
SELECT EffectiveDate, Holder, SUM(Quantity) AS Quantity
FROM (
SELECT EffectiveDate, Holder, Quantity FROM @Transactions
UNION ALL
SELECT D.*, H.*, 0
FROM (SELECT DISTINCT Holder FROM @Transactions) H
INNER JOIN (SELECT DISTINCT EffectiveDate FROM @Transactions) D ON 0 = 0
) Z
GROUP BY EffectiveDate, Holder
) X
) Y
WHERE HQuantity * 100 / TQuantity > 0
ORDER BY EffectiveDate, Holder
[/font]
Does anyone has a better solution to propose?
September 19, 2016 at 10:05 am
Post the DDL (create table) script, sample data as an insert statement and the expected results.
😎
September 19, 2016 at 10:09 am
Everything needed is there in [font="Courier New"]courier new[/font]!
September 19, 2016 at 10:32 am
Your sample data is simply too sparse. What happens when a person has multiple transactions? We can't tell, because you've only provided one transaction per person. What happens when a person's holdings goes to zero? We can't tell, because you don't have any exemplars in your sample data. We also don't know whether the quantity represents the current holdings for the person or the change in holdings.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 19, 2016 at 10:47 am
am not sure I fully understand....but using some sample data you posted in an earlier thread, I dont beleive your code gives you the results you are looking for.....but I am happy to be told otherwise <grin>
DECLARE @Transactions TABLE (EffectiveDate DATETIME, Holder VARCHAR(10), Quantity INT)
INSERT INTO @Transactions (EffectiveDate, Holder, Quantity) VALUES
( '2001-01-01', 'John', 10), --John becomes a holder
( '2001-01-02', 'John', 15), --John is still a holder (no change)
( '2001-01-02', 'Jane', 10), --Jane becomes a holder
( '2001-01-03', 'John', 0 ), --John ceases to be a holder
( '2001-01-04', 'John', 10), --John becomes a holder
( '2001-01-05', 'Jane', 0 ) --Jane ceases to be a holder
SELECT
EffectiveDate, Holder
HQuantity, TQuantity, HQuantity * 100 / TQuantity AS Percentage
FROM (
SELECT
EffectiveDate, Holder, Quantity,
SUM(Quantity) OVER (PARTITION BY Holder ORDER BY EffectiveDate, Holder RANGE UNBOUNDED PRECEDING) AS HQuantity,
SUM(Quantity) OVER (ORDER BY EffectiveDate RANGE UNBOUNDED PRECEDING) AS TQuantity
FROM (
SELECT EffectiveDate, Holder, SUM(Quantity) AS Quantity
FROM (
SELECT EffectiveDate, Holder, Quantity FROM @Transactions
UNION ALL
SELECT D.*, H.*, 0
FROM (SELECT DISTINCT Holder FROM @Transactions) H
INNER JOIN (SELECT DISTINCT EffectiveDate FROM @Transactions) D ON 0 = 0
) Z
GROUP BY EffectiveDate, Holder
) X
) Y
WHERE HQuantity * 100 / TQuantity > 0
ORDER BY EffectiveDate, Holder
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 19, 2016 at 11:33 am
Drew,
Sorry, you're right. I over-simplified thinking it would help. Here's something bigger:
INSERT INTO @Transactions (EffectiveDate, Holder, Quantity) VALUES
( '2001-01-01', 'John', 5 ),
( '2001-01-01', 'John', 5 ), --John Holds 100% at the end of the day
( '2001-01-02', 'Peter', 10 ), --John and Peter both hold 50% at the end of the day
( '2001-01-03', 'John', -5 ), --John holds 33% and Peter holds 66% at the end of the day
( '2001-01-04', 'John', -5 ) --Peter holds 100% at the end of the day
- Holders can have multiple transactions as of a same date
- Quantities can be positive or negative numbers, increasing or decreasing the balance held at the end of the day
September 19, 2016 at 11:34 am
J Livingston SQL,
My real data is more complex than the sample data I post with my examples. In the first example you referred to, quantity was the actual sum of holdings at the end of a day while in the current example, quantity is whatever is acquired or sold. For example, ( '2001-01-03', 'John', 0 ) does not mean anymore that John holds nothing but would rather be a "fake" transaction leaving the John's balance unchanged. Result is thus correct.
See previous post with richer data.
September 19, 2016 at 11:34 am
It seems that your sample code is missing a comma. I assume that you want to display the Holder and the HQuantity instead of aliasing the Holder AS HQuantity.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 19, 2016 at 11:40 am
I edited and added the comma. Sorry. Current result is
EffectiveDateHolderHQuantityTQuantityPercentage
2001-01-01John 10 10 100
2001-01-02John 10 20 50
2001-01-02Peter 10 20 50
2001-01-03John 5 15 33
2001-01-03Peter 10 15 66
2001-01-04Peter 10 10 100
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply