asked on

DataTable average by month

I have data from the following query loaded in a datatable. Now I need to average sales by each month for each item grouped by warehouse in my vb.net windows application. Date format for the info is e.g. 6/22/2011 12:00:00 AM.

SELECT Tech.dbo.CHISM_oemain.WhseID, Tech.dbo.CHISM_oemain.EnteredDate,Tech.dbo.Chism_oeline.TicketId, Tech.dbo.Chism_oeline.ProductId, Tech.dbo.Chism_oeline.UnitsOrdered FROM Tech.dbo.CHISM_oemain INNER JOIN Tech.dbo.Chism_oeline ON Tech.dbo.CHISM_oemain.Ticketid=Tech.dbo.Chism_oeline.TicketId WHERE Tech.dbo.CHISM_oemain.EnteredDate >= '10/27/2008' ORDER BY Tech.dbo.CHISM_oemain.WhseId, Tech.dbo.Chism_oeline.ProductId"

Information gathered will be loaded into this table

dtc.Columns.Add("WhseID")
dtc.Columns.Add("ProductID")
dtc.Columns.Add("MonthName")
dtc.Columns.Add("AvgQty")

Thanks so much for your help.

TheAvenger

Can you make another call to the database? Because it is easiest to use GROUP BY on the server side, something like:

SELECT Tech.dbo.CHISM_oemain.WhseID, Tech.dbo.Chism_oeline.ProductId, YEAR(Tech.dbo.CHISM_oemain.EnteredDate), MONTH(Tech.dbo.CHISM_oemain.EnteredDate), AVG(Tech.dbo.Chism_oeline.UnitsOrdered)
FROM Tech.dbo.CHISM_oemain INNER JOIN Tech.dbo.Chism_oeline ON Tech.dbo.CHISM_oemain.Ticketid=Tech.dbo.Chism_oeline.TicketId
WHERE Tech.dbo.CHISM_oemain.EnteredDate >= '10/27/2008'
GROUP BY Tech.dbo.CHISM_oemain.WhseID, Tech.dbo.Chism_oeline.ProductId, YEAR(Tech.dbo.CHISM_oemain.EnteredDate), MONTH(Tech.dbo.CHISM_oemain.EnteredDate)
ORDER BY Tech.dbo.CHISM_oemain.WhseId, Tech.dbo.Chism_oeline.ProductId, YEAR(Tech.dbo.CHISM_oemain.EnteredDate), MONTH(Tech.dbo.CHISM_oemain.EnteredDate)

In VB you can then create the real month info by making a new DateTime (year, month, 1) where year and month come from the SQL

Nasir Razzaq

If you still want to group in the data table then

http://codecorner.galanter.net/2009/04/20/group-by-and-aggregates-in-net-datatable/

http://support.microsoft.com/kb/326145

http://stackoverflow.com/questions/1225710/linq-to-dataset-multiple-group-by-on-a-data-table

jnordeng

ASKER

Good morning: Avenger. The column that is average is that suppose to be the qty sold that month in the query? Then I should do an average offline in vb and figure my month name in vb? My output and query as follows.....

SELECT Tech.dbo.CHISM_oemain.WhseID, Tech.dbo.Chism_oeline.ProductId, YEAR(Tech.dbo.CHISM_oemain.EnteredDate) as [YEAR], MONTH(Tech.dbo.CHISM_oemain.EnteredDate) as [MONTH], AVG(Tech.dbo.Chism_oeline.UnitsOrdered) as [AVERAGE]FROM Tech.dbo.CHISM_oemain INNER JOIN Tech.dbo.Chism_oeline ON Tech.dbo.CHISM_oemain.Ticketid=Tech.dbo.Chism_oeline.TicketId
WHERE Tech.dbo.CHISM_oemain.EnteredDate >= '10/27/2008'
GROUP BY Tech.dbo.CHISM_oemain.WhseID, Tech.dbo.Chism_oeline.ProductId, YEAR(Tech.dbo.CHISM_oemain.EnteredDate), MONTH(Tech.dbo.CHISM_oemain.EnteredDate)
ORDER BY Tech.dbo.CHISM_oemain.WhseId, Tech.dbo.Chism_oeline.ProductId, YEAR(Tech.dbo.CHISM_oemain.EnteredDate), MONTH(Tech.dbo.CHISM_oemain.EnteredDate)

WhseID      ProductId      YEAR      MONTH      AVERAGE
1      *104610.00      2010      10      1.000000
1      *104610.00      2010      11      -377.426875
1      *104610.00      2010      12      -8.964000
1      *104610.00      2011      1      -902.938571
1      *104610.00      2011      2      -1.000000
1      *104610.00      2011      4      -1.000000
1      *104610.00      2011      5      -1.000000
1      *104610.00      2011      6      -0.333333
1      *104610.00      2011      7      0.000000
1      *104610.00      2011      8      -0.333333
1      *104610.00      2011      9      -4189.307142
1      *104610.00      2011      10      -1.000000
1      *104617.00      2011      3      -1.000000
1      *104617.00      2011      4      -1.000000
1      *104617.00      2011      5      -1.000000
1      *104617.00      2011      6      -1.000000
1      *104617.00      2011      7      -1.000000
1      *107610.00      2010      10      -1.000000
1      *107610.00      2010      11      -126.000000

ASKER CERTIFIED SOLUTION

TheAvenger

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jnordeng

ASKER

Avenger is awesome!