Udtræk af §20-spørgsmål

gistfile1.sql

WITH MT AS ( SELECT row_number() over ( ORDER BY CASE WHEN
ftcn_casenumber.ftcn_casetypedesc in ('B ', 'S ', 'L ', 'Aktstk.', 'US') THEN
UPPER(ftcn_casenumber.ftcn_casetypedesc + ' ' + REPLICATE('0', 50 -
len(ftcn_casenumber.ftcn_casenumber)) + ftcn_casenumber.ftcn_casenumber + ' ' +
IsNull(ftcn_casenumber.ftcn_casenosuffix, '') + ' ' + REPLICATE('0', 50 -
len(convert(varchar(10),IsNull(documentParagraphNumber,0)))) +
convert(varchar(10),IsNull(documentParagraphNumber,0))) ELSE ( CASE WHEN
caseTypeRecno=100012 then 'UM-DEL' WHEN caseTypeRecno=100001 THEN 'ALM. DEL ' +
committeeAbbreviation + ' ' + REPLICATE('0', 50 -
len(convert(varchar(10),IsNull(documentParagraphNumber,0)))) +
convert(varchar(10),IsNull(documentParagraphNumber,0)) + ' ' + documentSortPrefix +
REPLICATE('0', 50 - len(documentDisplayNumber)) + documentDisplayNumber WHEN
caseTypeRecNo=100005 THEN 'RÅDSMØDE ' + caseNumber ELSE UPPER(caseNumber) END ) END
desc, committeeAbbreviation desc, documentSortPrefix desc, documentSortNumber desc ) AS
rowid , session, committeeAbbreviation, caseTypeRecno, caseRecno, caseNumber,
documentCategoryRecno, documentParagraphNumber, documentSortPrefix, documentSortNumber,
documentDisplayNumber, documentRestriction, docKey, CASE WHEN caseTypeRecNo=100005 then
'Rådsmøde '+ caseNumber WHEN caseTypeRecno=100012 then 'UM-del' WHEN
caseTypeRecno=100001 THEN 'Alm. del ' + committeeAbbreviation ELSE caseNumber END + '
'+ IsNull(documentDisplayParagraphNumber,'') + IsNull(documentSortPrefix, '') + ' ' +
IsNull(documentDisplayNumber, '') AS cNumber, documentTitle, [dbo].[CapitalizeString
(ct_name3) AS ct_name3, case when answerExists=2 then 'Ikke besvaret' when
answerExists=1 then 'Besvaret' else '' end AS status, documentCategoryDesc,
convert(nvarchar, IsNull(documentDistributionDate, documentDate),104) + ' ('+
sessionUserFriendly +')' AS dokDatoShow FROM (dbo.xWebCaseQuestions20 INNER JOIN
dbo.xWebCaseActivity ON dbo.xWebCaseQuestions20.caseRecno =
dbo.xWebCaseActivity.activityCaseRecno) left outer join ftcn_casenumber on
dbo.xWebCaseQuestions20.caseRecNo = ftcn_casenumber.ftcn_ca_recno WHERE ( (documentDate
between convert(datetime,N'20120227',112) AND convert(datetime,N'20120327',112)) AND
(caseTypeRecno=100007 and activityTypeRecno=100013 and activityStatusRecno in
(16,100060,100061,100062,100063)) ) ) SELECT * FROM MT WHERE rowid between 26 AND 50
FOR XML AUTO, ELEMENTS XSINIL, root('list')