CODEWITHSUNDEEP
sql
dotsent12
dotsent12

Reputation: 137

SQL selective grouping

I'm experiencing some trouble comparing related data in different tables, I would be very grateful to get some assistance on the topic below. Unfortunately I'm not sure what the DBS is (nothing cutting edge though), but running it on IBM hardware. So to simplify the dataset:

INVOICES

Doci         Sumi
1005         10
1006         15
1007         7
1008         20

PAYMENTS

Docp        Sump
1006         -15
1005         -4
1005         -6
1007         -7

The aim is to compare two tables and see if there is a match - essentially if the invoice amount has been covered in the payments or not. The first new thing for me was to compare the numeric data which is in negative values in the other chart, but got it to work with the -1 multiplier.

The remaining issue that I can't really come up with a solution for is essentially making the query understand that record / invoice no 1005 is covered, just with two transactions. Essentially the result should only be invoice no 1008 as it does not have a match in the payment table.

I did end up reviewing SQL aggregate functions and namely SUM, however really didn't figure out how to utilize it as I don't want to summarize values in the entire Sump column, but only those that have a identical record in the Docp column.

So far what I have is this:

SELECT * from INVOICES
inner join PAYMENTS on INVOICES.Doci = PAYMENTS.Docp
where Sumi <> (Sump*-1)

So, this query works to the extent that I will not get records such as 1006 and 1007 in the result, but I do get 1005, as seemingly value 10 is compared to 4, instead of 4 + 6.

Many thanks in advance for any feedback!

Upvotes: 2

Views: 176

Answers (4)

Josh Harris
Josh Harris

Reputation: 446

I did this in two steps? - had to group them together to identify if there was multiple payments, and whether it balanced the invoice or not:

SELECT doci, sumi, docp, sum(sump) as Sump INTO #Step1
from #INVOICES
inner join PAYMENTS on INVOICES.Doci = PAYMENTS.Docp
GROUP BY
doci, sumi, docp

GO

SELECT * from INVOICES
inner join #PAYMENTS on #INVOICES.Doci = #PAYMENTS.Docp
where Sumi <> (Sump*-1) AND doci NOT IN (select doci from #step1 WHERE sumi+Sump = 0)

Results are an empty dataset, as it will only return values where the payment was insufficient to cover the invoice (or too much!)

All the IDs in your example have a match (i.e. payments == invoice)

Upvotes: 0

Nazar Merza
Nazar Merza

Reputation: 3454

You need to break up the problem into steps:

First sum the payments by it's id, Docp. 

select 
    Docp,
    SUM(Sump) as payment_sum
from PAYMENTS
group by Docp

let's call the result of this query, PAYMENT_SUM.

Then compare it with invoice.

select 
    I.Doci,
    I.sumi + P.payment_sum as diff
from INVOICES I
left join PAYMENT_SUM P
on I.Doci = P.Docp
;

This will give you a result as:

Doci diff 
1005 0 
1006 0 
1007 0 
1008 null

Now looking at this result, you can add one more clause to the query to select what you want. If you want to select invoice with no match at all on payment side, then clause will be

where P.Docp is null

Or, if you want to also have those invoices which has a match in payment but the amount is not fully covered, then:

where (P.Docp is null) or ((I.sumi + P.payment_sum) <> 0)

Now, to combine all the steps:

    select 
        I.Doci,
        I.sumi + P.payment_sum as diff
    from INVOICES as I
    left join (
        select 
            Docp,
            SUM(Sump) as payment_sum
        from PAYMENTS
        group by Docp
    )   as P
    on I.Doci = P.Docp

    where (P.Docp is null) or (I.sumi + P.payment_sum) <> 0
    ;

Upvotes: 0

Stephen
Stephen

Reputation: 1542

try this, it will give you only oustanding amount.

declare @inv  table (Doci int, Sumi decimal)
insert @inv values (1005, 10), (1006, 15), (1007, 7), (1008, 20)

declare @pmt  table (Docp  int, Sump decimal)
insert @pmt values (1005, -4), (1006, -15), (1007, -7), (1005, -6)

;with payments(doc, amt)
as (
    select Docp, sum(Sump)
    from @pmt 
    group by Docp
)
select Doci, Sumi + isnull(amt,0) as Remaining
from @inv
left outer join payments p on p.doc = Doci 
where (Sumi + isnull(amt,0)) <> 0

result:

Doci        Remaining
----------- ----------
1008        20

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39497

You can find total sump of each docp and then join it with the invoices table based on doc and sum

select i.* from invoices i
inner join (
    select
        docp,
        sum(sump) sump
    from payments
    group by docp
) p on i.doci = p.docp
and i.sumi + p.sump <> 0

Upvotes: 1

Related Questions