CODEWITHSUNDEEP
sql
kuy
kuy

Reputation: 15

How can i compare 2 items in sql that are in the same column in the same table?

i have to produce a list with the following content:

For Example we have got an order with 3 Positions of the same Product, the same Quantity, etc The only difference is the desired date of shipment of the customer. e.g the first position should be delievered on the first of january. The 2nd Position should be delievered on the first of April, and the third Position on the first of July.

Furthermore we can set a checkmark in our System that the customer cant split his Orders for various Reasons.

So i need to find out which Orders have the checkmark set to "NO SPLIT ORDER-SHIPMENTS" and still have gotten different shipment-dates for the positions.

I'm atm completely clueless how to tackle that Problem.

For Example Table A contains:

ordernumber|desired-date|orderposition|productid|quantitiy
123456789  | 01-01-2022 |     10      | 0815    |    100
123456798  | 01-04-2022 |     20      | 0815    |    100
123456789  | 01-07-2022 |     30      | 0815    |    100
123456789  | 04-02-2022 |     10      | 5152    |    66

In our System we have set an option that the sutomer of this order can no get split shipments. So we have an issue here. The order containst three different shipment-dates but the system wont allow that.

How can i find exactly those rows in that Table that have this Problem. I dont want to see row Number 4 of Table A only the first 3.

Upvotes: 0

Views: 142

Answers (1)

user18098820
user18098820

Reputation:

The following query will find the orders with different delivery dates for the same product with the same order id for the same customer.
The column names will need to be replaced with the column names in your database and may have to join 2 or more tables in the query to get all the information.

create table tableA (ordernumber int, desired_date date, orderposition int, productid int, quantitiy int);
insert into tableA values
(123456789  , '2022-01-01' ,     10      , 0815    ,    100),
(123456798  , '2022-04-01' ,     20      , 0815    ,    100),
(123456789  , '2022-07-01' ,     30      , 0815    ,    100),
(123456789  , '2022-02-04' ,     10      , 5152    ,    66 );

select
  count(distinct desired_date) number_lines, 
  ordernumber,
  productid
from tableA
group by
  ordernumber,
  productid
having
  count(distinct desired_date) > 1
/*and
   checkmark = "NO SPLIT ORDER-SHIPMENTS";*/

number_lines | ordernumber | productid
-----------: | ----------: | --------:
           2 |   123456789 |       815

db<>fiddle here

Upvotes: 1

Related Questions