Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have two tables. One table 'sale' and the other table 'Purchase'. I created the stock through SQL Query. 
Can I use this query to create an RDLC report?
I would have benefited if an experienced person had wasted his precious time and assisted me in this matter. Thanks.


What I have tried:

select a.Company_Name as Company, 
           a.Product_name as product,
         
           stock =ISNULL( a.qty,0) - isnull(b.qty, 0)
    from   (
               select Company_Name, Product_name, qty = sum(Qty)
               from   tbl_purchase
               group by Company_Name, Product_name
           ) a
           left join 
           (
               select Company_Name, Product_name,qty = sum(qty)
               from  tbl_sales
    		   group by Company_Name, Product_name
           ) b   
on  a.Company_Name = b.Company_name
 and a.Product_name = b.Product_name
Posted
Updated 5-Sep-21 23:13pm

With RDLC you can first populate desired data from the database using your query and a DataAdapter, see Populating a DataSet from a DataAdapter - ADO.NET | Microsoft Docs[^]

After doing that, you can use the data table as the data source for your report. See Creating Data Sources for a Report | Microsoft Docs[^]
 
Share this answer
 
Having the company name and product name in the sales and purchases tables is wrong. You need to normalize your database.
Database normalization description - Office | Microsoft Docs[^]

For example:
SQL
CREATE TABLE Companies
(
    Id int NOT NULL IDENTITY(1, 1),
    Name nvarchar(100) NOT NULL,
    CONSTRAINT PK_Companies PRIMARY KEY (Id)
);

CREATE TABLE Products
(
    Id int NOT NULL IDENTITY(1, 1),
    Name nvarchar(100) NOT NULL,
    CONSTRAINT PK_Products PRIMARY KEY (Id)
);

CREATE TABLE Purchases
(
    Id int NOT NULL IDENTITY(1, 1),
    CompanyId int NOT NULL,
    ProductId int NOT NULL,
    Quantity decimal(18, 6) NOT NULL,
    /* ... OTHER COLUMNS HERE ... */
    CONSTRAINT PK_Sales PRIMARY KEY (Id),
    CONSTRAINT FK_Sales_Company FOREIGN KEY (CompanyId) REFERENCES Companies (Id),
    CONSTRAINT FK_Sales_Product FOREIGN KEY (ProductId) REFERENCES Products (Id)
);

CREATE TABLE Sales
(
    Id int NOT NULL IDENTITY(1, 1),
    CompanyId int NOT NULL,
    ProductId int NOT NULL,
    Quantity decimal(18, 6) NOT NULL,
    /* ... OTHER COLUMNS HERE ... */
    CONSTRAINT PK_Purchases PRIMARY KEY (Id),
    CONSTRAINT FK_Purchases_Company FOREIGN KEY (CompanyId) REFERENCES Companies (Id),
    CONSTRAINT FK_Purchases_Product FOREIGN KEY (ProductId) REFERENCES Products (Id)
);

You also need to consider what happens if you have sales with no purchases. Currently, that product/company combination would not be included in your report.
SQL
SELECT
    C.Name As CompanyName,
    P.Name As ProductName,
    B.Quantity
FROM
    (
        SELECT
            IsNull(P.CompanyId, S.CompanyId) As CompanyId,
            IsNull(P.ProductId, S.ProductId) As ProductId,
            IsNull(P.Quantity, 0) - IsNull(S.Quantity, 0) As Quantity
        FROM
            (SELECT CompanyId, ProductId, Quantity FROM Purchases) As P
            FULL OUTER JOIN (SELECT CompanyId, ProductId, Quantity FROM Sales) As S
            ON S.CompanyId = P.CompanyId And S.ProductId = P.ProductId
    ) As B
    INNER JOIN Companies As C ON C.Id = B.CompanyId
    INNER JOIN Products As P ON P.Id = B.ProductId
;
 
Share this answer
 

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