SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 previous 28 weeks
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

amreddy2k
Starting Member

4 Posts

Posted - 07/14/2004 :  21:18:56  Show Profile  Reply with Quote
Hi Friends,

I am using Sqlserver 2000, i have a column "Week_End_Date" that stores all the week end dates like 07/07/2004,07/14/2004.
i want a query to get previous 28 weeks from current week. and i need this week format like mm/dd(month/day i.e 07/07, 07/14) i need the following output.
07/14
07/07
06/30
06/23 .................upto prevous 28 week

Regards, Amr

timmy
Flowing Fount of Yak Knowledge

Australia
1242 Posts

Posted - 07/14/2004 :  21:43:46  Show Profile  Visit timmy's Homepage  Reply with Quote

SELECT Convert(varchar, month(Week_End_Date)) + '/' + Convert(varchar, day(Week_End_Date))
FROM table
WHERE Week_End_Date BETWEEN DateAdd(d, -196, GetDate()) and GetDate()



Go to Top of Page

JasonGoff
Posting Yak Master

United Kingdom
158 Posts

Posted - 07/15/2004 :  05:37:42  Show Profile  Reply with Quote
or...

SELECT TOP 28 LEFT(CONVERT(varchar,week_end_date,1),5)
FROM TABLE
ORDER BY week_end_date DESC
Go to Top of Page

amreddy2k
Starting Member

4 Posts

Posted - 07/15/2004 :  08:16:23  Show Profile  Reply with Quote
Hi

Thanks for your quick response, I have the week format(mm/dd) from Week_end_date(07/14/2004).

I want to get 28 previous weeks in the formartof week(mm/dd).


Regards, Amr
Go to Top of Page

amreddy2k
Starting Member

4 Posts

Posted - 07/15/2004 :  08:31:39  Show Profile  Reply with Quote
Hi jasongof

Thanks for your response

I tried your code its working but I am not getting the latest 28 weeks data, If i use top 28 it's giving the 28 weeks but not the previous 28 weeks. any help would be appreciated

Regards, Amr
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/15/2004 :  09:32:57  Show Profile  Reply with Quote
I'm not sure I understand....

Do you want to figure out the end dates?


DECLARE @x datetime, @y int, @z int
SELECT @x = '07/24/2004', @y = -7, @z = 1

DECLARE @d TABLE(prev_dates datetime)

WHILE @z < 28
  BEGIN
	INSERT INTO @d(prev_dates) SELECT DATEADD(d,@y,@x)
	SELECT @y = @y - 7, @z = @z + 1
  END
SELECT * FROM @d


Or do you want to find that specific day, or what about the days in between...


I'm so confused...



Brett

8-)
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 07/15/2004 :  09:49:31  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
Does this work for you??


Declare @seq table (n int)
Insert Into @Seq
Select 0 Union All Select 1 Union All Select 2 Union All Select 3 Union All Select 4 Union All Select 5 Union All Select 6 Union All Select 7 Union All Select 8 Union All Select 9 Union All Select 10 Union All 
Select 11 Union All Select 12 Union All Select 13 Union All Select 14 Union All Select 15 Union All Select 16 Union All Select 17 Union All Select 18 Union All Select 19 Union All Select 20 Union All
Select 21 Union All Select 22 Union All Select 23 Union All Select 24 Union All Select 25 Union All Select 26 Union All Select 27 Union All Select 28



Declare @periodEnd datetime

Set @periodEnd = '7/14/2004'

Select periodEnd = left(convert(nvarchar,dateAdd(dy,-7*n,@periodEnd),101),5) From @seq


If you don't want the current period then remove the 'Select 0 Union All' near the top.

Corey
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 07/15/2004 :  10:23:58  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
you have not given us enough info. you have a column called "Week_end_Date" which is great, but in what table? what is the PK of that table? what kind of data are you trying to return? do you just want a list of week-ending dates, or do you want all data within that timeperiod?

how about a CREATE TABLE statement, some sample data, and what you would like to have returned.

is it as simple as:

select * from
yourtable
where Week_Ending_Date between datediff(w, 28, getdate()) and getdate()

??

- Jeff
Go to Top of Page

amreddy2k
Starting Member

4 Posts

Posted - 07/15/2004 :  12:44:28  Show Profile  Reply with Quote
Hi JSmith

Thanks for your response.

Here is the sample code.
2003-09-03 00:00:00
2003-09-24 00:00:00
2003-10-01 00:00:00
2003-10-15 00:00:00
2003-10-22 00:00:00
2004-02-04 00:00:00
2004-02-11 00:00:00
2004-03-17 00:00:00
2004-03-31 00:00:00
2004-04-14 00:00:00
2004-04-21 00:00:00
2004-05-05 00:00:00
2004-06-23 00:00:00
2004-06-30 00:00:00
2004-07-07 00:00:00
2004-07-14 00:00:00
2004-08-04 00:00:00

these are all week end dates . I just want to display only previous 28 week end dates from current week end date.for example(
2004-07-14
2004-07-07
2004-06-30
2004-06-23
2004-05-05..................like upto previous 28 week end dates.

regards, Amr

Go to Top of Page

timmy
Flowing Fount of Yak Knowledge

Australia
1242 Posts

Posted - 07/18/2004 :  19:34:37  Show Profile  Visit timmy's Homepage  Reply with Quote
Amr,

You can calculate the week-end date for any date by using the following formula:

SET @weekEndDate = dateadd(d, 6-DatePart(w, @d), @d)

where: @d is the date in question and
6 is the day of the week (Friday in this case). 5=Thur, 4=Wed, etc

Plug this back into your formula and see how you go...



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000