Grouping Data By Weeks

  • Thread starter Thread starter CJM
  • Start date Start date
C

CJM

I have a requirement to produce a report that breaks down some data into
totals by week. 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 week. 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
 
Do you want to use ISO weeks or just a fixed 7-day week?

For a fixed 7-day week:

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 week)

For ISO weeks look at the CREATE FUNCTION topic in Books Online for the
relevant formula.
 
Be careful with the week returned by DATEPART. The week 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,

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: Week
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 week number but how do I engineer the
Week 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 week within a year,
since it is different from Microsoft's and finally talk to teh
accounting department about the weeks in the fiscal calendar. The best
way to handle this is to set up a calendar table with yourt fiscal
weeks in it.
 
Hello,

I test the issue on my side and the following code is for your reference:

-- create a table StockMovements
create table StockMovements
(
StockMoveID int,
SerialNo int,
MoveDate datetime,
LocationFrom int,
LocationTo int)

-- run the following query in SQL server Query Analyzer:


select DATEADD( ww , ROUND(DATEDIFF(D,'2004/01/01',movedate)/7.0,0,1) ,
'2004/01/01' ) 'Begin date of the week',
count(locationto) 'NumSent' ,
count(locationfrom) 'NumRecv',
count(locationto)+ count(locationfrom) 'totalCount'
from StockMovements
where
MoveDate > '20031231'
and MoveDate < '20050601'
GROUP BY DATEADD( ww , ROUND(DATEDIFF(D,'2004/01/01',movedate)/7.0,0,1) ,
'2004/01/01' )

I hope the information is helpful.

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.
 
Back
Top