Results 1 to 6 of 6

Thread: Week Number

  1. #1
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172

    Unanswered: Week Number

    Hi all!

    I'm working on a report that will look like this.

    WeekNb - Item - QtySold
    1 - Item1 - 25
    1 - Item2 - 6
    2 - Item1 - 10
    2 - Item3 - 73
    etc...

    I use this function to get the week number DATEPART(wk, transactionDate)

    But now I have a problem,

    Lets says I want the sells for 2008.

    The 1st of January is a Tuesday, but I also want the sells that I made on the 31st of December 2007 as it's on the 2008 1st week (monday).

    How can I get this to work ?

    Help please

    Regards,

    Or Tho
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your method will sum values from multiple years, which I assume is not what you want. Try something like this instead, which truncates dates to whole week values:
    Code:
    select	dateadd(week, datediff(week, 0, transactionDate), 0) as WeekBeginning,
    	Item,
    	count(*)
    from	[YourTable]
    group by dateadd(week, datediff(week, 0, transactionDate), 0),
    	Item
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The problem that I've seen with trying to shoehorn mismatched date ranges (such as weeks with months or years) is that people usually have different ways of dealing with the edge conditions. Without understanding exactly how you want to process the edges, I can't guess at how to code a solution.

    The most common user expectation is that there is a short week at the begining and the end of the doofangle (whatever the larger period is, be that month, quarter, year, etc). This means that there are typically two "dead soldier" weeks in the doofangle, but it makes drill down so much simpler because you don't have overlapping ranges.

    The next most common approach is that the week goes with whichever doofangle contains most of the days in that week. This means that weeks reamin whole and that fewer orphan days that show up in the wrong doofangle.

    After that, the most common approach is that the weeks that span doofangles are counted in whole, in each doofangle. This keeps weeks whole, but distorts all doofangles by including orphan days.

    There are a few other ways to look at the drill down from doofangles to weeks, but those are pretty obscure (usually only significant to CPAs).

    This drill down issue is a common problem in data warehousing. I'm sure that somebody has written up all of the permutations and discussed them ad-nauseum somewhere, but right now I'm just not sufficiently motivated to go find that discussion.

    So my question to you boils down to: How do you deal with the edges?

    -PatP

  4. #4
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Quote Originally Posted by blindman
    Your method will sum values from multiple years, which I assume is not what you want. Try something like this instead, which truncates dates to whole week values:
    Code:
    select	dateadd(week, datediff(week, 0, transactionDate), 0) as WeekBeginning,
    	Item,
    	count(*)
    from	[YourTable]
    group by dateadd(week, datediff(week, 0, transactionDate), 0),
    	Item
    Yes your right, that was the problem, I was about to use a where clause that looks like that: WHERE YEAR(date..)=@YEAR

    But then, I figured that if the year doesn't starts on a Sunday or it doesn't ends on a Saturday... then some sales will be missing...

    And using DATEPART(wk, date..), I also have a problem because in my report weeks have to be from Monday to Sunday and not from Sunday to Saturday.



    But using your Query I get a date and I want a week number.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Then build a look-up table cross referencing date to week ... three columns - week number (yyww), start date (smalldatetime), end date (smalldatetime). 52 or 53 rows per year, retain as need for historical reporting.

    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    I'm using this instead:

    Code:
    CREATE FUNCTION [dbo].[ISOweek] (@DATE datetime)
    RETURNS CHAR(7)
    WITH EXECUTE AS CALLER
    AS
    BEGIN
    	DECLARE @ISOweek INT, @ISOy CHAR(4), @ISOw VARCHAR(2)
    	SET @ISOweek=DATEPART(wk,@DATE)+1-DATEPART(wk,CAST(DATEPART(yy,@DATE) AS CHAR(4))+'0104')
    	SET @ISOy=CAST(DATEPART(yy, @DATE) AS CHAR(4))
    	SET @ISOw=CAST(@ISOweek AS VARCHAR(2))
    	-- Special cases: Jan 1-3 may belong to the previous year
    	IF (@ISOweek=0) 
    		BEGIN
    			SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
    			SET @ISOy=CAST(DATEPART(yy, @DATE) -1 AS CHAR(4))
    			SET @ISOw=CAST(@ISOweek AS VARCHAR(2))
    		END
    	-- Special case: Dec 29-31 may belong to the next year
    	IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
    		BEGIN
    			SET @ISOy=CAST(DATEPART(yy, @DATE) +1 AS CHAR(4))
    			SET @ISOw='01'
    		END 
    	-- Ajoute un zéro pour les semaines < 10
    	IF (LEN(@ISOw)<2)
    		SET @ISOw=STUFF(@ISOw, 1, 0, '0')
    	RETURN(@ISOy+'-'+@ISOw);
    END;
    And I use it like this:

    Code:
    SELECT dbo.ISOweek(datetime) ...
    And it returns something like that:
    "2008-01" AS char(7)

    So my problem is solved thank you all.

    Regards,

    Or Tho
    Less is more.
    How long is now?
    http://www.lesouterrain.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •