I have the following table
| Report Number | Department | Date | Price |
|---|---|---|---|
| 77e1117e-a248-4781-866f-704bea114d11 | Dep. 01 | 2022-06-13 10:12:42.000 | 685.10 |
| 77e1117e-a248-4781-866f-704bea114d11 | Dep. 01 | 2022-06-13 10:12:42.000 | 0.19 |
| a34f8425-c64f-47a9-b947-49e7d2fd5bba | Dep. 01 | 2022-06-13 13:16:45.000 | 102.45 |
| a34f8425-c64f-47a9-b947-49e7d2fd5bba | Dep. 01 | 2022-06-13 13:16:45.000 | 427.13 |
| h94e8421-h79j-25q9-n478-58w7f2af6ffe | Dep.02 | 2022-06-13 13:16:45.000 | 98.98 |
| h94e8421-h79j-25q9-n478-58w7f2af6ffe | Dep. 02 | 2022-06-13 13:16:45.000 | 500.50 |
These are two reports, each with two entries, generated at a different time and yielding a different price.
I would like to have a script/code that can automatically choose the latest date and return the sum of prices corresponding to that report number per department.
The output should be something like that:
| Report Number | Department | Date | Price |
|---|---|---|---|
| a34f8425-c64f-47a9-b947-49e7d2fd5bba | Dep. 01 | 2022-06-13 13:16:45.000 | 529.58 |
| h94e8421-h79j-25q9-n478-58w7f2af6ffe | Dep. 02 | 2022-06-13 13:16:45.000 | 599.48 |
CodePudding user response：
Select ReviewNr, [Date], Sum(Price)
from myTable
where ReviewNr in (select top(1) reviewNr from myTable order by [Date] desc)
group by ReviewNr, [Date];
CodePudding user response：
Something like this:
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ProductId ORDER BY Date DESC ) AS RowNumber,
SUM(Price) OVER (PARTITION BY ProductId ) PriceSum
FROM WhateverTableItIs
) T
WHERE RowNumber = 1
(Why have you posted HTML when your question is about SQL?!)