View: dbo.Check_Layout_Report_Definition_Descriptions_Per_Client_View
View definition
-- This allows for a common query to be shared between Scheduled_Payroll_Run_View and Scheduled_Payroll_Run_Enhanced_View
-- without adding to the copy-paste maintenance burden of those two files.
CREATE VIEW dbo.Check_Layout_Report_Definition_Descriptions_Per_Client_View
AS
WITH
CheckLayoutReportDefinitionDescriptionsPerClient (client_KEY, vendor_description, payroll_description) AS
(
SELECT cb.client_KEY ,
rd.description ,
rd1.description
FROM dbo.Checkbook AS cb
JOIN dbo.Report_Definition AS rd
ON cb.vendor_layout__report_definition_KEY = rd.report_definition_KEY
JOIN dbo.Report_Definition AS rd1
ON cb.payroll_layout__report_definition_KEY = rd1.report_definition_KEY
WHERE cb.is_account_inactive = 0
),
ConcatinatedCheckLayoutReportDefinitionDescriptionPerClient (client_KEY, vendor_description, payroll_description) AS
(
-- SELECT … FOR XML will turn the multiple rows of Report Definition descriptions into one big
-- string per client(Concatinated by the delimeter ';').
-- the STUFF() function has the purpose of removing the leading delimiter from the concatinated Report Definition descriptions string.
SELECT t2.client_KEY ,
vendor_description = STUFF((SELECT DISTINCT N'; ' + vendor_description
FROM CheckLayoutReportDefinitionDescriptionsPerClient AS t1
WHERE t1.client_KEY = t2.client_KEY
FOR
XML PATH(N'')
), 1, 2, N'') ,
payroll_description = STUFF((SELECT DISTINCT N'; ' + payroll_description
FROM CheckLayoutReportDefinitionDescriptionsPerClient AS t1
WHERE t1.client_KEY = t2.client_KEY
FOR
XML PATH(N'')
), 1, 2, N'')
FROM CheckLayoutReportDefinitionDescriptionsPerClient t2
GROUP BY client_KEY
)
SELECT vl.client_KEY ,
vl.payroll_description,
vl.vendor_description
FROM ConcatinatedCheckLayoutReportDefinitionDescriptionPerClient AS vl;