HEADER AND FOOTER

Product information is stored in table (Product_info). The product information I want to store it in a file, product wise. File should contain header (Product type is header) and footer (Total number of items is footer) shown below.

  

Source Table Script:

Create table Product_info

(

ID int,

Name varchar(20),

Type varchar(20),

Price decimal(8,4)

)

Source Data:

Insert into Product_info values(101,'Santoor','Soap',20)

Insert into Product_info values(102,'Margo','Soap',20)

Insert into Product_info values(103,'Sandel','Soap',25)

Insert into Product_info values(104,'Lux','Soap',35)

Insert into Product_info values(105,'Colget','Paste',20)

Insert into Product_info values(106,'Daber','Paste',15)

Insert into Product_info values(107,'Closeup','Paste',20)

Insert into Product_info values(108,'Parachoot','Hair oil',50)

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