Invalid column name shopNO ON [dt].[shopNo] = [a].[shopNo]
;WITH [dailyTotals]
AS (
SELECT
SUM([cr].[unitPrice]) AS [_DailyTotal]
, CAST([c].[ConfirmCancelDateTime] AS DATE) AS [_date]
, [c].[shop_no]
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;I gave up, thank you for your help @yosiasz and @harishgg1 This query below is not ideally what I want because it is not getting the earliest confirm date time but I will use it anyway.
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)hi cenk
How to explain to others !!
what you understand ..
is the the toughest part ..
many people dont see what you are seeing !!
what is it that they are seeing and what they are understanding
or
HOW they are seeing
anybody else who sees what you put
has to understand whats going on !!
so it is in your best interest to make it as clear as possible
HOW TO DO THAT is another PHD subject by itself !!
:-)remove ambiguity !! ..
@harishgg1you are 100% right I will try to explain it all over again but this time simple as possible This question needs one more try.
Here are my requirements @harishgg1
1)Get today's total price for a shopNo (which means shopNo is in the where clause)
2)Status should be 1
3)There might be more than one same referenceId for a shopNo, you should get the earliest one
4)In the result having total price for a shopNo for today is more than enough. (eg. 100)
Table names and data are in my first, 13th and 15th posts.
Ok sounds good 
I will take a look 
hi
what does this mean
4)In the result having total price for a shopNo for today is more than enough. (eg. 100)
Please explain ...
i have done 1) 2) 3) below .. please check
please click arrow for drop create sample data
drop create sample data
DROP TABLE [#GameRequest];
DROP TABLE [#Confirm];
DROP TABLE [#ConfirmRequest];
GO
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);
GO
--SELECT * FROM #GameRequest
--SELECT * FROM #Confirm
--SELECT * FROM #ConfirmRequest
--GO
Please click arrow for SQL
SQL ..
;WITH [CTE]
AS (
SELECT
[CR].[Price]
, [C].[ReferenceID]
, [GR].[ShopNo]
, ROW_NUMBER() OVER (PARTITION BY
[GR].[ShopNo]
, [C].[ReferenceID]
ORDER BY
[GR].[RequestDateTime]) AS [RN]
FROM
[#GameRequest] AS [GR]
JOIN [#Confirm] AS [C]
ON [GR].[ShopNo] = [C].[ShopNo]
JOIN [#ConfirmRequest] AS [CR]
ON [CR].[RequestDateTime] = [GR].[RequestDateTime]
WHERE
[C].[Status] = 1
)
SELECT
SUM([CTE].[Price])
, [CTE].[ShopNo]
FROM
[CTE]
WHERE
[CTE].[RN] = 1
GROUP BY
[CTE].[ShopNo];
GO
Hi @harishgg1,
I don't want to group by shopNo. Just want to get the total price for a shopNo which comes to the query as a parameter (eg. where shopNo = '123456') in the where clause. Am I clear?
ok to you !!
please click arrow for SQL
SQL with parameter
DECLARE @param_shopno INT = 111112;
WITH [CTE]
AS (
SELECT
[CR].[Price]
, [C].[ReferenceID]
, ROW_NUMBER() OVER (PARTITION BY
[GR].[ShopNo]
, [C].[ReferenceID]
ORDER BY
[GR].[RequestDateTime]) AS [RN]
FROM
[#GameRequest] AS [GR]
JOIN [#Confirm] AS [C]
ON [GR].[ShopNo] = [C].[ShopNo]
JOIN [#ConfirmRequest] AS [CR]
ON [CR].[RequestDateTime] = [GR].[RequestDateTime]
WHERE
[C].[Status] = 1
AND [C].[ShopNo] = @param_shopno
)
SELECT
@param_shopno AS [ShopNo]
, SUM([CTE].[Price]) AS [TotalPrice]
FROM
[CTE]
WHERE
[CTE].[RN] = 1;
GO
@harishgg1, unfortunately, you can't join on date times. In my sample data date times seem equal because I just copied/pasted them. It should be joined with referenceId's. ReferenceId is the PK.
JOIN [GameConfirmResponses] AS [CR]
ON [CR].[RequestDateTime] = [GR].[RequestDateTime]ok i have removed join on RequestDateTime
and added join on ReferenceID
Please check
Please click arrow for SQL
SQL with Reference ID join
DECLARE @param_shopno INT = 111112;
WITH [CTE]
AS (
SELECT
[CR].[Price]
, [C].[ReferenceID]
, ROW_NUMBER() OVER (PARTITION BY
[GR].[ShopNo]
, [C].[ReferenceID]
ORDER BY
[GR].[RequestDateTime]) AS [RN]
FROM
[#GameRequest] AS [GR]
JOIN [#Confirm] AS [C]
ON [GR].[ShopNo] = [C].[ShopNo]
JOIN [#ConfirmRequest] AS [CR]
ON [CR].[ReferenceID] = [GR].[ReferenceID]
WHERE
[C].[Status] = 1
AND [C].[ShopNo] = @param_shopno
)
SELECT
@param_shopno AS [ShopNo]
, SUM([CTE].[Price]) AS [TotalPrice]
FROM
[CTE]
WHERE
[CTE].[RN] = 1;
GO
thank you @harishgg1, it is what I want.
Finally Cenk 
time for a BEER !!!
