Daily sum of price calculation

I would like to calculate the daily total price of the shopNo if the status is 1. I retrieve the daily total price of shopNo (eg.111111), in my application, I will check the total with the limit (eg. 500). If it is higher than the limit, I will warn it.

Here is the sample data.

GameRequest

ReferenceId                              RequestDateTime           ShopNo
--------------------------------------  ----------------           -------
ED35BA0C-DBE1-4C57-A865-78BDFED686D1    2020-03-17 15:48:32.563    111111
BD11BA0C-D111-2C57-A125-12BDFED006C2    2020-03-17 13:55:32.000    111112
AD46BA1D-AA22-3BBD-B444-32ABCDE686E3    2020-03-17 12:46:32.550    111112
BB22BA7C-ABC1-2C77-F169-99DDEED323A2    2020-03-17 11:40:03.569    111111
CD99BA0F-DBE1-4C57-A865-78BDFED686A1    2020-03-16 11:00:21.562    111112
EE35BA3B-BBB3-2C12-A865-11BAAAD006A0    2020-03-16 10:11:22.000    111111

Confirm

ReferenceId                              ConfirmDateTime           ShopNo       Status
--------------------------------------  ----------------           -------      ------
ED35BA0C-DBE1-4C57-A865-78BDFED686D1    2020-03-17 15:48:32.563    111111         1
BD11BA0C-D111-2C57-A125-12BDFED006C2    2020-03-17 13:55:32.000    111112         1
AD46BA1D-AA22-3BBD-B444-32ABCDE686E3    2020-03-17 12:46:32.550    111112         2
BB22BA7C-ABC1-2C77-F169-99DDEED323A2    2020-03-17 11:40:03.569    111111         1
CD99BA0F-DBE1-4C57-A865-78BDFED686A1    2020-03-16 11:00:21.562    111112         2
EE35BA3B-BBB3-2C12-A865-11BAAAD006A0    2020-03-16 10:11:22.000    111111         1

ConfirmRequest

ReferenceId                              RequestDateTime            Price
--------------------------------------  ----------------           -------     
ED35BA0C-DBE1-4C57-A865-78BDFED686D1    2020-03-17 15:48:32.563    5       
BD11BA0C-D111-2C57-A125-12BDFED006C2    2020-03-17 13:55:32.000    5       
AD46BA1D-AA22-3BBD-B444-32ABCDE686E3    2020-03-17 12:46:32.550    10       
BB22BA7C-ABC1-2C77-F169-99DDEED323A2    2020-03-17 11:40:03.569    5       
CD99BA0F-DBE1-4C57-A865-78BDFED686A1    2020-03-16 11:00:21.562    10       
EE35BA3B-BBB3-2C12-A865-11BAAAD006A0    2020-03-16 10:11:22.000    5

Answer

If I understand correctly, you want to join the tables together and aggregate. I’m not sure which column you want for the date, but something like this:

select c.shopno,
       convert(date, cr.RequestDateTime) as date,
       sum(cr.price) as total_daily_price
from confirm c join
     confirmrequest cr
     on cr.ReferenceId = c.ReferenceId
where c.status = 1
group by c.shopno,
         convert(date, cr.RequestDateTime) ;

Advertisement

Answer