SQL Server 2008 SSRS要将两个表中的总/组合值输出到数据集中

Question

我有一个需要在三个不同数据集之间切换的SSRS报告，即订单类型=消耗品，服务和总计。

我有两个查询一个消耗品和一个服务，如下所示。我试图在他们之间建立一个联盟，但似乎并没有总结结果，即将两者加在一起。我怎样才能做到这一点？

SELECT COUNT(orderheader.orderid)    AS [Consumable Order Amount], 
     CONVERT(DATE, orderheader.datecreated) AS [Date], 
     CASE 
     WHEN orderheader.webref = '' THEN 'Call Centre' 
     ELSE 'Web' 
     END         AS [Order Type] 
FROM orderheader 
WHERE CONVERT(DATE, orderheader.datecreated) >= '21 February 2011' 
     AND CONVERT(DATE, orderheader.datecreated) <= '20 March 2011' 
GROUP BY CONVERT(DATE, orderheader.datecreated), 
      CASE 
      WHEN orderheader.webref = '' THEN 'Call Centre' 
      ELSE 'Web' 
      END 

SELECT COUNT(serviceid)   AS [Service Order Amount], 
     CONVERT(DATE, datecreated) AS [Date], 
     CASE 
     WHEN serviceorder.webref = '' THEN 'Call Centre' 
     ELSE 'Web' 
     END      AS SOURCE 
FROM serviceorder 
WHERE (CONVERT(DATE, datecreated) >= '21 February 2011') 
     AND (CONVERT(DATE, datecreated) <= '20 March 2011') 
GROUP BY CONVERT(DATE, datecreated), 
      CASE 
      WHEN serviceorder.webref = '' THEN 'Call Centre' 
      ELSE 'Web' 
      END 
ORDER BY [Date] 

Answer 1

你可以尝试这样的组合数据集？

;WITH Combined AS 
(
    SELECT orderid AS id, 
     datecreated as [datecreated], 
     webref as [webref] 
    FROM orderheader 
    UNION ALL 
    SELECT serviceid AS id, 
     datecreated as [datecreated], 
     webref as [webref] 
    FROM serviceorder 
) 
SELECT COUNT(id) AS [Service Order Amount], 
     CONVERT(DATE, datecreated) AS [Date], 
     CASE 
     WHEN webref = '' THEN 'Call Centre' 
     ELSE 'Web' 
     END AS SOURCE 
FROM Combined 
WHERE (CONVERT(DATE, datecreated) >= '21 February 2011') 
     AND (CONVERT(DATE, datecreated) <= '20 March 2011') 
GROUP BY CONVERT(DATE, datecreated), 
      CASE 
      WHEN webref = '' THEN 'Call Centre' 
      ELSE 'Web' 
      END 
ORDER BY [Date]