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... 3 1424
msg (ms******@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, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/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 ........... This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Brian Coy |
last post by:
I am creating a database to track scrap on a daily basis at my plant.
I have to provide a weekly scrap report with the amount of each part
scrapped per day. I have the basic database set up, and...
|
by: Miguelito Bain |
last post by:
hi-
i have a simple table. i have 3 fields, id, indate, and outdate. i
want to group these fields in my report by month. for example, for
february 2004, i want to display all the ids and dates...
|
by: Alicia |
last post by:
Hi, I am trying to group by week and do a count of my data in between
the dates. In Microsoft Access.
Something similar to
Before:
Date Count
--------- ---------...
|
by: Alicia |
last post by:
Yes, but will that skip a week and group by the date for me? I
basically wanted something that would do a count of the dates, then
group them by their week name..
BEFORE:
Resource Date ...
|
by: Peter Bailey |
last post by:
I have a query that returns , and :
12/05/04 3 Wednesday
13/05/04 0 Thursday
and so on
what I would like to do now is count the number of bookings by week so from
monday to...
|
by: John |
last post by:
I'm building a report where each row represents one month and its 1 to
5 weeks. A report query groups the source table date into a "month and
year" expression. A subreport links to the main report...
|
by: Tom |
last post by:
- matches "a", "b", or "c"
- matches anything except "a", "b", and "c"
(abc) - matches "abc", with back reference
??? - matches anything except "abc" literally?? What is the syntax for
this...
|
by: jnice814 |
last post by:
I have created a frmTimesheet form where auditors will enter their hours for audits that they've worked on. I know how to build a very basic, no-frills database, and what I'm trying to accomplish...
|
by: CoopEng |
last post by:
Hey Team,
Here's one for you all to ponder. In Access 2003, I'm updating a Operations Database for a manufacturing plant. The data is of production runs for the plant. So there are start date's...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |