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)
insert into #Confirm
select 'ED35BA0C-DBE1-4C57-A865-78BDFED686D1', '2020-03-17 15:48:32.563', 111111, 1 union
select 'ED35BA0C-DBE1-4C57-A865-78BDFED686D1', '2020-03-17 15:30:32.563', 111111, 1 union
select 'BD11BA0C-D111-2C57-A125-12BDFED006C2', '2020-03-17 13:55:32.000', 111112, 1 union
select 'BD11BA0C-D111-2C57-A125-12BDFED006C2', '2020-03-17 13:56:32.000', 111112, 1 union
select 'AD46BA1D-AA22-3BBD-B444-32ABCDE686E3', '2020-03-17 12:46:32.550', 111112, 2 union
select 'BB22BA7C-ABC1-2C77-F169-99DDEED323A2', '2020-03-17 11:40:03.569', 111111, 1 union
select 'CD99BA0F-DBE1-4C57-A865-78BDFED686A1', '2020-03-16 11:00:21.562', 111112, 2 union
select 'EE35BA3B-BBB3-2C12-A865-11BAAAD006A0', '2020-03-16 10:11:22.000', 111111, 1
--daily total price of shopNo if it's status is 1 and the earliest confirm date time for the shopNo
;with dailyTotals
as
(
Select sum(cr.Price) _DailyTotal, c.ShopNo, cast(c.ConfirmDateTime as date) as _date
from #GameRequest gr
join #Confirm c
on gr.ReferenceID = c.ReferenceID
and c.Status = 1
join #ConfirmRequest cr
on gr.ReferenceID = cr.ReferenceID
group by c.ShopNo, cast(c.ConfirmDateTime as date)
)
select dt._DailyTotal, ConfirmDateTime, dt.ShopNo
From dailyTotals dt
join (
select ROW_NUMBER() OVER (
PARTITION BY ShopNo
ORDER BY ConfirmDateTime desc
) row_num,
ShopNo,
ConfirmDateTime
from #Confirm
) a on dt.ShopNo = a.ShopNo
where row_num = 1
Thank you @yosiasz, is there a way to get the totals of current date?
your requirements keep changing with every post. and they are not very clear. what is current date, I don't see any column named current date. and which date column would that apply on and on which table?
Confirm table,
ConfirmDateTime = CONVERT(DATE, GETDATE())
and what happens when you do that? Does it work?
didn't figure it out where to put on your query 
you can add it
from #GameRequest gr
join #Confirm c
on gr.ReferenceID = c.ReferenceID
and c.Status = 1
join #ConfirmRequest cr
on gr.ReferenceID = cr.ReferenceID
-->> add it here
where CONVERT(DATE,ConfirmDateTime) = CONVERT(DATE, GETDATE())
Why do you have those datetime columns as date time? Do you really need the time part?
Thank you @yosiasz, yes I need time part as well.
what if I change the where clause and add shopNo condition, how should I change your query?
where CONVERT(DATE,ConfirmCancelDateTime) = CONVERT(DATE, GETDATE()) and c.shopNo = '10908878'
just like you have it there.
I mean I don't want shopNo in the result, just in the where condition.
like I said your requirements change with every post. Just remove it from where you dont want it and see what happens.
Make it work like this but this is NOT getting the earliest confirm cancel date time right?
Select sum(cr.unitPrice) _DailyTotal, cast(c.ConfirmCancelDateTime as date) as _date
from GameRequests gr
join ConfirmCancels c
on gr.ReferenceID = c.ReferenceID
and c.Status = 1
join GameConfirmResponses cr
on gr.ReferenceID = cr.ReferenceID
where CONVERT(DATE,ConfirmCancelDateTime) = CONVERT(DATE, GETDATE()) and c.shopNo = '10908878'
group by cast(c.ConfirmCancelDateTime as date)
you tell me. are you asking me?
yes I am asking you @yosiasz Your query I am trying to modify
show us the result you want to see. cant understand what you want from the original question your posted: Daily sum of price
I want today's total price for a shopNo (where clause condition: c.shopNo = '10908878') and of course getting the earliest confirm date-time in case there might be more than one same referenceId from the Confirm table. Now is it clear?
| _DailyTotal | _date |
|---|---|
| 22 | 2020-03-22 |
hi Cenk
This going back and forth is a waste of time .. !!!!
One Option .. that I have done is
for three people i have remote desk topped
into their systems ... saw .. and did the solution .. via remote
Maybe somebody can remote into your system !!
and solve it very very very quickly !!!
Hope his helps
or at least have a live discussion .. with you and your database
Can you please modify your query by removing the shopNO after the where clause? It shouldn't be too hard since you wrote
;with dailyTotals
as
(
Select sum(cr.unitPrice) _DailyTotal, cast(c.ConfirmCancelDateTime as date) as _date
from GameRequests gr
join ConfirmCancels c
on gr.ReferenceID = c.ReferenceID
and c.Status = 1
join GameConfirmResponses cr
on gr.ReferenceID = cr.ReferenceID
where CONVERT(DATE,ConfirmCancelDateTime) = CONVERT(DATE, GETDATE()) and c.shopNo = '10908878'
group by cast(c.ConfirmCancelDateTime as date)
)
select dt._DailyTotal, ConfirmCancelDateTime,
From dailyTotals dt
join (
select ROW_NUMBER() OVER (
PARTITION BY ShopNo
ORDER BY ConfirmCancelDateTime desc
) row_num,
shopNo,
ConfirmCancelDateTime
from ConfirmCancels
) a on dt.shopNo = a.shopNo
where row_num = 1;WITH [dailyTotals]
AS (
SELECT
SUM([cr].[unitPrice]) AS [_DailyTotal]
, CAST([c].[ConfirmCancelDateTime] AS DATE) AS [_date]
FROM
[GameRequests] AS [gr]
JOIN [ConfirmCancels] AS [c]
ON [gr].[ReferenceID] = [c].[ReferenceID]
AND [c].[Status] = 1
JOIN [GameConfirmResponses] AS [cr]
ON [gr].[ReferenceID] = [cr].[ReferenceID]
WHERE
CONVERT(DATE, [ConfirmCancelDateTime]) = CONVERT(DATE, GETDATE())
GROUP BY
CAST([c].[ConfirmCancelDateTime] AS DATE)
)
SELECT
[dt].[_DailyTotal]
, [ConfirmCancelDateTime]
FROM
[dailyTotals] AS [dt]
JOIN
( SELECT
ROW_NUMBER() OVER (PARTITION BY
[ShopNo]
ORDER BY
[ConfirmCancelDateTime] DESC) AS [row_num]
, [shopNo]
, [ConfirmCancelDateTime]
FROM
[ConfirmCancels]) AS [a]
ON [dt].[shopNo] = [a].[shopNo]
WHERE
[a].[row_num] = 1;