Group by bi-weekly

Okay, I cannot think of a clever way to group things into 2 w
periods. One w is easy with something like:
group by DateDiff(wk, '01/1/2006', SomeDate)
but I have 2 w periods. The ws are Sun-Sat. Suggestions? Thanks!
-John
Maybe this
group by (DateDiff(wk, '01/1/2006', SomeDate) - 1)/2
Alternatively use a calendar table
http://www.aspfaq.com/2519|||Not sure how your tables are structured, but based on the GROUP BY clause
you posted you could use integer division like:
GROUP BY DATEDIFF( wk, '01/01/2006', dt_col ) / 2 ;
This will return an integer for the odd numbers resulting with DATEDIFF
function.
Anith|||Think SQL and sets, not procedural code. Create an auxiliary reporting
periods table will about 10 years of your fiscal calendar in it.
CREATE TABLE ReportPeriods
(period_name CHAR(10) NOT NULL,
period_type CHAR(5) NOT NULL
CHECK (period_type IN ('biwk', 'mnth', 'annl', ..) )
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date)),
PRIMARY KEY (period_name, start_date));
Now just use a BETWEEN predicate in your JOINs. Oh, did you notice
that this is a general tool that is non-proprietary, adjust for
holidays, and can be used for all your other reporting problems? Beat
the heck out of doing temporal math!|||CELKO The man!
"--CELKO--" wrote:

> Think SQL and sets, not procedural code. Create an auxiliary reporting
> periods table will about 10 years of your fiscal calendar in it.
> CREATE TABLE ReportPeriods
> (period_name CHAR(10) NOT NULL,
> period_type CHAR(5) NOT NULL
> CHECK (period_type IN ('biwk', 'mnth', 'annl', ..) )
> start_date DATETIME NOT NULL,
> end_date DATETIME NOT NULL,
> CHECK (start_date < end_date)),
> PRIMARY KEY (period_name, start_date));
> Now just use a BETWEEN predicate in your JOINs. Oh, did you notice
> that this is a general tool that is non-proprietary, adjust for
> holidays, and can be used for all your other reporting problems? Beat
> the heck out of doing temporal math!
>