Prev: Adding Statistics
Next: Like Operator with Wildcard
From: Jonathan on 17 Mar 2010 22:11
Hi using sql2005, I would like to return summary statistics that are grouped
by weekending date (Friday) between date range start and end parameters.

Any ideas or suggestions appreciated :-)

Many thanks,
Jonathan
From: Plamen Ratchev on 17 Mar 2010 22:24
You can use a formula based on well know Friday date (here Jan 5, 1900). Replace the WHERE clause predicate literals
with your parameters for start/end period.

SELECT DATEADD(DAY, (DATEDIFF(DAY, '19000105', date_column) / 7) * 7, '19000112') AS friday_date,
COUNT(*) AS cnt
FROM Foo
WHERE date_column >= '20100101'
AND date_column < '20100401'
GROUP BY DATEADD(DAY, (DATEDIFF(DAY, '19000105', date_column) / 7) * 7, '19000112');

--
Plamen Ratchev
http://www.SQLStudio.com
From: Jonathan on 17 Mar 2010 23:48
Awesome, many thanks
Jonathan

"Plamen Ratchev" wrote:

> You can use a formula based on well know Friday date (here Jan 5, 1900). Replace the WHERE clause predicate literals
> with your parameters for start/end period.
>
> SELECT DATEADD(DAY, (DATEDIFF(DAY, '19000105', date_column) / 7) * 7, '19000112') AS friday_date,
> COUNT(*) AS cnt
> FROM Foo
> WHERE date_column >= '20100101'
> AND date_column < '20100401'
> GROUP BY DATEADD(DAY, (DATEDIFF(DAY, '19000105', date_column) / 7) * 7, '19000112');
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
> .
>
 | 
Pages: 1
Prev: Adding Statistics
Next: Like Operator with Wildcard