CODEWITHSUNDEEP
sqlsql-server
Daryll C
Daryll C

Reputation: 11

SQL split one column into two columns based on values and use columns

Table: ProductionOrder

Id     Ordernumber     Lotsize
1        Order1            50
2        Order 2           75
3        WO-order1         1
4        WO-order2         1

Table: history

Id          ProductionOrderID          Completed  
1            3                         1
2            3                         1
3            4                         1
4            4                         1

Table: ProductionOrderDetail

ID   ProductionOrderID     ProductionOrderDetailDefID   Content
1     1                    16                            50
2     1                    17                            7-1-2018
3     2                    16                            75
4     2                    17                            7-6-2018

Start of my code: 

 Select p.ID, p.OrderNumber, 
     Case productionOrderDetailDefID
         Where(Select pd1.productionOrderDetailDefID where ProductionOrderDetialDefID = 16) then min(pd1.content)
 from ProductionOrder p
     Left join History h1 on  p.id = h1.productionOrderID
     Left Join ProductionOrderDetail pd1 on p.ID = ProductionOrderID

The result in trying to get is

Id      Ordernumber     Lotsize   Productionorder        Completed
1       Order1            50           WO-order1           2
2       Order 2           75           WO-order2           2

Any help would be appreciated.

Upvotes: 1

Views: 424

Answers (2)

Pramit Sawant
Pramit Sawant

Reputation: 811

Try this

SELECT ordernumber,lotsize,Ordernumber,count(Ordernumberid) 
       FROM productionorder inner join history on productionorder.id = history.Ordernumberid 
       GROUP BY Ordernumber;

Upvotes: 2

Edward
Edward

Reputation: 762

A bit of weird joins going on here. You should add this to a SQL fiddle so that we can see our work easier.

A link to SQL fiddle: http://sqlfiddle.com/

Here is my first attempt 

SELECT
        po.id
    ,   po.ordernumber
    ,   po.lotsize 
    ,   po2.productionorder 
    ,   SUM(h.completed) 
FROM productionorder as po 
INNER JOIN history as h 
    ON h.id = po.id 
INNER JOIN prodcuctionorder as po2 
    ON po2.ordernumberid = h.ordernumberid 
WHERE po.id NOT EXISTS IN ( SELECT ordernumberid FROM history ) 
GROUP BY 
        po.id 
    ,   po.ordernumber 
    ,   po.lotzise 
    ,   po2.productionorder

How far does that get you?

Upvotes: 1

Related Questions