Thread: Design help-can this be done?

Originally Posted by Wolffy

@R
You data is probably going to be returned as rows and you will needs to 'pivot' the data into columns. For example consider the following ABLE data:

Code:

Prod Year Month Sales
ABLE 2008 11 223
ABLE 2008 12 252
ABLE 2009 1  125
ABLE 2009 2  435

Which you will no doubt want to display Excel like:

Code:

Prod NOV08 DEC08 JAN09 FEB09
ABLE   223   252   125   435

SQL2005 has the PIVOT verb, but you don't have that so it's a bit more complicated. Probably going to require some dynamic SQL in a stored proc. The SQL for the above would be something like

Code:

Select Prod
      sum(case when Year = 2008 and Month = 11 then Sales else null end) as 'Nov08',
      sum(case when Year = 2008 and Month = 12 then Sales else null end) as 'Dec09',
      sum(case when Year = 2009 and Month = 1 then Sales else null end) as 'Jan09',
      sum(case when Year = 2009 and Month = 2 then Sales else null end) as 'Feb09'
From myTable

Ok, don't have 2005 but I'll give that a try in sql2000 with my query/view. oh oh...then this means each time a new month is added I have to change/update the view/query?

Originally Posted by Wolffy

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?

in the actual table, actualval is just the value (int4) that is actual # for the month but there are multiple job categories for each location so each will have its own. I have built the forecast table which will have a forecastvalue also but just want to get started with the actual first to see if i can doit.

each location will have approx 19 jobcategories for each month, so yes the date(month/year)field will be repeated 19 times.
the RevenueDt stands for Revenue date, this exists in my tblRevenue...for this there will only be 1 revenue value for each location each month.

need some help here...

I have this but getting error about incorrect syntax near 'then'. I've highlighted in blue, line 3 where the issue is.

Code:

SELECT     dbo.tblHRLocation.LocID, dbo.tblHRLocation.Location, dbo.tblJobCategory.JobDescID, dbo.tblJobCategory.JobDesc, dbo.tblHRActual.HRActualDt, 
                      dbo.tblHRActual.HRActual,
sum(case when Month(dbo.tblHRActual.HRActualDt) = Month(dateadd(mm, -3, getdate()) then dbo.tblHRActual.HRActual else null) as ThreeMonthsAgo
FROM         dbo.tblHRActual INNER JOIN
                      dbo.tblHRRevenue ON dbo.tblHRActual.HRActualDt = dbo.tblHRRevenue.RevenueDt LEFT OUTER JOIN
                      dbo.tblHRLocation ON dbo.tblHRRevenue.LocID = dbo.tblHRLocation.LocID AND dbo.tblHRActual.LocID = dbo.tblHRLocation.LocID LEFT OUTER JOIN
                      dbo.tblJobCategory ON dbo.tblHRActual.JobDescID = dbo.tblJobCategory.JobDescID
WHERE     (dbo.tblHRLocation.LocID = 1)

okies...Thank you very much! after that needed group by...got that added now i can see results. Gonna go add the rest of the months.

w00t!

Originally Posted by Wolffy

Worry about setting the column names then in the ASP page rather than the query.

A little confused about this statement above...

I've got the 3 actual month query setup as in your example and its looking good.

Ok, I didn't know if you were trying to tell me only do so much in sql and do most in asp...got ya now.

I've been trying to put it all together in analyzer but I'm having trouble when
I bring in the tblForecast fields. My question is, should I be trying to put
together here or leave separate?...ex, how i want to be displayed:

Code:

fieldcategoriesnames - last 3mo actuals - current mo and 9 mos. out
name1 - jun09 - jul09 - aug09 - sep09 - oct09 -nov09...etc.