InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 01:26:29 GMT20http://www.sqlservercentral.com/Forums/Topic428641-338-1.aspx[quote][b]Prakash (12/3/2007)[/b][hr]Dear AllI want the output in this wayDays No of.RequestsMon 5Tue 6Wed 7Thu 3Fri 2Sat 1I will pass only the date to the queryPls help me to solve this problem[/quote]Hmm, having misunderstood your original question (and suspecting that I'm not alone), and being curious enough about the problem, I'm wondering if you could explain it in more detail. Jeff Moden has a brilliant guideline about how to describe your question so that you can get a result you are happy with quickly :) You can access this on [url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]Regards, AndrasTue, 04 Dec 2007 02:18:33 GMTAndras Belokosztolszkihttp://www.sqlservercentral.com/Forums/Topic428641-338-1.aspxAssuming more things than I care to list, a strored procedure might look like...create procedure getThatWeek(@inputDate datetime)asbeginset @inputDate = convert(varchar(10), @inputDate, 111)select left(datename(dw,createdate),3) as ThatDay ,NumOfRequestsfrom YourRequestLogTablewhere createdate >= dateadd(dd, 1 - datepart(dw, @inputDate), @inputDate) and createdate < dateadd(dd, 8 - datepart(dw, @inputDate), @inputDate)endTue, 04 Dec 2007 02:06:44 GMTKeith Hoffmanhttp://www.sqlservercentral.com/Forums/Topic428641-338-1.aspxIt would be better, if you could post your code with the results you want, rather we working blindly thinking something else:cool:Mon, 03 Dec 2007 22:53:17 GMTRamesh Saivehttp://www.sqlservercentral.com/Forums/Topic428641-338-1.aspxDear AllI want the output in this wayDays No of.RequestsMon 5Tue 6Wed 7Thu 3Fri 2Sat 1I will pass only the date to the queryPls help me to solve this problemMon, 03 Dec 2007 21:07:05 GMTPrakash-485822http://www.sqlservercentral.com/Forums/Topic428641-338-1.aspxThanks Andras, for explaining what you meant earlier.:)Mon, 03 Dec 2007 07:40:02 GMTRamesh Saivehttp://www.sqlservercentral.com/Forums/Topic428641-338-1.aspx[quote][b]Ramesh (12/3/2007)[/b][hr]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?[/quote]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 307012/5/2007 307012/6/2007 307012/7/2007 307012/8/2007 307012/9/2007 3071 (Sunday, new week, new number)12/10/2007 307112/11/2007 307112/12/2007 307112/13/2007 307112/14/2007 307112/15/2007 307112/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 :)AndrasMon, 03 Dec 2007 07:26:36 GMTAndras Belokosztolszkihttp://www.sqlservercentral.com/Forums/Topic428641-338-1.aspxI 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?Mon, 03 Dec 2007 06:48:22 GMTRamesh Saivehttp://www.sqlservercentral.com/Forums/Topic428641-338-1.aspx[quote][b]Ramesh (12/3/2007)[/b][hr]Well, you need to interchange * with / [code]You are right, (I probably need to sleep more) [/code]You'll surely need...:D[/quote]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 :)Regards, AndrasMon, 03 Dec 2007 06:33:54 GMTAndras Belokosztolszkihttp://www.sqlservercentral.com/Forums/Topic428641-338-1.aspxWell, you need to interchange * with / [code]You are right, (I probably need to sleep more) [/code]You'll surely need...:DMon, 03 Dec 2007 06:14:01 GMTRamesh Saivehttp://www.sqlservercentral.com/Forums/Topic428641-338-1.aspxYou are right, (I probably need to sleep more) :)A corrected version is:DATEPART( wk, '3/12/2007' ) + (DATEPART( yyyy, '3/12/2007' )-1950) * 53(the *53 should be on the year :), deducting 1950 will make the numbers even smaller)Thanks, AndrasMon, 03 Dec 2007 06:06:24 GMTAndras Belokosztolszkihttp://www.sqlservercentral.com/Forums/Topic428641-338-1.aspx[quote]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.[/quote]Hey Andras, are u sure that will give what you said. Or you just missed something in between?Here are the results from it...[code]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' )--Results2590 583 11 2007[/code]Here is what I use in my projects...[code]DECLARE @sdtAnyDate SMALLDATETIMESET @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[/code]Mon, 03 Dec 2007 06:00:15 GMTRamesh Saivehttp://www.sqlservercentral.com/Forums/Topic428641-338-1.aspx[quote][b]Prakash (12/3/2007)[/b][hr]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[/quote]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, AndrasMon, 03 Dec 2007 05:34:47 GMTAndras Belokosztolszkihttp://www.sqlservercentral.com/Forums/Topic428641-338-1.aspxDear 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 weekMon, 03 Dec 2007 04:24:32 GMTPrakash-485822