I am working on a chart which uses xml data to build the graph. Well, here I am not going to do anything on xml. Rather I will look into what data the chart is expecting, what were the challenges that I faced and how did I overcome it.
I have a table named ‘OfficeDetails’, of which I am interested in 3 columns viz., ‘AppID’, ‘ManagedBy’, ‘BU’. My graph will contain the information of the last 2 columns, but as I need some unique column for filtering the data, I had chosen AppID for that purpose. ManagedBy and BU contains the ID and the actual values are available in OFFICELookupValue table.
The graph need the count of ManagedBy for each BU. However the graph need the count even if a ManagedBy –BU count is 0; i.e, if there are 10 unique BU and 5 ManagedBy then we need to return 10*5=50 records. The logic becomes more complicated because the BU column consists of comma separated Ids. I looked for some sort of logic to loop through the rows in OfficeDetails table. Most of them suggested cursors. Well, I am aware that cursor in SqlServer is not a very good idea. Looked around and I found a much better solution.
Step 1
Create a table variable:
DECLARE @tempOfficeDetails TABLE(AppID INT , ManagedBy INT, BU VARCHAR(200))
Step 2
Insert the required data to the table variable, we may also filter data in this step:
INSERT INTO @tempOfficeDetails
SELECT AppID, ManagedBy, BU FROM OfficeDetails ORDER BY BU DESC
Step 3
Handling the comma separated values of BU need one more table variable:
DECLARE @tempOfficeDetailsSplitBU TABLE(AppID INT , ManagedBy INT, BU VARCHAR(200))
Step 4
As we move through the While loop the top values are inserted to tempOfficeDetailsSplitBU table:
SELECT TOP 1 @AppID = AppID, @ManagedBy = ManagedBy, @BU = BU FROM @tempOfficeDetails
WHILE @AppID IS NOT NULL
BEGIN
INSERT INTO @tempOfficeDetailsSplitBU SELECT AppID, ManagedBy, S.part FROM @tempOfficeDetails CROSS APPLY [dbo].[SplitString](@BU, ',') AS S WHERE AppID =@AppID
DELETE @tempOfficeDetails WHERE @AppID = AppID
SET @AppID = NULL
SELECT TOP 1 @AppID = AppID, @ManagedBy = ManagedBy, @BU = BU FROM @tempOfficeDetails
END
Step 5
We Created one more table variable @BusinessFunctManagedBy which will store the all the records where ManagedBy –BU count is >0.
DECLARE @BusinessFunctManagedBy TABLE
(
BusinessFunct VARCHAR(254),
ManagedBy VARCHAR(254),
OfficeCount INT
)
Step 6
The step/logic to insert the records from tempOfficeDetailsSplitBU is below:
INSERT INTO @BusinessFunctManagedBy
SELECT officelkp2.[Value] AS BusinessFunct, officelkp.[Value] AS ManagedBy,
OfficeCount = CASE WHEN officelkp2.[Value] IS NULL THEN 0 ELSE COUNT(1) END
FROM @tempOfficeDetailsSplitBU AS OfficeDetails
LEFT JOIN OFFICELookupValue officelkp
ON officelkp.pkID = OfficeDetails.ManagedBy
LEFT JOIN OFFICELookupValue officelkp2
ON officelkp2.pkID = OfficeDetails.BU
GROUP BY officelkp2.[Value], officelkp.[Value] ORDER BY officelkp2.[Value] DESC
Step 7
Since we need the records where BU-ManagedBy count is 0, we created 2 table variables and inserted records as below:
DECLARE @BusinessFunct TABLE
(
BusinessFunct VARCHAR(254)
)
DECLARE @ManagedByTab TABLE
(
ManagedBy VARCHAR(254)
)
INSERT INTO @BusinessFunct
SELECT DISTINCT officelkp.[Value] AS BusinessFunct
FROM @tempOfficeDetailsSplitBU AS OfficeDetails
LEFT JOIN OFFICELookupValue officelkp
ON officelkp.pkID = OfficeDetails.BU
--SELECT * from @BusinessFunct
INSERT INTO @ManagedByTab
SELECT DISTINCT officelkp.[Value] AS ManagedBy
FROM @tempOfficeDetailsSplitBU AS OfficeDetails
LEFT JOIN OFFICELookupValue officelkp
ON officelkp.pkID = OfficeDetails.ManagedBy
Step 8
We created one more table variable TotalMatching which inserts the Cartesian product of ManagedBy-BU columns with count 0. Next it update the OfficeCount with values from BusinessFunctManagedBy table that we created earlier. Next delete all the records where record count is 0. Hence we are left with only the records with 0 count. Insert them to BusinessFunctManagedBy.
DECLARE @TotalMatching TABLE
(
BusinessFunct VARCHAR(254),
ManagedBy VARCHAR(254),
OfficeCount INT
)
INSERT INTO @TotalMatching
SELECT H.BusinessFunct,M.ManagedBy,0
FROM @BusinessFunct H
CROSS JOIN @ManagedByTab M
UPDATE T
SET T.OfficeCount = A.OfficeCount
FROM @TotalMatching T
INNER JOIN @BusinessFunctManagedBy A
ON T.BusinessFunct = A.BusinessFunct
AND T.ManagedBy=A.ManagedBy
DELETE FROM @TotalMatching
WHERE OfficeCount > 0
INSERT INTO @BusinessFunctManagedBy
SELECT * FROM @TotalMatching
The complete stored procedure is below:
CREATE PROC dbo.OFFICE_BusinessFunctManagedBy
AS
BEGIN
DECLARE @tempOfficeDetails TABLE(AppID INT , ManagedBy INT, BU VARCHAR(200))
DECLARE @tempOfficeDetailsSplitBU TABLE(AppID INT , ManagedBy INT, BU VARCHAR(200))
DECLARE @AppID INT, @ManagedBy INT, @BU VARCHAR(200)
INSERT INTO @tempOfficeDetails
SELECT AppID, ManagedBy, BU FROM OfficeDetails ORDER BY BU DESC
SELECT TOP 1 @AppID = AppID, @ManagedBy = ManagedBy, @BU = BU FROM @tempOfficeDetails
WHILE @AppID IS NOT NULL
BEGIN
INSERT INTO @tempOfficeDetailsSplitBU SELECT AppID, ManagedBy, S.part FROM @tempOfficeDetails CROSS APPLY [dbo].[SplitString](@BU, ',') AS S WHERE AppID =@AppID
DELETE @tempOfficeDetails WHERE @AppID = AppID
SET @AppID = NULL
SELECT TOP 1 @AppID = AppID, @ManagedBy = ManagedBy, @BU = BU FROM @tempOfficeDetails
END
DECLARE @BusinessFunctManagedBy TABLE
(
BusinessFunct VARCHAR(254),
ManagedBy VARCHAR(254),
OfficeCount INT
)
INSERT INTO @BusinessFunctManagedBy
SELECT officelkp2.[Value] AS BusinessFunct, officelkp.[Value] AS ManagedBy,
OfficeCount = CASE WHEN officelkp2.[Value] IS NULL THEN 0 ELSE COUNT(1) END
FROM @tempOfficeDetailsSplitBU AS OfficeDetails
LEFT JOIN OFFICELookupValue officelkp
ON officelkp.pkID = OfficeDetails.ManagedBy
LEFT JOIN OFFICELookupValue officelkp2
ON officelkp2.pkID = OfficeDetails.BU
GROUP BY officelkp2.[Value], officelkp.[Value] ORDER BY officelkp2.[Value] DESC
--SELECT * from @BusinessFunctManagedBy order by BusinessFunct asc
DECLARE @BusinessFunct TABLE
(
BusinessFunct VARCHAR(254)
)
DECLARE @ManagedByTab TABLE
(
ManagedBy VARCHAR(254)
)
INSERT INTO @BusinessFunct
SELECT DISTINCT officelkp.[Value] AS BusinessFunct
FROM @tempOfficeDetailsSplitBU AS OfficeDetails
LEFT JOIN OFFICELookupValue officelkp
ON officelkp.pkID = OfficeDetails.BU
--SELECT * from @BusinessFunct
INSERT INTO @ManagedByTab
SELECT DISTINCT officelkp.[Value] AS ManagedBy
FROM @tempOfficeDetailsSplitBU AS OfficeDetails
LEFT JOIN OFFICELookupValue officelkp
ON officelkp.pkID = OfficeDetails.ManagedBy
DECLARE @TotalMatching TABLE
(
BusinessFunct VARCHAR(254),
ManagedBy VARCHAR(254),
OfficeCount INT
)
INSERT INTO @TotalMatching
SELECT H.BusinessFunct,M.ManagedBy,0
FROM @BusinessFunct H
CROSS JOIN @ManagedByTab M
UPDATE T
SET T.OfficeCount = A.OfficeCount
FROM @TotalMatching T
INNER JOIN @BusinessFunctManagedBy A
ON T.BusinessFunct = A.BusinessFunct
AND T.ManagedBy=A.ManagedBy
DELETE FROM @TotalMatching
WHERE OfficeCount > 0
INSERT INTO @BusinessFunctManagedBy
SELECT * FROM @TotalMatching
SELECT * FROM @BusinessFunctManagedBy ORDER BY LEN(BusinessFunct) DESC, 2, 1
END
No comments:
Post a Comment