Being selective about which records to return?

rasta_pickles · April 5, 2016, 6:42pm

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.

JamesK · April 5, 2016, 7:35pm

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.

rasta_pickles · April 6, 2016, 5:00pm

Very good, thank you.

Never come across the LAG function before.

harishgg1 · December 27, 2018, 3:07pm

hi

i know this topic is from 2 years ago

I tried to do something different
I dont have SQL Server 2012 .. so cant use LEAD LAG

drop create data

use tempdb 

go 

drop table #test 
go


create table #test 
(
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' 
)

SQL ..using SQL Server 2008

;WITH cte 
     AS (SELECT a.rn, 
                a.refid, 
                a.income, 
                a.tdate, 
                CASE 
                  WHEN a.rn = 1 THEN a.interest 
                  ELSE 0 
                END AS Interest 
         FROM   (SELECT Row_number() 
                          OVER( 
                            ORDER BY refid) rn, 
                        * 
                 FROM   #test) a) 
SELECT a.refid, 
       a.tdate, 
       a.income - Isnull(b.income, 0), 
       a.interest AS Income1 
FROM   cte a 
       LEFT JOIN cte b 
              ON a.rn = b.rn + 1 

go

Results