Below is my SQL query
Select [Item No_][Expiration Date],[Manufacturing Date] ,([Lot No_] ), Sum(Quantity)Quantity from [Warehouse Entry] Group by [Lot No_],[Expiration Date],[Manufacturing Date],[Item No_]
Data is coming like this
+----------+-----------------+--------------------+--------------+--------------------------+ | Item No_ | Expiration Date | Manufacturing Date | Lot No_ | Quantity | +----------+-----------------+--------------------+--------------+--------------------------+ | BRN00171 | 2020-05-01 | 2019-05-01 | A08E03 X 5 | 7.00000000000000000000 | | BRN00171 | 2020-05-01 | 2019-05-01 | BLRDUM42 X 1 | 1.00000000000000000000 | | BRN00171 | NULL | NULL | JULY10 | -69.00000000000000000000 | | BRN00171 | 2011-04-01 | NULL | JULY10 | 69.00000000000000000000 | | BRN00171 | 2020-07-01 | 2019-07-01 | A16F15 X 24 | 24.00000000000000000000 | | BRN00171 | 2020-01-01 | 2019-01-01 | FFWW | 29.00000000000000000000 | +----------+-----------------+--------------------+--------------+--------------------------+
How can i modify my code to achieve data like below?
+----------+-----------------+--------------------+--------------+----------+ | Item No_ | Expiration Date | Manufacturing Date | Lot No_ | Quantity | +----------+-----------------+--------------------+--------------+----------+ | BRN00171 | 01-05-20 | 01-05-19 | A08E03 X 5 | 7 | | BRN00171 | 01-05-20 | 01-05-19 | BLRDUM42 X 1 | 1 | | BRN00171 | 01-07-20 | 01-07-19 | A16F15 X 24 | 24 | | BRN00171 | 01-01-20 | 01-01-19 | FFWW | 29 | +----------+-----------------+--------------------+--------------+----------+
Advertisement
Answer
Please check that, the below answer remove the third and fourth column.
with [Warehouse Entry]( [Item No_], [Expiration Date], [Manufacturing Date], [Lot No_], [Quantity] ) as ( select 'BRN00019','2020-05-01','2019-05-01','A19F14 X 252',133 union all select 'BRN00019','2020-05-01','2019-05-01','Jun-11',-12 union all select 'BRN00019',null,null,'Jun-11',7 union all select 'BRN00019','2011-04-01',null,'Jun-11',-7 union all select 'BRN00019','2020-07-01','2019-07-01','Jun-11',12 union all select 'BRN00019','2020-07-01','2019-07-01','A19F14 X 48',1 ) select [Item No_], [Expiration Date], [Manufacturing Date], [Lot No_], [Quantity] from [Warehouse Entry] where [Expiration Date] in (select [Expiration Date] from [Warehouse Entry] where [Expiration Date] is not null ) and [Manufacturing Date] in (select [Manufacturing Date] from [Warehouse Entry] where [Manufacturing Date] is not null )