Complex select query for SQL Server

Question

I have a temp table in SQL server like below

╔═══════╦═════════╦══════╦═════════╗
║Serial ║ Account ║ Due  ║ Balance ║
║1      ║ A1      ║ 1000 ║ 3100    ║
║2      ║ A1      ║ 1000 ║ 3100    ║
║3      ║ A1      ║ 1000 ║ 3100    ║
║4      ║ A1      ║ 1000 ║ 3100    ║
║1      ║ A2      ║ 100  ║ 3100    ║
║2      ║ A2      ║ 100  ║ 3100    ║    
║1      ║ B1      ║ 1000 ║ 1100    ║
║2      ║ B1      ║ 1000 ║ 1100    ║
║1      ║ B2      ║ 100  ║ 1100    ║
║2      ║ B2      ║ 100  ║ 1100    ║
╚═══════╩═════════╩══════╩═════════╝

I want to identify the rows which due would be collected. A1 and A2 Due will be collected from 3100 and B1 and B2 due will be collected from 1100.

Firstly I have used cumulative Due as following

╔═══════╔═════════╦══════╦════════════╦═════════╦
║Serial ║ Account ║ Due  ║ Cumulative ║ Balance ║
║1      ║ A1      ║ 1000 ║  1000      ║    3100 ║
║2      ║ A1      ║ 1000 ║  2000      ║    3100 ║
║3      ║ A1      ║ 1000 ║  3000      ║    3100 ║
║4      ║ A1      ║ 1000 ║  4000      ║    3100 ║
║1      ║ A2      ║ 100  ║   100      ║    3100 ║
║2      ║ A2      ║ 100  ║   200      ║    3100 ║
║1      ║ B1      ║ 1000 ║  1000      ║    1100 ║
║2      ║ B1      ║ 1000 ║  2000      ║    1100 ║
║1      ║ B2      ║ 100  ║   100      ║    1100 ║
║2      ║ B2      ║ 100  ║   200      ║    1100 ║
╚═══════╚═════════╩══════╩════════════╩═════════╝

Now I want to select following rows as output

╔═══════╔═════════╦══════╦════════════╦═════════╦
║Serial ║ Account ║ Due  ║ Cumulative ║ Balance ║  
║1      ║ A1      ║ 1000 ║  1000      ║    3100 ║
║2      ║ A1      ║ 1000 ║  2000      ║    3100 ║ 
║3      ║ A1      ║ 1000 ║  3000      ║    3100 ║  
║1      ║ A2      ║ 100  ║   100      ║    3100 ║
║1      ║ B1      ║ 1000 ║  1000      ║    1100 ║
║1      ║ B2      ║ 100  ║   100      ║    1100 ║
╚═══════╚═════════╩══════╩════════════╩═════════╩

Here is where I am stuck. How can I select those rows without using cursor or loop. All I want to do this with select statement and window functions. Thanks.

Possible Solution: If the table can be updated as following then the problem would be solved.

╔═══════╔═════════╦══════╦═══════════════════╦
║Serial ║ Account ║ Due  ║ Balance Remaining ║
║1      ║ A1      ║ 1000 ║  3100             ║
║2      ║ A1      ║ 1000 ║  2100             ║
║3      ║ A1      ║ 1000 ║  1100             ║
║4      ║ A1      ║ 1000 ║   100             ║
║1      ║ A2      ║ 100  ║   100             ║
║2      ║ A2      ║ 100  ║     0             ║
║1      ║ B1      ║ 1000 ║  1100             ║
║2      ║ B1      ║ 1000 ║   100             ║
║1      ║ B2      ║ 100  ║   100             ║
║2      ║ B2      ║ 100  ║     0             ║
╚═══════╚═════════╩══════╩═══════════════════╩

The cases Balance Remaining is equal/greater than Due we update it with difference else it will remain as before. Problem is here to update rows by partitioning between A & B.

UPDATE I am providing link with new data set to express my requirement more clearly. new dataset

