CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Blood-HaZaRd
Blood-HaZaRd

Reputation: 2138

Get Data in one SQL query

My aim is easy, But I Don't know if I could retrieve my Data in one SQL query.

I have a table containing 4 columns the first holds some amounts to pay and the last is the installment's ID. I want, for a given amount get all the installments that I can pay.

For example (using the table below) if I have an amount = 2800 I want to get the 3 first rows (500/2000/2000) because I would pay 500 for the first, 2000 for the second and 300 for the third.

enter image description here

So is it possible to get desired rows in only one query ? I use SQL Server.

EDITED :

let's say the Amount that I have is 2800

The question, which installments will I pay with that amount ?

Response : I would pay the 3 installments with ID's 136 / 135 and 134.

  1. first I have 2800 so I can pay the first 500
  2. second I have 2300 left So I can pay the second 2000
  3. third I have 300 left so I can pay the third 2000 (and 1700 still remain unpaid)

Thanks.

Upvotes: 0

Views: 92

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You seem to want a cumulative sum. If you are using SQL Server 2012+, then this is easy:

select t.*
from (select t.*, sum(col1) over (order by installmenthiddenid desc) as cumesum
      from table t
     ) t
where cumesum - col1 <= 2800;

I am making guesses about the column names and how you are defining the ordering.

In earlier versions of SQL Server, you can do the same thing with apply or a correlated subquery.

EDIT:

In SQL Server 2008, you would do:

select t.*
from table t cross apply
     (select t2.*, sum(t2.col1) as cumesum
      from table t2
      where t2.installmenthiddenid >= t.installmenthiddenid
     ) t2
where t2.cumesum - t.col1 <= 2800;

Upvotes: 3

Pரதீப்
Pரதீப்

Reputation: 93754

Try this. Find Running total using Self join.

;WITH cte
     AS (SELECT Sum(b.amount) - 2800 AS amnt,
                a.installpaidamount,
                a.endinstallment,
                a.installhiddenid
         FROM   Yourtable a
                JOIN Yourtable b
                  ON b.endinstallment >= a.endinstallment
         GROUP  BY a.installpaidamount,
                a.endinstallment,
                a.installhiddenid),
     cte2
     AS (SELECT TOP 1 *
         FROM   cte
         WHERE  amnt > 0
         ORDER  BY amnt) SELECT *
FROM   cte
WHERE  amnt < 0
UNION ALL
SELECT *
FROM   cte2

Upvotes: 1

Related Questions