previous 28 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

Transact-SQL (2000)

previous 28 weeks

Author

Topic

amreddy2k
Starting Member

4 Posts

Posted - 2004-07-14 : 21:18:56

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
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-14 : 21:43:46

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()

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-15 : 05:37:42

or...


SELECT TOP 28 LEFT(CONVERT(varchar,week_end_date,1),5)
FROM TABLE
ORDER BY week_end_date DESC

amreddy2k
Starting Member

4 Posts

Posted - 2004-07-15 : 08:16:23

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

amreddy2k
Starting Member

4 Posts

Posted - 2004-07-15 : 08:31:39

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

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-15 : 09:32:57

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-)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-15 : 09:49:31

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-15 : 10:23:58

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

amreddy2k
Starting Member

4 Posts

Posted - 2004-07-15 : 12:44:28

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

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-18 : 19:34:37

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...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources