Need help excluding records in select based on sum of records

Tom · October 29, 2019, 3:41pm

Hi,

I'm trying to create a select statement that will show me:

orderlines that don't have a corrsponding location record (meaning: that orderline was not picked in a warehouse)
OR
orderlines where the total of the quantities of the corresponding location record(s) are less than the quantity that was ordered (meaning not all items could be picked)

This is what I have so far:

select OutgoingDetails.OrderLineDescription,outgoingdetails.Quantity, outgoingdetails.SessionId,
OutgoingLocation.LocationId, outgoinglocation.Quantity,
OutgoingSessionDelivery.DeliveryNumber,
customer.CustomerName
from OutgoingDetails
left join OutgoingLocation on OutgoingDetails.Id = OutgoingLocation.OutgoingDetailsId
left join OutgoingSessionDelivery on outgoingdetails.SessionId = OutgoingSessionDelivery.SessionId
left join Customer on OutgoingSessionDelivery.CustomerId = Customer.Id
where outgoingdetails.SessionId = 122 and outgoinglocation.LocationId is null

This is fine. This is the case where there is no location found, meaning there was no stock at all. I want to see this record.

However, sometimes an orderline is picked on one, or two or more locations, and only if the sum of the picked quantities on these location(s) is less than the quantiy that was ordered, they should be in my result set too.

But if the sum of the quantities is equal, then it should be excluded.

Here's an example where the result should be excluded. the 2 records highlighted in blue. the sum of the 2 quantity records (the second quantity column) is 3+1 = 4. This is equal to the first quantity column . So the 4 items have been all picked in 2 locations, which is fine, and should be excluded from the result.

I don't know how to exclude this type of record, and I also need to combine both possibilities in 1 select statement.

query-locs

I hope I explained this well enough so that someone can help me.

Thanks,
Tom

Tom · October 29, 2019, 3:42pm

Just for a better understanding, here's a snippet of the case where no location id was found (I tried to add this to my original post, but new users can only post one image per post apparently.

query-locs-2

ScottPletcher · October 29, 2019, 7:47pm

Welcome Tom!

I'm trying to get the data set up but I can't get the 'BeyersPlus 450' (Qty 4) to match to two other rows (Qty 3 and Qty 1). If you can help me correct the data, and get the basic query just to list all the data fully, I can work on the specific query(ies) you need to restrict data based on totals.

USE tempdb;
CREATE TABLE dbo.OutgoingDetails (
    Id int NOT NULL,
    OrderLineDescription nvarchar(200) NULL,
    Quantity decimal(19, 6) NULL,
    SessionId int NULL
    )
TRUNCATE TABLE dbo.OutgoingDetails;
INSERT INTO dbo.OutgoingDetails VALUES
    (1, N'Nestmatjes', 30.0, 122),
    (2, N'Pigo', 4.0, 122),
    (3, N'BeyersPlus 600', 4.0, 122),
    (4, N'Natural', 6.0, 122),
    (5, N'BeyersPlus 450', 4.0, 122),
    (7, N'DHPCultura', 3.0, 122),
    (8, N'Drinkbak', 10.0, 122),
    (9, N'Rohnfried', 1.0, 103)
CREATE TABLE dbo.OutgoingLocation (
    OutgoingDetailsId int NOT NULL,
    Quantity decimal(19, 6) NULL,
    LocationId int NULL
    )
INSERT INTO dbo.OutgoingLocation VALUES
    (1, 30.0, 1888),
    (2, 4.0, 1417),
    (3, 4.0, 2),
    (4, 6.0, 374),
    (5, 3.0, 799),
    (6, 1.0, 2),
    (7, 3.0, 935),
    (8, 10.0, 569)
CREATE TABLE dbo.OutgoingSessionDelivery (
    SessionId int NOT NULL,
    CustomerId int NULL,
    DeliveryNumber int NULL
    )
INSERT INTO dbo.OutgoingSessionDelivery VALUES
    (122, 888, 12754)
CREATE TABLE dbo.Customer (
    Id int NOT NULL,
    CustomerName nvarchar(100) NOT NULL
    )
INSERT INTO dbo.Customer VALUES
    (888, N'Jo')    

