Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
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 9:43am

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

use this query:
 
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...
--------------------------------------------------------------
  Permalink  
Comments
Milind Thakkar at 12-Nov-12 0:11am
   
I don;t know Access. trying to understand and confused by the multiplication. Why year multiplied by 100 ?
Andrew Cherednik at 12-Nov-12 0:20am
   
This is a trick to allow grouping by month. See, how the result magically become 'YYYYMM'
Sunil Bansode at 12-Nov-12 21:24pm
   
THANKS..!!!

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

  Print Answers RSS
0 OriginalGriff 7,105
1 DamithSL 5,079
2 Maciej Los 4,866
3 Sergey Alexandrovich Kryukov 4,617
4 Kornfeld Eliyahu Peter 4,384


Advertise | Privacy | Mobile
Web03 | 2.8.141223.1 | Last Updated 11 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100