need to find rows in a table based upon a type field BUT stop when a select type

Question

Post reply

need to find rows in a table based upon a type field BUT stop when a select type

roy.tollison

SSCommitted

Points: 1934
More actions
August 21, 2025 at 10:49 pm

#4638880

i have a table
account number, date, type, amount
0000001, 20250815, P, 200.00
0000001, 20250810, C, -125.00
0000001, 20250801, C, -75.00
0000001, 20250725, C, -125.00
0000001, 20250720, P, 175.00
......
i need the query to stop when it finds the first P type after the first one. so i would get
0000001, 20250815, P, 200.00
0000001, 20250810, C, -125.00
0000001, 20250801, C, -75.00
0000001, 20250725, C, -125.00
and stop.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135556 More actions · Answer 1

Define your sort order to determine "first"

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Ken McKelvey SSCoach Points: 18945 More actions · Answer 2

The order in which rows are returned in a result set aren't (sic) guaranteed unless an ORDER BY clause is specified.

roy.tollison SSCommitted Points: 1934 More actions · Answer 3

the account, date is the sort order and and FYI the type field values can be C, D, A, L and O so in my example i just used C for in between the P types. What i am looking for is like a statement report for all accounts and i want to pull/report the entries from the payment to previous P.

Jo Pattyn SSC-Dedicated Points: 32513 More actions · Answer 4

Hmm, you may find the range with cross apply

Perhaps you can get lucky with windows functions

/*pseudocode as there is no copy paste sample data*/
with payments

(

select current.key currentpayment, previous.key previous payment

from mytable current where current.type=payment

cross apply (select top 1 key from mytable previous where previous.type = payment  and previous.key<current.key order by previous.key desc) /* find the previous one*/
/* where current.key = ... */
)
select  mytable...

from mytable inner join payments

where mytable.key<=payments.currentpayment and (mytable.key>payments.previouspayment or payments.payments.previouspayment is null

Ken McKelvey SSCoach Points: 18945 More actions · Answer 5

/* *** Test Data *** */
CREATE TABLE #t
(
   Account char(7) NOT NULL
   ,TransDate date NOT NULL
   ,TransType char(1) NOT NULL
   ,Amount decimal(19, 5) NOT NULL
   ,PRIMARY KEY (Account, TransDate)
);
INSERT INTO #t
VALUES ('0000001', '20250815', 'P', 200.00)
   ,('0000001', '20250810', 'C', -125.00)
   ,('0000001', '20250801', 'C', -75.00)
   ,('0000001', '20250725', 'C', -125.00)
   ,('0000001', '20250720', 'P', 175.00);
/* *** End Test Data *** */

WITH PCounts
AS
(
   SELECT Account, TransDate, TransType, Amount
      ,SUM(IIF(TransType = 'P', 1, 0)) OVER (PARTITION BY Account ORDER BY TransDate DESC) AS PCount
   FROM #t
)
SELECT Account, TransDate, TransType, Amount
FROM PCounts
WHERE PCount = 1
ORDER BY Account, TransDate DESC;

roy.tollison SSCommitted Points: 1934 More actions · Answer 6

Thank you both, i am still learning/testing the cross apply but the sum over partition sample works for sure.

i am working on the final product being a stored procedure to run by account number and a starting date.

thank you both, i really appreciate the information