Grouping Dates

SELECT     TOP 100 w.TimeDate AS [date], p.ProdCode, COUNT(w.BoxID) AS
cases, AVG(w.PrintedWeight / w.PkgsPerBox) AS avgtrayweight
FROM         dbo.WIPSixtyDay w INNER JOIN
dbo.Products p ON w.PLUNo = p.PLUNo
WHERE     (p.PrePack = 1) AND (w.PkgsPerBox > 0)
GROUP BY w.TimeDate, p.ProdCode
ORDER BY w.TimeDate


This is what I need to do...... sum these by the date(w.timedate) my
original I was converting it to a string, but then none of my date
queries would work....



Here is my original
SELECT     TOP 100  CONVERT(varchar(10),
 w.TimeDate, 101) AS [date],
p.ProdCode, COUNT(w.BoxID) AS cases, AVG(w.PrintedWeight /
w.PkgsPerBox)
AS avgtrayweight
FROM         dbo.WIPSixtyDay w INNER JOIN
dbo.Products p ON w.PLUNo = p.PLUNo
WHERE     (p.PrePack = 1) AND (w.PkgsPerBox > 0)
GROUP BY  CONVERT(varchar(10),
 w.TimeDate, 101), p.ProdCode
ORDER BY  CONVERT(varchar(10),
 w.TimeDate, 101)


This is in a View....if that matters...

msg (msg35803@yahoo.com)  writes:
> SELECT     TOP 100 w.TimeDate AS [date], p.ProdCode, COUNT(w.BoxID) AS
> cases, AVG(w.PrintedWeight / w.PkgsPerBox) AS avgtrayweight
> FROM         dbo.WIPSixtyDay w INNER JOIN
>                       dbo.Products p ON w.PLUNo = p.PLUNo
> WHERE     (p.PrePack = 1) AND (w.PkgsPerBox > 0)
> GROUP BY w.TimeDate, p.ProdCode
> ORDER BY w.TimeDate
>
>
> This is what I need to do...... sum these by the date(w.timedate) my
> original I was converting it to a string, but then none of my date
> queries would work....

I'm sorry, but I will have to ask: what is the question?




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

On 14 Dec 2005 13:27:21 -0800, msg wrote:

>SELECT     TOP 100 w.TimeDate AS [date], p.ProdCode, COUNT(w.BoxID) AS
>cases, AVG(w.PrintedWeight / w.PkgsPerBox) AS avgtrayweight
>FROM         dbo.WIPSixtyDay w INNER JOIN
>                      dbo.Products p ON w.PLUNo = p.PLUNo
>WHERE     (p.PrePack = 1) AND (w.PkgsPerBox > 0)
>GROUP BY w.TimeDate, p.ProdCode
>ORDER BY w.TimeDate
>
>
>This is what I need to do...... sum these by the date(w.timedate) my
>original I was converting it to a string, but then none of my date
>queries would work....
>
>
>
>Here is my original
>SELECT     TOP 100  CONVERT(varchar(10),
 w.TimeDate, 101) AS [date],
>p.ProdCode, COUNT(w.BoxID) AS cases, AVG(w.PrintedWeight /
>w.PkgsPerBox)
>                      AS avgtrayweight
>FROM         dbo.WIPSixtyDay w INNER JOIN
>                      dbo.Products p ON w.PLUNo = p.PLUNo
>WHERE     (p.PrePack = 1) AND (w.PkgsPerBox > 0)
>GROUP BY  CONVERT(varchar(10),
 w.TimeDate, 101), p.ProdCode
>ORDER BY  CONVERT(varchar(10),
 w.TimeDate, 101)
>
>
>This is in a View....if that matters...

Hi msg,

Try if this works:

SELECT TOP 100 DATEADD(day, DATEDIFF(day, 0, w.TimeDate), 0) AS [date],
p.ProdCode, COUNT(w.BoxID) AS cases,
AVG(w.PrintedWeight / w.PkgsPerBox) AS avgtrayweight
FROM       dbo.WIPSixtyDay AS w
INNER JOIN dbo.Products AS p
ON   w.PLUNo = p.PLUNo
WHERE      p.PrePack = 1
AND        w.PkgsPerBox > 0
GROUP BY   DATEDIFF(day, 0, w.TimeDate), p.ProdCode
ORDER BY   [date]

(untested - see www.aspfaq.com/5006 if you prefer a tested reply)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

i hate variable names the same as internal functions. like "date".

without knowing exactly what the data types are, it is a little tough.
You might look at the functions DTOS, DTOC, CTOD, and read CONVERT
regarding dates once again.

Date data types can include minutes, seconds, adn all sorts of weird
stuff. "Date" is a funny thing. Date can mean the exact instant
something happened, or it can mean day month year, or it can mean year
month date, or it can mean ...........