PRODUCT SALES

Requirement:

I have two tables

1. ProdInfo (Product information)
2. SalesInfo (Sales information)

       ProdInfo table have product information (Item name, quantity and price) of that particular day (Before selling), and saleinfo table have sales information of that particular day. End of the day I want to know starting product quantity (Product wise), how much quantity is soled and how much is remain. 

Table info:

Source Table info:-

ProdInfo columns:-

      a.      Prod_ID

      b.      Prod_Name
      c.      Prod_Unit_Price
      d.      Prod_Qty

SalesInfo columns:-

      a.      Sale_ID
      b.     Prod_ID
      c.      Sale_Qty

Target table columns:-

     a.      Prod_ID
     b.      Prod_Name
     c.      Prod_Unit_Price
     d.      Prod_Starting_Qty
     e.      Prod_Soled_Qty
     f.        Prod_Soled_Qty_Amount
     g.      Prod_Remaining_Qty
     h.      Load_Date (Current date)

Source tables creation script:

Table creation:-

create table ProdInfo

(

Prod_ID int

,Prod_Name varchar(30)

,Prod_Unit_Price decimal(10,2)

,Prod_Qty int

);

create table SaleInfo

(

Sale_ID int

,Prod_ID int

,Sale_Qty int

);

Source table data:

ProdInfo:-

insert into ProdInfo values(101,'Mouse',50,30);

insert into ProdInfo values(102,'Moniter',500,50);

insert into ProdInfo values(103,'Key board',250,37);

insert into ProdInfo values(104,'CPU',5000,55);

insert into ProdInfo values(105,'Speakers',550,300);

insert into ProdInfo values(106,'Ram',500,32);

insert into ProdInfo values(201,'Book',50,300);

insert into ProdInfo values(202,'Pen',50,250);

insert into ProdInfo values(203,'Pensel',50,550);

SaleInfo:-

insert into SaleInfo values(1001,202,92);

insert into SaleInfo values(1002,201,52);

insert into SaleInfo values(1003,105,230);

insert into SaleInfo values(1004,103,30);

insert into SaleInfo values(1005,102,50);

Expected output:

 
Package creation (Solution): Click here