Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL VB.NET
Respected sir,
I m developing windows application using vb.net with MS-Access as backend.
I want to generate sales report by selecting perticuler month
for that i m trying to develope sql query...

table : Stock
----------------------------------------------
id item      size unit  MRP
----------------------------------------------
1  abc        500 gm    120  
2  xyz        500 gm    180
3  pqr        150 gm     60
4  lmn         50 gm     20
5  pqr          1 kg    500
----------------------------------------------    
 
table : Sale
---------------------------------------------
sr   Billno  bill_Date    itemID  Qnty   Total
---------------------------------------------
1     123    14/11/2013    1      2      240
2     123    14/11/2013    3      2      120
3     123    14/11/2013    4      3       60
4     124    20/02/2013    5      2     1000
5     124    20/02/2013    3      5      300  
---------------------------------------------
 
I want to show required month's sales report like below
for example month=11
 
sr  ItemName size unit  MRP  totalQnty TotalAmt
--------------------------------------------------
1.    abc     500 gm    120     1         240
2.    xyz     500 gm    180     0           0
3.    pqr     150 gm     60     7         420
4.    lmn      50 gm     20     3          60
5.    pqr       1 kg    500     0           0
---------------------------------------------------
here i want to dislpay all the items present in stock and beside that how many items are sold in this perticular month,if that item is not sold in given month then it should be blank as i shown in above example sr. 5 pqr was sold in month no=2 and not in 11 so its showing zero..likewise...
 
what would be SQL query for this..
 
Please help me,
Thank you,..
Posted 4-Jan-13 20:38pm
Comments
Sandeep Mewara at 5-Jan-13 2:21am
   
Have you tried to write the query by yourself - anything so far?
Sunil Bansode at 5-Jan-13 9:58am
   
yes i have tried so much but didnt get proper result..
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You have to use LEFT OUTER JOIN for this. Please try following
 
SELECT stock.item AS ItemName, stock.size,
stock.unit,stock.MRP,ISNULL(MonthSale.Qnty,0) AS totalQnty, ISNULL(MonthSale.Total,0) AS TotalAmt
FROM stock LEFT OUTER JOIN (SELECT sale.itemID,SUM(sale.Qnty) AS Qnty,SUM(sale.Total) AS Total
FROM sale
WHERE YEAR(sale.bill_Date)=2013 AND MONTH(sale.bill_Date)=11
GROUP BY sale.itemID, sale.billdate) MonthSale
on stock.id = MonthSale.itemID
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Try this:
 
;WITH cte as
(SELECT t.id
,t.item
,t.size
,t.unit
,t.MRP
,CASE WHEN MONTH(a.bill_Date)=11 THEN sum(a.Qnty)
ELSE 0 END AS totalQnty
,CASE WHEN month(a.bill_Date) = 11 THEN sum(a.Total)
ELSE 0 END AS totalAmt,a.bill_Date
FROM Stock t
left JOIN sale a ON t.id = a.itemID
WHERE a.bill_Date IS NULL OR MONTH(a.bill_Date) = 11
GROUP BY a.itemID,t.item,t.size,t.unit,t.MRP,t.id,a.bill_Date)
 
SELECT s.item AS ItemName,
       s.size,
       s.unit,
       s.MRP,
       isnull(c.totalQnty,0) AS TotalQnty,
       isnull(c.totalAmt,0) AS TotalAmt
FROM Stock as s
left join cte as c ON s.id = c.id
 
Actually your result should be as follows for 11th month for your requirement:
 
sr ItemName size unit MRP totalQnty TotalAmt
-----------------------------------------------------------------------------
1. abc 500 gm 120 2 240
2. xyz 500 gm 180 0 0
3. pqr 150 gm 60 2 120
4. lmn 50 gm 20 3 60
5. pqr 1 kg 500 0 0
------------------------------------------------------------------------------
 
You incorrectly calculated totlaQnty of item 'abc' and TotalAmt of pqr.
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

This way...
select id,item,size,unit,MRP, sum(Qnty) as totalQnty, sum(Total) as TotalAmt
from stock s
left join Sale sl on s.id=sl.itemid
Where month(bill_Date) = 2
group by id,item,size,unit,MRP
Happy Coding!
Smile | :)
  Permalink  
v2

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

  Print Answers RSS
0 OriginalGriff 390
1 Jochen Arndt 165
2 Richard MacCutchan 135
3 DamithSL 95
4 Garth J Lancaster 90
0 OriginalGriff 6,045
1 DamithSL 4,601
2 Maciej Los 4,032
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,220


Advertise | Privacy | Mobile
Web03 | 2.8.141220.1 | Last Updated 5 Jan 2013
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