Home » SQL & PL/SQL » SQL & PL/SQL » Need to correlate and reconcile two data sets with different number of records (Oracle Database, 10g Rel.2 , Windows Server 2012)
Need to correlate and reconcile two data sets with different number of records [message #655088] Mon, 22 August 2016 00:10 Go to next message
mazam
Messages: 47
Registered: October 2009
Location: Multan
Member

Hi,
I need to correlate and reconcile two data sets with different number of records. Available data for this purpose is as below:

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);

From this data I need results as below.

Purpose of this data is to get Adjustment Value Item and Shop-wise on Weekly basis. This adjustment is allocated to each Item according to it's Sales contribution. "ActVal" is standard value in this regards and we calculate Adjustment Value from the difference of "ActVal" and "TotalSalePerShopAndWeek". This way, if we add-up "TotalSalePerShopAndWeek" and sum of "AdjustmentVal", it should be equal to "ActValPerShopAndWeek".

FinYear	FinMonth	FinWeek	ShopCode	ItemCode	SaleValPerWeekAndItem	TotalSalePerShopAndWeek	AdjustmentPercentage=(SaleValPerWeekAndItem / TotalSalePerShopAndWeek)	ActValPerShopAndWeek	AdjustmentValPerWeekAndItem= ((ActValPerShopAndWeek - TotalSalePerShopAndWeek) * AdjustmentPercentage)
2017	1		1	1234		111PN		5			5			1									0				-5
2017	1		2	1234		111PN		0			0			0									10				10
2017	1		3	1234		111PN		0			0			0									50				50
2017	1		4	1234		111PN		0			0			0									40				40
2017	1		5	1234		111PN		0			0			0									20				20
2017	2		6	1234		100AB		50			150			0.333333								100				-16.66665
2017	2		6	1234		100BC		60			150			0.4									100				-20
2017	2		6	1234		110JK		40			150			0.266666								100				-13.133333
2017	2		7	5678		122AB		90			90			1									90				90
2017	2		8	1290		111PN		35			70			0.50									90				15
2017	2		8	1290		123CD		35			70			0.50									90				15

Can you help me to get below results ? (I couldn't format results properly therefore I've attached the image for this)

/forum/fa/13237/0/


[mod-edit: image inserted into message body by bb]
  • Attachment: Results.png
    (Size: 28.61KB, Downloaded 332 times)

[Updated on: Mon, 22 August 2016 13:51] by Moderator

Report message to a moderator

Re: Need to correlate and reconcile two data sets with different number of records [message #655127 is a reply to message #655088] Mon, 22 August 2016 13:54 Go to previous message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Please see my response to your post on the OTN forums:

https://community.oracle.com/thread/3963552

[Updated on: Mon, 22 August 2016 13:55]

Report message to a moderator

Previous Topic: How to remove duplicate value from type in SQL
Next Topic: Sequence partition clause
Goto Forum:
  


Current Time: Sun Jun 13 15:27:55 CDT 2021