November 2, 2010 at 2:57 pm
Is there a way I can group a return set of data, by Month, and Week?
I am trying to get a result set that looks similar to this:
Total Sold : Month: Weekof
5 Sept 9/1/2010
10 Sept 9/8/2010
15 Sept 9/15/2010
Is there a datepart function that does this?
November 2, 2010 at 3:19 pm
Can you give me an example of the raw data that would make up the groupings that you have there? Table DDL would help too.
November 2, 2010 at 3:42 pm
Well, there's
DATEPART( mm, @date) which will give you the month.
DATEPART( ww, @date) which will give you the week # in the year.
You'll use those for sorting.
For display, you can then:
DATENAME( m, @date) for the month name.
and you should be able to find the actual week's beginning date off a combination of datepart(DW) (day of week), datepart(ww) (week number), and a datediff to get to the beginning of the year. I forget the calcultion offhand to find it easily.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 2, 2010 at 4:02 pm
Figured it out again (I think):
DECLARE @beginYear DATETIME
SET @beginYear = DATEADD (yy, YEAR(getdate()) - 1900 , 0)
PRINT dateadd( dd, (DATEPART( wk, getdate()) - 1) * 7 - datepart(dw, @beginYear) + 1, @beginYear)
or in single line format for a selectable column:
PRINT dateadd( dd,
(DATEPART( wk, getdate()) - 1) * 7
- datepart(dw,
DATEADD (yy, YEAR(getdate()) - 1900 ,
0)
) + 1,
DATEADD (yy, YEAR(getdate()) - 1900 , 0)
)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 3, 2010 at 5:51 pm
Marv-1058651 (11/2/2010)
Is there a way I can group a return set of data, by Month, and Week?I am trying to get a result set that looks similar to this:
Total Sold : Month: Weekof
5 Sept 9/1/2010
10 Sept 9/8/2010
15 Sept 9/15/2010
Is there a datepart function that does this?
Since you've listed Wednesdays for the Weekof, I have to ask... what day of the week does your week start on and are you sure you want your Weekof to show Wednesdays???
--Jeff Moden
Change is inevitable... Change for the better is not.
November 8, 2010 at 9:45 am
Yes your right, my mistake. I would want the output to be :
8/29/2010
9/5/2010
9/12/2010
9/19/2010
9/26/2010
November 8, 2010 at 10:32 am
here is the ddl for the table and some raw data.
Create table [Orders] (
[item] [varchar] (40) NOT NULL,
[productkey] [int] NOT NULL,
[price] [money] NOT NULL,
[Dateofpurchase] [datetime] NOT NULL,
[ordernumber] [int] NOT NULL
)
Item Product Key Price Date of Purchase Order Number
Keyboard 123 $10.00 9/1/2010 1
Mouse 124 $12.00 9/5/2010 2
Monitor 125 $30.00 9/17/2010 3
Keyboard 123 $10.00 9/2/2010 4
Keyboard 123 $10.00 9/6/2010 5
select sum(price), item
from orders
where productkey =123
and dateofpurchase*** between '9/1/2010' and '10/1/2010'
group by item
*** I would like to group the dateofpurchase by week instead of one grand total for the month of sept. This is basically what I am trying to achieve.
November 8, 2010 at 11:02 am
This would be very easy if you had a dates table in your database. Another option would be to use a UDF to find the last day of the week and use that in your group by. This will work, but may not be the best performer if you are running this accross a large date range and a large data set.
IF OBJECT_ID('dbo.udf_GetEndOfWeekDate') IS NOT NULL
DROP FUNCTION dbo.udf_GetEndOfWeekDate
GO
CREATE FUNCTION dbo.udf_GetEndOfWeekDate (@Date datetime)
RETURNS datetime
AS
BEGIN
DECLARE @EndOfWeekDate datetime
SELECT @EndOfWeekDate = DATEADD(d, Num, @Date)
FROM(
SELECT 0 as NUM UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6
) Days
WHEREDATENAME(dw,DATEADD(d, Num , @Date)) = 'Sunday'
RETURN(@EndOfWeekDate)
END
GO
SELECTItem,
dbo.udf_GetEndOfWeekDate(DateOfPurchase) as EOW,
SUM(Price) as Price
FROMOrders o
GROUP BYItem,
dbo.udf_GetEndOfWeekDate(DateOfPurchase)
November 8, 2010 at 11:22 am
Thank You very much!
Great job on this also, I would've never figured this out on my own. Very much appreciated. You
are obviously a genius.
:cool::cool::cool:
November 11, 2010 at 8:45 am
Thought I'd throw my 2 cents in. This eliminates the need for a function altogether.
SELECT Item,
EndOfWeekDate = d.EoW,
Price = SUM(Price),
Quantity = COUNT(*)
FROM dbo.Orders
CROSS APPLY (SELECT EoW = DATEADD(dd,DATEDIFF(dd,-7,DateOfPurchase)/7*7,-1)) d
GROUP BY Item, d.EoW
--Jeff Moden
Change is inevitable... Change for the better is not.
November 11, 2010 at 8:50 am
Now that's genius. I knew there would be a way to do this without a function, but time constratints kept me from persuing one.
Thanks Jeff!
November 11, 2010 at 8:56 am
Thanks for the feedback, John. You had the right idea... I just turned it into a formula.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 11, 2010 at 9:44 am
Alright Jeff....you're making my brain hurt.
I'm humbled by your formula....would you be so kind as to 'splain it to me?
November 11, 2010 at 10:13 am
John,
Let's see if I can do it justice.
Formula: DATEADD(dd,DATEDIFF(dd,-7,DateOfPurchase)/7*7,-1))
select DATENAME(weekday, 0); -- = Monday
select DATENAME(weekday, -1); -- = Sunday
select DateAdd(day, -7, 0); -- = 18991225
So, take the number of days between 18991225 (Date=-7) and the date in the field.
Divide this by the number 7 (number of days in a week). Since this is an integer (7) vs. decimal # (7.0), the result will have the fractions of a week truncated. Multiply back by 7 to get the number of days for entire weeks. Add this to 18991231 (Date=-1, a Sunday) to get the start of the week.
Clear as mud?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 11, 2010 at 12:35 pm
WayneS (11/11/2010)
Formula: DATEADD(dd,DATEDIFF(dd,-7,DateOfPurchase)/7*7,-1))select DATENAME(weekday, 0); -- = Monday
select DATENAME(weekday, -1); -- = Sunday
select DateAdd(day, -7, 0); -- = 18991225
So, take the number of days between 18991225 (Date=-7) and the date in the field.
Divide this by the number 7 (number of days in a week). Since this is an integer (7) vs. decimal # (7.0), the result will have the fractions of a week truncated. Multiply back by 7 to get the number of days for entire weeks. Add this to 18991231 (Date=-1, a Sunday) to get the start of the week.
Clear as mud?
Okay, now I'm with John. 🙂
My understanding was this:
Take 7 days off off DateOfPurchase. Basically, move it to the same day, last week.
Next, strip the decimal off (/7*7). this moves it to the Monday of last week.
Now, move it forward a day via the dateadd and datediff from the -1 to the Tuesday of last week.
What's got me confused is... why are we moving backwards a week?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply