sql - 在 SQL 中加入日期层次结构表

Question

我正在寻找某种方式将这两个查询加入到一个表中。

这是我的周、月和年数字层次结构的查询。

SQL 查询 #1：

DECLARE @Year AS INT,
        @FirstDateOfYear DATETIME,
        @LastDateOfYear DATETIME

-- You can change @year to any year you desire
SELECT @year = 2018
SELECT @FirstDateOfYear = DATEADD(yyyy, @Year - 1900, 0)
SELECT @LastDateOfYear = DATEADD(yyyy, @Year - 1900 + 1, 0)

-- Creating Query to Prepare Year Data
;WITH cte AS 
(
    SELECT 
        1 AS DayID,
        @FirstDateOfYear AS FromDate,
        DATENAME(dw, @FirstDateOfYear) AS Dayname

    UNION ALL

    SELECT 
        cte.DayID + 1 AS DayID,
        DATEADD(d, 1 ,cte.FromDate),
        DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
    FROM 
        cte
    WHERE 
        DATEADD(d,1,cte.FromDate) < @LastDateOfYear
)
SELECT 
    FromDate AS WeekEnd, 
    Dayname, 
    WeekofYr = DATEPART(WEEK, FromDate), 
    MonthofYr = DATEPART(MONTH, FromDate), 
    YearofYR = DATEPART(YY, FromDate)
--into #temp
FROM 
    CTE
WHERE 
    DayName LIKE 'Saturday' 
    OPTION (MaxRecursion 370)

这是另一个提供不同数据的查询。我想将此结果加入我的 SQL 查询 1 结果

SQL 查询 #2：

select 
    a.ClaimNum,
    featureName, case when Subro = 1 and PD + BI +SIU + LITIGATION = 0 THEN 1 else 0 end as subro_only, PD,BI,Subro,SIU,Litigation, 
    c.FeatureStatus
from 
    (select
         f.ClaimNum,                            
         sum(case when rtrim([FeatureName]) = 'Prop'   then 1  else 0 end ) as  'PD', 
         sum(case when ( rtrim([FeatureName]) = 'CL-BI'   or rtrim([FeatureName]) = 'CLMT.MED' )   then 1  else 0 end )as  'BI',
         sum(case when rtrim([FeatureName]) = 'CL-Subro'   then 1  else 0 end) as  'Subro',
         sum(case when rtrim([FeatureName]) = 'CL-SI'   then 1  else 0 end )as  'SIU',
         sum(case when rtrim([FeatureName]) = 'CL-Litigation'    then 1  else 0 end) as  'Litigation',
         sum(case when rtrim([FeatureName]) = 'Claim'    then 1  else 0 end) as  'Claim'
     from
         [dbo].[Snapshot]  F                                                                                                                                   
     where
         RowIsCurrent = 'Y'
         and isnull(FeatureStatus,'') not in ('Closed','Cancelled','Abandoned','') 
     group by ClaimNum) a   
inner join 
    [dbo].[Snapshot] C on c.ClaimNum = a.ClaimNum and c.FeatureName = 'Claim'  
                       and isnull(c.FeatureStatus, '') not in ('Closed', 'Cancelled', 'Abandoned', '')

SQL 查询 2 的示例数据

  ClaimNum    ModifiedDate     featureName  subro_    PD    BI  Subro   SIU Litigation  FeatureStatus
------------------------------------------------------------------------------------------------------
    2062682     12/25/2015          Claim       1       0   0   1       0       0       Open
    2278808     2/11/2018           Claim       0       0   0   0       0       0       Open
    2278678     2/11/2018           Claim       0       0   0   0       0       0       Open
    2269016     2/11/2018           Claim       1       0   0   1       0       0       Open
    2267544     1/2/2018            Claim       0       0   0   0       0       0       Open
    2268099     1/3/2018            Claim       0       0   0   0       0       0       Open
    2038182     9/4/2015            Claim       0       0   0   0       0       0       Open
    2330116     6/5/2018            Claim       0       0   0   0       0       0       Open
    2330084     6/5/2018            Claim       0       0   0   0       0       0       Open
    2329982     6/5/2018            Claim       0       0   0   0       0       0       Open
    2330218     6/5/2018            Claim       0       0   0   0       0       0       Open
    2330165     6/5/2018            Claim       0       0   0   0       0       0       Open
    2330399     6/5/2018            Claim       0       0   0   0       1       0       Suspended - SIU

