[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 531
  • Last Modified:

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.
0
jnordeng
Asked:
jnordeng
  • 2
  • 2
1 Solution
 
TheAvengerCommented:
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
0
 
jnordengAuthor Commented:
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
0
 
TheAvengerCommented:
You can also get the month (e.g. the first day of the month) directly in the SQL. Instead of getting the year and month you would then create a new date by using this formula:

dateadd(month,((YEAR(Tech.dbo.CHISM_oemain.EnteredDate)-1900)*12)+MONTH(Tech.dbo.CHISM_oemain.EnteredDate)-1,0)

So the whole will be:

SELECT Tech.dbo.CHISM_oemain.WhseID, Tech.dbo.Chism_oeline.ProductId, dateadd(month,((YEAR(Tech.dbo.CHISM_oemain.EnteredDate)-1900)*12)+MONTH(Tech.dbo.CHISM_oemain.EnteredDate)-1,0) 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, dateadd(month,((YEAR(Tech.dbo.CHISM_oemain.EnteredDate)-1900)*12)+MONTH(Tech.dbo.CHISM_oemain.EnteredDate)-1,0)
ORDER BY Tech.dbo.CHISM_oemain.WhseId, Tech.dbo.Chism_oeline.ProductId, dateadd(month,((YEAR(Tech.dbo.CHISM_oemain.EnteredDate)-1900)*12)+MONTH(Tech.dbo.CHISM_oemain.EnteredDate)-1,0)
0
 
jnordengAuthor Commented:
Avenger is awesome!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now