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 = 1Editor is loading...
Leave a Comment