我想通过使用修改日期将两个查询的结果加入到单个表中。例如，如果修改日期是 2018 年 6 月 5 日，那么它应该在周末日期是 06/09/2018 的行中。

另外，我只想要当前日历年的结果。我希望我的最终表包含第一个查询的所有列和第二个查询的所有列

提前致谢。

标签： sqlsql-server

Answer 1

您可能想要发挥性能，但您基本上需要将所有数据与您的日历进行 LEFT JOIN。由于您需要所有数据，因此您需要获得比每个数据记录都大的 MIN(Saturday)。您本可以在 AlignedSaturdays 的选择中完成最后一部分，但是，为了便于阅读，我将其拆分并重新加入了星期六表。

;WITH cte AS 
(
    SELECT 
        1 AS DayID,
        @FirstDateOfYear AS FromDate,
        DATENAME(dw, @FirstDateOfYear) AS Dayname

    UNION ALL

    SELECT 
        cte.DayID + 1 AS DayID,
        DATEADD(d, 1 ,cte.FromDate),
        DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
    FROM 
        cte
    WHERE 
        DATEADD(d,1,cte.FromDate) < @LastDateOfYear
)
,Saturdays AS
(
    SELECT 
        FromDate AS WeekEnd, 
        Dayname, 
        WeekofYr = DATEPART(WEEK, FromDate), 
        MonthofYr = DATEPART(MONTH, FromDate), 
        YearofYR = DATEPART(YY, FromDate)
    --into #temp
    FROM 
        CTE
    WHERE 
        DayName LIKE 'Saturday' 
)  
,AllData AS
(
    select 
        a.ClaimNum,
        c.ModifiedDate     
        featureName, case when Subro = 1 and PD + BI +SIU + LITIGATION = 0 THEN 1 else 0 end as subro_only, PD,BI,Subro,SIU,Litigation, 
        c.FeatureStatus
    from 
        (select
             f.ClaimNum,                            
             sum(case when rtrim([FeatureName]) = 'Prop'   then 1  else 0 end ) as  'PD', 
             sum(case when ( rtrim([FeatureName]) = 'CL-BI'   or rtrim([FeatureName]) = 'CLMT.MED' )   then 1  else 0 end )as  'BI',
             sum(case when rtrim([FeatureName]) = 'CL-Subro'   then 1  else 0 end) as  'Subro',
             sum(case when rtrim([FeatureName]) = 'CL-SI'   then 1  else 0 end )as  'SIU',
             sum(case when rtrim([FeatureName]) = 'CL-Litigation'    then 1  else 0 end) as  'Litigation',
             sum(case when rtrim([FeatureName]) = 'Claim'    then 1  else 0 end) as  'Claim'
         from
             [dbo].[Snapshot]  F                                                                                                                                   
         where
             RowIsCurrent = 'Y'
             and isnull(FeatureStatus,'') not in ('Closed','Cancelled','Abandoned','') 
         group by ClaimNum) a   
    inner join 
        [dbo].[Snapshot] C on c.ClaimNum = a.ClaimNum and c.FeatureName = 'Claim'  
                           and isnull(c.FeatureStatus, '') not in ('Closed', 'Cancelled', 'Abandoned', '')
)
,AlignedSaturdays AS
(
    SELECT 
        ClaimNum,
        featureName,
        FeatureStatus,
        ModifiedDate,
        NextSaturday = MAX(Saturdays.FromDate),
    FROM
        Saturdays
        LEFT OUTER JOIN AllData ON AllData.ModifiedDate < Saturdays.FromDate
    GROUP BY
        ClaimNum,
        featureName,
        FeatureStatus,
        ModifiedDate
)
SELECT
    *
FROM
    AlignedSaturdays
    INNER JOIN Saturdays ON Saturdays.FromDate = AlignedSaturdays.NextSaturday
 OPTION (MaxRecursion 370)