CODEWITHSUNDEEP
sql-serversql-server-2012
Olaf D.
Olaf D.

Reputation: 717

retrieving sql data from each nth row only

I have a table containing 'contracts', using a contractId as primary key. In another table I have prices which relate to those contracts (FK contractId). I previously had to calculate price differences (return) between prices grouped by contracts and always across a certain date period. So I just fetched the price for each day in the period of interest and compared it to the price from the previous day, for each contract. That wasn't to hard.

Now I need to calculate returns again from the same data but always skipping prices that relate to a number (n) contracts. so for n = 1 I just skip one contract. For n = 2 I need to skip 2 contracts... For each contract in question I need to calculate the return of two prices by date again but once i'm done with let's say contractId = 1, I need to skip over the contract with contractId = 2 and start over calculating returns from the date where I stopped calculating returns for the contract with the id = 3. A list of contracts is looking like this:

ContractId  ContractCode
100001  NAM0899
100002  NAM0999
100003  NAM1099
100004  NAM1199
100005  NAM1299
100006  NAM0100
100007  NAM0200
100008  NAM0300
100009  NAM0400
100010  NAM0500

So for N = 1, i'd need to find prices that are relate to ContractIds:

100001, 100003, 100005, 100007
N = 2: 100001, 100004, 100007, 100010...
N = 3: 100001, 100005, 100009, 100013...

I have tried to use Lead and Offset but I'm not able to find a way to properly skip contracts and link the relevant contracts together the way I need to. What am I missing? Thanks in advance!

Upvotes: 0

Views: 34

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

Took a little tweaking on the WHERE (I should have read the comments), but I think this generates your desired results

Declare @Table table (ContractId int,ContractCode varchar(25))
Insert Into @Table values 
(100001,'NAM0899'),
(100002,'NAM0999'),
(100003,'NAM1099'),
(100004,'NAM1199'),
(100005,'NAM1299'),
(100006,'NAM0100'),
(100007,'NAM0200'),
(100008,'NAM0300'),
(100009,'NAM0400'),
(100010,'NAM0500')

Declare @Rows   int = 2
Declare @RetVal varchar(max) = ''
;with cteBase as (
    Select *,RowNr=Row_Number() over (Order By ContractId) from @Table
)
Select @RetVal = Replace(ltrim(rtrim(concat(@RetVal,' ',ContractId))),' ',',')
 From cteBase
 Where RowNr=1 or (RowNr-1) % (@Rows+1) = 0
 Order by RowNr

Select RetVal=concat('N = ',@Rows,': ',@RetVal)

Returns

N = 2: 100001,100004,100007,100010

To return the normalized list just replace final Select with

Select ContractId

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can use row_number() and modulo arithmetic:

select loc.*
from (select loc.*,
              row_number() over (order by Contractid) as seqnum
      from listOfContracts loc
     ) loc
where (seqnum - 1 % @n) = 0;

Upvotes: 2

Related Questions