SQLTeam.com | Weblogs | Forums

Daily sum of price

Hey there,

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.

There might be more than one referenceId for a specific shopNo in the Confirm table so I need to get the first one in terms of datetime for the specific shopNo.

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

based on the data, what are you expecting as a result?

DDL:

Create table #GameRequest (ReferenceID uniqueIdentifier,
RequestDateTime datetime2(7),
ShopNo int)

Create table #Confirm (ReferenceID uniqueIdentifier,
ConfirmDateTime datetime2(7),
ShopNo int,
Status int)

Create table #ConfirmRequest (ReferenceID uniqueIdentifier,
RequestDateTime datetime2(7),
Price int)
Insert into #GameRequest values
('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)

insert into #Confirm values
('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)

insert into #ConfirmRequest Values
('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)

Select gr.*, cr.Price
from #GameRequest gr
join #Confirm c
on gr.ReferenceID = c.ReferenceID
and c.Status = 1
join #ConfirmRequest cr
on gr.ReferenceID = cr.ReferenceID

Does your answer cover this? And also the daily total?

based on the data, what are you expecting as a result?

Expecting to retrieve daily total price of shopNo if it's status is 1 and the earliest confirm date time for the shopNo because there might be more than one.

so, given the data, is this right?

|ShopNo|ConfirmDateTime|Price|
|111111|3/16/2020 10:11:22|5|
|111112|3/17/2020 13:55:32|5|

what do you mean is this right? Are talking about sample data? Are talking about the result? What?

I asked you for expected results based on the data and DDL you didn't provide. Not written in words, but actually representative of what you are expecting. I took a guess at what you are expecting, but I don't know if that is what you are expecting or not. We are volunteers here, so we can't spend a lot of time trying to figure out what you are asking. I would suggest starting with DDL and sample data above. provide a table of EXACTLY what you are expecting as a result.

Here is the expected result according to my sample data, but as I mentioned there may be more than one same referenceId for the shopNo. Then query must get the earliest record.

2020-03-16 111111 5
2020-03-17 111111 10
2020-03-17 111112 5

I don't know why you are expecting 10 to come back as a price. Both records with 10 have a status of 2, you want only those records with status of 1.

Select gr.*, cr.Price, c.Status
from #GameRequest gr
join #Confirm c
on gr.ReferenceID = c.ReferenceID
join #ConfirmRequest cr
on gr.ReferenceID = cr.ReferenceID

Hi

Something I tried
Something i am missing from what Cenk's Output is !!!

Maybe I need to dig deeper
Or
Cenk needs to explain

Anyhow here it is

;WITH cte AS 
(
SELECT
     [gr].[RequestDateTime]
    , [gr].[ShopNo]
    , [cr].[Price]
	, ROW_NUMBER() OVER(PARTITION BY [gr].shopno,CAST([cr].[RequestDateTime] AS DATE)  ORDER BY [gr].[ReferenceID]) AS RN 
FROM
    [#GameRequest] AS [gr]
    JOIN [#Confirm] AS [c]
        ON [gr].[ReferenceID] = [c].[ReferenceID]
    JOIN [#ConfirmRequest] AS [cr]
        ON [gr].[ReferenceID] = [cr].[ReferenceID]
) 
SELECT * FROM cte WHERE rn =1 

image

May be something like this but if there are same referenceId's in the Confirm table, I have to get the earliest one.

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

hi

Your table names are different than what I used
Which table is Confirm Table ??

[#GameRequest] 
[#Confirm]
[#ConfirmRequest]

Confirm -> ConfirmCancels
ConfirmRequest ->GameConfirmResponses

The Reference ID's I saw in Confirm Table
Confirm -> ConfirmCancels
Are All
Different !!

Maybe we need better Sample Data .. :slight_smile:

SELECT
    [C].[ReferenceID]
    , COUNT(*)
FROM
    [#Confirm] AS [C]
GROUP BY
    [C].[ReferenceID];

image

I know that :slight_smile: I will add some same refereceId's and let you know.

Here is updated data @harishgg1

ConfirmCancels

ReferenceId                              ConfirmDateTime           ShopNo       Status
--------------------------------------  ----------------           -------      ------
ED35BA0C-DBE1-4C57-A865-78BDFED686D1    2020-03-17 15:48:32.563    111111         1
ED35BA0C-DBE1-4C57-A865-78BDFED686D1    2020-03-17 15:30:32.563    111111         1
BD11BA0C-D111-2C57-A125-12BDFED006C2    2020-03-17 13:55:32.000    111112         1
BD11BA0C-D111-2C57-A125-12BDFED006C2    2020-03-17 13:56: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

@harishgg1did you see the updated data?

Hi Cenk

Sorry
Very Busy with Office Work

Will Take a Look at it Later

:slight_smile:

still going to office :flushed: