CODEWITHSUNDEEP
sqlsql-server
Erric
Erric

Reputation: 13

Select without - values from table SQL

I'm taking items [I1] from this SQL Server table and returning to this table as - QTY and - TOTAL ..

https://i.sstatic.net/bdnPF.png

After that I have another row with the same Item ID and - QTY AND -Total

https://i.sstatic.net/4vUNP.png

I need to filter every time without returned product,

In this case I already returned the I1, when I'm going to return sale invoice .. I need to select the none returned products,

    SELECT  
        DEL_PurchasesLines.ItemIdentityCode, 
        SUM(DEL_PurchasesLines.Qty) As Qty, 
        SUM(DEL_PurchasesLines.Total) As Total

    FROM DEL_PurchasesLines

    WHERE InvoiceNo = '1' AND DealerCode = 'S0002M'

    GROUP BY
        DEL_PurchasesLines.ItemIdentityCode

   HAVING SUM(DEL_PurchasesLines.Total) > 0 AND SUM(DEL_PurchasesLines.Qty) > 0

Upvotes: 0

Views: 1021

Answers (1)

CRAFTY DBA
CRAFTY DBA

Reputation: 14915

I always like to create some test data in tempdb.

--
-- Create sample data
--

use tempdb;
go

if object_id('items') > 0
drop table items
go

create table items
(
  id varchar(8),
  qty int,
  total int
);
go

insert into items values
('I1', 2, 4),
('I2', 3, 6),
('I3', 5, 10),
('I1', -2, -4);
go

select * from items;
go

One way to solve this problem is to group by the id, summing both the qty and total columns. Display only rows that have > 0.

--
-- Show lines in which qty * total > 0
--

select id, sum(qty) as sum_qty, sum(total) as sum_total
from items
group by id
having sum(qty) > 0 and sum(total) > 0;

Another way to think of this is to show all orders that do not have returns.

--
-- Show rows that do not have any returns
--

select * 
from items i left join
(
    select id
    from items
    where qty < 0 or total < 0
) r on i.id = r.id
where r.id is null

Upvotes: 1

Related Questions