SQL- Week number of the Month NOT the Year & Why Query is Returning 6 Week in a Month

Question

Possible Duplicate:
TSQL Calculate week number of the month

This is my SQL Query

 SELECT DATEPART(MONTH,PaymentDate) AS 'Month',
       DATEPART(WEEK,PaymentDate) AS 'Week #',
       SUM(COALESCE(Amount,0)) AS 'Amount',
       SUM(COALESCE(Balance,0)) AS 'Balance'
FROM Payment
WHERE     (MONTH(PaymentDate) = MONTH('2012-09-01'))
GROUP BY DATEPART(MONTH,PaymentDate),
DATEPART(WEEK,PaymentDate)
GO

enter image description here

I am try to get Total payment of a month in weekly fashion.

Now I have two Issues

1)As you can see it shows week # of the year not that month.

2)Also It show 6 weeks Groups not 4.

How can I Fix that.

Thanks.

Re: #2: It is actually possible for a given calendar-month to intersect with six different calendar-weeks, if it includes the last day or two of one week, the entirety of the four weeks after that, and the first day or two of a week after that. The only way to "fix" it is to define "week #" in some different way; and that's a requirements issue, not something we can do for you. — ruakh, Oct 06 '12 at 15:05
Exactly right. As I said in my answer, the most important thing is to define the required behaviour up front, and then just work to that. — Scott Earle, Oct 06 '12 at 15:14

score 5 · Accepted Answer · answered Oct 06 '12 at 15:11

Dates are hard, because calendars are illogical.

I'm assuming Gregorian calendar in my comments here.

If you are grouping by 'weeks of the month' you need to define first what you expect to see. If you want 'week one' of each month to start on the first day of the month, then every month will have five weeks except for February (three times out of four).

If you want the 'week' to start on a particular day, then every month will have five or six weeks, except for the occasional February.

Assuming that you want the first 'week of the month' to be days 1-7, and the second to be days 8-14, etc., then your best bet is to use something like

CASE
    WHEN DATEPART(DAY, PaymentDate) BETWEEN 1 AND 7 THEN 1
    WHEN DATEPART(DAY, PaymentDate) BETWEEN 8 AND 14 THEN 2
    WHEN DATEPART(DAY, PaymentDate) BETWEEN 15 AND 21 THEN 3
    WHEN DATEPART(DAY, PaymentDate) BETWEEN 22 AND 28 THEN 4
    ELSE 5
END

(for example).

The most important thing to do, however, is to define exactly how you want it to behave in every single case. The query can then follow that.

score 1 · Answer 2 · answered Oct 06 '12 at 18:49

Thank you for all your Help, Just worked it Out :-)

SELECT 
             SUM(Amount) AS 'Amount',
             SUM(COALESCE(Balance,0)) AS 'Balance' ,
             CASE
            WHEN DATEPART(DAY, PaymentDate) BETWEEN 1 AND 7 THEN 1
            WHEN DATEPART(DAY, PaymentDate) BETWEEN 8 AND 14 THEN 2
            WHEN DATEPART(DAY, PaymentDate) BETWEEN 15 AND 21 THEN 3
            WHEN DATEPART(DAY, PaymentDate) BETWEEN 22 AND 28 THEN 4
            ELSE 5
        END   AS MyWeek

        FROM Payment
        WHERE     (MONTH(PaymentDate) = MONTH('2012-09-01'))
        GROUP BY (CASE
            WHEN DATEPART(DAY, PaymentDate) BETWEEN 1 AND 7 THEN 1
            WHEN DATEPART(DAY, PaymentDate) BETWEEN 8 AND 14 THEN 2
            WHEN DATEPART(DAY, PaymentDate) BETWEEN 15 AND 21 THEN 3
            WHEN DATEPART(DAY, PaymentDate) BETWEEN 22 AND 28 THEN 4
            ELSE 5
        END 
        )
        GO

if you use Scott's solution, don't repeat his answer. Just choose his answer — t-clausen.dk, Oct 06 '12 at 19:51

score 0 · Answer 3 · answered Oct 06 '12 at 22:11

This is how I would write your query:

  SELECT DATEPART(MONTH,PaymentDate) AS 'Month',
         datepart(wk,PaymentDate)-datepart(wk,'20120901')+1 AS 'Week #',
         SUM(COALESCE(Amount,0)) AS 'Amount',
         SUM(COALESCE(Balance,0)) AS 'Balance'
    FROM Payment
   WHERE PaymentDate >= '20120901'
     AND PaymentDate <  '20121001'
GROUP BY DATEPART(MONTH,PaymentDate),
         datepart(wk,PaymentDate);

I changed the PaymentDate filter to be a range, to fix 2 errors:
(1) It did not take into account the year
(2) It was not SARGABLE, i.e. because you are performing a function on the PaymentDate column, it would have ignored any indexes on the PaymentDate column

While your accepted solution allows for only 5 week # values, since it defines weeks as 1-7, 8-14 etc, this one allows the month December 2012 to be numbered as follows:

Sun Mon Tue Wed Thu Fri Sat   Week #
                         1     1
 2   3   4   5   6   7   8     2
 9  10  11  12  13  14  15     3
16  17  18  19  20  21  22     4
23  24  25  26  27  28  29     5
30  31                         6

SQL- Week number of the Month NOT the Year & Why Query is Returning 6 Week in a Month

3 Answers3