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)

GROUP BY syntax

I'm trying to group rows in ItemKey to produce a sum total of prices. Then
update ItemKeyprice. The select statement works, but I get the below error.
Where is my syntax wrong?
thanks
UPDATE ItemKeyPrice
SET RegPrice =
(SELECT ItemKey.KeyNumber, SUM(ItemKey.Price) AS PriceInd
FROM ItemKey INNER JOIN ItemKeyPrice ON ItemKey.KeyNumber =
ItemKeyPrice.KeyNumber
GROUP BY ItemKey.KeyNumber)
error: Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS.shank
(untested)
UPDATE ItemKeyPrice
SET RegPrice =
(SELECT SUM(ItemKey.Price) AS PriceInd
FROM ItemKey INNER JOIN ItemKeyPrice ON ItemKey.KeyNumber =
ItemKeyPrice.KeyNumber
GROUP BY ItemKey.KeyNumber)
Note: Without seeing your table stucture + samle data I could only guess
,it is possible yo9u get an error like
"Subquery returned more than 1 value."
"shank" <shank@.tampabay.rr.com> wrote in message
news:eTrms$l$FHA.1288@.TK2MSFTNGP09.phx.gbl...
> I'm trying to group rows in ItemKey to produce a sum total of prices. Then
> update ItemKeyprice. The select statement works, but I get the below
> error. Where is my syntax wrong?
> thanks
> UPDATE ItemKeyPrice
> SET RegPrice =
> (SELECT ItemKey.KeyNumber, SUM(ItemKey.Price) AS PriceInd
> FROM ItemKey INNER JOIN ItemKeyPrice ON ItemKey.KeyNumber =
> ItemKeyPrice.KeyNumber
> GROUP BY ItemKey.KeyNumber)
> error: Only one expression can be specified in the select list when the
> subquery is not introduced with EXISTS.
>|||shank (shank@.tampabay.rr.com) writes:
> I'm trying to group rows in ItemKey to produce a sum total of prices.
> Then update ItemKeyprice. The select statement works, but I get the
> below error. Where is my syntax wrong?
> thanks
> UPDATE ItemKeyPrice
> SET RegPrice =
> (SELECT ItemKey.KeyNumber, SUM(ItemKey.Price) AS PriceInd
> FROM ItemKey INNER JOIN ItemKeyPrice ON ItemKey.KeyNumber =
> ItemKeyPrice.KeyNumber
> GROUP BY ItemKey.KeyNumber)
> error: Only one expression can be specified in the select list when the
> subquery is not introduced with EXISTS.
The immediate error is the inclusion of ItemKey.KeyNumber in the Select
list. You are assigning RegPrice value, but you are sending it two
values.
However, when you fix that error, you will get another error saying that
subquery returned more than one value.
Here are two ways of writing what I think you want to achieve:
UPDATE ItemKeyPrice
SET RegPrice = (SELECT SUM(ItemKey.Price)
FROM ItemKey
WHERE ItemKey.KeyNumber = ItemKeyPrice.KeyNumber)
UPDATE ItemKeyPrice
SET RegPrice = ik.totprice
FROM ItemKeyPrice ikp
JOIN (SELECT KeyNumber, totprice = SUM(ItemKey.Price)
FROM ItemKey
GROUP BY KeyNumber) AS ik ON ik.KeyNumber = ikp.KeyNumber
The first uses a correlated subquery, and this syntax is by the ANSI
standard and should run on any DBMS. The second uses a FROM clause in
the UPDATE statement. This syntax is particular to MS SQL Server and
Sybase, and thus less portable. This query also includes a derived
table (which is part of ANSI SQL) to compute the sums per key.
While the first syntax is portable, my preference is very strongly
for the second, as it is easier to write and understand, and usually
also gives better performance. Furthermore, assume that you would have
more columns to set, like an average price, min price etc. In this case,
you would have to have several correlated subqueries, but the FROM
solution is very extensible in this regard.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Group By problem

Hi,

I need help on constructing a SQL statement that will produce me the following result:

The table is:

TABLE sample
F1 F2
-- --
1 a
1 b
2 c
2 d

select F1, ? what group function?
from sample
group by F1

Result should be:
F1 xxx
-- ---
1 a,b
2 c,d

Thanks for all the help.select F1,F2 from sample group by f1 order by f1|||Originally posted by kharsa60
select F1,F2 from sample group by f1 order by f1

I don't think this is possible since F2 is not a group by function. I still tried it though and got the following error message:

Column 'sample.f2' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Any other suggestions?

Thanks.|||using pl/sql and or java you can do this quite easily. pseudo code;

rs1="select distinct(f1) from x order by f1"
while rs1.next
{
display rs1.f1
rs2="select f2 from x where f1=rs1.f1 order by f2"
while rs2.next
{
display rs2.f2
}
}|||I believe the only way you will get your output is with a cursor. This example is against PUBS database.
DECLARE @.pline varchar(200),
@.title_id tid,
@.cur_title tid,
@.au_id id

SET @.pline = ''

DECLARE out_cur CURSOR
READ_ONLY
FOR
SELECT title_id,
au_id
FROM titleauthor
ORDER BY
title_id

OPEN out_cur

FETCH NEXT FROM out_cur INTO @.title_id, @.au_id
SET @.cur_title= @.title_id

WHILE (@.@.fetch_status <> -1) BEGIN
IF (@.@.fetch_status <> -2) BEGIN
IF ( @.cur_title=@.title_id )
SET @.pline = @.pline + @.au_id + ', '
ELSE BEGIN
PRINT @.cur_title + ' : ' + SUBSTRING(@.pline,1,LEN(@.pline)-2)
SET @.pline = @.au_id + ', '
SET @.cur_title= @.title_id
END
END
FETCH NEXT FROM out_cur INTO @.title_id, @.au_id
END

CLOSE out_cur
DEALLOCATE out_cur
GO

The PL/SQL code that rhs98 showed can be done using ADO and VB,VBScript,Perl,etc. However I believe the output is not in the format that you want, it looks like the output would be a list.

F1
F2
F2
F1
F2
F2
F1
F2|||yeah ok, i see now, easily fixed though (that was only psuedo code btw). just don't use a println use a print; so the subsequent output stays on the same line.

or if that is a problem, you could just build up the output using a string or a buffer, and then display it before moving to the next loop.

I think this may be quicker to implement, but slightly slower than the pl/sql?|||guess it's really which way you know best; pl/sql or java...|||Considering that this is Microsoft SQL Server, PL/SQL is out and T-SQL is in.|||Thanks all. I see that there is no way of doing it via SQL statement. So I'll just going to do it in java then, since I am actually using java. I just thought I can do it with one SQL statement.

Thanks a lot.|||ooooooops :D
missed that one... :rolleyes: