Public_SQL

avatar
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