Test
unknown
sqlserver
a year ago
12 kB
17
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