Grouping Data By Weeks

I have a requirement to produce a report that breaks down some data into
totals by w. The data is in SQL Server, but the user just wants a one-off
report, so we can use Access or Excel as alternatives is more suitable.
We have a table of stock movements, and we want to total the number of
incoming and outgoing items for each w. We obviously know the dates of
these movements, but I'm unclear as to the SQL (or even general approach)
needed to break down this data.
StockMovements Table:
StockMoveID int
SerialNo int
MoveDate int
LocationFrom int
LocationTo int
etc
Report Format:
Wk Beginning | Num Issued | Num Returned | Running Total | Num Overdue
Any suggestions or pointers?
Thanks in advance,
Chris
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]Do you want to use ISO ws or just a fixed 7-day w?
For a fixed 7-day w:
SELECT ...
FROM YourTable
GROUP BY ROUND(DATEDIFF(D,'20000101',movedate)/7.0,0,1)
(where '20000101' is a "base date" representing your chosen beginning
date of some arbitrary w)
For ISO ws look at the CREATE FUNCTION topic in Books Online for the
relevant formula.
David Portas
SQL Server MVP
--|||SELECT *
FROM <TABLE>
GROUP BY datepart(ww, <date> )
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"CJM" wrote:

> I have a requirement to produce a report that breaks down some data into
> totals by w. The data is in SQL Server, but the user just wants a one-o
ff
> report, so we can use Access or Excel as alternatives is more suitable.
> We have a table of stock movements, and we want to total the number of
> incoming and outgoing items for each w. We obviously know the dates of
> these movements, but I'm unclear as to the SQL (or even general approach)
> needed to break down this data.
> StockMovements Table:
> StockMoveID int
> SerialNo int
> MoveDate int
> LocationFrom int
> LocationTo int
> etc
> Report Format:
> Wk Beginning | Num Issued | Num Returned | Running Total | Num Overdue
> Any suggestions or pointers?
> Thanks in advance,
> Chris
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>|||Be careful with the w returned by DATEPART. The w numbering
convention it uses is a bit unusual. Even assuming that the DATEFIRST
setting is correct many people won't find the result of this function
useful.
David Portas
SQL Server MVP
--|||David,
Thanks for that - it looks to be a start, but I'm not quite there yet.
As I mention before I want the following columns for the report: W
commencing, Total Sent, Total Received, Running Total, & Total Overdue
I've started creating the SQL for the Total Sent/Received, but I still have
a problem - your code provides for a w number but how do I engineer the
W Commencing date from this?
Select Count(*) as NumSent, ROUND(DATEDIFF(D,'20031229',movedate)/7.0,0,1)
as Wk
from StockMovements
where LocationTo = 2
and MoveDate > '20031231'
and MoveDate < '20050601'
GROUP BY ROUND(DATEDIFF(D,'20031229',movedate)/7.0,0,1)
Select Count(*) as NumRecd, ROUND(DATEDIFF(D,'20031229',movedate)/7.0,0,1)
as Wk
from StockMovements
where LocationFrom = 2
and MoveDate > '20031231'
and MoveDate < '20050601'
GROUP BY ROUND(DATEDIFF(D,'20031229',movedate)/7.0,0,1)
Also, how do I combine these into one query? I tried Select (Select
...etc), (Select...etc) but it came up with an error:
"Only one expression can be specified in the select list when the subquery
is not introduced with EXISTS"
I'm not clear on what this means, not how to avoid it.
Thanks for your help so far...
Chris|||You might want to look up the ISO definition of a w within a year,
since it is different from Microsoft's and finally talk to teh
accounting department about the ws in the fiscal calendar. The best
way to handle this is to set up a calendar table with yourt fiscal
ws in it.|||Hello,
I notice you have posted the same question in our newsgroup, which I have
already responded. So please check my answer there.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||On Thu, 26 May 2005 15:09:54 +0100, CJM wrote:

>Thanks for that - it looks to be a start, but I'm not quite there yet.
>As I mention before I want the following columns for the report: W
>commencing, Total Sent, Total Received, Running Total, & Total Overdue
>I've started creating the SQL for the Total Sent/Received, but I still have
>a problem - your code provides for a w number but how do I engineer the
>W Commencing date from this?
(snip)
>Also, how do I combine these into one query?
Hi CJM,
I noticed that you crossposted this to a SQL Server group and an Access
group. Since my answer works in SQL Server only, I removed the access
group for my reply.
Check if the following works (note: this assumes you consider monday to
be the first day of the wee; change the date constant '20031229' to
something else if you need another first day of the w - and remember
that it has to be changed in all four places it's used!)
SELECT DATEADD(day,
DATEDIFF(day, '20031229', MoveDate) / 7 * 7,
'20031229') AS WCommencingDate,
COUNT(CASE WHEN LocationTo = 2 THEN 1 END) AS TotalSent,
COUNT(CASE WHEN LocationFrom = 2 THEN 1 END) AS TotalReceived
FROM StockMovements
WHERE MoveDate > '20031231'
AND MoveDate < '20050601'
GROUP BY DATEADD(day,
DATEDIFF(day, '20031229', MoveDate) / 7 * 7,
'20031229')
This is untested, since you didn't post CREATE TABLE and INSERT
statements to create a test database. See www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)