SELECT 
    OD.OrderLineDescription,OD.Quantity, OD.SessionId,
    OL.LocationId, OL.Quantity,
    OSD.DeliveryNumber,
    C.CustomerName
FROM dbo.OutgoingDetails OD
LEFT OUTER JOIN dbo.OutgoingLocation OL on OD.Id = OL.OutgoingDetailsId
LEFT OUTER JOIN dbo.OutgoingSessionDelivery OSD on OD.SessionId = OSD.SessionId
LEFT OUTER JOIN dbo.Customer C on OSD.CustomerId = C.Id
WHERE OD.SessionId IN (103, 122)

Tom · October 30, 2019, 11:01am

Hi Scott,

wow thanks for the time you put in to this.
the problem is in the outgoinglocation, there should be 2 records with the same id 5.

This is the correct query:

CREATE TABLE dbo.OutgoingDetails (

Id int NOT NULL,

OrderLineDescription nvarchar(200) NULL,

Quantity decimal(19, 6) NULL,

SessionId int NULL

)

TRUNCATE TABLE dbo.OutgoingDetails;

INSERT INTO dbo.OutgoingDetails VALUES

(1, N'Nestmatjes', 30.0, 122),

(2, N'Pigo', 4.0, 122),

(3, N'BeyersPlus 600', 4.0, 122),

(4, N'Natural', 6.0, 122),

(5, N'BeyersPlus 450', 4.0, 122),

(7, N'DHPCultura', 3.0, 122),

(8, N'Drinkbak', 10.0, 122),

(9, N'Rohnfried', 1.0, 103)

CREATE TABLE dbo.OutgoingLocation (

OutgoingDetailsId int NOT NULL,

Quantity decimal(19, 6) NULL,

LocationId int NULL

)

INSERT INTO dbo.OutgoingLocation VALUES

(1, 30.0, 1888),

(2, 4.0, 1417),

(3, 4.0, 2),

(4, 6.0, 374),

(5, 3.0, 799),

(5, 1.0, 2),

(7, 3.0, 935),

(8, 10.0, 569)

CREATE TABLE dbo.OutgoingSessionDelivery (

SessionId int NOT NULL,

CustomerId int NULL,

DeliveryNumber int NULL

)

INSERT INTO dbo.OutgoingSessionDelivery VALUES

(122, 888, 12754)

CREATE TABLE dbo.Customer (

Id int NOT NULL,

CustomerName nvarchar(100) NOT NULL

)

INSERT INTO dbo.Customer VALUES

(888, N'Jo')

The result of the query (without any where clauses):
query-locs-3

The records that are of interest to me, and that I should have in the output are:

record 9: because there's no location record