The problem is that even with the powerful window functions provided in SQL Server 2012, there is no good way to conditionally cumulative sum. In other words, contribute to the sum conditionally based on whether the previous total is larger than the balance. A cursor-based solution would probably be the easiest and most efficient - why don't you want a cursor-based solution? — mellamokb, Aug 12 '15 at 05:31
For a large data set cursor / while loop both become slower. — Esty, Aug 12 '15 at 07:24
Yes cursors/while loop are 99% of the time the worst solution. However in your case, where you need to calculate running totals or balance remaining, set-based is not always the answer. In fact, because of the condition (don't allow balance to drop below 0), I'm not even sure it's possible with set-based/window functions (or if it is, it may actually be *much slower* than a cursor). This would be one of the 1% cases where a cursor is probably the best solution (see http://sqlperformance.com/2012/07/t-sql-queries/running-totals and http://stackoverflow.com/a/11313533/116614). — mellamokb, Aug 12 '15 at 16:20

score 2 · Accepted Answer · answered Aug 16 '15 at 03:56

At last, solved this with update query.

UPDATE A
SET
A.Balance = @Balance
, @PreBalance = @Balance
, @Balance = ( CASE WHEN (@Balance IS NULL OR @AccountType <> A.AccountType)
                    THEN 
                        CASE WHEN A.Balance - A.Due >= 0
                            THEN A.Balance
                            ELSE A.Balance + A.Due
                        END
                    ELSE 
                        CASE WHEN @Balance - A.Due >= 0 AND (@Flag = 1 OR @AccountNO <> A.AccountNO)
                              THEN @Balance
                              ELSE @Balance + A.Due
                         END
                END) - A.Due
, A.FLAG = @Flag
, @AccountNO = CASE WHEN A.Flag = 0 THEN A.AccountNO ELSE 'NoDueFoundForAcc' END
, @Flag = CASE WHEN @AccountType = A.AccountType 
                THEN 
                    CASE WHEN @PreBalance = @Balance 
                            THEN 0 
                            ELSE 1 
                        END
                ELSE 
                    CASE WHEN A.Balance - A.Due >= 0
                            THEN 1
                            ELSE 0 
                        END
                END
, @AccountType = A.AccountType
FROM #tempTable A

SELECT * FROM #tempTable A WHERE A.Flag = 1

VISHMAY · Answer 2 · 2015-08-12T06:45:21.270

1

Very simple

select * from account 
where (Balance-(Select sum(ac.Due) from account ac where 
ac.SerialNo<=account.SerialNo and  ac.Account =account.Account )>0)

Update

there is no relation between A1 and A2 that say that balance 3100 is to be share between A1 and A2 and not with B1.

So you have to specify some where that a1 and a2 are on same group
there is suggested option for you
Add group no column in you table and give same no for A1 and A2, other same no for B1 and B2. Then add Priority column that specifies A1 should deduct first due then if balance left a2 will get chance

then query will be

    SELECT          *
    FROM            account
    WHERE
        ( Balance - ( SELECT
                        SUM(ac.Due)
                      FROM
                        account ac
                      WHERE
                        ( ac.GroupNo = account.GroupNo
                          AND ( ( ac.Account = account.Account
                                  AND ( ac.SerialNo <= account.SerialNo )
                                )
                                OR ac.Prioirty < account.Prioirty
                              )
                        )
                    ) > 0 )

edited Aug 12 '15 at 06:45

answered Aug 12 '15 at 05:10

VISHMAY

699
5
20

Your query returns as following [link](http://pastebin.com/MnMJqRWN) which is not the required output. Serial 2 for account A2 and B2 are the false rows. – Esty Aug 12 '15 at 05:51
U r right @VISHMAY..I was just thinking about it. Perhaps I couldn't give my actual requirement in data set. I am giving a new set of data.[link](http://pastebin.com/mmbnCWb9) – Esty Aug 12 '15 at 07:21
@Tanjim Rahman this query is also possible in your given link, but it will not give you performance as it need to go two levels in sub query, instead take data in code and then count remaining and use proper if else – VISHMAY Aug 12 '15 at 08:01
U mean to use cursor or While I think. But for a large data set doesn't that would be more expensive??? – Esty Aug 12 '15 at 08:48
@Tanjim Rahman i was saying that you will have used some programming lang like c#,java, for c# take this data in datatable, you have and then manipulate it by for loop, it will consist only one for loop, so you can remove headache of sub queries it will surely be cost effective and quick result providing, and will be created in half an hour – VISHMAY Aug 12 '15 at 09:27
@Tanjim Rahman dont need to use other queries just select * from table to datatable then manipulate it there by datatable .Rows[i][j] – VISHMAY Aug 12 '15 at 09:37
it will be a piece of cake if i could do it in code. But I have the restriction to do it in Stored procedure. – Esty Aug 12 '15 at 09:53
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/86769/discussion-between-tanjim-rahman-and-vishmay). – Esty Aug 12 '15 at 09:59
what are restrictions? i can do it but cannot do it now, by tomorrow, i will send it but i need database structure with the data inputted you have shown in final link, make a query of it, so that i can verify the final result, as system is complex verification is neccesary table is requered so that i can use designer – VISHMAY Aug 12 '15 at 10:45

score 0 · Answer 3 · answered Aug 12 '15 at 05:00

0

For getting this output you can use a simple query like below:

╔═══════╔═════════╦══════╦════════════╦═════════╦
║Serial ║ Account ║ Due  ║ Cumulative ║ Balance ║  
║1      ║ A1      ║ 1000 ║  1000      ║    3100 ║
║2      ║ A1      ║ 1000 ║  2000      ║    3100 ║ 
║3      ║ A1      ║ 1000 ║  3000      ║    3100 ║  
║1      ║ A2      ║ 100  ║   100      ║    3100 ║
║1      ║ B1      ║ 1000 ║  1000      ║    1100 ║
║1      ║ B2      ║ 100  ║   100      ║    1100 ║
╚═══════╚═════════╩══════╩════════════╩═════════╩

select Serial,Account,Due,Cumulative,Balance from
(Select *, row_number() over(Partition by Account, order by serial number desc) as r from Temp) t
where t.r>1

answered Aug 12 '15 at 05:00

DhruvJoshi

17,041
6
41
60

You've gotten the exact output... but you are not applying any of the actual logic (selecting rows until cumulative > balance). This would easily be broken by a different example data set. – mellamokb Aug 12 '15 at 05:18
Agreed.. For following data set this query will not work. [link](http://pastebin.com/cYAeXfMJ) – Esty Aug 12 '15 at 06:07
@TanjimRahman As i mentioned in my answer `For getting this output` – DhruvJoshi Aug 12 '15 at 06:25
Yeah @DhruvJoshi, it seems my dataset is not expressing my requirement properly. I am giving a new set of data. – Esty Aug 12 '15 at 07:23

Complex select query for SQL Server

3 Answers3