Here you are, Is it possible to add subtotals and grand total like in my sample first post? The query in here retrieves similar to my sample first post, I wonder if I can add subtotals and grandtotal of unitPrice and totalPrice based on 3rd and 4th characters of shopNo that I explained in my first post. Is it clear now?
Create table #GameRequests (referenceId uniqueIdentifier,
requestDateTime datetime2(7),shopNo varchar(max),safeNo varchar(max), cashierNo varchar(max))
Create table #ConfirmCancels (referenceId uniqueIdentifier,
confirmCancelDatetime datetime2(7),status int, shopNo varchar(max),safeNo varchar(max), cashierNo varchar(max))
Create table #GameConfirmResponses (referenceId uniqueIdentifier,
purchaseStatusDate datetime2(7), productCode varchar(max),productDescription varchar(max),quantity int, unitPrice int,
totalPrice int)
Insert into #GameRequests values
('AD7E534A-7084-4B9F-B2DD-24F2483B703B','2020-04-01 13:05:54.047','96174860','1','167834'),
('1DF20FEB-6921-4010-B8B1-445EB8A314AC','2020-04-01 13:00:44.000','96174860','1','167834'),
('2D87E61E-3281-46BC-A433-247B683D5715','2020-04-01 14:00:39.000','96184860','2','167834'),
('70332F57-F70A-4AA3-B5B4-E983AAD18157','2020-04-01 14:01:34.003','96184860','2','167834'),
('89BFD7D0-0243-44EF-84A5-81CE61FFD51C','2020-04-01 14:02:44.000','96064860','1','167834'),
('8AA067D5-84CF-4F40-87D8-CADCC83592B7','2020-04-01 14:09:55.023','96064860','3','167834'),
('2F36AFFB-B10F-4F0B-8682-7A77E7BCCD90','2020-04-01 14:10:28.700','96224860','3','167834'),
('02351525-7EEF-4B25-8584-470225EAFE50','2020-04-01 14:09:00.773','96224860','1','167834'),
('BB28E1D8-F2E8-45EC-89F5-95A012D8E4C0','2020-04-01 14:11:55.520','96174860','1','167834')
Insert into #GameConfirmResponses values
('AD7E534A-7084-4B9F-B2DD-24F2483B703B','2020-04-01 13:47:41.047','000000001570','10 TLlik Steam Bakiyesi',1,10,10),
('1DF20FEB-6921-4010-B8B1-445EB8A314AC','2020-04-01 13:48:24.000','000000001570','10 TLlik Steam Bakiyesi',1,10,10),
('2D87E61E-3281-46BC-A433-247B683D5715','2020-04-01 14:16:35.000','000000001570','10 TLlik Steam Bakiyesi',1,10,10),
('70332F57-F70A-4AA3-B5B4-E983AAD18157','2020-04-01 14:18:11.003','000000001572','Bim 400 Riot Points',1,16,16),
('89BFD7D0-0243-44EF-84A5-81CE61FFD51C','2020-04-01 14:18:29.000','000000001573','Bim 840 Riot Points',1,32,32),
('8AA067D5-84CF-4F40-87D8-CADCC83592B7','2020-04-01 14:19:39.023','000000001574','25 Hükümdarlık',1,6,6),
('2F36AFFB-B10F-4F0B-8682-7A77E7BCCD90','2020-04-01 14:20:13.700','000000001575','50 Hükümdarlık',1,12,12),
('02351525-7EEF-4B25-8584-470225EAFE50','2020-04-01 14:20:48.773','000000001582','Bim 10 TL Razer Gold Pin',1,10,10),
('BB28E1D8-F2E8-45EC-89F5-95A012D8E4C0','2020-04-01 14:21:26.520','000000001583','Bim 25 TL Razer Gold Pin',1,25,25)
Insert into #ConfirmCancels values
('AD7E534A-7084-4B9F-B2DD-24F2483B703B','2020-04-01 13:47:54.047',1,'96174860','1','167834'),
('1DF20FEB-6921-4010-B8B1-445EB8A314AC','2020-04-01 13:48:44.000',1,'96174860','1','167834'),
('2D87E61E-3281-46BC-A433-247B683D5715','2020-04-01 14:16:39.000',1,'96184860','2','167834'),
('70332F57-F70A-4AA3-B5B4-E983AAD18157','2020-04-01 14:18:34.003',0,'96184860','2','167834'),
('89BFD7D0-0243-44EF-84A5-81CE61FFD51C','2020-04-01 14:18:44.000',1,'96064860','1','167834'),
('8AA067D5-84CF-4F40-87D8-CADCC83592B7','2020-04-01 14:19:55.023',2,'96064860','3','167834'),
('2F36AFFB-B10F-4F0B-8682-7A77E7BCCD90','2020-04-01 14:20:28.700',1,'96224860','3','167834'),
('02351525-7EEF-4B25-8584-470225EAFE50','2020-04-01 14:21:00.773',1,'96224860','1','167834'),
('BB28E1D8-F2E8-45EC-89F5-95A012D8E4C0','2020-04-01 14:21:55.520',1,'96174860','1','167834')
SELECT cf.referenceId, ISNULL(cc.shopNo,gr.shopNo) AS shopNo, ISNULL(cc.safeNo,gr.safeNo) AS safeNo,
ISNULL(cc.cashierNo, gr.cashierNo) AS cashierNo, cf.purchaseStatusDate, cc.confirmCancelDatetime, cf.productCode, cf.productDescription,
cf.quantity, cf.unitPrice, cf.totalPrice, ISNULL(cc.status, 0) As status
FROM #GameConfirmResponses cf LEFT JOIN (SELECT *, Row_number()
OVER(partition BY referenceId ORDER BY confirmCancelDatetime) AS row_num
FROM #ConfirmCancels) AS cc ON cf.referenceId = cc.referenceId AND cc.row_num = 1
JOIN #GameRequests AS gr ON gr.referenceId = cf.referenceId WHERE cf.purchaseStatusDate >= '20200401' AND cf.purchaseStatusDate < '20200402'
proof of concept