December 15, 2016 at 10:42 pm
Hi I need to analyse the value of an item at any particular time period - either current or past values. In my data a particular item might have one or more values with different dates. I need to return the current value as well as a balance at the beginning of the period or total for any date range.
My data is like this:
IFObject_ID('tempdb..#TestTable') is not null
DROP TABLE #TestTable
go
Create Table #TestTable(
RowID int,
ID int,
value money,
valuedate datetime
)
INSERT INTO #TestTable(RowID, ID, value, valuedate)
SELECT * FROM (select '220517', '1', '4000.00', '2016-11-01 00:00:00.000' union all select '220518', '1', '4440.00', '2016-12-12 00:00:00.000' union all select '220519', '1', '5000.00', '2014-03-01 00:00:00.000' union all
select '220520', '2', '100.00', '2014-03-01 00:00:00.000' union all select '220521', '2', '300.00', '2015-11-01 00:00:00.000' union all select '220522', '2', '6000.00', '2016-09-01 00:00:00.000' union all
select '220523', '2', '350000.00', '2016-12-09 00:00:00.000') t(Col, Col2, Col3, Col4)
SELECT * from #TestTable
My attempt is this:
declare @StartDate DATETIME, @EndDate DATETIME
set @StartDate ='2015-11-01 00:00:00.000'
set @EndDate = '2015-11-30 00:00:00.000';
with CurrentValue as (
select RowID,
ID,
Last_Value(Value) OVER (PARTITION BY ID ORDER BY ValueDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as CurrentValue,
Last_Value(cast(ValueDate as datetime)) OVER (PARTITION BY ID ORDER BY ValueDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)as CurrentValueDate
from #TestTable
),
PreviousValues as (
select RowID,
ID,
lag(cast(ValueDate as datetime)) OVER (PARTITION BY ID ORDER BY ValueDate ) as PreviousValueDate,
lag(Value) OVER (PARTITION BY ID ORDER BY ValueDate) PreviousValue
from #TestTable
)
select V.ID, V.CurrentValue, V.CurrentValueDate, Pv.PreviousValue, Pv.PreviousValueDate,
case
when V.CurrentValueDate <@EndDate then V.CurrentValue
when V.CurrentValueDate >@StartDate then Pv.PreviousValue
when Pv.PreviousValueDate < @StartDate then lag(Pv.PreviousValue) OVER (PARTITION BY Pv.ID ORDER BY Pv.PreviousValueDate)
end as OpeningBalance
from CurrentValue V join PreviousValues Pv
on V.RowID = Pv.RowID
I would like to be able to return one row for each item showing the balance at the start date as well as the current value but I can't work out how to access any previous value for the relevant date. So at 2015-11-01 I would expect to see these rows returned :
IDCurrentValue CurrentValueDate PreviousValueDate OpeningBalance at 2015-11-01
14440.00 2016-12-12 00:00:00.0002014-03-01 00:00:00.0005000.00
2350000.00 2016-12-09 00:00:00.0002015-11-01 00:00:00.000300.00
What I need to do eventually is more complicated than this in that I need to analyse the opening balance, new values added and total for a group of items at any particular time. I intend to use SSRS to make a final report but need help with this above before I can proceed. I would be really grateful for any help, Sally
December 16, 2016 at 2:05 am
This gives the expected results:
SELECT C.Id,
C.[value] AS CurrentValue,
C.valuedate AS CurrentValueDate,
P.valuedate AS PreviousValueDate,
P.[value] AS OpeningBalance
FROM #TestTable C
OUTER APPLY (SELECT TOP 1
oa.ID,
oa.value,
oa.valuedate
FROM #TestTable oa
WHERE oa.ID = C.ID
AND oa.valuedate <= @StartDate
ORDER BY oa.valuedate DESC) P
WHERE C.valuedate = (SELECT MAX(sq.valuedate)
FROM #TestTable sq
WHERE sq.ID = C.ID)
ORDER BY C.ID ASC
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 16, 2016 at 8:21 am
This version only requires one table scan.
;
WITH CTE AS (
SELECT
C.Id,
C.value AS CurrentValue,
C.valuedate AS CurrentValueDate,
MAX(CASE WHEN C.valuedate <= @StartDate THEN CAST(C.valuedate AS BINARY(6)) + CAST(C.value AS BINARY(4)) END) OVER(PARTITION BY Id) AS binvalue,
ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ValueDate DESC) AS rn
FROM #TestTable C
)
SELECT Id, CurrentValue, CurrentValueDate, CAST(SUBSTRING(binvalue, 1, 6) AS datetime), CAST(SUBSTRING(binvalue, 7, 4) AS money)
FROM CTE
WHERE rn = 1
I'm casting to binary rather than char, because I don't have to worry about localization for the dates.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 20, 2016 at 6:08 pm
Dear Drew
Thank you so much for this - it is very cool and succinct and does what I needed perfectly. There is no way that I could have worked this out myself so thank you for taking the trouble to help me.
Kind regards, Sally
December 20, 2016 at 6:11 pm
Dear Thom
Thank you very much for responding - your solution works very well and gives me the results that I need. I have marked the solution below as the solution to my problem though as it is a bit easier for me to understand and reuse this code. Thanks again for taking the trouble to help me.
Kind regards, Sally
January 11, 2017 at 10:19 pm
Dear Drew
Further to the great solution you gave me I have a problem with some results that I just can't figure out. I am trying to return a money value based on date parameters and for the most part this works very well. However I get inconsistent results if I change the date parameter for some rows only. I have examined the underlying data and can't see any differences that would account for this. In the example below if I change the start date parameter to 2016-06-30 I get an unexpected result for ObjectID 47238 ie 20503.2704 where I would expect to see 450000.00 but I do get the expected result for ObjectID 189547 ie 250000. I am at a loss as to what is going on so hope you will see this and respond.
IFObject_ID('tempdb..#TestTable1') is not null
DROP TABLE #TestTable1
go
Create Table #TestTable1(
ObjectID int,
Value money,
Valuedate nvarchar(19)
)
INSERT INTO #TestTable1(ObjectID, Value, ValueDate)
select * from (select 47238, '250000.00','2016-07-04' union all
select 47238, '450000.00', '2012-07-01' union all
select 189547, '75000.00', '2016-07-04' union all
select 189547, '250000.00', '2012-07-01') t(col1, Col2, Col3)
select * from #TestTable1
declare @StartDate DATETIME, @EndDate DATETIME
/*I get the expected OpenBalance results with this date parameter*/
set @StartDate ='2016-11-01 00:00:00.000'
set @EndDate = '2016-11-30 00:00:00.000';
/*I get unexpected result for ObjectID 47238 ie 20503.2704 but expected result for ObjectID 189547 ie 250000 - with this date parameter*/
--set @StartDate ='2016-06-30 00:00:00.000'
--set @EndDate = '2016-07-31 00:00:00.000';
WITH CTE AS (
SELECT
C.ObjectID,
C.Value AS CurrentValue,
C.ValueDate AS CurrentValueDate,
/*Original code for dates : MAX(CASE WHEN C.valuedate <= @StartDate THEN CAST(C.valuedate AS BINARY(6)) + CAST(C.value AS BINARY(4)) END) OVER(PARTITION BY ObjectId) AS binvalue, */
/*Changed to convert datetime as my original data is nvarchar for dates*/
MAX(CASE WHEN convert(datetime, C.ValueDate) <= @StartDate THEN CONVERT(varbinary (6), convert(datetime, C.ValueDate)) + CAST(C.Value AS VARBINARY(4)) END ) OVER(PARTITION BY C.ObjectID) AS binvalue,
ROW_NUMBER() OVER(PARTITION BY C.ObjectID ORDER BY ValueDate DESC) AS rn
FROM #TestTable1 C
)
SELECT ObjectID,CurrentValue, CurrentValueDate, CAST(SUBSTRING(binvalue, 1, 6) AS datetime) as OpenBalanceDate,
CAST(SUBSTRING(binvalue, 7, 4) AS money) as OpenBalance, rn
FROM CTE
--WHERE rn = 1
January 12, 2017 at 10:03 am
sfletcher-933270 - Wednesday, January 11, 2017 10:19 PMDear Drew Further to the great solution you gave me I have a problem with some results that I just can't figure out. I am trying to return a money value based on date parameters and for the most part this works very well. However I get inconsistent results if I change the date parameter for some rows only. I have examined the underlying data and can't see any differences that would account for this. In the example below if I change the start date parameter to 2016-06-30 I get an unexpected result for ObjectID 47238 ie 20503.2704 where I would expect to see 450000.00 but I do get the expected result for ObjectID 189547 ie 250000. I am at a loss as to what is going on so hope you will see this and respond.IFObject_ID('tempdb..#TestTable1') is not null DROP TABLE #TestTable1goCreate Table #TestTable1(ObjectID int,Value money,Valuedate nvarchar(19))INSERT INTO #TestTable1(ObjectID, Value, ValueDate)select * from (select 47238, '250000.00','2016-07-04' union allselect 47238, '450000.00', '2012-07-01' union allselect 189547, '75000.00', '2016-07-04' union allselect 189547, '250000.00', '2012-07-01') t(col1, Col2, Col3)select * from #TestTable1declare @StartDate DATETIME, @EndDate DATETIME/*I get the expected OpenBalance results with this date parameter*/set @StartDate ='2016-11-01 00:00:00.000' set @EndDate = '2016-11-30 00:00:00.000';/*I get unexpected result for ObjectID 47238 ie 20503.2704 but expected result for ObjectID 189547 ie 250000 - with this date parameter*/--set @StartDate ='2016-06-30 00:00:00.000' --set @EndDate = '2016-07-31 00:00:00.000';WITH CTE AS (SELECTC.ObjectID,C.Value AS CurrentValue,C.ValueDate AS CurrentValueDate,/*Original code for dates : MAX(CASE WHEN C.valuedate <= @StartDate THEN CAST(C.valuedate AS BINARY(6)) + CAST(C.value AS BINARY(4)) END) OVER(PARTITION BY ObjectId) AS binvalue, *//*Changed to convert datetime as my original data is nvarchar for dates*/MAX(CASE WHEN convert(datetime, C.ValueDate) <= @StartDate THEN CONVERT(varbinary (6), convert(datetime, C.ValueDate)) + CAST(C.Value AS VARBINARY(4)) END ) OVER(PARTITION BY C.ObjectID) AS binvalue,ROW_NUMBER() OVER(PARTITION BY C.ObjectID ORDER BY ValueDate DESC) AS rnFROM #TestTable1 C)SELECT ObjectID,CurrentValue, CurrentValueDate, CAST(SUBSTRING(binvalue, 1, 6) AS datetime) as OpenBalanceDate, CAST(SUBSTRING(binvalue, 7, 4) AS money) as OpenBalance, rnFROM CTE--WHERE rn = 1
It's because the max money value for (VAR)BINARY(4) is 429496.7295, and 450000 is larger than that. You'll need to change to VARBINARY(5) or larger. You'll also need to update your substring function to correspond.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 12, 2017 at 3:09 pm
drew.allen - Thursday, January 12, 2017 10:02 AMsfletcher-933270 - Wednesday, January 11, 2017 10:19 PMDear Drew Further to the great solution you gave me I have a problem with some results that I just can't figure out. I am trying to return a money value based on date parameters and for the most part this works very well. However I get inconsistent results if I change the date parameter for some rows only. I have examined the underlying data and can't see any differences that would account for this. In the example below if I change the start date parameter to 2016-06-30 I get an unexpected result for ObjectID 47238 ie 20503.2704 where I would expect to see 450000.00 but I do get the expected result for ObjectID 189547 ie 250000. I am at a loss as to what is going on so hope you will see this and respond.IFObject_ID('tempdb..#TestTable1') is not null DROP TABLE #TestTable1goCreate Table #TestTable1(ObjectID int,Value money,Valuedate nvarchar(19))INSERT INTO #TestTable1(ObjectID, Value, ValueDate)select * from (select 47238, '250000.00','2016-07-04' union allselect 47238, '450000.00', '2012-07-01' union allselect 189547, '75000.00', '2016-07-04' union allselect 189547, '250000.00', '2012-07-01') t(col1, Col2, Col3)select * from #TestTable1declare @StartDate DATETIME, @EndDate DATETIME/*I get the expected OpenBalance results with this date parameter*/set @StartDate ='2016-11-01 00:00:00.000' set @EndDate = '2016-11-30 00:00:00.000';/*I get unexpected result for ObjectID 47238 ie 20503.2704 but expected result for ObjectID 189547 ie 250000 - with this date parameter*/--set @StartDate ='2016-06-30 00:00:00.000' --set @EndDate = '2016-07-31 00:00:00.000';WITH CTE AS (SELECTC.ObjectID,C.Value AS CurrentValue,C.ValueDate AS CurrentValueDate,/*Original code for dates : MAX(CASE WHEN C.valuedate <= @StartDate THEN CAST(C.valuedate AS BINARY(6)) + CAST(C.value AS BINARY(4)) END) OVER(PARTITION BY ObjectId) AS binvalue, *//*Changed to convert datetime as my original data is nvarchar for dates*/MAX(CASE WHEN convert(datetime, C.ValueDate) <= @StartDate THEN CONVERT(varbinary (6), convert(datetime, C.ValueDate)) + CAST(C.Value AS VARBINARY(4)) END ) OVER(PARTITION BY C.ObjectID) AS binvalue,ROW_NUMBER() OVER(PARTITION BY C.ObjectID ORDER BY ValueDate DESC) AS rnFROM #TestTable1 C)SELECT ObjectID,CurrentValue, CurrentValueDate, CAST(SUBSTRING(binvalue, 1, 6) AS datetime) as OpenBalanceDate, CAST(SUBSTRING(binvalue, 7, 4) AS money) as OpenBalance, rnFROM CTE--WHERE rn = 1It's because the max money value for (VAR)BINARY(4) is 429496.7295, and 450000 is larger than that. You'll need to change to VARBINARY(5) or larger. You'll also need to update your substring function to correspond.
Drew
Thank you very much Drew - works a treat and I have learnt a little bit more re SQL.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply