CODEWITHSUNDEEP
sqlsql-server-2008
Palanikumar
Palanikumar

Reputation: 7150

Group Row By Sum of Specific Column equal to Specific Value

Is that anyway to group the data by some specific value. For example, a table have Amount column, i want to group the rows by every 100 amount without while loop/Cursor :)

DueAmount = 100

Receipt Table structure:

ReceiptNo   Amount
------------------
R1          100
R2          100
R3          70
R4          50
R5          200

Output: (Group the Receipt table by 100)

DueNo/GroupKey      ReceiptNo   Amount
--------------------------------------
D1                  R1          100
D2                  R2          100
D3                  R3          70
D3                  R4          30
D4                  R4          20
D4                  R5          80
D5                  R5          100
D6                  R5          20

Upvotes: 2

Views: 568

Answers (1)

Y.B.
Y.B.

Reputation: 3586

With a helper table defining "Due" boundaries imposed over source table with running total calculated, an intersection of each running total amount with the "Due" boundaries can be calculated:

With Receipt As ( --< Sample source table
    Select * From (Values
        ('R1', 100),
        ('R2', 100),
        ('R3',  70),
        ('R4',  50),
        ('R5', 200)
    ) V (ReceiptNo, Amount)
), ReceiptWithTotal As ( --< Source table with Running Totals calculated
    Select *,
        SUM(Amount) Over (Order By ReceiptNo Rows Unbounded Preceding) - Amount As RunningTotalBefore,
        SUM(Amount) Over (Order By ReceiptNo Rows Unbounded Preceding) As RunningTotalAfter
    From Receipt
), Due As ( --< Helper table to define intervals (can be generated dynamically to cover any Total)
    Select * From (Values
        ('D1',   0, 100),
        ('D2', 100, 200),
        ('D3', 200, 300),
        ('D4', 300, 400),
        ('D5', 400, 500),
        ('D6', 500, 600)
    ) V (DueNo, AmountLow, AmountHigh)
)
Select DueNo, ReceiptNo,
    IIF(AmountHigh < RunningTotalAfter, AmountHigh, RunningTotalAfter) -
    IIF(AmountLow > RunningTotalBefore, AmountLow, RunningTotalBefore) As Amount
From Due
Inner Join ReceiptWithTotal On NOT (RunningTotalAfter <= AmountLow OR RunningTotalBefore >= AmountHigh)

Please note: SUM(...) Over (Order By ... Rows Unbounded Preceding) and IIF(...) are available on SQL Server 2012+ only. The same can be done on SQL Server 2008 through sub-query though much less efficient:

With Receipt As ( --< Sample source table
    Select * From (Values
        ('R1', 100),
        ('R2', 100),
        ('R3',  70),
        ('R4',  50),
        ('R5', 200)
    ) V (ReceiptNo, Amount)
), ReceiptWithTotal As ( --< Source table with Running Totals calculated
    Select *, RunningTotalAfter - Amount As RunningTotalBefore
    From (
        Select *,
            (Select SUM(Amount) From Receipt B Where B.ReceiptNo <= A.ReceiptNo) As RunningTotalAfter
        From Receipt A
    ) A
), Due As ( --< Helper table to define intervals (can be generated dynamically to cover any Total)
    Select * From (Values
        ('D1',   0, 100),
        ('D2', 100, 200),
        ('D3', 200, 300),
        ('D4', 300, 400),
        ('D5', 400, 500),
        ('D6', 500, 600)
    ) V (DueNo, AmountLow, AmountHigh)
)
Select DueNo, ReceiptNo,
    CASE WHEN AmountHigh < RunningTotalAfter THEN AmountHigh ELSE RunningTotalAfter END -
    CASE WHEN AmountLow > RunningTotalBefore THEN AmountLow ELSE RunningTotalBefore END As Amount
From Due
Inner Join ReceiptWithTotal On NOT (RunningTotalAfter <= AmountLow OR RunningTotalBefore >= AmountHigh)

Upvotes: 2

Related Questions