Dear team!
Yesterday, I changed my code to link the report itself with grouping by department. Today, I noticed that the report is running slower. Perhaps the issue lies in the numerous cases present in the report?
-- Insert dữ liệu vào bảng mới
INSERT INTO #pl_sparkline_temple (common_col, actual, month_value, year_value)
SELECT
case
when @isDepartmentView = 'Yes' then b.ma_bp
when @isDepartmentView = '' then b.code
end as 'common_col',
sum(b.recent) as 'actual',
b.month AS 'month_value',
b.year AS 'year_value'
FROM cdc_KT1S_Release_App_2024..pl_report_v3 b
where b.month is not null and (@filterDepartment = '' OR b.ma_bp IN (SELECT REPLACE(value, '''', '') FROM STRING_SPLIT(@filterDepartment, ',')))
--and (@version = '' OR b.Version IN (SELECT REPLACE(value, '''', '') FROM STRING_SPLIT(@version, ',')))
and (@filterAccount = '' OR b.code = @filterAccount)
and (b.date BETWEEN @startDate AND @endDate)
and b.group_id = 1
GROUP BY
CASE
WHEN @filterViewDirection = 'Admin' THEN
CASE WHEN @language = 'Vietnamese' THEN b.description
WHEN @language = 'English' THEN b.e_name
END
WHEN @filterViewDirection = 'Accounting' THEN
CASE WHEN @language = 'Vietnamese' THEN b.description_v2
WHEN @language = 'English' THEN b.e_name_v2
END
END,
b.row_class,
b.pos,
b.date,
b.month,
b.year,
concat(b.month,'/',b.year),
case when @isDepartmentView = 'Yes' then b.ma_bp
when @isDepartmentView = '' then b.code
end,
b.group_id
ORDER BY b.pos, b.date, b.month, b.year;
and somewhere have if then, case when like this
Thanks team!
