TSQL QUERY Using UNION - 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

Transact-SQL (2012)

TSQL QUERY Using UNION

Author

Topic

maunishq
Yak Posting Veteran

71 Posts

Posted - 2014-07-01 : 04:02:15

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.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-07-01 : 05:00:09

[code]
DECLARE @dtSysDateTime AS DATETIME2 = '2014-05-30'

;with ActualTable
AS
(SELECT 3 AS [MonthNum], 'Mar' AS [MONTH],30 AS KG UNION ALL
SELECT 4,'Apr' , 40 UNION ALL
SELECT 5,'May' , 50)
,ForecastTable
AS
(SELECT 4 AS [MonthNum],'Apr' AS [Month], 35 AS KG UNION ALL
SELECT 5,'May' ,30 UNION ALL
SELECT 6,'Jun' ,40 UNION ALL
SELECT 7,'Jul' ,50)

,FinalTable
AS
(
SELECT
[Month]
,KG
FROM
ActualTable
WHERE MonthNum<=Month(@dtSysDateTime)
UNION ALL
SELECT
[Month]
,KG
FROM
ForecastTable
WHERE MonthNum > Month(@dtSysDateTime)
)

SELECT
*
FROM
FinalTable AS FT
PIVOT (SUM(KG) FOR [Month] IN ([Mar],[Apr],[May],[Jun],[Jul])) AS PV
[/code]

output:
[code]
Mar Apr May Jun Jul
30 40 50 40 50
[/code]

sabinWeb MCP

maunishq
Yak Posting Veteran

71 Posts

Posted - 2014-07-02 : 10:28:19

Thanks Sabin.
I'll check and see if this works for me.

=======================
Not an Expert, Just a learner.
!_(M)_!

maunishq
Yak Posting Veteran

71 Posts

Posted - 2014-07-02 : 12:29:06

Thanks, It gave me a right direction. I didn't resolve the problem completely, but got some hands for sure.
Thanks a lot.

=======================
Not an Expert, Just a learner.
!_(M)_!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources