我有两个表。
包含客户和日期的订单表。数据仓库中的日期维度表。
订单表不包含给定月份中每个日期的活动,但我需要返回一个结果集,用日期和客户填补空白。
例如,我需要这个:
客户日期===============================客户1 2012年1月15日客户1 2012年1月18日客户2 2012年5月1日客户2 2012年8月1日
如下所示:
客户日期============================客户1 2012年1月15日客户1 2012年1月16日客户1 2012年1月17日客户1 2012年1月18日客户2 2012年5月1日客户2 2012年6月1日客户2 2012年7月1日客户2 2012年8月1日
这看起来像是一个左外部联接,但并没有返回预期的结果。这是我正在使用的,但这并不是像预期的那样从日期表中返回每个日期。
SELECT o.customer,
d.fulldate
FROM datetable d
LEFT OUTER JOIN orders o
ON d.fulldate = o.orderdate
WHERE d.calendaryear IN ( 2012 );
问题是您需要所有日期的所有客户。执行left outer join
时,customer字段为NULL。
以下内容通过cross join
设置客户名称和日期来设置驱动程序表:
SELECT driver.customer, driver.fulldate, o.amount
FROM (select d.fulldate, customer
from datetable d cross join
(select customer
from orders
where year(orderdate) in (2012)
) o
where d.calendaryear IN ( 2012 )
) driver LEFT OUTER JOIN
orders o
ON driver.fulldate = o.orderdate and
driver.customer = o.customer;
请注意,此版本假定calendaryear
与year(orderdate)
相同。
您可以使用递归CTE来获取两个日期之间的所有日期,而无需datetable
:
;WITH CTE_MinMax AS
(
SELECT Customer, MIN(DATE) AS MinDate, MAX(DATE) AS MaxDate
FROM dbo.orders
GROUP BY Customer
)
,CTE_Dates AS
(
SELECT Customer, MinDate AS Date
FROM CTE_MinMax
UNION ALL
SELECT c.Customer, DATEADD(DD,1,Date) FROM CTE_Dates c
INNER JOIN CTE_MinMax mm ON c.Customer = mm.Customer
WHERE DATEADD(DD,1,Date) <= mm.MaxDate
)
SELECT c.* , COALESCE(o.Amount, 0)
FROM CTE_Dates c
LEFT JOIN Orders o ON c.Customer = o.Customer AND c.Date = o.Date
ORDER BY Customer, Date
OPTION (MAXRECURSION 0)
SQLFiddle DEMO
这里有一个简单的方法:
SELECT A.Customer,
B.fulldate [Date],
ISNULL(C.Amount,0) Amount
FROM ( SELECT Customer,
MIN([Date]) MinDate,
MAX([Date]) MaxDate
FROM Orders
GROUP BY Customer) A
LEFT JOIN DateTable B
ON B.fulldate BETWEEN A.MinDate AND A.MaxDate
LEFT JOIN Orders C
ON A.Customer = C.Customer
AND B.fulldate = C.[Date]
假设日期表包括一年中的每个日期,您可以使用一个简单的CTE
WITH OrdersCustomerDateBorders AS
(
SELECT CustomerID, MIN(fulldate) AS FirstOrderDate, MAX(fulldate) AS LastOrderDate
FROM orders
GROUP BY customer
)
select o.customer, d.fulldate, ISNULL(o.amount, 0) AS Amount
from orders o
INNER JOIN OrdersCustomerDateBorders OCDB ON OCDB.CustomerID = o.CustomerID
INNER JOIN datetable d ON ON d.fulldate between OCDB.FirstOrderDate AND OCDB.LastOrderDate
WHERE d.calendaryear in (2012);