how to make group by query with dates

Question

0.00/5 (No votes)

See more:

Respected sir/s,

i m using ms-access as backend and vb.net as front end for developing windows application...
In which i m using query for generate monthly report..
like bellow

Item_Name   TotQntyPurchsed    TotQntySale    TotQntyInStock
-------------------------------------------------------------
abc            15                 10              5
xyz            20                 18              2

--------------------------------------------------------------

here all the columns are grouped by for getting respective item total...

but i want this for every month..
then how can i do this with group by clause because sale and perchase will be on daily basis
or
any other idea to achieve this...?

plz help me..
thank you

Posted 11-Nov-12 8:43am

Sunil Bansode

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

chaau · Accepted Answer · 2012-11-11T12:25:00

use this query:

SQL

SELECT 
Item_Name,
YEAR(DatePurchased)*100+MONTH(DatePurchased) AS YearMonth,
Sum(QtyInStock) as TotQtyInStock
FROM Stock
GROUP BY Item_Name,
YEAR(DatePurchased)*100+MONTH(DatePurchased)

Then you'd have this:

Item_Name   YearMonth  TotQntyInStock
-------------------------------------------------------------
abc         201201      5
abc         201202      2
abc         201203      1
xyz         201201      1
xyz         201205      5
etc...
--------------------------------------------------------------

how to make group by query with dates

1 solution

Solution 1

Add your solution here

Preview 0

how to make group by query with dates

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us