根据销售人员和销售日期连接Sales表和Sales Region表

Question

我有一个销售表，但它不包括销售的地区。我还有一个基于区域和日期的销售人员分配表。我想要加入这些表，这样我就可以获取区域并将其包含到我的sales表中。

我在销售人员的首字母(键)上加入，但我还想将销售日期与区域开始和区域停止进行比较，以加入正确的区域。我尝试使用开始和停止之间的销售日期，但这不起作用，因为如果它们当前仍在区域中，它将提供空值。

谢谢你的帮助，布伦特

IF NOT EXISTS (
    select * from sysobjects where name='sales' and xtype='U'
)CREATE TABLE sales (
    [Sale_Date] DATETIME,
    [Sales_Person] NVARCHAR(3),
    [Sales_Amount] INT,
    [Region] INT
);
INSERT INTO sales VALUES
    ('2016-07-01 00:00:00',N'MDD',152,NULL),
    ('2016-09-21 00:00:00',N'MDD',278,NULL),
    ('2018-03-01 00:00:00',N'STE',385,NULL),
    ('2018-04-01 00:00:00',N'MDD',426,NULL),
    ('2019-02-25 00:00:00',N'MDD',224,NULL),
    ('2020-02-15 00:00:00',N'STE',261,NULL),
    ('2020-03-01 00:00:00',N'STE',480,NULL),
    ('2020-06-05 00:00:00',N'BBB',245,NULL),
    ('2020-07-05 00:00:00',N'BBB',178,NULL);


IF NOT EXISTS (
    select * from sysobjects where name='SalesPersonAssignment' and xtype='U'
) CREATE TABLE SalesPersonAssignment (
    [sales_person] NVARCHAR(4),
    [Region_ID] INT,
    [Region_Name] NVARCHAR(6),
    [Region_Start_Date] DATETIME,
    [Region_Stop_Date] NVARCHAR(10)
);
INSERT INTO SalesPersonAssignment VALUES
    (N'MDD',2,N'North ','2015-01-05 00:00:00',N'12/31/2017'),
    (N'MDD',6,N'West','2018-01-01 00:00:00',N'NULL'),
    (N'STE ',6,N'West','2018-10-02 00:00:00',N'12/31/2019'),
    (N'STE',2,N'North ','2020-01-01 00:00:00',N'NULL'),
    (N'BBB',1,N'South','2019-01-01 00:00:00',N'NULL');



Select s.Sale_Date, s.Sales_Amount, s.Sales_Person, spa.Region_Name
FROM sales s LEFT OUTER JOIN SalesPersonAssignment spa ON s.Sales_Person = spa.sales_person
--join based on the sales date and region's start/stop date of the sales person

Answer 1

您正在将日期存储为字符串。并且时间部分是不必要的。更好的方法是：

CREATE TABLE SalesPersonAssignment (
    [sales_person] NVARCHAR(4),
    [Region_ID] INT,
    [Region_Name] NVARCHAR(6),
    [Region_Start_Date] DATE,
    [Region_Stop_Date] DATE
);

INSERT INTO SalesPersonAssignment VALUES
    (N'MDD',2,N'North ','2015-01-05','2017-12-31'),
    (N'MDD',6,N'West','2018-01-01', NULL),
    (N'STE ',6,N'West','2018-10-02', '2019-12-31'),
    (N'STE',2,N'North ','2020-01-01', NULL),
    (N'BBB',1,N'South','2019-01-01', NULL);

只需在ON子句中包含这些条件：

SELECT s.Sale_Date, s.Sales_Amount, s.Sales_Person, spa.Region_Name
FROM sales s LEFT OUTER JOIN
     SalesPersonAssignment spa
     ON s.Sales_Person = spa.sales_person AND
        s.Sale_Date >= spa.Region_Start_Date AND
        (s.Sale_Date <= spa.Region_End_Date OR spa.Region_End_Date IS NULL);