SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting records created in a particular week


Getting records created in a particular week

Author
Message
Prakash-485822
Prakash-485822
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 124
Dear All,
I want to get the records created in a particular week.
if i pass 3/12/2007 i want to get the records created in that week
Andras Belokosztolszki
Andras Belokosztolszki
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5595 Visits: 1585
Prakash (12/3/2007)
Dear All,
I want to get the records created in a particular week.
if i pass 3/12/2007 i want to get the records created in that week


You could use :


datepart(wk, '3/12/2007')*53 + datepart(yyyy, '3/12/2007')

The first part gives you the week number in that year, the second part the year.
If the year is not needed (you have only data for the current year), then just use : datepart(wk, '3/12/2007')

Regards,
Andras



Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Ramesh Saive
Ramesh Saive
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6896 Visits: 2643

datepart(wk, '3/12/2007')*53 + datepart(yyyy, '3/12/2007')

The first part gives you the week number in that year, the second part the year.


Hey Andras, are u sure that will give what you said. Or you just missed something in between?

Here are the results from it...


SELECT DATEPART( wk, '3/12/2007' ) * 53 + DATEPART( yyyy, '3/12/2007' ),
DATEPART( wk, '3/12/2007' ) * 53, DATEPART( wk, '3/12/2007' ), DATEPART( yyyy, '3/12/2007' )

--Results
2590 583 11 2007




Here is what I use in my projects...


DECLARE @sdtAnyDate SMALLDATETIME

SET @sdtAnyDate = '03-Dec-2007'

SELECT DATEADD( DAY, 1 - DATEPART( dw, @sdtAnyDate), @sdtAnyDate ) AS WeekStartDate,
@sdtAnyDate AS InputDate,
DATEADD( DAY, 7 - DATEPART( dw, @sdtAnyDate ), @sdtAnyDate ) AS WeekEndDate



--Ramesh


Andras Belokosztolszki
Andras Belokosztolszki
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5595 Visits: 1585
You are right, (I probably need to sleep more) Smile

A corrected version is:
DATEPART( wk, '3/12/2007' ) + (DATEPART( yyyy, '3/12/2007' )-1950) * 53

(the *53 should be on the year Smile, deducting 1950 will make the numbers even smaller)

Thanks,
Andras



Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Ramesh Saive
Ramesh Saive
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6896 Visits: 2643
Well, you need to interchange * with /


You are right, (I probably need to sleep more)



You'll surely need...BigGrin

--Ramesh


Andras Belokosztolszki
Andras Belokosztolszki
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5595 Visits: 1585
Ramesh (12/3/2007)
Well, you need to interchange * with /


You are right, (I probably need to sleep more)



You'll surely need...BigGrin



Actually, I did indeed mean *.
multiplying the year (or part of it) by 53 will give a unique range of 53 numbers to every year. Adding the week number withing the year to this number (sine there are no more than 53 weeks a year) will assign a unique number to any year week pair. I really should have explained this Smile

Regards,
Andras



Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Ramesh Saive
Ramesh Saive
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6896 Visits: 2643
I still couldn't understand what you're trying to point...Unsure

Multiplying the value gives me 3032, whereas I get 12 when divided...

I am curious to know, how the said value can be used in the context of the post?

--Ramesh


Andras Belokosztolszki
Andras Belokosztolszki
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5595 Visits: 1585
Ramesh (12/3/2007)
I still couldn't understand what you're trying to point...Unsure

Multiplying the value gives me 3032, whereas I get 12 when divided...

I am curious to know, how the said value can be used in the context of the post?


Hi Ramesh,

if one would like to group items belonging to the same week, one can assign a unique number to all days of a particular week.

For example:

12/2/2007 3070 (Sunday, new week, new number)
12/3/2007 3070
12/4/2007 3070
12/5/2007 3070
12/6/2007 3070
12/7/2007 3070
12/8/2007 3070
12/9/2007 3071 (Sunday, new week, new number)
12/10/2007 3071
12/11/2007 3071
12/12/2007 3071
12/13/2007 3071
12/14/2007 3071
12/15/2007 3071
12/16/2007 3072 (Sunday, new week, new number)
12/17/2007 3072


So if I want all the items that are on the week 12/2/2007, then for the dates you are checking the formula should evaluate to 3070
(which you get by evaluating it for any day in the requested week, in this case '12/3/2007').

Generating a single number is just a convenience (or not). One can find a particular week by:

DATEPART( wk, somedayfromtable ) = DATEPART( wk, '3/12/2007' ) AND (DATEPART( yyyy, somedayfromtable ) = (DATEPART( yyyy, '3/12/2007' )

The above will get the items that belong to a particular week (the week for '3/12/2007'), the single number is equivalent
to the above statement (it just maps the year and week pair to a number).

Anyway, the above approach is useful if you would like to group by rows (which is the way I mis?understood the question Blush ). Sorry.

Your solution is also better for the original question from the point of view that it calculates the start and end date, and then it uses only datetime comparison, no need for calculations (or breaking up the datetime to a year and week number part)

Thanks for correcting me Smile

Andras



Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Ramesh Saive
Ramesh Saive
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6896 Visits: 2643
Thanks Andras, for explaining what you meant earlier.Smile

--Ramesh


Prakash-485822
Prakash-485822
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 124
Dear All

I want the output in this way

Days No of.Requests
Mon 5
Tue 6
Wed 7
Thu 3
Fri 2
Sat 1

I will pass only the date to the query
Pls help me to solve this problem
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search