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

SPLIT SOURCE DATA INTO TWO FILES

Source file is having item details. Item names are ending with the letter E or K. I want to split the data based on the product name ending letters (E and K) and create two different files.

Source file data:

Id,Name,Price
101,tee,10
102,cofee,20
103,milk,15
104,cool drink,20
105,hot drink,19
106,Chocolate,9

Expected output files:

    Target 1:
Id,Name,Price
103,milk,15
104,cool drink,20
105.hot drink,19

    Target 2:
Id,Name,Price
101,tee,10
102,cofee,20
106,Chocolate,9

Package creation: Click here