SQL Server 2008 SSRS要将两个表中的总/组合值输出到数据集中
问题描述:
我有一个需要在三个不同数据集之间切换的SSRS报告,即订单类型=消耗品,服务和总计。SQL Server 2008 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]
答
你可以尝试这样的组合数据集?
;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]
这看起来不错...我会用UNION ALL得到死亡吗?这会抛出总计吗? – jhowe 2011-05-17 15:29:44
不确定...尝试一下,看看它是你在找什么。有几点需要帮助:(1)结果中每个源和日期只有一行,(2)即使orderid与特定的serviceid匹配,UNION ALL也会对每行进行计数。 (3)将UNION ALL更改为UNION以消除重复项(即,当orderid和serviceid碰巧匹配,并且您只需要对该编号进行一次计算) – 2011-05-17 17:07:02
我认为这会解决我的问题,谢谢......现在我必须创建一个新问题弄清楚我将如何将3个不同的查询合并到一个报告中! – jhowe 2011-05-18 08:32:38