February 24, 2012 at 4:07 am
Hi Team
i have a data of year 2012 and table structure is (SN ,Name ,DATE,Error) and i need data according to
1 Jan 2012 to 5 Jan 2012- Week 1
6 Jan 2012 to 12 Jan 2012 week 2
13 Jan 2012 to 19 Jan 2012 week 3
20 Jan 2012 to 26 Jan 2012 week 4
27 Jan 2012 to 31 Jan 2012 week 6
1 Feb 2012 to 2 Feb 2012 week 7
3 Feb 2012 to 9 Feb 2012 week 8
10 Feb 2012 to 16 Feb 2012 week 9
17 Feb 2012 to 23 Feb 2012 week 10
24 Feb 2012 to 29 Feb 2012 week 11--------------------------------------
February 24, 2012 at 4:18 am
Hello and welcome to SSC!
I'd like to be able to help, but it seems you've forgot to post readily consumable sample data and ddl scripts.
If you could read this article[/url] about the best way to post DDL and sample data then edit your post to include it, it would be extremely useful for the unpaid volunteers of this site.
Thanks!
February 24, 2012 at 4:58 am
You can use DATEPART function to calculate week number.
-- SELECT DATEPART(WEEK,[DateTime])
SELECT DATEPART(WEEK, '1 Jan 2012')
February 24, 2012 at 5:52 am
hi Friend,
Try This way..
DECLARE
@start_date DATETIME,
@end_date DATETIME,
@start_date1 DATETIME,
@end_date1 DATETIME
DECLARE @Table table(StartDate datetime,Enddate datetime,WeekNo varchar(20))
Declare @WeekDt as varchar(10)
SET @start_date = '2011-01-01'
SET @end_date = '2011-12-30'
Set @WeekDt = DATEPART(WEEK,@start_date)
SET @start_date1 = @start_date
While @start_date<=@end_date
Begin
--Select @start_date,@start_date+1
IF @WeekDt<>DATEPART(WEEK,@start_date)
BEGIN
Set @WeekDt = DATEPART(WEEK,@start_date)
SET @end_date1=@start_date-1
INSERT INTO @Table Values(@start_date1,@end_date1,DATEPART(WEEK,@start_date1))
SET @start_date1 = @start_date
END
set @start_date = @start_date+1
END
Select * from @Table
February 24, 2012 at 6:11 am
Bipinkumar Yadav (2/24/2012)
hi Friend,Try This way..
DECLARE
@start_date DATETIME,
@end_date DATETIME,
@start_date1 DATETIME,
@end_date1 DATETIME
DECLARE @Table table(StartDate datetime,Enddate datetime,WeekNo varchar(20))
Declare @WeekDt as varchar(10)
SET @start_date = '2011-01-01'
SET @end_date = '2011-12-30'
Set @WeekDt = DATEPART(WEEK,@start_date)
SET @start_date1 = @start_date
While @start_date<=@end_date
Begin
--Select @start_date,@start_date+1
IF @WeekDt<>DATEPART(WEEK,@start_date)
BEGIN
Set @WeekDt = DATEPART(WEEK,@start_date)
SET @end_date1=@start_date-1
INSERT INTO @Table Values(@start_date1,@end_date1,DATEPART(WEEK,@start_date1))
SET @start_date1 = @start_date
END
set @start_date = @start_date+1
END
Select * from @Table
There are couple of questions to the above solution:
1. Why weekno is varchar?
2. Why using loop here?
The following will do the same without looping (therefore much more efficient, try it to populate data for 10 years...):
DECLARE @start_date DATETIME, @end_date DATETIME
DECLARE @Table table(StartDate datetime,Enddate datetime,WeekNo int)
SET @start_date = '1 Jan 2011'
SET @end_date = '31 Dec 2021'
INSERT @Table
SELECT MIN(dt), MAX(dt), w
FROM
(
SELECT dt, year(dt) y, DATEPART(week,dt) w
FROM
(
SELECT @start_date + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) dt
FROM sys.columns s1 cross join sys.columns s2
) q
WHERE dt BETWEEN @start_date AND @end_date
) a
group by y,w
Select * from @Table order by startdate, weekno
February 24, 2012 at 6:55 am
Another option would be to use a calendar table with the columns you need and join to that table.
A calender table is especially helpful if there are special date ranges (e.g. fiscal year, work days, holidays) that would need to be considered in serveral queries.
February 24, 2012 at 3:35 pm
Bipinkumar Yadav!
i got this error msg when i tried your script. could you please let me know where i am wrong. I am new sql learner.
thanks
Msg 8115, Level 16, State 2, Line 7
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.
(0 row(s) affected)
February 24, 2012 at 6:55 pm
binodbabu (2/24/2012)
Bipinkumar Yadav!i got this error msg when i tried your script. could you please let me know where i am wrong. I am new sql learner.
thanks
Msg 8115, Level 16, State 2, Line 7
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.
(0 row(s) affected)
Ok... you said it first. You're a "new sql learner" and I'm here to tell you that using a loop for such a thing is about the worst thing you could learn to do. Just forget you ever saw that code or it will cripple your thinking as a learner. For the first two years of learning SQL, just pretend that loops and cursors don't exist and learn how to do things the right way. There are very, very few thing that actually require loops and cursors.
If you want to learn SQL properly, see the tag line in my signature below... the one about rows and columns.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 24, 2012 at 7:07 pm
Eugene Elutin (2/24/2012)
Bipinkumar Yadav (2/24/2012)
hi Friend,Try This way..
DECLARE
@start_date DATETIME,
@end_date DATETIME,
@start_date1 DATETIME,
@end_date1 DATETIME
DECLARE @Table table(StartDate datetime,Enddate datetime,WeekNo varchar(20))
Declare @WeekDt as varchar(10)
SET @start_date = '2011-01-01'
SET @end_date = '2011-12-30'
Set @WeekDt = DATEPART(WEEK,@start_date)
SET @start_date1 = @start_date
While @start_date<=@end_date
Begin
--Select @start_date,@start_date+1
IF @WeekDt<>DATEPART(WEEK,@start_date)
BEGIN
Set @WeekDt = DATEPART(WEEK,@start_date)
SET @end_date1=@start_date-1
INSERT INTO @Table Values(@start_date1,@end_date1,DATEPART(WEEK,@start_date1))
SET @start_date1 = @start_date
END
set @start_date = @start_date+1
END
Select * from @Table
There are couple of questions to the above solution:
1. Why weekno is varchar?
2. Why using loop here?
The following will do the same without looping (therefore much more efficient, try it to populate data for 10 years...):
DECLARE @start_date DATETIME, @end_date DATETIME
DECLARE @Table table(StartDate datetime,Enddate datetime,WeekNo int)
SET @start_date = '1 Jan 2011'
SET @end_date = '31 Dec 2021'
INSERT @Table
SELECT MIN(dt), MAX(dt), w
FROM
(
SELECT dt, year(dt) y, DATEPART(week,dt) w
FROM
(
SELECT @start_date + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) dt
FROM sys.columns s1 cross join sys.columns s2
) q
WHERE dt BETWEEN @start_date AND @end_date
) a
group by y,w
Select * from @Table order by startdate, weekno
Eugene,
Just a suggestion... Don't forget that the OP is an absolute newbie and is being lured to the "dark side" by a "familiar" loop. Adding a couple of comments as to what each part of the code is doing just might help sway the OP away from loops and maybe even inspire him/her to look for better ways in the future.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 24, 2012 at 7:11 pm
mahesh15dec1986 (2/24/2012)
Hi Teami have a data of year 2012 and table structure is (SN ,Name ,DATE,Error) and i need data according to
1 Jan 2012 to 5 Jan 2012- Week 1
6 Jan 2012 to 12 Jan 2012 week 2
13 Jan 2012 to 19 Jan 2012 week 3
20 Jan 2012 to 26 Jan 2012 week 4
27 Jan 2012 to 31 Jan 2012 week 6
1 Feb 2012 to 2 Feb 2012 week 7
3 Feb 2012 to 9 Feb 2012 week 8
10 Feb 2012 to 16 Feb 2012 week 9
17 Feb 2012 to 23 Feb 2012 week 10
24 Feb 2012 to 29 Feb 2012 week 11--------------------------------------
Given the table structure you have, what would you expect the output to actually look like? I ask because I don't know what you mean by ...
i need data according to
1 Jan 2012 to 5 Jan 2012- Week 1
6 Jan 2012 to 12 Jan 2012 week 2
13 Jan 2012 to 19 Jan 2012 week 3
20 Jan 2012 to 26 Jan 2012 week 4
27 Jan 2012 to 31 Jan 2012 week 6
1 Feb 2012 to 2 Feb 2012 week 7
3 Feb 2012 to 9 Feb 2012 week 8
10 Feb 2012 to 16 Feb 2012 week 9
17 Feb 2012 to 23 Feb 2012 week 10
24 Feb 2012 to 29 Feb 2012 week 11--------------------------------------
Are you looking for a count by week or ???
--Jeff Moden
Change is inevitable... Change for the better is not.
February 27, 2012 at 10:45 am
Thank you Jeff and Bipin
February 27, 2012 at 12:14 pm
Jeff Moden (2/24/2012)
... Adding a couple of comments as to what each part of the code is doing just might help sway the OP away from loops and maybe even inspire him/her to look for better ways in the future.
Cannot argue with above, therefore, revised post:
-- declaring variables to hold required date range, these can be made to be input
-- parameters of stored procedure if you wish to create one
DECLARE @start_date DATETIME, @end_date DATETIME
-- table variable is for convenience of re-runnable example. #-temp table would
-- offer better performance. And, at the end, why not create permanent calendar-week table
-- which you can index as required and use this table in many other queries
DECLARE @Table table(StartDate datetime,Enddate datetime,WeekNo int)
-- let say that is 10 year range to populate
SET @start_date = '1 Jan 2011'
SET @end_date = '31 Dec 2021'
-- populate our "calendar-week" table variable
INSERT @Table
SELECT MIN(dt) -- start date of the week as minimum of the date range within the week
, MAX(dt) -- end date of the week as maximum of the date range within the week
, w -- week of the year
FROM
(
SELECT dt -- date
,year(dt) AS y -- year of the date
,DATEPART(week,dt) AS w -- week of year 1 to 53 depending on SET DATEFIRST...
FROM
(
SELECT @start_date + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) dt
-- the above will increment @start_date by 1 as many times as many rows possibly to
-- produce by the following:
FROM sys.columns s1 cross join sys.columns s2
-- the above cross join creates a Cartesian product which results to
-- thousands of rows (count of records in sys.columns ^ 2)
) q
-- we limit number of rows to be in a requested date-range
WHERE dt BETWEEN @start_date AND @end_date
) a
group by y, w -- we group result by year and week to get final ranges
--displaying results:
Select * from @Table order by startdate, weekno
February 27, 2012 at 4:05 pm
Eugene Elutin (2/27/2012)
Jeff Moden (2/24/2012)
... Adding a couple of comments as to what each part of the code is doing just might help sway the OP away from loops and maybe even inspire him/her to look for better ways in the future.Cannot argue with above, therefore, revised post:
You, good Sir, are a gentleman and a scholar. Thanks, Eugene.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply