Posted by Hugo Kornelis on 12/15/05 02:08

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)

[Back to original message]