Thread: Design help-can this be done?

Consider doing a UNION of your tblActual and tblForcast in a View:

Code:

Create View tblAlldata as
Select recDate, sales, 1 as isActual
  From tblActual
UNION ALL
Select recDate, sales, 0 as isActual
  From tblForecast

Then use that view in your massive Select..SUM..Case query. You'll need to add the isActual flag to the Case's When clause:

Code:

sum (case when <3 months ago> and isActual = 1 then sales else null end) as ..
:
sum (case when <next month> and isActual = 0 then forecast else null end) as ..

Ah, true. You can't do this is the Designer. Open a New Query end execute the following:

Code:

Create View vActualAndForcast -- or whatwever you want to name it
As
SELECT dbo.tblHRActual.LocID, dbo.tblHRActual.HRActualDt,
       dbo.tblHRActual.HRActual, 1 as isActual
       from dbo.tblHRActual
union all
SELECT dbo.tblHRForecast.LocID,dbo.tblHRForecast.HRForecastDt,
       dbo.tblHRForecast.HRForecast, 0 as isActual
       from dbo.tblHRForecast

I think the Top 100 percent is superfluous here

Well in Management Studio, you would right click on the view name in the Object Explorer and select Script View As->Alter To->New Query Editor. I imagine there is something equivalent in Query Analyzer.

Then make you changes and execute the script.

Why not just use Month(getdate()) and Year(getdate())?