Need to do a crosstab report, that lists the week across the top, and in
the left hand column the months; cf. example:

WK1 WK2 WK3 WK4 WK5
JAN $ $ $ $ $
FEB
MAR

I know you need to use the T-SQL function datepart, but it doesn't seem to
work very well. Here is what I've done so far:
Select datepart(mm,'07/01/2004'),datepart(ww,'07/05/2004') -
datepart(ww,'07/01/2004')
This will give me the month, and the week. Can anyone improve on this for
my crosstab report??? The data is coming from one table, with the fields
sales_date,sales_amt,cust_no,sales_id - obviously, I can't input the
sales_date hardcoded as in my example. Also, I'm pretty sure you have to
use the sum function.

Any suggestions????

[posted and mailed, please reply in news]

William F. O'Neill (wfoneill@bellsouth.net) writes:
[quoted text, click to view]

I'm not sure that I understand this correctly, but I assume that WK1 is
the first week of the month, from the 1st to the next Sunday (or whenever
you think that the week ends). WK2 is from the first Monday after the 1st
of month and so on.

The week number within the month would be:

SELECT datediff(wk, convert(char(6), datecol, 112) + '01', datecol) + 1

datediff counts crossings of date boundaries. So if datecol is '20041003',
a Sunday, the above gives 2 with the default setting for DATEFIRST (which
says that weeks starts on Sundays).

Since this expression could crop in a number of places, it may be an
idea to fill in a table with a mapping from date to week number with
month.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at