Private_SQL
user_1767831
sqlserver
a year ago
6.3 kB
5
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, --[Business Unit], BusinessUnit, SectorID, Sector, AssetClassID, [Asset Class], [ObservationGroupId/ QI], LeadAnalyst, [Observation Date], TemplateType, [PRL Generated from Mantis] from ( Select *, CASE WHEN TemplateType in ('CF Frankfurt', 'CF London', 'CF Madrid', 'CF New York', 'CF Toronto','CF Chicago', 'FIG Frankfurt', 'FIG London', 'FIG Madrid', 'FIG New York', 'FIG Canada','SF Frankfurt', 'SF London', 'SF Madrid', 'SF New York', 'SF Canada','SF Chicago') and DateCreated > '2023-10-03' THEN 'YES' WHEN TemplateType in ('CF Frankfurt', 'CF London', 'CF Madrid', 'CF New York', 'CF Toronto','CF Chicago', 'FIG Frankfurt', 'FIG London', 'FIG Madrid', 'FIG New York', 'FIG Canada','SF Frankfurt', 'SF London', 'SF Madrid', 'SF New York', 'SF Canada','SF Chicago') and DateCreated = '2023-10-03' THEN 'NO' 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') THEN CASE WHEN le.BusinessUnitID in (15,14, 33, 34, 39) THEN 'CF Toronto' WHEN le.BusinessUnitID in (2,3,4,6,7,8,27,28,11,12,9,37,38,35,30,32,26,3,7,12,38) THEN 'SF Canada' WHEN bu.IsFIG = 1 THEN CASE WHEN le.SectorID in (10, 23, 56, 62, 64, 76, 93, 122, 129,6, 19, 51, 57, 58, 60,92,128,34) then 'FIG Canada' END END ELSE CASE WHEN ccode.Alpha2Code in ('US') THEN CASE WHEN le.BusinessUnitID in (15,14, 33, 34, 39) THEN 'CF New York' WHEN le.BusinessUnitID in (2,3,4,6,7,8,27,28,11,12,9,37,38,35,30,32,26,3,7,12,38) THEN 'SF New York' WHEN bu.IsFIG = 1 THEN CASE WHEN le.SectorID in (10, 23, 56, 62, 64, 76, 93, 122, 129,6, 19, 51, 57, 58, 60,92,128,34) then 'FIG New York' END END ELSE CASE WHEN ccode.Alpha2Code in ('ES') THEN CASE WHEN le.BusinessUnitID in (15,14, 33, 34, 39) THEN 'CF Madrid' WHEN le.BusinessUnitID in (2,3,4,6,7,8,27,28,11,12,9,37,38,35,30,32,26) THEN 'SF Madrid' WHEN bu.IsFIG = 1 THEN CASE WHEN le.SectorID in (10, 23, 56, 62, 64, 76, 93, 122, 129,6, 19, 51, 57, 58, 60,92,128,34) THEN 'FIG Madrid' END END ELSE CASE WHEN ccode.Alpha2Code in ('GB') THEN CASE WHEN le.BusinessUnitID in (15,14, 33, 34, 39) THEN 'CF London' WHEN le.BusinessUnitID in (2,3,4,6,7,8,27,28,11,12,9,37,38,35,30,32,26) THEN 'SF London' WHEN bu.IsFIG = 1 THEN CASE WHEN le.SectorID in (10, 23, 56, 62, 64, 76, 93, 122, 129,6, 19, 51, 57, 58, 60,92,128,34) THEN 'FIG London'END END ELSE CASE WHEN ccode.Alpha2Code in ('DE') THEN CASE WHEN le.BusinessUnitID in (15,14, 33, 34, 39) THEN 'CF Frankfurt' WHEN le.BusinessUnitID in (2,3,4,6,7,8,27,28,11,12,9,37,38,35,30,32,26) THEN 'SF Frankfurt' WHEN bu.IsFIG = 1 THEN CASE WHEN le.SectorID in (10, 23, 56, 62, 64, 76, 93, 122, 129,6, 19, 51, 57, 58, 60,92,128,34) THEN 'FIG Frankfurt'END END END END END END END AS 'TemplateType', CASE WHEN qd.IsGeneratedFromMantis = 1 THEN 'YES' ELSE 'NO' END as 'PRL 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 ObservationDocumentAutomation qd on qd.ObservationID = obs.ObservationID 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 = 4 full outer join ObservationEndorsementMap oem on oem.ObservationID = obs.ObservationID full outer join ObservationEndorsementReasonMap oerm on oerm.ObservationID = obs.ObservationID full outer JOIN PrivateRatingsLetterDocumentTemplate r on r.PrivateRatingsLetterDocumentTemplateID = 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-02-26' -- between '2023-01-01' AND '2023-10-10' and ra.RatingActionTypeID = 2 and qm.DocumentTypeId= 4 ) t ) PRLMetrics where ISAutomated = 'YES') f ) select * from ( select * , rn1 = row_number() over ( partition by [ObservationGroupId/ QI] order by rn desc ) from rankedrows ) as sub where sub.rn1 = 1
Editor is loading...
Leave a Comment