SQL Server 2008 SSRS Total/Combine values from two tables to output into a dataset
I have a SSRS report that needs to switch between three different datasets i.e. order types = Consumable, Service and Total.
I have two queries one for Consumable and one for Service as shown below. I tried putting a union between them but it doesn't seem to be totalling the results i.e. adding the two together. How can I do this?
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.datecreat开发者_如何学Goed) <= '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]
Can you try something like this for the combined dataset?
;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]
精彩评论