| Author |
 Topic  |
|
|
amreddy2k
Starting Member
4 Posts |
 Posted - 07/14/2004 : 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
Flowing Fount of Yak Knowledge
Australia
1242 Posts |
Posted - 07/14/2004 : 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
United Kingdom
158 Posts |
Posted - 07/15/2004 : 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 - 07/15/2004 : 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 - 07/15/2004 : 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 - 07/15/2004 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 07/15/2004 : 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
USA
7423 Posts |
Posted - 07/15/2004 : 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 - 07/15/2004 : 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
Flowing Fount of Yak Knowledge
Australia
1242 Posts |
Posted - 07/18/2004 : 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...
|
|
|
| |
Topic |
|
previous 28 weeks
