SQLTeam.com | Weblogs | Forums

Being selective about which records to return?


#1
DECLARE @test AS TABLE
(
TDate VARCHAR (8),
RefID VARCHAR (10),
Income DECIMAL (15, 2),
Interest DECIMAL (15, 2)
)

INSERT INTO @test
VALUES 
(
'20160301', '100234', '100.50', '13.44'
)
INSERT INTO @test
VALUES 
(
'20160311', '100299', '145.50', '13.44' 
)
INSERT INTO @test
VALUES 
(
'20160329', '100777', '174.50', '13.44' 
)

SELECT * FROM @test

What I'm trying to achieve here is the following output:

TDate       RefID     Income    Interest
20160301    100234    100.50    13.44
20160311    100299     45.00    0.00
20160329    100777     29.00    0.00

In other words
(a) keep the first value in the interest column and zero all subsequent entries
(b) where income is concerned, only display the difference between the previous entry and the current one

RefID is sequential so will always increase as entries are added to the table.

Any pokes in the right direction very gratefully received.


#2
select
	TDate,
	RefId,
	Income - Lag(Income,1,0) over(Order by Tdate) as Income,
	case when ROW_NUMBER() over (order by TDate) = 1 then Interest else 0 end 
	    	as Interest
from
	@test;

You will very likely need some kind of partition clause in the windowing functions, for example partition by account number.

As an aside, storing Dates as DATE data type rather than varchar(8) is a recommended best practice.


#3

Very good, thank you.

Never come across the LAG function before.