Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sun-Sat Date calculation

Status
Not open for further replies.

gkrenton

MIS
Jul 2, 2003
151
US
I'm sure I'm missing the obvious reading through faq 701-4230 & the numerous other date posts here but for the life of me my brain isn't getting it.
I have a table where all 52 weeks of the year are accounted for Sun-Sat for expample. In the table there is a record for week 4
which is defined by 2 fields
begweek :02/25/2007
endofweek :03/03/2007.
I'm trying to pull data from a sales table for that range only. The sales table has the date information in it but I'm stumped as to how to basically do something that says

Sum up the sales when the sales date is between beg & end of week 4.

I'm sure this is simple but it's got my brain frazzled.

Thanks in advance.

gina



 
You will want to join to your week table like this:

Code:
....
from myTable
inner join 
(
select * from myWeekTable
where week = [WeekNum]
) myWeekTable
on myTable.DateCol >= myWeekTable.StartDateCol
and myTable.DateCol <= myWeekTable.EndDateCol

Hope it helps,
Alex

Ignorance of certain subjects is a great part of wisdom
 
Glad it helped :)

Ignorance of certain subjects is a great part of wisdom
 
There are a couple of other approaches to this problem that may interest you.

A boring but efficient solution is to create a table called "weekCrosswalk" with the key field "date1" and the field "weekstartDate". Make one entry for each day of the year.

A convuleted but interesting approach uses nested function calls. The weekday() function accepts a date and returns an integer 1-7. The dateadd() function can add or subtract days from a date. You can use them in tandem to find the weekstartDate for any date. Here is the syntax:

weekStartDate: DateAdd('d',(Weekday([saleDate])*-1)+1,[saleDate])
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top