SQL Server Get MAX(LastUploadDate) IF BatchUpload IS NULL

7z5jn7bk 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(95)

Every time we upload an invoice I need to run INSERT INTO tblVersapayInvoice :
| CustomerID | LastUploadDate | BillNum | BatchUpload |
| ------------ | ------------ | ------------ | ------------ |
| 1433 | 2023-09-25 09:21:32.010 | 229985 | NULL |
| 1433 | 2023-09-26 09:21:32.010 | 229985 | NULL |
| 1433 | 2023-09-27 09:21:32.010 | 229985 | NULL |
| 1433 | NULL | 229985 | Y |
| 1433 | 2023-09-27 09:21:32.010 | 240022 | NULL |
| 1447 | 2023-09-22 09:21:32.010 | 230071 | NULL |
| 1447 | 2023-09-25 09:21:32.010 | 230071 | NULL |

As you can see I have uploaded Bill_Num 229985 and 230071 multiple times and 240022 only once. I want the MAX(LastUploadDate) if the BatchUpload IS NULL, otherwise I want ‘Batch’ in the result set.

This is my desired result (I tried to show it in a table but it didn't work):

Bill_NUM 240022 2023-09-27 09:21:32.010
Bill_NUM 229985 Batch
Bill_NUM 230071 2023-09-25 09:21:32.010

Here is my query but it gives me a row for the BatchUpload = Y and a row for the Max(LastUploadDate). I only want 1 row for each Bill_Num.

SELECT 
    CASE 
        WHEN BatchUpload IS NOT NULL THEN 'Batch' 
        ELSE CONVERT(varchar, MAX(LastUploadDate), 1) 
    END AS LastUploadDate, 
    Bill_Num 
FROM 
    tblVersapayInvoice 
GROUP BY 
    Bill_Num, BatchUpload

sql-server

来源：https://stackoverflow.com/questions/77225841/get-maxlastuploaddate-if-batchupload-is-null

1条答案

按热度按时间

cqoc49vn1#

If you only want one row per Bill_Num , then BatchUpload shouldn't be in the GROUP BY . Maybe you meant:

SELECT Bill_Num, CASE MAX(BatchUpload) 
    WHEN 'Y' THEN 'Batch' 
    ELSE CONVERT(varchar(30), MAX(LastUploadDate), 120) 
  END AS LastUploadDate
FROM dbo.tblVersapayInvoice  
GROUP BY Bill_Num;

Example db<>fiddle

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Get MAX(LastUploadDate) IF BatchUpload IS NULL

1条答案

相关问题

热门标签

最新问答