SQL Server Performance

How to determine particular date is in which week?

Discussion in 'SQL Server 2005 General Developer Questions' started by Taurusgal83, Jan 23, 2007.

  1. Taurusgal83 New Member

    Hi there~ I need some help on this problem. How should I write the SQL coding so that I could know the order that I get on that particular date is in which week of the month? And how can i get the total order for Week 1, Week 2, Week 3, Week 4 for particular month?

    Example:
    Order No. Order Date Amount
    1 01.01.07 200.00
    2 06.01.07 300.00
    3 16.01.07 540.00
    4 01.02.07 600.00

    Desired Output:
    Year Month Week Amount
    07 1 1 500.00
    07 1 2 540.00
    07 2 1 600.00

    I need the output like the above shown. Is there anyone can guide me on this? Thanks in advance.. ^_^
  2. madhuottapalam New Member

    Hi,

    select DATEPART ( year, OrderDate) as Year,DATEPART ( Month, OrderDate) as Month,DATEPART ( week, OrderDate) as Week,Amount from yourtable order by orderdate

    Madhu
  3. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by madhuottapalam</i><br /><br />Hi,<br /><br />select DATEPART ( year, OrderDate) as Year,DATEPART ( Month, OrderDate) as Month,DATEPART ( week, OrderDate) as Week,Amount from yourtable order by orderdate <br /><br />Madhu<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />That doesn't do any grouping! [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />Try something like this:<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT YEAR(DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0))<br /> , MONTH(DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0))<br /> , DATEPART(WEEK, (DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0)))<br /> , SUM(Amount)<br /> FROM &lt;your_table&gt;<br /> GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0)<br /></font id="code"></pre id="code"><br />You probably need to check for the correct first day of the year. To do so, check CREATE FUNCTION in BOL. You'll find there the ISOWeek example.<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  4. madhuottapalam New Member

    my mistake......... my apology

    Madhu
  5. Adriaan New Member

    Ah, the problem is that the DATEPART(Week, &lt;date&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /> gives you the week number for the whole year, not the week within the month. <br /><br />Get the week number for the given date,<br />DATEPART(WEEK, OrderDate)<br /><br />subtract the week number for the first of the month,<br />- DATEPART(WEEK, YEAR(OrderDate) + '-' + MONTH(OrderDate) + '-1')<br /><br />and add one:<br />+ 1<br /><br />There is a way to use DATEADD instead of YEAR(OrderDate) + '-' + MONTH(OrderDate) + '-1' - I'm pretty sure it's documented in Frank Kalis' article about dates, but I can't seem to find it on this site.
  6. FrankKalis Moderator

    Excellent catch!!!

    So, what about something like this?


    DROP TABLE #t
    CREATE TABLE #t
    (
    OrderNo INT
    , OrderDate DATETIME
    , Amount INT
    )

    INSERT INTO #t SELECT 1, '20070101', 200
    UNION ALL SELECT 2, '20070106', 300
    UNION ALL SELECT 3, '20070116', 300
    UNION ALL SELECT 4, '20070201', 600

    SELECT
    YEAR(MIN(OrderDate)) AS [Year]
    , MONTH(OrderDate) AS [Month]
    , dbo.ISOweek(MIN(OrderDate))
    -
    dbo.ISOweek(DATEADD(MONTH,DATEDIFF(MONTH,30,MIN(OrderDate)),0)) + 1 AS [Week in month]
    , SUM(Amount) AS Amount
    FROM #t
    GROUP BY MONTH(OrderDate), dbo.ISOweek(OrderDate)


    Year Month Week in month Amount
    ----------- ----------- ------------- -----------
    2007 1 1 500
    2007 1 3 300
    2007 2 1 600

    (3 row(s) affected)


    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  7. Adriaan New Member

    That's a step in the right direction, but you know this type of question - they want it in a cross-tab fashion.

    Don't you love it.
  8. FrankKalis Moderator

    Hm, I don't see much of a difference between the required output and mine. Apart from this formatting thingy, of course. But you're right, who knows aynway... [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  9. FrankKalis Moderator

    Btw, the GROUP BY can be simplified to


    GROUP BY dbo.ISOweek(OrderDate)

    If that report spans more than 1 year, you get problems anyway.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  10. Taurusgal83 New Member

    SELECT YEAR(DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0)) , MONTH(DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0)) , DATEPART(WEEK, (DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0))) , SUM(Amount) FROM &lt;your_table&gt; GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0)<br /><br />This example I have try already but when I try to run the coding, it shows an error message saying that "The year functions requires 1 argument". Any solutions?<br /><br />Thanks~ [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
  11. Taurusgal83 New Member

    Get the week number for the given date,<br />DATEPART(WEEK, OrderDate)<br /><br />subtract the week number for the first of the month,<br />- DATEPART(WEEK, YEAR(OrderDate) + '-' + MONTH(OrderDate) + '-1')<br /><br />and add one:<br />+ 1<br /><br />This solutions doesn't seems working out for me. I dont know whether the way I type out is wrong/ what. Can you show me the proper SQL sample? Thanks~ [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
  12. khtan New Member

    I tried to run your query, it does not gives me the same error message "The year functions requires 1 argument" but "OrderDate is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

    Actually your YEAR(DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0)) is correct there is nothing wrong with it. The problem is the GROUP BY. You need to
    GROUP BY YEAR(DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0)) ,
    MONTH(DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0)) ,
    DATEPART(WEEK, (DATEADD(WEEK, DATEDIFF(WEEK, 0, OrderDate), 0)))

    By the way, what's wrong with Frank's solution ? Doesn't it work for you ? The result is exactly what you posted.


    KH
  13. khtan New Member

    Or perhaps this is what you want ?



    DROP TABLE #t
    CREATE TABLE #t
    (
    OrderNo INT
    , OrderDate DATETIME
    , Amount INT
    )

    INSERT INTO #t
    SELECT 1, '20070101', 200UNION ALL
    SELECT 2, '20070106', 300UNION ALL
    SELECT 3, '20070116', 540UNION ALL
    SELECT 4, '20070201', 600UNION ALL
    SELECT 5, '20061205', 400

    select[Year],
    [Month] = month(dateadd(week, WeekNo, dateadd(year, [Year] - 1900, 0))),
    WeekNo,
    Amount
    from
    (
    select[Year]= year(OrderDate),
    WeekNo = datepart(week, OrderDate),
    Amount= sum(Amount)
    from#t
    group by year(OrderDate), datepart(week, OrderDate)
    ) d
    order by [Year], [Month], [WeekNo]

    /* RESULT
    Year Month WeekNo Amount
    ----------- ----------- ----------- -----------
    2006 12 49 400
    2007 1 1 500
    2007 1 3 540
    2007 2 5 600
    */



    KH
  14. Taurusgal83 New Member

    Thanks KHTan for your advice.The coding manage to produce the outputs already but the week is wrong. Cause supposely date 01.02.07 is consider Week 1 for February, but is counted as Week 5. That is not my desired output. Any idea to solve again? ^_^
  15. khtan New Member


    DROP TABLE #t
    CREATE TABLE #t
    (
    OrderNo int,
    OrderDate datetime,
    Amount int
    )

    INSERT INTO #t
    SELECT 1, '20070101', 200UNION ALL
    SELECT 2, '20070106', 300UNION ALL
    SELECT 3, '20070116', 540UNION ALL
    SELECT 4, '20070201', 600UNION ALL
    SELECT 5, '20061205', 400

    select[Year],
    [Month],
    WeekNo = WeekNo - datepart(week, dateadd(month, [Month] - 1, dateadd(year, [Year] - 1900, 0))) + 1,
    Amount
    from
    (
    select[Year],
    [Month] = month(dateadd(week, WeekNo, dateadd(year, [Year] - 1900, 0))),
    WeekNo,
    Amount
    from
    (
    select[Year]= year(OrderDate),
    WeekNo = datepart(week, OrderDate),
    Amount= sum(Amount)
    from#t
    group by year(OrderDate), datepart(week, OrderDate)
    ) a
    ) b
    order by [Year], [Month], [WeekNo]

    /* RESULT
    Year Month WeekNo Amount
    ----------- ----------- ----------- -----------
    2006 12 2 400
    2007 1 1 500
    2007 1 3 540
    2007 2 1 600
    */



    KH
  16. Taurusgal83 New Member

    Thanks KHTan~ It can manage show the example desired output. But when I apply to my real data, let's say the Order Date is 28th January 2007, it suppose to be week 5 order amount for January, but it sums up to February 1st week. How should I do so that I can get the order 28th January 2007 amount show in week 5? Any idea? Thanks a lot~ ^_^
  17. FrankKalis Moderator

    Just out of curiosity... You have seen my reply:


    SELECT
    YEAR(MIN(OrderDate)) AS [Year]
    , MONTH(OrderDate) AS [Month]
    , dbo.ISOweek(OrderDate)
    -
    dbo.ISOweek(DATEADD(MONTH,DATEDIFF(MONTH,30,MIN(OrderDate)),0)) + 1 AS [Week in month]
    , SUM(Amount) AS Amount
    FROM #t
    GROUP BY MONTH(OrderDate), dbo.ISOweek(OrderDate)

    I think it does what you want. If not, please tell me what's wrong with it.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  18. Taurusgal83 New Member

    SELECT YEAR(MIN(OrderDate)) AS [Year] , MONTH(OrderDate) AS [Month] , dbo.ISOweek(OrderDate) - dbo.ISOweek(DATEADD(MONTH,DATEDIFF(MONTH,30,MIN(OrderDate)),0)) + 1 AS [Week in month] , SUM(Amount) AS Amount FROM #t GROUP BY MONTH(OrderDate), dbo.ISOweek(OrderDate)


    Sorry Frank~ I have try the coding but when i run it, it says "Invalid object name 'dbo.ISOweek'". Any idea how to solve? Thanks a lot~ ^_^
  19. FrankKalis Moderator

    You will find the definition of dbo.ISOWeek, when you look at the explanations of CREATE FUNCTION in BOL. Just copy and paste that to your database, execute it to create the function and there you go.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  20. Taurusgal83 New Member

    CREATE FUNCTION in BOL? Mind I ask what is BOL? How can I see the explanations??
  21. khtan New Member

    try this ver. Changes is in bold


    DROP TABLE #t
    CREATE TABLE #t
    (
    OrderNo int,
    OrderDate datetime,
    Amount int
    )

    INSERT INTO #t
    SELECT 1, '20070101', 200UNION ALL
    SELECT 2, '20070106', 300UNION ALL
    SELECT 3, '20070128', 540UNION ALL
    SELECT 4, '20070201', 600UNION ALL
    SELECT 5, '20061205', 400

    select[Year],
    [Month],
    WeekNo = WeekNo - datepart(week, dateadd(month, [Month] - 1, dateadd(year, [Year] - 1900, 0))) + 1,
    Amount
    from
    (
    select[Year],
    [Month],
    WeekNo,
    Amount
    from
    (
    select[Year]= year(OrderDate),
    [Month] = month(OrderDate),
    WeekNo = datepart(week, OrderDate),
    Amount= sum(Amount)
    from#t
    group by year(OrderDate), month(OrderDate), datepart(week, OrderDate)
    ) a
    ) b
    order by [Year], [Month], [WeekNo]

    /* RESULT

    Year Month WeekNo Amount
    ----------- ----------- ----------- -----------
    2006 12 2 400
    2007 1 1 500
    2007 1 5 540
    2007 2 1 600
    */



    KH
  22. khtan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Taurusgal83</i><br /><br />CREATE FUNCTION in BOL? Mind I ask what is BOL? How can I see the explanations??<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />BOL is Books OnLine. aka SQL Server help file<br /><br />Here it is. Look for CREATE FUNCTION. It is there as Frank pointed<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Examples<br />A. Scalar-valued user-defined function that calculates the ISO week<br />In this example, a user-defined function, ISOweek, takes a date argument and calculates the ISO week number. For this function to calculate properly, SET DATEFIRST 1 must be invoked before the function is called. <br /><br />CREATE FUNCTION ISOweek (@DATE datetime)<br />RETURNS int<br />AS<br />BEGIN<br /> DECLARE @ISOweek int<br /> SET @ISOweek= DATEPART(wk,@DATE)+1<br /> -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')<br />--Special cases: Jan 1-3 may belong to the previous year<br /> IF (@ISOweek=0) <br /> SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 <br /> AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1<br />--Special case: Dec 29-31 may belong to the next year<br /> IF ((DATEPART(mm,@DATE)=12) AND <br /> ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))&gt;= 2<img src='/community/emoticons/emotion-11.gif' alt='8)' />)<br /> SET @ISOweek=1<br /> RETURN(@ISOweek)<br />END<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><hr noshade size="1"><br /><font size="1">KH</font id="size1">
  23. Taurusgal83 New Member

    Thanks KHTan for your codings~ It works nicely.. Thanks a lot~ ^_^
  24. FrankKalis Moderator

    quote:Originally posted by Taurusgal83

    CREATE FUNCTION in BOL? Mind I ask what is BOL? How can I see the explanations??
    Sorry for that. Sometimes one uses technical terms like these without even thinking about it that someone else doesn't even know them.

    BOL is the common abbreviation for the SQL Server Books Online, aka SQL Servers help files. They are exceptionally good and usually the first source to consult in any case.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  25. dhilditch New Member

    Use a CASE statement.


    case when day(calldatetime) between 1 and 7 then 1 when day(calldatetime) between 8 and 14 then 2 when day(calldatetime) between 15 and 21 then 3 when day(calldatetime) between 22 and 28 then 4 else 5 end WeekNumber,

    and then include that whole CASE statement in your GROUP BY clause as well (but remove the WeekNumber alias in the GROUP BY).


    Add flight search to your own site for free
    www.skyscanner.net
  26. Taurusgal83 New Member

    SELECT YEAR(MIN(OrderDate)) AS [Year] , MONTH(OrderDate) AS [Month] , dbo.ISOweek(OrderDate) - dbo.ISOweek(DATEADD(MONTH,DATEDIFF(MONTH,30,MIN(OrderDate)),0)) + 1 AS [Week in month] , SUM(Amount) AS Amount <br />FROM #t<br />GROUP BY MONTH(OrderDate), dbo.ISOweek(OrderDate)<br /><br /><br />Frank, after I do the "Create Function" part that KHTan give the sample to me, I try run the coding u give. When it runs, it show an error msg "Column 'db:confused:rder.OrderDate' is invalid in the select list because it is not contained in either an aggregrate function or the Group By clause". So I try change the Group By to:<br /><br /><br />SELECT YEAR(MIN(OrderDate)) AS [Year] , MONTH(OrderDate) AS [Month] , dbo.ISOweek(OrderDate) - dbo.ISOweek(DATEADD(MONTH,DATEDIFF(MONTH,30,MIN(OrderDate)),0)) + 1 AS [Week in month] , SUM(Amount) AS Amount <br />FROM #t <br />GROUP BY MONTH(OrderDate), OrderDate<br /><br /><br />It can show the week correctly but 01/01/07 & 06/01/07 order amount won't sum up. Any missing part need to add up?[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  27. khtan New Member

    Frank's script actually work. If you just copy and paste and it will run.

    Anyway, the query that you posted, you missed out the MIN() on the OrderDate. I have highlighted it in red


    SELECT YEAR(MIN(OrderDate)) AS [Year] ,
    MONTH(OrderDate) AS [Month] ,
    dbo.ISOweek(MIN(OrderDate)) -
    dbo.ISOweek(DATEADD(MONTH,DATEDIFF(MONTH,30,MIN(OrderDate)),0)) + 1 AS [Week in month] ,
    SUM(Amount) AS Amount
    FROM #t
    GROUP BY MONTH(OrderDate), dbo.ISOweek(OrderDate)



    KH
  28. Taurusgal83 New Member

    Thanks KHTan for showing me the missing part of the coding.[<img src='/community/emoticons/emotion-2.gif' alt=':D' />] Sorry Frank for the silly mistake i make.[:I] Thanks a lot for giving me another solutions for solving this coding problem that I faced. Really appreciate the help given by u guys~ [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]
  29. FrankKalis Moderator

    Guess how many times I am wrong, oversee the obvious and all things like that. [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  30. Taurusgal83 New Member

    Haha.. Sure a lot of times one. But important is we learn from mistake right? [<img src='/community/emoticons/emotion-2.gif' alt=':D' />] Feel great on this forum cause can learn faster than I thought it would be. Thanks a lot for those who has been giving me suggestion of coding for my problems. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]

Share This Page