Thread: Design help-can this be done?

Maybe about a month -- tho is was done in Oracle, Coldfusion and Javascript.

Luck.

I usually get the question "We need a Forecast to Actual comparison web site. How many hours is this going to take?"

Hi W0lffy,

so you mentioned earlier you kept tables separate, can you take alook at screenshot...you mean like the ones on the left vs the one on right, correct?

Also, I will have the Region and Location tables as JMH suggested but won't I need RegionID in the actual/forecast table(s) so I can sum values by region?

Thanks!

hey jmh,
you had this listed:

Code:

Regions
RegionID | Region
 
 
Locations
LocationID | Location
 
 
LocationsInRegions
RegionID | LocationID

but is there anything wrong with doing this?

Code:

Regions
RegionID | Region
 
 
Locations
LocationID | Location | RegionID

You can determine the Region by the Location by doing a JOIN. So there is no need to store the RegionID in the table as well.

Might I add, that I'm against using two separate tables...not that it doesn't work, it's just a preference that tables that hold the same data (same column names) shouldn't need to be duplicated.

You will probably find it easier to store the Year and Month values as integer rather than a single datetime value. It will allow you to join on these fields and it work semantically when referring to the April 2009 estimate.

Value is a reserved word. Probably don't want to use it for a field name (tho I sure have in the past)

Yes, I like the tables on the left. To get to your report then, you would a join something like:

Code:

Select F.Year, F.Month, F.LocationID, F.JobDescID
  F.[Value] as ForeCast, A.[Value] as Actual
From tblForecast F
Left Join tblActual A
 On (F.Year = A.Year AND F.Month = A.Month 
  AND F.LocationID = A.LocationID And F.JobDescID = A.JobDescID)

Wolffy,

On the Actuals and Forecast tables I have the Date(date/time-mm-dd-yyyy) value vs Month and Year, is this ok or should I change it to Month(int) and Year(int)?

I am testing and made view/query in sql but I'm not sure how/what my results should look like to make it easy to display on how I want it on the asp web page? currently shows as for location 1:

Code:

LocID-LocName-JobCatID-JobCat-ActualDt-ActualVal-RevenueDt-RevenueAmt
	1	Australia	1	FieldDD	1/1/2009	11	1/1/2009	1902
	1	Australia	2	FieldLWD	1/1/2009	22	1/1/2009	1902
	1	Australia	3	FieldSDL	1/1/2009	0	1/1/2009	1902
	1	Australia	4	FieldADT	1/1/2009	0	1/1/2009	1902
	1	Australia	5	FieldMLT	1/1/2009	2	1/1/2009	1902
	1	Australia	6	FieldUBA	1/1/2009	0	1/1/2009	1902

This query only contains the actuals atm, haven't yet added data for the Forecast.

After further thought, store the date values as DateTimes, but then use the Month() and Year() functions in the queries and in the reports.

Not sure what your data means -- what do the fields ActualVal and RevenueAmt mean?

For each record will ActualDt and RevenueDt be the same Month and Year? If not, in what Year/Month will it be reported?

Originally Posted by Wolffy

After further thought, store the date values as DateTimes...

Gee, why didn't I say that here? Design help-can this be done?