I need to find out the recursive sum of each node of a tree. Please find the image below.
Here the number(assuming no of sales made) in blue is the sum of Parent Quantity Sum of its children and number in red is sale made by node itself . So if we take example of Node C = 44 , then its calculation is something like this.
Node C has a child F and F has again two children G and H. G & H got no children. But they made 5 and 6 number of sales respectively. Also their Parent F too made 11 sales on his own, so total sales of F will be 22 [ 11 his own and 11 from his children]. Also in the same line C, the parent of F , also got 22 sales of his own. So now total number of sales made by C will be 44 (his own 22 and 22 from his child and grand children).
And this hierarchy and calculation will go on. There's no restriction in depth and breadth of hierarchy as well. A node can have N number of children.
My database has 3 tables as shown in this screenshot:
StoreMaster: this table contains a master list of all stores.
StoreMaps: this is a mapping table which maintains the parent child hierarchies. Here
MasterStoreId -> ParentIdandSlaveStoreId -> ChildId. Both masterstoreid and slavestoreid references to storeid of theStoreMastertable. Also, this maps table supports N level of children in any hierarchy. There's no restrictions at all.StoreSales: transaction table for StoreMaster. This table contains store specific sales.
What I want to achieve is that I need a recursive query that will help finding me TotalAmount and Quantity of Sales made by each and every store as shown in figure 1 [above].
I tried using CTE but not getting the desired output. Here's what I tried
;WITH ChildStores AS
(
SELECT SlaveStoreId
FROM StoreMaps
WHERE SlaveStoreId = 35
UNION ALL
SELECT t.SlaveStoreId
FROM StoreMaps t
INNER JOIN ChildStores r ON t.MasterStoreId = r.SlaveStoreId
)
INSERT INTO @StoreTable
SELECT *
FROM ChildStores
SELECT DISTINCT ss.StoreId, ss.StoreCode, ss.StoreName
FROM StoreSales ss
WHERE ss.StoreId IN (SELECT * FROM @StoreTable)
SELECT ss.StoreName, COUNT(ss.SaleId), SUM(ss.AmountPaid)
FROM StoreSales ss
WHERE ss.StoreId IN (SELECT * FROM @StoreTable)
AND ss.SaleStatus = 'Paid'
AND ss.PlanCode <> '12345'
GROUP BY ss.StoreName
Second query:
WITH cteAggregateCost AS
(
SELECT
i.SlaveStoreId AS rootid, i.SlaveStoreId, i.MasterStoreId
FROM
StoreMaps i
UNION ALL
SELECT
rootid, i.SlaveStoreId, i.MasterStoreId
FROM
StoreMaps i
JOIN
cteAggregateCost c ON i.MasterStoreId = c.SlaveStoreId
)
SELECT
t.StoreName, SUM(t.AmountPaid) AS AggregateCost
FROM
StoreMaps a
LEFT JOIN
cteAggregateCost i ON a.SlaveStoreId = i.rootid
LEFT JOIN
StoreSales t ON i.SlaveStoreId = t.StoreId
WHERE
t.SaleStatus = 'Paid'
AND t.PlanCode <> 12345
GROUP BY
t.StoreName
Schema
CREATE TABLE StoreMaps
(
[StoreMapId] [int] IDENTITY(1,1) NOT NULL,
[MasterStoreId] [int] NULL,
[MasterStoreCode] [varchar](10) NULL,
[SlaveStoreId] [int] NULL,
[SlaveStoreCode] [varchar](10) NULL,
[IsActive] [bit] NOT NULL,
CONSTRAINT [PK_StoreMaps]
PRIMARY KEY CLUSTERED ([StoreMapId] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE StoreMaster
(
[StoreId] [int] IDENTITY(1,1) NOT NULL,
[StoreName] [varchar](150) NULL,
[StoreCode] [varchar](10) NULL,
[IsActive] [bit] NOT NULL,
CONSTRAINT [PK_StoreMaster]
PRIMARY KEY CLUSTERED ([StoreId] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE StoreSales
(
[SaleId] [int] IDENTITY(1,1) NOT NULL,
[StoreId] [int] NULL,
[StoreCode] [varchar](10) NULL,
[StoreName] [varchar](150) NULL,
[SaleStatus] [varchar](20) NULL,
[AmountPaid] [int] NULL,
[IsActive] [bit] NOT NULL,
CONSTRAINT [PK_StoreSales]
PRIMARY KEY CLUSTERED ([SaleId] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Full query
DECLARE @StoreCode VARCHAR(10) = NULL,
@StartDate DATE = NULL, @EndDate DATE = NULL
SET NOCOUNT ON;
DECLARE @Description VARCHAR(50),
@NetSales DECIMAL(16, 2),
@Quantity TINYINT,
@PayoutPercent TINYINT, @PayableAmount DECIMAL(16, 2),
@StoreName VARCHAR(150), @PartnerId TINYINT,
@Rowcount INT, @BillingMonth VARCHAR(10)
DECLARE @StoreTable AS TABLE (StoreId INT);
--INSERT INTO SpDebug (ObjectName, ObjectParam)
--VALUES (OBJECT_NAME(@@PROCID), @JsonParams)
IF ISNULL(TRIM(@StoreCode), '') = ''
BEGIN
SET @StoreCode = 'GW'
END
IF ISNULL(@StartDate, '') = ''
BEGIN
SET @StartDate = DATEADD(DD, - 7, GETDATE());
END
IF ISNULL(@EndDate, '') = ''
BEGIN
--Select * from @StoreTable
SET @EndDate = DATEADD(dd, - 1, GETDATE());
END
BEGIN TRY
BEGIN TRANSACTION;
WITH ChildStores
AS (
SELECT SlaveStoreId
FROM StoreMaps
WHERE SlaveStoreCode = @StoreCode
UNION ALL
SELECT t.SlaveStoreId
FROM StoreMaps t
INNER JOIN ChildStores r
ON t.MasterStoreId = r.SlaveStoreId
)
INSERT INTO @StoreTable
SELECT *
FROM ChildStores
SELECT @Rowcount = COUNT(*)
FROM @StoreTable;
WHILE (@Rowcount > 0)
BEGIN
SELECT @Rowcount = @Rowcount - 1;
SELECT @PartnerId = StoreId
FROM @StoreTable
ORDER BY StoreId DESC OFFSET @Rowcount ROWS
FETCH NEXT 1 ROWS ONLY;
IF EXISTS (
SELECT 1
FROM StoreMaster
WHERE StoreId = @PartnerId
AND IsActive = 1
AND ExcludeFromPayout = 0
)
BEGIN
SELECT @PayoutPercent = PayoutPercent, @StoreName = StoreName, @StoreCode = StoreCode
FROM StoreMaster
WHERE StoreId = @PartnerId
SET @Description = CONCAT ('Payout for ', FORMAT(@StartDate, 'dd-MMM-yyyy'), ' & ', FORMAT(@EndDate, 'dd-MMM-yyyy'));
SELECT @Quantity = COUNT(SaleId), @NetSales = SUM(AmountPaid / 1.18)
FROM StoreSales
WHERE StoreId = @PartnerId
AND (
CAST(ModifiedOn AS DATE) BETWEEN CAST(@StartDate AS DATE)
AND CAST(@EndDate AS DATE)
)
AND SaleStatus = 'Paid'
AND PlanCode <> '12345'
IF NOT EXISTS (
SELECT *
FROM PartnerInvoices
WHERE (
(
@StartDate BETWEEN PayoutStartDate
AND PayoutEndDate
)
OR (
@EndDate BETWEEN PayoutStartDate
AND PayoutEndDate
)
)
AND PartnerId = @PartnerId
)
BEGIN
SET @Quantity = ISNULL(@Quantity, 0)
SET @NetSales = ISNULL(@NetSales, 0)
SET @PayableAmount = ISNULL((@NetSales * @PayoutPercent * 0.01), 0)
SET @BillingMonth = FORMAT(GETDATE(), 'MMyyyy')
INSERT INTO PartnerInvoices (PartnerId, StoreName, PayoutStartDate, PayoutEndDate, BillingMonth, TxnRefNumber, [Description], NetSales, Quantity, PayoutPercent, PayableAmount)
VALUES (@PartnerId, @StoreName, @StartDate, @EndDate, @BillingMonth, '', @Description, @NetSales, @Quantity, @PayoutPercent, @PayableAmount)
END
--ELSE
--BEGIN
-- PRINT 'Invoice already generated for this duration.';
--END
END
END
COMMIT TRANSACTION
SELECT 1;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT INTO AppErrors (ErrorMessage, ObjectName, [Parameters])
VALUES (ERROR_MESSAGE(), OBJECT_NAME(@@procid), @PartnerId);
THROW
END CATCH
Please help me fixing this query as this is a for extracting our channel partners payout and i cant spend much time in having R&D. This is a critical bug.
CodePudding user response：
Is something like this what you're looking for?
WITH CTE AS (
SELECT NULL AS MasterID, @MasterStoreID AS SlaveID
UNION ALL
SELECT M.MasterStoreId, M.SlaveStoreId AS SlaveID
FROM CTE C
INNER JOIN StoreMaps M ON M.MasterStoreId = C.SlaveID
)
SELECT @MasterStoreID AS StoreID, SUM(ISNULL(S.AmountPaid, 0)) AS AmountPaidTotal
FROM CTE C
LEFT JOIN StoreSales ON S.StoreId = C.SlaveID;
Where @MasterStoreID is the ID of the store you want the total sales of.
Edit:
This query will return AmountPaid sum for every store in StoreMaster table, without the @MasterStoreID parameter:
WITH CTE AS (
SELECT StoreID AS RootID, NULL AS MasterID, StoreID AS SlaveID
FROM StoreMaster
UNION ALL
SELECT C.RootID, M.MasterStoreid, M.SlaveStoreId AS SlaveID
FROM CTE C
INNER JOIN StoreMaps M ON M.MasterStoreid = C.SlaveID
)
SELECT C.RootID AS StoreID, SUM(ISNULL(S.AmountPaid, 0)) AS AmountPaidTotal
FROM CTE C
LEFT JOIN StoreSales S ON S.StoreId = C.SlaveID
GROUP BY C.RootID;