please click arrow to the left for DROP Create Sample Data
drop create sample data
drop table #Result
go
create table #Result
(
Id varchar(50),
Val int
)
go
insert into #Result select '96171063',10
insert into #Result select '97173711',20
insert into #Result select '98184487',15
insert into #Result select '96174860',5
insert into #Result select '96173728',6
insert into #Result select '98184487',7
insert into #Result select '96173528',10
insert into #Result select '98154487',11
insert into #Result select '96173761',13
insert into #Result select '97014860',20
insert into #Result select '97064860',11
go
SELECT 'data', * from #Result
Please click arrow to the left for SQL
SQL
;WITH [cte]
AS (
SELECT
SUBSTRING([#Result].[Id], 3, 2) AS [ok]
, [#Result].[Val]
FROM
[#Result]
)
, [cte_1]
AS (
SELECT 1 AS [rn], NULL AS [subtot], [cte].[ok], [cte].[Val] FROM [cte]
)
, [cte_2]
AS (
SELECT
2 AS [rn]
, ' sub total ' AS [subtot]
, [cte].[ok]
, SUM([cte].[Val]) AS [sumval]
FROM
[cte]
GROUP BY
[cte].[ok]
)
, [cte_3]
AS (
SELECT
3 AS [rn]
, ' Grand Total ' AS [GrandTot]
, NULL AS [ok]
, SUM([cte].[Val]) AS [Grandtotal]
FROM
[cte]
)
SELECT
'SQL Output '
, [a].[rn]
, [a].[subtot]
, [a].[ok]
, [a].[Val]
FROM
( SELECT
[cte_1].[rn]
, [cte_1].[subtot]
, [cte_1].[ok]
, [cte_1].[Val]
FROM
[cte_1]
UNION ALL
SELECT
[cte_2].[rn]
, [cte_2].[subtot]
, [cte_2].[ok]
, [cte_2].[sumval]
FROM
[cte_2]
UNION ALL
SELECT
[cte_3].[rn]
, [cte_3].[GrandTot]
, [cte_3].[ok]
, [cte_3].[Grandtotal]
FROM
[cte_3]) AS [a]
ORDER BY
[a].[ok]
, [a].[rn];
I setup a structure for you to do this in SQL - you need to at least make an attempt at learning and writing the code yourself.
As for doing this in SSRS - do you have SSRS and can you publish a report to the SSRS report server? If you don't have that then it does no good to try to show you how...
How are you going to display this data to the user? A much simpler method would be to copy/paste the data into Excel and use Excel to add totals - but if you use the above you can get the expected results.
@yosiasz I don't agree. My requirements did NOT change. Anyways thank you for your help.
My query in post #1:
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'
people here are very very much experienced and SENIORs
they can catch you very easily !!
Your SQL ..
; with cte as
(
SELECT
[cf].[referenceId]
, ISNULL([cc].[shopNo], [gr].[shopNo]) AS [shopNo]
,substring(ISNULL([cc].[shopNo], [gr].[shopNo]),3,2) as [3rd to 4th 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] AS [cf]
LEFT JOIN
( SELECT
[#ConfirmCancels].[referenceId]
, [#ConfirmCancels].[confirmCancelDatetime]
, [#ConfirmCancels].[status]
, [#ConfirmCancels].[shopNo]
, [#ConfirmCancels].[safeNo]
, [#ConfirmCancels].[cashierNo]
, ROW_NUMBER() OVER (PARTITION BY
[#ConfirmCancels].[referenceId]
ORDER BY
[#ConfirmCancels].[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'
)
select [3rd to 4th shopno],unitPrice, totalPrice
from cte
