Query with Multiple Values

Question

I have written a query in such a way that, I will get multiple values in single row.

This is the result I am getting now:

G.R.N Number    Item No. Location Cartons   BATCH REF NO
122             CBD0002    a1       184    07-04; 07-05; 07-06; 07-08; 07-09
122             CBD0002    a2        16    07-04; 07-05; 07-06; 07-08; 07-09
125         CBD0001    a1        500    200018

But I actually need an output like this:

G.R.N Number    Item No. Location Cartons   BATCH REF NO
122             CBD0002    a1       184    07-04; 07-05; 07-06; 07-08; 07-09
122             CBD0002    a2        16    07-09
125             CBD0001    a1        500   200018

If you compare the both tables, In the location column, The location is different for the same item, Son in the Batch Field it should display only one values.

But in my current query, it's displaying all the information which is misleading & not correct.

Have included my coding. For Reference please find the table structure from which I am picking up the information.

OIBT.BaseNum    OIBT.Item No.   OIBT.SuppSerial OIBT.BatchNum   OBTQ.Quantity
125        CBD0001                a1       200018   500
122        CBD0002                A1        07-01   0
122        CBD0002                A1        07-02   0
122        CBD0002                A1        07-03   0
122        CBD0002                a1        07-04   48
122        CBD0002                a1        07-05   48
122        CBD0002                a1        07-06   48
122        CBD0002                a1        07-07   0
122        CBD0002                a1        07-08   40
122        CBD0002                a2        07-09   16

SELECT T0.BaseNum AS 'G.R.N Number',

T0.IntrSerial as 'G.R.N Type',

t0.ItemCode,
T0.ItemName AS 'Item Name',

t0.SuppSerial as 'Location',

sum(t1.Quantity) as 'Cartons',

count( t0.BatchNum) as 'Pallets',

[BATCH REF NO]  = STUFF((SELECT DISTINCT '; ' + US.BatchNum
     FROM OIBT US WHERE US.Basenum = T0.BaseNum AND US.WhsCode = T0.WhsCode AND US.ItemCode = t0.ItemCode and us.Quantity <> 0 FOR XML PATH('')), 1, 1, '')


 FROM OIBT T0 INNER JOIN OBTQ T1 ON T0.ItemCode = T1.ItemCode AND T0.WhsCode = T1.WhsCode AND T0.SysNumber = T1.SysNumber

  WHERE T1.WhsCode = 'SPARE' and t0.Quantity <> 0

  group by T0.BaseNum,T0.itemName,t0.IntrSerial,t0.ItemCode,t0.SuppSerial,t0.WhsCode

Actual GRN Number is :G.R.N Number 122 122 125

DeePak
– DeePak

2019-07-24 07:08:22 +00:00
Commented Jul 24, 2019 at 7:08 — DeePak
– DeePak, Commented Jul 24, 2019 at 7:08
i have edited the question now...Kindly check

DeePak
– DeePak

2019-07-24 07:10:33 +00:00
Commented Jul 24, 2019 at 7:10 — DeePak
– DeePak, Commented Jul 24, 2019 at 7:10

Squirrel · Accepted Answer · 2019-07-24 07:09:01Z

2

change the BTACH REF NO to include SuppSerial in the WHERE clause

[BATCH REF NO]  = STUFF(( SELECT DISTINCT '; ' + US.BatchNum
                          FROM   OIBT US 
                          WHERE  US.Basenum  = T0.BaseNum 
                          AND    US.WhsCode  = T0.WhsCode 
                          AND    US.ItemCode = T0.ItemCode 
                          AND    US.SuppSerial = T0.SuppSerial
                          and    US.Quantity <> 0 
                          FOR XML PATH('')), 1, 1, '')

answered Jul 24, 2019 at 7:09

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query with Multiple Values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related