Click here to Skip to main content
15,391,277 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I need a report to get the item details i.e the purchase and sales done for that item

What I have tried:

Select (A.ItemCode), A.ItemName, A.Barcode, A.Unit, A.PurchaseRate, A.SellingRate, A.DiscountPercent, A.OpeningStock, A.BatchNo, A.ExpiryDate AS ExpDate,
				B.Quantity AS StockQuantity,E.PurchaseDate,F.InvoiceDate from tblItemMaster A
				INNER JOIN tblStockMaster B ON B.ItemCode=A.ItemCode AND B.BatchNo=A.BatchNo
				INNER JOIN tblPurchaseDetails C ON C.ItemCode=A.ItemCode AND C.BatchNo=A.BatchNo
				INNER JOIN tblSalesDetails D ON D.ItemCode=A.ItemCode AND D.BatchNo=A.BatchNo
				INNER JOIN tblPurchaseMaster E ON E.PurchaseBill=C.PurchaseBill
				INNER JOIN tblSalesMaster F ON F.InvoiceNo=D.InvoiceNo
				Where A.ItemCode='10'
Posted
Updated 30-Jun-18 1:48am
Comments
#realJSOP 28-Jun-18 6:46am
   
Identifying the problem is 50% of coming up with a solution. What's your question?
Member 13569764 28-Jun-18 6:48am
   
i have sales table purchase table i need to get all the details from these two tables according to the item
#realJSOP 28-Jun-18 7:24am
   
That's nice, but you have SIX tables referenced in your query, and you're retrieving data from four of them. Which TWO are you referring to?
Member 13569764 28-Jun-18 7:28am
   
I have Sales as Master and Details 2 tables same for purchase
I need to get the query like this
Sno. ItemCode Type Date Quantity Rate Total
1 10 Sales 1/12/2018 5 20 25
2 10 Purchase 11/11/2018 50 18 500
3 ......
#realJSOP 28-Jun-18 8:22am
   
Edit your question so someone can answer it.
W Balboos, GHB 28-Jun-18 8:45am
   
Let me rephrase for you what is in the past several responses:

You show us what you tried. Well? What happened?

As you wrote it, for all we know, what you tried worked perfectly.

Your "INNER JOINS" will yield nothing if ANY table does not contain a corresponding record (i.e. you're assuming every item has "sales" AND "purchases").

You need to consider "OUTER JOINS".

You should probably also do a "UNION" on your sales and purchase details as "transactions".
   
v2
Comments
kmoorevs 30-Jun-18 17:55pm
   
Just to add, subqueries in the following form can solve many problems:
Select Sum(sq.Sum_Sales) as Sales, Sum(sq.Sum_Purchases) as Purchases, sq.ItemCode
From (Select Sum(A.Sales) as Sum_Sales, 0 as Sum_Purchases, A.ItemCode
From tblSales A
Group By A.ItemCode
Union All
Select 0 as Sum_Sales, Sum(B.Purchases) as Sum_Purchases, B.ItemCode
From tblPurchases B
Group By B.ItemCode) as sq
This is your query:
SQL
SELECT
    A.ItemCode
  , A.ItemName
  , A.Barcode
  , A.Unit
  , A.PurchaseRate
  , A.SellingRate
  , A.DiscountPercent
  , A.OpeningStock
  , A.BatchNo
  , A.ExpiryDate AS ExpDate
  , B.Quantity AS StockQuantity
  , E.PurchaseDate
  , F.InvoiceDate 
FROM
  tblItemMaster                 A
  INNER JOIN tblStockMaster     B ON B.ItemCode       = A.ItemCode AND B.BatchNo=A.BatchNo
  INNER JOIN tblPurchaseDetails C ON C.ItemCode       = A.ItemCode AND C.BatchNo=A.BatchNo
  INNER JOIN tblPurchaseMaster  E ON E.PurchaseBill   = C.PurchaseBill
  INNER JOIN tblSalesDetails    D ON D.ItemCode       = A.ItemCode AND D.BatchNo=A.BatchNo
  INNER JOIN tblSalesMaster     F ON F.InvoiceNo      = D.InvoiceNo
WHERE
  A.ItemCode='10'


Query seems alright. The query will result fine as long as you have a sails record for that item. If you want to see results even if no sales exists. Then rewrite as follow:
SQL
-- INNER JOIN reordered
SELECT
    A.ItemCode
  , A.ItemName
  , A.Barcode
  , A.Unit
  , A.PurchaseRate
  , A.SellingRate
  , A.DiscountPercent
  , A.OpeningStock
  , A.BatchNo
  , A.ExpiryDate AS ExpDate
  , B.Quantity AS StockQuantity
  , E.PurchaseDate
  , F.InvoiceDate 
FROM
  tblItemMaster A
  INNER JOIN tblStockMaster     B ON B.ItemCode       = A.ItemCode AND B.BatchNo=A.BatchNo
  INNER JOIN tblPurchaseDetails C ON C.ItemCode       = A.ItemCode AND C.BatchNo=A.BatchNo
  INNER JOIN tblPurchaseMaster  E ON E.PurchaseBill   = C.PurchaseBill
  LEFT OUTER JOIN tblSalesDetails    D ON D.ItemCode       = A.ItemCode AND D.BatchNo=A.BatchNo
  LEFT OUTER JOIN tblSalesMaster     F ON F.InvoiceNo      = D.InvoiceNo
WHERE
  A.ItemCode='10'


There is one issue though. What would happen if item was returned and resold? Since I do not know how you manage this. I can't suggest anything.
   
SELECT (what items you want to achieve)
FROM (select the table from which you want to achieve any data from, if tables are different then use innerjoin with alias)(for example: FROM Table_a as a INNERJOINS Table_b as b on a.empID=b.empID)(innerjoin will happen until the data is achieved and will only happen if the attributes are same or linked with one another being in different tables)
WHERE (whatever the condition is) (For example: WHERE b.empID="5555")
   

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900