September 13, 2016 at 1:11 pm
I have a table that contains a list of holders with the amount held at the end of the day. The table therefore contains at most one entry per holder per day. I want to produce a report that would display each holder with a start date and an end date. You can test with:
DECLARE @Holdings TABLE (EffectiveDate DATETIME, Holder VARCHAR(10), Amount INT)
INSERT INTO @Holdings (EffectiveDate, Holder, Amount) 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
DECLARE @Report TABLE (Holder VARCHAR(10), StartDate DATETIME, EndDate DATETIME)
DECLARE @EffectiveDate DATETIME, @Holder VARCHAR(10), @Amount INT
DECLARE cur CURSOR FOR SELECT EffectiveDate, Holder, Amount FROM @Holdings ORDER BY EffectiveDate
OPEN cur
FETCH cur INTO @EffectiveDate, @Holder, @Amount
WHILE @@FETCH_STATUS = 0 BEGIN
IF @Amount <> 0 AND NOT EXISTS(SELECT NULL FROM @Report WHERE Holder = @Holder AND EndDate IS NULL)
INSERT INTO @Report SELECT @Holder, @EffectiveDate, NULL
IF @Amount = 0 UPDATE @Report SET EndDate = @EffectiveDate WHERE Holder = @Holder AND EndDate IS NULL
FETCH cur INTO @EffectiveDate, @Holder, @Amount
END
CLOSE cur
DEALLOCATE cur
SELECT * FROM @Report ORDER BY StartDate, Holder
and the result is:
John 2001-01-01 2001-01-03
Jane 2001-01-02 2001-01-05
John 2001-01-04
Does someone have a brilliant idea so the same result would be efficiently achieved without the use of cursors?
September 13, 2016 at 1:36 pm
can you please confirm what version of SQL you are using.....looking at some of your previous posts I cannot be sure.
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 13, 2016 at 1:40 pm
I have 2014 installed on my local computer. My clients have 2008 and up. If the right solution requires some clients to upgrade then it will be done! Thanks.
September 13, 2016 at 3:09 pm
This solution requires 2012, since it uses LEAD, FIRST_VALUE, and LAST_VALUE.
WITH holding_ends AS (
SELECT *,
CASE
WHEN h.Amount = 0 THEN h.EffectiveDate
WHEN h.EffectiveDate = FIRST_VALUE(h.EffectiveDate) OVER(PARTITION BY h.Holder ORDER BY h.EffectiveDate ROWS UNBOUNDED PRECEDING) THEN DATEADD(DAY, -1, h.EffectiveDate)
WHEN h.EffectiveDate = LAST_VALUE(h.EffectiveDate) OVER(PARTITION BY h.Holder ORDER BY h.EffectiveDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) THEN '9999-12-30'
END AS no_holdings_date
FROM @Holdings h
)
, holding_ranges AS (
SELECT h.Holder, DATEADD(DAY, 1, h.no_holdings_date) start_dt, LEAD(h.no_holdings_date) OVER(PARTITION BY h.Holder ORDER BY h.no_holdings_date) AS end_dt
FROM holding_ends h
WHERE h.no_holdings_date IS NOT NULL
)
SELECT h.Holder, h.start_dt, NULLIF(h.end_dt, '9999-12-30')
FROM holding_ranges h
WHERE h.end_dt IS NOT NULL
ORDER BY h.start_dt, h.Holder
Part of the issue is that you have boundary conditions that are not represented in the data. Specifically, you don't have that the holders started off with no holdings.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 14, 2016 at 1:37 pm
With the given dataset your solution gives the exact same result. With different datasets I sometimes get slightly different results but I am fixing it as I am learning new SQL Server concepts. Thanks.
I found the problem! If there is only one transaction (in my example, if the sole transaction is the first transaction) then the result is an empty dataset since the LEAD function returns nothing and the record is then discarded.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply