Private_SQL

 avatar
user_1767831
sqlserver
15 days ago
6.3 kB
2
Indexable
Never
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
Leave a Comment