all other records should be excluded because either od.qty = ol.qty, or (for id 5: the sum of 3 + 1 = od.qty

Thanks a bunch already!

harishgg1 · October 30, 2019, 12:58pm

PLEASE CLICK ARROW TO THE LEFT FOR sql SCRIPT

SELECT OD.orderlinedescription, 
       OD.quantity, 
       OD.sessionid, 
       OL.locationid, 

       OL.quantity, 
       OSD.deliverynumber, 
       C.customername 
FROM   dbo.outgoingdetails OD 
       LEFT OUTER JOIN dbo.outgoinglocation OL 
                    ON OD.id = OL.outgoingdetailsid 
       LEFT OUTER JOIN dbo.outgoingsessiondelivery OSD 
                    ON OD.sessionid = OSD.sessionid 
       LEFT OUTER JOIN dbo.customer C 
                    ON OSD.customerid = C.id 
WHERE  OD.sessionid IN ( 103, 122 ) 
       AND locationid IS NULL

Tom · October 30, 2019, 2:49pm

Hi Harish,

Thanks for collaborating with me ons this one.

Unfortunately, the query does not work in all circumstances. I modified the original query somewhat so you better understand:

CREATE TABLE dbo.OutgoingDetails (

Id int NOT NULL,

OrderLineDescription nvarchar(200) NULL,

Quantity decimal(19, 6) NULL,

SessionId int NULL

)
TRUNCATE TABLE dbo.OutgoingDetails;

INSERT INTO dbo.OutgoingDetails VALUES

(1, N'Nestmatjes', 30.0, 122),

(2, N'Pigo', 4.0, 122),

(3, N'BeyersPlus 600', 4.0, 122),

(4, N'Natural', 6.0, 122),

(5, N'BeyersPlus 450', 4.0, 122),

(7, N'DHPCultura', 3.0, 122),

(8, N'Drinkbak', 10.0, 122),

(9, N'Rohnfried', 1.0, 103)
CREATE TABLE dbo.OutgoingLocation (

OutgoingDetailsId int NOT NULL,

Quantity decimal(19, 6) NULL,

LocationId int NULL

)
INSERT INTO dbo.OutgoingLocation VALUES

(1, 27.0, 1888),

(2, 4.0, 1417),

(3, 4.0, 2),

(4, 6.0, 374),

(5, 3.0, 799),

(5, 1.0, 2),

(7, 3.0, 935),

(8, 10.0, 569)
CREATE TABLE dbo.OutgoingSessionDelivery (

SessionId int NOT NULL,

CustomerId int NULL,

DeliveryNumber int NULL

)
INSERT INTO dbo.OutgoingSessionDelivery VALUES

(122, 888, 12754)
CREATE TABLE dbo.Customer (

Id int NOT NULL,

CustomerName nvarchar(100) NOT NULL

)
INSERT INTO dbo.Customer VALUES

(888, N'Jo')

In the select, I added the column 'OL.outgoingdetailsid because that refers to the original orderline no.

As you can see from the output without the where clause, the returned set of records is this:

Now, in your suggestion, I would only see record 9.
But I also want record 1 (because 27 < 30) but....

I do not want records 5 and 6 in the output, because the totals of the righthandside quantity column is 4 which equals the quantity in the lefthandside quantity column. (there are two columns because the goods are picked from 2 locations, but this all comes from a single orderline (outgoingdetailsid: 5).

So it's way more complex I'm afraid...

ScottPletcher · October 30, 2019, 3:28pm

Good move making another quantity mismatch, I was going to suggest that. Try the query below. The OL.LocationId gets "lost" because of the GROUPing, but, if you really need that, let me know and we can work it back into the result.

SELECT 
    OD.OrderLineDescription,OD.Quantity, OD.SessionId,
    OL.Quantity,
    OSD.DeliveryNumber,
    C.CustomerName
FROM dbo.OutgoingDetails OD
LEFT OUTER JOIN (
    SELECT OutgoingDetailsId, SUM(Quantity) AS Quantity
    FROM dbo.OutgoingLocation
    GROUP BY OutgoingDetailsId
) AS OL on OD.Id = OL.OutgoingDetailsId
LEFT OUTER JOIN dbo.OutgoingSessionDelivery OSD on OD.SessionId = OSD.SessionId
LEFT OUTER JOIN dbo.Customer C on OSD.CustomerId = C.Id
WHERE OD.Quantity <> ISNULL(OL.Quantity, 0) AND
    OD.SessionId IN (103, 122)

Tom · November 5, 2019, 7:39pm

Hi Scott, I tested it your code and it works like a charm.

But this is not the end of it, I'm a bit afraid.

the purpose of this code was to run it via a web report generator, in this case Seal Report

While Seal Reports sql editor confirms the code is valid, I get this error when I try to save it:

"Error when building columns from the SQL select statement:
The column 'Quantity was specified multiple times for 'a'"

I'm not sure what triggers this error message, but I assume some ambiguity in the field name.

Would you have any suggestions on how to adapt the code?

Thanks,
Tom

jeffw8713 · November 5, 2019, 7:44pm

You have the column Quantity listed twice:

SELECT
OD.OrderLineDescription,OD.Quantity, OD.SessionId,
OL.Quantity,
OSD.DeliveryNumber,
C.CustomerName

These have to have different column names - so something like:

SELECT
OD.OrderLineDescription,ODQuantity = OD.Quantity, OD.SessionId,
OLQuantity = OL.Quantity,
OSD.DeliveryNumber,
C.CustomerName

Or - remove one of them...or give them some other names that make more sense.

ScottPletcher · November 5, 2019, 7:46pm

First, let's just change the name in the inner query so we always have unique column names:

SELECT 
    OD.OrderLineDescription,OD.Quantity, OD.SessionId,
    Total_Quantity,
    OSD.DeliveryNumber,
    C.CustomerName
FROM dbo.OutgoingDetails OD
LEFT OUTER JOIN (
    SELECT OutgoingDetailsId, SUM(Quantity) AS Total_Quantity --<--
    FROM dbo.OutgoingLocation
    GROUP BY OutgoingDetailsId
) AS OL on OD.Id = OL.OutgoingDetailsId
LEFT OUTER JOIN dbo.OutgoingSessionDelivery OSD on OD.SessionId = OSD.SessionId
LEFT OUTER JOIN dbo.Customer C on OSD.CustomerId = C.Id
WHERE OD.Quantity <> ISNULL(OL.Quantity, 0) AND
    OD.SessionId IN (103, 122)

Tom · November 13, 2019, 4:06pm

Hello @ScottPletcher @jeffw8713

Here I am again. I've been evaluating your answers, and while they appeared correct at first sight, I felt something was wrong when looking at the results I got (for a small fraction of the orders anyway, most were fine).

I found the problem, and it's actually a bit of extra complexity that I didn't know of until now:

the table OutgoingLocation contains a list of records (locations, and quantities) that the orderpicker must go through to complete an order. But these records are merely suggestions (using an ideal picking route through the warehouse). (Initially I thought these were the actual picked quantities)

But the actual pickings are stored in the tabel OutgoingSerial. (because we can actually also have different serial numbers per qty picked in 1 location).

So the real picked quantity is stored in OutgoingSerial, not in OutgoingLocation.
This means that not the sum of the quantity in the outgoinglocation table is important, but the sum of the quantity in the outgoingserial table (OutgoingSerial.Quantity). This should be equal to the OD.quantity

the join between the outgoingserial and outgoinglocation table is via outgoinglocation.id = outgoingserial.outgoinglocationid

I modified the original sql query. The outgoingserial table now contains 2 records (id's 5 and 6) of which the sum is 4 which is equal to the OD.quantity. (if the sum would be 3, then we were one item short, and it should be in the output of the select query.

I hope you guys are not bored of this yet, and can help me in modifying the select query?

CREATE TABLE dbo.OutgoingDetails (

Id int NOT NULL,

OrderLineDescription nvarchar(200) NULL,

Quantity decimal(19, 6) NULL,

SessionId int NULL

)
TRUNCATE TABLE dbo.OutgoingDetails;

INSERT INTO dbo.OutgoingDetails VALUES

(1, N'Nestmatjes', 30.0, 122),

(2, N'Pigo', 4.0, 122),

(3, N'BeyersPlus 600', 4.0, 122),

(4, N'Natural', 6.0, 122),

(5, N'BeyersPlus 450', 4.0, 122),

(7, N'DHPCultura', 3.0, 122),

(8, N'Drinkbak', 10.0, 122),

(9, N'Rohnfried', 1.0, 103)
CREATE TABLE dbo.OutgoingLocation (

Id int NOT NULL,
OutgoingDetailsId int NOT NULL,

Quantity decimal(19, 6) NULL,

LocationId int NULL

)
INSERT INTO dbo.OutgoingLocation VALUES

(1,1, 27.0, 1888),

(2,2, 4.0, 1417),

(3,3, 4.0, 2),

(4,4, 6.0, 374),

(5,5, 3.0, 799),

(6,5, 1.0, 2),

(7,7, 3.0, 935),

(8,8, 10.0, 569)
CREATE TABLE dbo.OutgoingSessionDelivery (

SessionId int NOT NULL,

CustomerId int NULL,

DeliveryNumber int NULL

)
INSERT INTO dbo.OutgoingSessionDelivery VALUES

(122, 888, 12754)
CREATE TABLE dbo.Customer (

Id int NOT NULL,

CustomerName nvarchar(100) NOT NULL

)
INSERT INTO dbo.Customer VALUES

(888, N'Jo')

CREATE TABLE dbo.OutgoingSerial (

id int NOT NULL,

Quantity decimal(18, 6) NULL,

OutgoingLocationId int NULL

)

INSERT INTO dbo.OutgoingSerial VALUES

(1, 27.0, 1),

(2, 4.0, 2),

(3, 4.0, 3),

(4, 6.0, 4),

(5, 3.0, 5),

(5, 1.0, 6),

(7, 3.0, 7),

(8, 10.0, 8)