Calculating prior weeks - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Calculating prior weeks

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-26 : 09:15:13

Cassandra writes "After doing several searches and even reading the manual, I am having difficulty getting a start on a certain issue. Basically I am working on a Forecasting report (SQl Server 7.0) and trying to figure out how to get fifteen work weeks back from any given workday. I then plan to attach business sales created or instantiated in each of those weeks. Is there an easy direction or function to use in order to create these weeks? Any help would be so greatly appreciated."

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-26 : 09:35:54

To find the begining of the week

dateadd(dd,datepart(dw,getdate()) * -1,getdate()

To get 15 weeks back
dateadd(ww,-15,dateadd(dd,datepart(dw,getdate()) * -1,getdate())
to get the date without the time
convert(varchar(8),dateadd(ww,-15,dateadd(dd,datepart(dw,getdate()) * -1,getdate()),112)

I'll leave it to you to sort out the actual numbers and any bugs.

If you want to report on each of the weeks easiest to use a temp table and join to that.
declare #weeks (weekdate datetime)

declare @d datetime
select @d = convert(varchar(8),dateadd(ww,-15,dateadd(dd,datepart(dw,getdate()) * -1,getdate()),112)

while @d < getdate()
begin
insert #weeks @d
select @d = dateadd(ww,1,@d)
end

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

CaseyC
Starting Member

8 Posts

Posted - 2002-02-26 : 15:02:22

Thank you dearly for help on the functions. However, I am not able to get them to work correctly in my query analyzer. It says that a dateadd function must accept three arguments.

Anyway, I was curious for the best method to handle these weeks...would it be easiest to make a case for each week (as my table will only have the 15 records for each year) making CASE WHEN <date function week 15> then 'week 15' and so on and so forth to the current week? Please advise.

Casey Cullinan

CaseyC
Starting Member

8 Posts

Posted - 2002-02-26 : 15:21:09

After looking through past forums about date functions, I wanted to clarify my problem more clearly (note the London Labs forum - very similar but I am not understanding how it would relate to my examples).

I have ~90,000 proposals. Each proposal has a date created and a date effective (date policy goes into effect) and a dollar amount for 10 different products offered in the proposal.

The part I am having most difficulty with is trying to find for today's effective date - 15 work weeks of prior to today of create dates. Or another words:

Week 15 (15 weeks before this one) what is the sum for each product created this week and so on down the line all the way to week 1 past (1 week prior to the current).

I have myself so confused with date functions, I am not quite sure how to get a start on this. If anyone could help I would be so completely grateful. Many thanks in advance.

Lou
Yak Posting Veteran

59 Posts

Posted - 2002-02-26 : 17:34:22

Casey,

It sounds like you want to GROUP your data.
You currently have a table like this:
TABLE1
createDate
effDate
productType
amount

To GROUP By, you need to add another column which tells which week, the row belongs to. I don't know how you define a week. You will probably have to play with datepart, dateadd & datediff. Here is example code:
select a.*, week=datepart(week,effDate)
into #temp
from table1 as a

The week column will be 15 (for the 15th week), 1 (for the 1st week) ... You can then query and group by #temp as needed.

HTH

Edited by - lou on 02/26/2002 17:36:06

Edited by - lou on 02/26/2002 17:43:42

CaseyC
Starting Member

8 Posts

Posted - 2002-03-01 : 10:02:43

I understand the whole Group By option, but I am not getting the fifteen weeks previous of work weeks thing. I have tried all of the suggestions with datepart and datediff but my query analyzer keeps telling me that it the dateadd requires 3 arguments. No matter how I rearrange things I come back to the same error. I am terrible with figuring this out, if anyone can give me any more hints I would greatly appreciate it.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-01 : 10:13:27

Can you post the code you're using now, the EXACT error message (copy and paste it), and the point where this error is occurring?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources