sql - SQL Server 查询日期

Question

我有一个包含两列 start_date 和 End_date 的主表，我需要加入 Date dim 表，并且有两列作为 start_date 和 end_date。但我无法得到预期的结果。

对于某些记录，我得到了重复的结果。

有人可以帮忙吗？

主表：

日期表：

这是我的查询：

SELECT DISTINCT
    Invoice_Credit_Fact.Inv_Crd_key,
    Invoice_Credit_Fact.Customer_Key, 
    Invoice_Credit_Fact.Period_Key, 
    Invoice_Credit_Fact.Product_Key, 
    Invoice_Credit_Fact.SalesPerson_Key, 
    Invoice_Credit_Fact.Inv_Crd_No, 
    Invoice_Credit_Fact.Your_Ref,
    Invoice_Credit_Fact.Alt_Ref, 
    Invoice_Credit_Fact.Order_No, 
    Invoice_Credit_Fact.Bid_No, 
    Invoice_Credit_Fact.Creator, 
    Invoice_Credit_Fact.Support_Start_Date,
    Invoice_Credit_Fact.Support_End_Date,
    Invoice_Credit_Fact.MLA_Number,
    Invoice_Credit_Fact.New_Renew_Cancelled, 
    Invoice_Credit_Fact.Qty,
    Invoice_Credit_Fact.Unit_Price, 
    Invoice_Credit_Fact.Total_Value, 
    Invoice_Credit_Fact.Cost, 
    Invoice_Credit_Fact.Cogs_Adj, 
    Invoice_Credit_Fact.Margin,
    Period_Ref_Start.Year_Num as Start_Year,
    Period_Ref_Start.Month_Num as Start_Month,
    Period_Ref_Start.Period_Key as Supp_start_key,
    Period_Ref_End.Year_Num as End_Year,
    Period_Ref_End.Month_Num as End_Month,
    Period_Ref_End.Period_Key as Supp_end_key,
    Invoice_credit_fact.description
FROM
    Invoice_Credit_Fact,
    Product_Ref,
    Period_Ref Period_Ref_Start,
    Period_Ref Period_Ref_End
WHERE 
    Invoice_Credit_Fact.Product_Key = Product_Ref.Product_Key   
    AND DATEADD(day, 5, ISNULL(Invoice_Credit_Fact.Support_Start_Date, '9999-01-01')) 
            BETWEEN Period_Ref_Start.Start_Date AND Period_Ref_Start.End_Date
    AND DATEADD(day, -5, ISNULL(Invoice_Credit_Fact.Support_End_Date, '9999-01-01')) 
            BETWEEN Period_Ref_End.Start_Date AND Period_Ref_End.End_Date
ORDER BY 
    Invoice_Credit_Fact.Inv_Crd_No

结果包含重复的行。请告诉我如何消除查询中的重复项

标签： sqlsql-server

Answer 1

您正在获取重复的数据，因为在 Maintable 中，该列SUPPORT_START_DATE是Star_date第二个表中的列，您可以通过PERIOD_KEY