CREATE TABLE FinDayInfo ( --Table to get Financial Year , Month and Week information against a date
CalDate Date
,WeekNo Number (16, 6)
,FinYear Number (16, 6)
,FinMM Number(6)
);
---------------------
INSERT INTO FinDayInfo ( CalDate ,WeekNo ,FinYear ,FinMM )
VALUES ( '27-Jun-2016' , 1 , 2017 , 1);
INSERT INTO FinDayInfo ( CalDate ,WeekNo ,FinYear ,FinMM )
VALUES ( '03-Aug-2016' , 6 , 2017 , 2);
INSERT INTO FinDayInfo ( CalDate ,WeekNo ,FinYear ,FinMM )
VALUES ( '08-Aug-2016' , 7 , 2017 , 2);
INSERT INTO FinDayInfo ( CalDate ,WeekNo ,FinYear ,FinMM )
VALUES ( '15-Aug-2016' , 8 , 2017 , 2);
----------------
CREATE TABLE Act ( --Accounts table having Financial Week and Shop-wise data
FinYear Number(6)
,FinMonth Number(6)
,FinWeek Number(6)
,ShopCode Number(4)
,ActVal Number(16, 6)
);
--------------------
INSERT INTO ACT ( FinYear ,FinMonth ,FinWeek ,ShopCode ,ActVal )
VALUES ( 2017, 1 , 2 , 1234 , 10);
INSERT INTO ACT ( FinYear ,FinMonth ,FinWeek ,ShopCode ,ActVal )
VALUES ( 2017 , 1 , 3 , 1234 , 50);
INSERT INTO ACT ( FinYear ,FinMonth ,FinWeek ,ShopCode ,ActVal )
VALUES ( 2017 , 1 , 4 , 1234 , 40);
INSERT INTO ACT ( FinYear ,FinMonth ,FinWeek ,ShopCode ,ActVal )
VALUES ( 2017 , 1 , 5 , 1234 , 20);
INSERT INTO ACT ( FinYear ,FinMonth ,FinWeek ,ShopCode ,ActVal )
VALUES ( 2017 , 2 , 6 , 1234 , 100);
INSERT INTO ACT ( FinYear ,FinMonth ,FinWeek ,ShopCode ,ActVal )
VALUES ( 2017 , 2 , 7 , 5678 , 90);
INSERT INTO ACT ( FinYear ,FinMonth ,FinWeek ,ShopCode ,ActVal )
VALUES ( 2017 , 2 , 8 , 1290 , 100);
-------------------
Create Table Sal( --Sales data table
SalDate Date
,ShopCode Number(6)
,ItemCode Varchar2(6)
,SaleVal Number(16,6)
);
------------------
INSERT INTO Sal ( SalDate , ShopCode , ItemCode , SaleVal )
VALUES ( '27-Jun-2016', 1234, '111PN' , 5);
INSERT INTO Sal ( SalDate , ShopCode , ItemCode , SaleVal )
VALUES ( '03-Aug-2016', 1234, '100AB' , 50);
INSERT INTO Sal ( SalDate , ShopCode , ItemCode , SaleVal )
VALUES ( '03-Aug-2016', 1234, '100BC' , 60);
INSERT INTO Sal ( SalDate , ShopCode , ItemCode , SaleVal )
VALUES ( '03-Aug-2016', 1234, '110JK' , 40);
INSERT INTO Sal ( SalDate , ShopCode , ItemCode , SaleVal )
VALUES ( '08-Aug-2016', 5678, '122AB' , 90);
INSERT INTO Sal ( SalDate , ShopCode , ItemCode , SaleVal )
VALUES ( '15-Aug-2016', 1290, '111PN' , 35);
INSERT INTO Sal ( SalDate , ShopCode , ItemCode , SaleVal )
VALUES ( '15-Aug-2016', 1290, '123CD' , 35);