April 21, 2017 at 7:25 am
Hi,
I want to create a table function which will show when passing the RefNo it will return one row for each RefNo which will show the Highest Total which had the biggest increase.
CREATE TABLE [dbo].[TableA](
[RefNo] [int] NOT NULL,
[ValidFrom] [datetime] NOT NULL,
[Total] [int] NULL
) ON [PRIMARY]
GO
insert into TableA values (1234,'17-Nov-2003',10000)
insert into TableA values (1234,'01-Nov-2004',12000)
insert into TableA values (1234,'01-Jun-2005',12500)
insert into TableA values (1234,'30-Jun-2006',15200)
insert into TableA values (1234,'01-Jul-2007',15500)
insert into TableA values (1234,'01-Jan-2008',18000)
insert into TableA values (1235,'01-Jun-2005',15000)
insert into TableA values (1235,'30-Jun-2006',25000)
insert into TableA values (1235,'01-Jul-2007',20000)
insert into TableA values (1236,'17-Nov-2003',25000)
insert into TableA values (1236,'01-Nov-2004',25200)
insert into TableA values (1236,'01-Jun-2005',25400)
So if there was a table function created called tvf_HighestIncreased and I ran the below
Select *
From dbo.tvf_HighestIncreased(1234)
It should return the below result set
RecNo Increased
1234 2700
Because between ValidFrom 01-JUN-2005 and 30-JUN-2006, there was an increase of 2700. The ValidFrom Date of 01-JUN-2005 had a Total 12500 the next Date after this is the 30-JUN-2006 which was 15200 so when you deduct 15200 minuses 12500 this equals 2700.
If I ran this for RefNo 1236 I would expect one row of 200.
April 21, 2017 at 7:36 am
How's this?CREATE FUNCTION tvf_HighestIncreased (@Refno int)
RETURNS TABLE AS RETURN (
WITH Increases AS(
SELECT *,
Total - LAG(Total) OVER (PARTITION BY Refno ORDER BY ValidFrom ASC) AS Increase
FROM TableA)
SELECT I.RefNo, MAX(I.Increase) AS MaxIncrease
FROM Increases I
WHERE I.RefNo = @Refno
GROUP BY I.RefNo
);
And now with added SQL 2008 version!:CREATE FUNCTION tvf_HighestIncreased (@Refno int)
RETURNS TABLE AS RETURN (
WITH RN AS(
SELECT A.*,
ROW_NUMBER() OVER (PARTITION BY A.RefNo ORDER BY A.ValidFrom) AS RN
FROM TableA A),
Increases AS (
SELECT RN.RefNo, RN.Total,
RN.Total - P.Total AS Increase
FROM RN
LEFT JOIN RN P ON RN.RefNo = P.RefNo AND RN.RN - 1 = P.RN)
SELECT I.RefNo, MAX(Increase) AS MaxIncrease
FROM Increases I
WHERE I.RefNo = @Refno
GROUP BY I.RefNo
);
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 21, 2017 at 7:41 am
--edit-- - use Thom A's solution - it actually answers your question... 😉
The body of the function definition could have something like this doing the work:
WITH Deltas AS
(
SELECT
* ,
Total - ISNULL(LAG(Total) OVER ( PARTITION BY RefNo ORDER BY ValidFrom ), Total) AS Delta
FROM
TableA
) ,
OrderedDeltas AS
(
SELECT
* ,
ROW_NUMBER() OVER ( PARTITION BY RefNo ORDER BY Delta DESC ) AS DeltaRow
FROM
Deltas
)
SELECT * FROM OrderedDeltas WHERE DeltaRow = 1 ;
You'll obviously want to parameterise this, but it should do the job.
<disclaimer>It might not be the most efficient piece of code... and if you don't know what it does, then you might want to think about it for a while before implementing it...</disclaimer>
Thomas Rushton
blog: https://thelonedba.wordpress.com
April 21, 2017 at 7:46 am
Thom A - Friday, April 21, 2017 7:36 AMHow's this?CREATE FUNCTION tvf_HighestIncreased (@Refno int)
RETURNS TABLE AS RETURN (
WITH Increases AS(
SELECT *,
Total - LAG(Total) OVER (PARTITION BY Refno ORDER BY ValidFrom ASC) AS Increase
FROM TableA)
SELECT I.RefNo, MAX(I.Increase) AS MaxIncrease
FROM Increases I
WHERE I.RefNo = @Refno
GROUP BY I.RefNo
);
I am using 2008 r2 so the LAG function is not support I'm afraid? Do you have a alternative solution?
April 21, 2017 at 7:48 am
ThomasRushton - Friday, April 21, 2017 7:41 AM--edit-- - use Thom A's solution - it actually answers your question... 😉The body of the function definition could have something like this doing the work:
WITH Deltas AS
(
SELECT
* ,
Total - ISNULL(LAG(Total) OVER ( PARTITION BY RefNo ORDER BY ValidFrom ), Total) AS Delta
FROM
TableA
) ,
OrderedDeltas AS
(
SELECT
* ,
ROW_NUMBER() OVER ( PARTITION BY RefNo ORDER BY Delta DESC ) AS DeltaRow
FROM
Deltas
)
SELECT * FROM OrderedDeltas WHERE DeltaRow = 1 ;You'll obviously want to parameterise this, but it should do the job.
<disclaimer>It might not be the most efficient piece of code... and if you don't know what it does, then you might want to think about it for a while before implementing it...</disclaimer>
Yes sam as before, I am using 2008 R2 so the LAG function is not support I'm afraid
April 21, 2017 at 7:53 am
SQL_Kills - Friday, April 21, 2017 7:46 AMThom A - Friday, April 21, 2017 7:36 AMHow's this?CREATE FUNCTION tvf_HighestIncreased (@Refno int)
RETURNS TABLE AS RETURN (
WITH Increases AS(
SELECT *,
Total - LAG(Total) OVER (PARTITION BY Refno ORDER BY ValidFrom ASC) AS Increase
FROM TableA)
SELECT I.RefNo, MAX(I.Increase) AS MaxIncrease
FROM Increases I
WHERE I.RefNo = @Refno
GROUP BY I.RefNo
);I am using 2008 r2 so the LAG function is not support I'm afraid? Do you have a alternative solution?
Yep, noticed this just after I posted (thought I was in the 2012 forum). I've added an extra solution for you.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 21, 2017 at 8:05 am
Thom A - Friday, April 21, 2017 7:53 AMSQL_Kills - Friday, April 21, 2017 7:46 AMThom A - Friday, April 21, 2017 7:36 AMHow's this?CREATE FUNCTION tvf_HighestIncreased (@Refno int)
RETURNS TABLE AS RETURN (
WITH Increases AS(
SELECT *,
Total - LAG(Total) OVER (PARTITION BY Refno ORDER BY ValidFrom ASC) AS Increase
FROM TableA)
SELECT I.RefNo, MAX(I.Increase) AS MaxIncrease
FROM Increases I
WHERE I.RefNo = @Refno
GROUP BY I.RefNo
);I am using 2008 r2 so the LAG function is not support I'm afraid? Do you have a alternative solution?
Yep, noticed this just after I posted (thought I was in the 2012 forum). I've added an extra solution for you.
Thanks Thom A nice work!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply