如何修改SQL查询以输出季度末日期对应的数据

Got it, let's tweak your SQL to pull only 2022 quarter-end data (adjusted to the previous business day if the quarter falls on a weekend) instead of month-end data. Here's the full modified code, followed by key changes explained:

DECLARE @LegalName AS VARCHAR(255) = LOWER ('xx')
DECLARE @IndexId AS VARCHAR (255) ='xx'-------------provide the index legal name

; with CTE as (
SELECT DISTINCT ph.[Date],ph.IndexShares, EDD.[Close], EDD.[LocalCurrency], dsm.PerformanceId, ds.SecurityName COLLATE Latin1_General_BIN AS SecurityName, ds.TICKER COLLATE Latin1_General_BIN AS Ticker, icm.SectorName COLLATE Latin1_General_BIN AS SectorName,icm.IndustryName, ds.ISIN COLLATE Latin1_General_BIN AS ISIN, coc.MsCountry,

        ph.MarketValue, ph.ThirdPartyId, ds.SEDOL COLLATE Latin1_General_BIN AS SEDOL, ds.MIC
        
            FROM TimeSeries..PortfolioHoldings ph               
                INNER JOIN TimeSeries.dbo.EquityDailyData AS EDD
                         ON PH.ThirdPartyId = EDD.ThirdPartyId
                          AND PH.[Date] = EDD.[Date]                
                LEFT JOIN StagingData..DMA_DimSecurityMapping dsm
                        ON dsm.ThirdPartyId  = ph.ThirdPartyId 
                        AND ph.Date BETWEEN dsm.StartDate AND dsm.EndDate
                LEFT JOIN StagingData.dbo.DMA_DimCompanyCoC coc
                        ON dsm.CompanyId=coc.CompanyId  
                        AND ph.Date BETWEEN coc.StartDate AND coc.EndDate
                LEFT JOIN StagingData.dbo.DMA_DimCompanyIndustry dci
                        ON dsm.CompanyId= dci.CompanyId
                        AND ph.Date BETWEEN dci.StartDate AND dci.EndDate
                LEFT JOIN IDW.[dbo].[GECSIndustryMapping]icm
                        ON dci.IndustryId= icm.IndustryCode
                        AND ph.Date BETWEEN dsm.StartDate AND dsm.EndDate


                LEFT JOIN StagingData.dbo.DMA_DimSecurity ds
                        ON dsm.PerformanceId = ds.PerformanceId
                        AND ph.Date BETWEEN ds.StartDate AND ds.EndDate
                WHERE ph.PortfolioId in
                
                                (
                                    SELECT CAST (PortfolioId AS VARCHAR (16)) COLLATE Latin1_General_BIN
                                    FROM MSTAR_INDEX..Indexidentifier 
                                    WHERE Name = @LegalName
                                    AND IndexStatus = 1
                                )
                -- 关键修改：替换月度日期筛选为季度末工作日筛选
                AND ph.[Date] IN (
                    SELECT 
                        CASE 
                            -- 判断是否为周末（兼容SQL Server不同的星期起始设置）
                            WHEN DATEPART(weekday, q_end) IN (
                                CASE WHEN @@DATEFIRST = 7 THEN 1 ELSE 7 END, -- 周日
                                CASE WHEN @@DATEFIRST = 7 THEN 7 ELSE 1 END  -- 周六
                            )
                            THEN DATEADD(day, 
                                CASE DATEPART(weekday, q_end) 
                                    WHEN CASE WHEN @@DATEFIRST = 7 THEN 1 ELSE 7 END THEN -1 -- 周日→周五
                                    WHEN CASE WHEN @@DATEFIRST = 7 THEN 7 ELSE 1 END THEN -2 -- 周六→周五
                                END, 
                                q_end)
                            ELSE q_end
                        END AS adjusted_q_end
                    FROM (
                        -- 生成2022年四个季度的基准结束日期
                        SELECT '2022-03-31' AS q_end UNION ALL
                        SELECT '2022-06-30' UNION ALL
                        SELECT '2022-09-30' UNION ALL
                        SELECT '2022-12-31' -- 若业务要求Q4用12/30，替换为'2022-12-30'即可
                    ) AS quarters
                    -- 确保调整后的日期在IndexLevel表中有对应数据
                    WHERE EXISTS (SELECT 1 FROM Timeseries.dbo.IndexLevel WHERE IndexId = @IndexId AND [Date] = adjusted_q_end)
                )

)

select t.[Date],SectorName, IndustryName, SUM([Weight]) as Weights
from (select DISTINCT *, 100*CAST (c.MarketValue as FLoat)/sum (c.MarketValue) over (Partition by c.[Date]) as [Weight]
     FROM CTE as c
    ) as t

GROUP BY t.[Date],SectorName,IndustryName

Key Changes Explained: #

• Quarter-End Date Base: We explicitly list the 4 natural quarter-end dates for 2022. If your business rule actually requires Q4 to use 2022-12-30 instead of 2022-12-31, just swap that value in the subquery.
• Business Day Adjustment: The CASE statement uses @@DATEFIRST to handle different SQL Server settings for the start of the week (some use Sunday as day 1, others Monday). It checks if the quarter-end is a Saturday or Sunday, then subtracts 1 or 2 days to get the preceding business day.
• Existence Validation: We keep the check against IndexLevel to ensure we only pull dates that actually have data, matching the core logic of your original query.

内容的提问来源于stack exchange，提问作者Grishin Gopalkrishnan