Microsoft SQL Server forum

Author

Grouping Dates

msg

2005-12-14, 8:25 pm

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

Erland Sommarskog

2005-12-14, 8:25 pm

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

Hugo Kornelis

2005-12-14, 8:25 pm

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)

Doug

2005-12-16, 1:23 pm

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

Sponsored Links