Help With Design

[UltraSmooth]

I have a table that contains sales summaries by item and date period. The date field is actually a 7-digit Clarion representation of a date (ie., 74563). The sales summaries are saved for each week/month/year period. I need to write a sales query off this table that will compare sales between years so I will have to join the sales table with itself to get records for both years. The problem I'm facing is how to go about the join since the date field is not easy to work with.

What I'm thinking I'll have to do is to create and populate a table that contains the clarion date field from the sales table and actual fiscal date information such as date,fiscalweek,fiscalmonth,fiscalyear, etc.

This would allow me to join the sales table to the date table to get the correct date information for the join. Does this seem like a good solution to this problem?

I'm assuming my query would have to look something like this:

Select columns...
From
(Select columns from Sales Join Date_Tbl
on Sales.datefield=Date_Tbl.datefield
where fiscalweek=1 and fiscalyear=2009) as SalesA
join
(Select columns from Sales Join Date_Tbl
on Sales.datefield=Date_Tbl.datefield
where fiscalweek=1 and fiscalyear=2008) as SalesB
on SalesA.Item=SalesB.Item and SalesA.fiscalweek=SalesB.fiscalweek

If the tables had proper indexing does this seem like an efficient way to go about the query?

I can't think of any other way to go about this. Any input would be greatly appreciate!

 

[r937]

yes, the fiscal calendar table is an excellent idea


r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[UltraSmooth]

Thanks for your reply! My only worry is that the speed of the join on two subqueries will be slow...I'm assuming that no indexes can be used for the join between SalesA and SalesB and the sales table has hundreds of thousands of records. I guess when I implement it I'll find out