时间戳

首页 > 解决方案 > 与 SQL 中的最新日期相加

sql - 与 SQL 中的最新日期相加

问题描述

下面是我的 SQL 查询

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_]

数据是这样来的

+----------+-----------------+--------------------+--------------+--------------------------+
| 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 |
+----------+-----------------+--------------------+--------------+--------------------------+

如何修改我的代码以实现如下数据？

+----------+-----------------+--------------------+--------------+----------+
| 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 |
+----------+-----------------+--------------------+--------------+----------+

标签： sqlsql-server

解决方案

[Item No_]我认为，无论何时按&分组，您都在寻找消除的零数量[Lot No_]

Select [Item No_],
       max([Expiration Date]) as [Expiration Date],
       max([Manufacturing Date]) as [Manufacturing Date],
       [Lot No_], 
       Sum([Quantity]) as Quantity 
  from [Warehouse Entry] 
 Group by [Item No_],[Lot No_]
 Having Sum([Quantity]) > 0;

Item No_    Expiration Date Manufacturing Date  Lot No_       Quantity
BRN00019    01-07-20        01-07-19            A19F14 X 252  133
BRN00019    01-07-20        01-07-19            A19F14 X 48   1

Demo

推荐阅读