Dear all,
I have this stored procedure which returns aggregated data, and I have faced a problem, it has been a while since I worked on sql scripts, I wanted to count a column that has more than one frequency, however, the script's output is not as I wanted.
The sample script is as follows:
NOTE: for convenience, I have changed the real names of the tables...
Please feel free if you guys have any question or explanation.
Thanks in Advance
What I have tried:
BEGIN
DECLARE @AppSettings TABLE(Name NVARCHAR(50) NULL)
INSERT INTO @AppSettings
SELECT SettingName FROM ApplicationSetting
DECLARE @Users TABLE(Name NVARCHAR(50) NULL)
INSERT INTO @Users
SELECT UserName FROM UserProfile
DECLARE @Activities TABLE(Name NVARCHAR(50) NULL)
INSERT INTO @Activities
SELECT Name FROM StateTemplate
SELECT Table2.Name AS ActivityName,
Count(Table2.Name) AS ActivityCount,
Table1.PerformedBy,
Table4.SettingName
FROM Table1 INNER JOIN
Table5 ON Table1.ParentBusinessProcessID = Table5.BusinessProcessID INNER JOIN
Table3 ON Table5.ProcessTypeID = Table3.ProcessTemplateID INNER JOIN
Table2 ON Table3.ProcessTemplateID = Table2.ParentProcessTemplateID INNER JOIN
Table4 ON Table3.ProcessTemplateID = Table4.SettingValue
WHERE Table4.SettingName IN (SELECT * FROM @AppSettings) AND
Table1.PerformedBy IN (SELECT * FROM @Users)
Table2.Name In (SELECT * FROM @Activities)
GROUP BY Table2.Name,
Table1.PerformedBy,
Table4.SettingName
ORDER BY PerformedBy
END
Wrong OUTPUT
ZZ 8 userNameX AA
ZZ 4 userNameX BB
YY 8 userNameX AA
YY 4 userNameX BB
XX 8 userNameX AA
XX 4 userNameX BB
WW 8 userNameX AA
WW 4 userNameX BB
VV 8 userNameX AA
VV 4 userNameX BB