UNION QUERY REPORT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Analysis Server and Reporting Services (2012)

UNION QUERY REPORT

Author

Topic

maunishq
Yak Posting Veteran

71 Posts

Posted - 2014-07-01 : 04:00:24

Hi All,
I'll describe my situation and problem first.
I have two tables. Actual and Forecast.
Actual table:
Month KG
Mar 30
Apr 40
May 50

Forecast table:
Month KG
Apr 35
May 30
Jun 40
Jul 50

I want to generate a report which pulls the Actual data if exist otherwise the forecast data. So the output should be something like this:
Mar Apr May | Jun Jul
30 40 50 | 40 50

So here Mar, Apr, May pulls data from Actual and Jun, Jul pulls data from Forecast.

I wrote a query using union.

SELECT SUM(....)
FROM (
SELECT ....
FROM Actual table
WHERE Monthnum <= MONTH(Getdate())
UNION
SELECT ....
FROM Forecast table
WHERE Monthnum > MONTH(Getdate())
)
GROUP BY ....
ORDER BY ....

SSMS pulls correct data:
Mar 30
Apr 40
May 50
Jun 40
Jul 50

PROBLEM:

On creating a dataset using the same query results in strange report:
Mar Apr May Jun Jul
30 40 50
Mar Apr May Jun Jul
- - - 40 50

It seems they are grouping the Actual and Forecast in different rows which I do not want.
I want them together.

===========================================================
Can anybody suggest me a better way to achieve the desired result?
Thanks in anticipation.

Rgds.

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-28 : 09:30:05

CREATE TABLE #Actual
(
ID int identity(1,1),
MonthAbrev varchar(10),
MonthValue money

)

CREATE TABLE #Forecast
(
ID int identity(1,1),
MonthAbrev varchar(10),
MonthValue money

)

INSERT INTO #Actual
VALUES('Mar',30.0),('Apr',40.0),('May',50)

INSERT INTO #Forecast
VALUES('Apr',35.0),('May',30.0),('Jun',40),('Jul',50)

;With MyResults
as
(
SELECT MonthAbrev,MonthValue
FROM #Actual A
UNION
SELECT MonthAbrev,MonthValue
FROM #Forecast F
WHERE NOT EXISTS
( SELECT 1 FROM #Actual Ai
WHERE Ai.MonthAbrev = F.MonthAbrev
)
)

SELECT MonthAbrev,SUM(MonthValue) FROM MyResults
GROUP BY MonthAbrev

-- if you do not want to use a CTE
SELECT MonthAbrev,SUM(MonthValue) FROM
(
SELECT MonthAbrev,MonthValue
FROM #Actual A
UNION
SELECT MonthAbrev,MonthValue
FROM #Forecast F
WHERE NOT EXISTS
( SELECT 1 FROM #Actual Ai
WHERE Ai.MonthAbrev = F.MonthAbrev
)
) MyResults
GROUP BY MonthAbrev

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources