Public_SQL
user_1767831
sqlserver
9 months ago
12 kB
6
Indexable
WITH rankedrows AS ( SELECT *, row_number() over (partition by [ObservationGroupId/ QI] order by [ObservationGroupId/ QI] desc ) as rn FROM (SELECT DISTINCT Country, DC, BusinessUnitID, BusinessUnit, SectorID, Sector, AssetClassID, [Asset Class], [ObservationGroupId/ QI], LeadAnalyst, [Observation Date], TemplateType, [PR Generated from Mantis] from (Select *, --1693 CASE WHEN TemplateType = 'SF-EU' and [Observation Date] >= '2022-07-01' and DateCreated < '2023-10-03' THEN 'YES' WHEN TemplateType = 'SF-EU' and [Observation Date] < '2022-07-01' and DateCreated < '2023-10-03' THEN 'NO' WHEN TemplateType = 'SF-EU' and DateCreated >= '2023-10-03' THEN 'YES' WHEN TemplateType = 'SF-NA' and [Observation Date] >= '2023-07-01' and DateCreated < '2023-10-03' THEN 'YES' WHEN TemplateType = 'SF-NA' and [Observation Date] < '2023-07-01' and DateCreated < '2023-10-03' THEN 'NO' WHEN TemplateType = 'SF-NA' and DateCreated >= '2023-10-03' THEN 'YES' WHEN TemplateType = 'SOV-EU' and [Observation Date] >= '2023-07-01' and DateCreated < '2023-10-03' THEN 'YES' WHEN TemplateType = 'SOV-EU' and [Observation Date] < '2023-07-01' and DateCreated < '2023-10-03' THEN 'NO' WHEN TemplateType = 'SOV-EU' and DateCreated >= '2023-10-03' THEN 'YES' WHEN TemplateType = 'SOV-NA' and [Observation Date] > '2023-10-03' and DateCreated < '2023-10-03' THEN 'YES' WHEN TemplateType = 'SOV-NA' and [Observation Date] < '2023-10-03' and DateCreated < '2023-10-03' THEN 'NO' WHEN TemplateType = 'SOV-NA' and DateCreated >= '2023-10-03' THEN 'YES' WHEN TemplateType = 'CF-EU' and [Observation Date] > '2023-10-03' and DateCreated < '2023-10-03' THEN 'YES' WHEN TemplateType = 'CF-EU' and [Observation Date] < '2023-10-03' and DateCreated < '2023-10-03' THEN 'NO' WHEN TemplateType = 'CF-EU' and DateCreated >= '2023-10-03' THEN 'YES' WHEN TemplateType = 'FIG-EU' and [Observation Date] > '2023-10-03' and DateCreated < '2023-10-03' THEN 'YES' WHEN TemplateType = 'FIG-EU' and [Observation Date] < '2023-10-03' and DateCreated < '2023-10-03' THEN 'NO' WHEN TemplateType = 'FIG-EU' and DateCreated >= '2023-10-03' THEN 'YES' WHEN TemplateType = 'FIG-EU-BANK' and [Observation Date] > '2023-10-03' and DateCreated < '2023-10-03' THEN 'YES' WHEN TemplateType = 'FIG-EU-BANK' and [Observation Date] < '2023-10-03' and DateCreated < '2023-10-03' THEN 'NO' WHEN TemplateType = 'FIG-EU-BANK' and DateCreated >= '2023-10-03' THEN 'YES' WHEN TemplateType = 'FIG-NA-BANK' and [Observation Date] > '2023-10-03' and DateCreated < '2023-10-03' THEN 'YES' WHEN TemplateType = 'FIG-NA-BANK' and [Observation Date] < '2023-10-03' THEN 'NO' WHEN TemplateType = 'FIG-NA-BANK' and DateCreated >= '2023-10-03' THEN 'YES' WHEN TemplateType = 'FIG-NA-ENDORSED-BANK' and [Observation Date] > '2023-10-03' and DateCreated < '2023-10-03' THEN 'YES' WHEN TemplateType = 'FIG-NA-ENDORSED-BANK' and [Observation Date] < '2023-10-03' and DateCreated < '2023-10-03' THEN 'NO' WHEN TemplateType = 'FIG-NA-ENDORSED-BANK' and DateCreated >= '2023-10-03' THEN 'YES' WHEN TemplateType = 'CF-NA' and [Observation Date] > '2023-12-17' and DateCreated < '2023-10-03' THEN 'YES' WHEN TemplateType = 'CF-NA' and [Observation Date] < '2023-12-17' and DateCreated < '2023-10-03' THEN 'NO' WHEN TemplateType = 'CF-NA' and DateCreated >= '2023-12-17' THEN 'YES' WHEN TemplateType = 'CF-NA-ENDORSED' and [Observation Date] > '2023-12-17' and DateCreated < '2023-10-03' THEN 'YES' WHEN TemplateType = 'CF-NA-ENDORSED' and [Observation Date] < '2023-12-17' and DateCreated < '2023-10-03' THEN 'NO' WHEN TemplateType = 'CF-NA-ENDORSED' and DateCreated >= '2023-12-17' THEN 'YES' WHEN TemplateType = 'FIG-NA' and [Observation Date] > '2023-12-17' and DateCreated < '2023-10-03' THEN 'YES' WHEN TemplateType = 'FIG-NA' and [Observation Date] < '2023-12-17' and DateCreated < '2023-10-03' THEN 'NO' WHEN TemplateType = 'FIG-NA' and DateCreated >= '2023-12-17' THEN 'YES' WHEN TemplateType = 'FIG-NA-ENDORSED' and [Observation Date] > '2023-10-03' and DateCreated < '2023-10-03' THEN 'YES' WHEN TemplateType = 'FIG-NA-ENDORSED' and [Observation Date] < '2023-10-03' and DateCreated < '2023-10-03' THEN 'NO' WHEN TemplateType = 'FIG-NA-ENDORSED' and DateCreated >= '2023-10-03' THEN 'YES' WHEN TemplateType = 'CMBS-NA' and [Observation Date] > '2023-12-17' and DateCreated < '2023-10-03' THEN 'YES' WHEN TemplateType = 'CMBS-NA' and [Observation Date] < '2023-12-17' and DateCreated < '2023-10-03' THEN 'NO' WHEN TemplateType = 'CMBS-NA' and DateCreated >= '2023-12-17' THEN 'YES' WHEN TemplateType = 'CMBS-NA-ENDORSED' and [Observation Date] > '2023-12-17' and DateCreated < '2023-10-03' THEN 'YES' WHEN TemplateType = 'CMBS-NA-ENDORSED' and [Observation Date] < '2023-12-17' and DateCreated < '2023-10-03' THEN 'NO' WHEN TemplateType = 'CMBS-NA-ENDORSED' and DateCreated >= '2023-12-17' THEN 'YES' WHEN TemplateType = 'SF-NA-ENDORSED' and [Observation Date] > '2023-12-17' and DateCreated < '2023-10-03' THEN 'YES' WHEN TemplateType = 'SF-NA-ENDORSED' and [Observation Date] < '2023-12-17' and DateCreated < '2023-10-03' THEN 'NO' WHEN TemplateType = 'SF-NA-ENDORSED' and DateCreated >= '2023-12-17' THEN 'YES' END AS 'ISAutomated' from (select distinct rg.Description as 'Country', LongName as 'DC', vbs.BusinessUnitID, vbs.BusinessUnitName as 'Business Unit', vac.SectorID, vac.SectorName as 'Sector', CASE WHEN vbs.BusinessUnitName = 'Financial Institutions/Sovereigns' AND vac.SectorName like '%Sovereign' THEN 'Sovereign' WHEN vbs.BusinessUnitName = 'Financial Institutions/Sovereigns' AND vac.SectorName not like '%Sovereign' THEN 'Financial Institutions' WHEN rg.Description in ('Canada') AND vbs.BusinessUnitName in ('ABS/RMBS/CB', 'ABS/RMBS/CB/ABCP', 'Commercial Mortgages', 'Structured Credit') THEN 'Structured Finance' WHEN rg.Description in ('Europe') AND vbs.BusinessUnitName in ('ABS/RMBS/CB', 'CMBS', 'Structured Credit') THEN 'Structured Finance' WHEN rg.Description in ('U.S.') AND vbs.BusinessUnitName in ('ABS/RMBS', 'ABS/RMBS/CB', 'ABS/RMBS/CB/ABCP', 'Structured Credit') THEN 'Structured Finance' ELSE vbs.BusinessUnitName END as 'BusinessUnit', vac.AssetClassID, vac.AssetClassName as 'Asset Class', obs.ObservationGroupId as 'ObservationGroupId/ QI', obs.ObservationDate as 'Observation Date', cc.DisplayName as LeadAnalyst, obs.DateCreated, CASE WHEN ccode.Alpha2Code in ('CA','US') THEN CASE WHEN le.BusinessUnitID in (3,7,12,38) THEN --CMBS CASE WHEN oem.EndorsedByID in (2,16) and oem.EndorsedByID is not null THEN 'CMBS-NA-ENDORSED' ELSE 'CMBS-NA' END ELSE CASE WHEN le.BusinessUnitID in (15,14, 33, 34, 39) THEN --CF CASE WHEN (select top 1 EffectiveDate from RatingsPressReleaseDocumentTemplateMap order by EffectiveDate desc) < obs.ObservationDate THEN CASE WHEN oem.EndorsedByID in (2,16) and oem.EndorsedByID is not null THEN 'CF-NA-ENDORSED' ELSE 'CF-NA' END ELSE 'CF-NA' END ELSE CASE WHEN le.BusinessUnitID in (2,3,4,6,7,8,27,28,11,12,9,37,38,35,30,32,26) THEN --SF CASE WHEN (select top 1 EffectiveDate from RatingsPressReleaseDocumentTemplateMap order by EffectiveDate desc) < obs.ObservationDate THEN CASE WHEN oem.EndorsedByID in (2,16) and oem.EndorsedByID is not null THEN 'SF-NA-ENDORSED' ELSE 'SF-NA' END ELSE CASE WHEN oem.EndorsedByID in (2,16) and oem.EndorsedByID is not null THEN 'SF-NA-ENDORSED' ELSE 'SF-NA' END END ELSE CASE WHEN bu.IsFIG = 1 THEN CASE WHEN le.SectorID in (10, 23, 56, 62, 64, 76, 93, 122, 129) THEN 'SOV-NA' --FigSovereignSector ELSE CASE WHEN le.SectorID in (6, 19, 51, 57, 58, 60,92,128,34) and obs.ObservationDate > '01-01-2022' THEN --FigBankSector CASE WHEN oem.EndorsedByID in (2,16) and oem.EndorsedByID is not null THEN 'FIG-NA-ENDORSED-BANK' ELSE 'FIG-NA-BANK' END ELSE CASE WHEN oem.EndorsedByID in (2,16) and oem.EndorsedByID is not null THEN 'FIG-NA-ENDORSED' ELSE 'FIG-NA' END END END END END END END ELSE CASE WHEN ccode.Alpha2Code in ('DE','ES','GB') THEN CASE WHEN le.BusinessUnitID in (15,14, 33, 34, 39) THEN CASE WHEN (select top 1 EffectiveDate from RatingsPressReleaseDocumentTemplateMap order by EffectiveDate desc) < obs.ObservationDate THEN 'CF-EU' ELSE CASE WHEN ra.RecoveryRatingID is not null THEN 'CF-EU-RECOVERY-RATING' ELSE 'CF-EU' END END ELSE CASE WHEN le.BusinessUnitID in (2,3,4,6,7,8,27,28,11,12,9,37,38,35,30,32,26) THEN 'SF-EU' ELSE CASE WHEN bu.IsFIG = 1 THEN CASE WHEN le.SectorID in (10, 23, 56, 62, 64, 76, 93, 122, 129) THEN 'SOV-EU' --FigSovereignSector ELSE CASE WHEN le.SectorID in (6, 19, 51, 57, 58, 60,92,128,34) and obs.ObservationDate > '01-01-2022' THEN --FigBankSector 'FIG-EU-BANK' ELSE 'FIG-EU'END END END END END END END AS 'TemplateType', CASE WHEN qd.IsGeneratedFromMantis = 1 THEN 'YES' ELSE 'NO' END as 'PR Generated from Mantis' from LegalEntity le, ViewBusinessSector vbs, ViewAssetClass vac, LegalEntityRegionMap lrm,businessunit bu inner join DBRSCompany e on e.DBRSCompanyID = bu.DBRSCompanyID, Region rg, Observation Obs full outer join QiDocumentAutomation qd on qd.observationgroupid = obs.ObservationGroupId full outer join ratingaction ra on ra.ObservationID = obs.ObservationID full outer join QiDocumentApprovalMap qam on qam.ObservationGroupId = obs.ObservationGroupId full outer JOIN DocumentMetaData qm on qm.DocumentId = qam.DocumentId full outer JOIN QiAnalysts q on q.ObservationGroupId = obs.ObservationGroupId full outer Join UserProfile cc on cc.UserProfileId = q.UserId full outer join CountryCode ccode on ccode.Alpha2Code = cc.CountryCode full outer join DocumentAutomation DA on DA.DocumentTypeId = 3 full outer join ObservationEndorsementMap oem on oem.ObservationID = obs.ObservationID full outer join ObservationEndorsementReasonMap oerm on oerm.ObservationID = obs.ObservationID full outer JOIN RatingsPressReleaseDocumentTemplateMap r on r.RatingsPressReleaseDocumentTemplatemapID = DA.DocumentTemplateId where rg.RegionID=lrm.RegionID and lrm.LegalEntityID=le.LegalEntityID and vac.AssetClassID=le.AssetClassID and le.SectorID=vbs.SectorID and le.LegalEntityID = obs.LegalEntityID and le.BusinessUnitID = bu.BusinessUnitID and q.LegalEntityRoleId in (1 ,3) and obs.observationgroupid is not null AND obs.ObservationDate > '2024-01-23' -- between '2023-01-01' AND '2023-10-10' and ra.RatingActionTypeID = 1 and qm.DocumentTypeId= 3 ) t ) PRMetrics where ISAutomated = 'YES') f ) select country, DC, BusinessUnit, SectorID, Sector, [Asset Class], [ObservationGroupId/ QI], LeadAnalyst, [Observation Date], TemplateType, [PR Generated from Mantis] from ( select * , rn1 = row_number() over ( partition by [ObservationGroupId/ QI] order by rn desc ) from rankedrows ) as sub where sub.rn1 = 1 order by [ObservationGroupId/ QI] desc
Editor is loading...
Leave a Comment