CODEWITHSUNDEEP
sqlsql-server
Alex K
Alex K

Reputation: 17

How do I join 2 tables and calculate 1 new column

I'm need to get 3 new columns with my SQL query. The first 2 columns only requires 1 table (Shipment_Info), but the 3rd column requires the 2nd table (Item_Info).

  1. 1st new column = Shipment_Size (# of itemsID in that shipment)
  2. 2nd new column = Shipment_ready (entire shipmentID is ready to be shipped. For an shipmentID to be ready to be shipped all the ItemIDs must be in a "Packed" status)
  3. 3rd new column = Item_Stored (was this item stored atleast 1 time). If it was stored atleast 1 time value should be yes and if the item was never stored atleast 1 time the value should be no.

Shipment_Info_table:

Shipment_Info has three columns ShipmentID and ItemID and Item_Status. ItemID values are always unique, but ShipmentIDs will repeat because different Items can be in the same shipment, and item status can be in different states (Allocated, Filled and Packed).

Item_Info table: Item_Info has two columns. ItemID, Operation, Op_time. ItemIDs will repeat because this table shows the different operations that have occurred to this itemID and the associated times.

DB Table: Shipment_INFO

ShipmentID ItemID Item_status
10001 20001 Packed
10002 20002 Allocated
10002 20003 Packed
10003 20004 Filled
10004 20005 Packed
10004 20006 Packed
10004 20007 Packed
10005 20008 Filled
10005 20009 Packed
10006 20010 Filled

DB Table: Item_Info

ItemID Item_status
20001 Induct
20001 Stock
20002 Induct
20002 Stock
20002 Stored
20002 Dock
20003 Induct
20003 Stock
20003 Stored
20004 Induct
20004 Cancelled
20004 Stored
20005 Induct
20005 Stock
20005 Stored
20006 Induct
20006 Reject
20006 Induct
20006 Stock
20007 Induct
20007 Stock
20007 Stored
20007 Stored
20008 Induct
20008 Stock
20008 Reject
20009 Induct
20009 Stock
20009 Induct
20009 Stored
20010 Induct
20010 Stock

Ideal Output:

ShipmentID ItemID Shipment_Size Shipment_Ready Item_Stored
10001 20001 1 Yes No
10002 20002 2 No Yes
10002 20003 2 No Yes
10003 20004 1 No Yes
10004 20005 3 Yes Yes
10004 20006 3 Yes No
10004 20007 3 Yes Yes
10005 20008 2 No No
10005 20009 2 No Yes
10006 20010 1 No Yes

The code below gets me the first two columns, but I'm having trouble on how to join 2nd table and get the 3 column. Any help would be greatly appreciated.

 select ShipmentID,ItemID,
           count(ItemID) over (partition by ShipmentID) Shipment_Size,
          case when 
      sum(case when Item_status='Packed' then 1 else 0 end) OVER (partition by ShipmentID ) =count(ItemID) over (partition by ShipmentID)
                   then 'Yes' else 'no' end as Shipment_Ready
      from Shipment_INFO
    group by ShipmentID,Item_status,ItemID

Upvotes: 0

Views: 72

Answers (1)

Dale K
Dale K

Reputation: 27202

I think you just want a simple case expression using exists:

select ShipmentID, ItemID
    , count(ItemID) over (partition by ShipmentID) Shipment_Size
    , case when
        sum(case when Item_status='Packed' then 1 else 0 end) over (partition by ShipmentID ) = count(ItemID) over (partition by ShipmentID)
        then 'Yes' else 'No' end as Shipment_Ready
    , case when exists (select 1 from Item_Info ii where ii.ItemId = si.ItemId and ii.Item_Status = 'Stored') then 'Yes' else 'No' end as Item_Stored
from Shipment_INFO si
group by ShipmentID, Item_status, ItemID;

Upvotes: 1

Related Questions