Skip to content
Advertisement

Sum with latest date in SQL

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 ) 
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement