Link 2 tables and distribute the count

Database
Enthusiast

Link 2 tables and distribute the count

Hi,

I have requirment to link 2 tables and distribute the count based on condition.

 

Table 1:

Discharge_IDSvn_CdLoc_CdValidDateCount
10007ACHN04/10/2019 17:00:00.000000200
10107ACHN04/24/2019 17:00:00.000000150
10207ACHN05/1/2019 17:00:00.000000150
10307ACHN05/1/2019 17:00:00.00000060
10407ACHN05/15/2019 17:00:00.000000

11

 

Table 2:

Load_IDSvn_CdLoc_CdValidDateCount
20107ACHN14/30/2019 19:00:00.00000050
20207ACHN14/30/2019 19:00:00.000000100
20307ACHN15/7/2019 19:00:00.00000050
20407ACHN15/7/2019 19:00:00.000000150
20507ACHN15/14/2019 19:00:00.00000010
20607ACHN15/21/2019 19:00:00.000000

11

 

 

 

I need to join Table 2 with Table 1 on Loc_Cd,Svn_Cd and Table2.Date between Table1.Date and Table1.Date + Interval '15' Day. 

The count should be then distributed based on the Table1 count. Here is the output:

Load_IDSvn_CdLoc_CdValidDateCountDischarge_IDSvn_CdLoc_CdValidDateCount
20107ACHN14/30/2019 19:00:00.0000005010107ACHN004/24/2019 17:00:00.00000050
20207ACHN14/30/2019 19:00:00.00000010010107ACHN004/24/2019 17:00:00.000000100
20307ACHN15/7/2019 19:00:00.0000005010207ACHN05/1/2019 17:00:00.00000050
20407ACHN15/7/2019 19:00:00.00000015010207ACHN05/1/2019 17:00:00.000000100
20407ACHN15/7/2019 19:00:00.00000015010307ACHN05/1/2019 17:00:00.00000050
20507ACHN15/14/2019 19:00:00.0000001010307ACHN05/1/2019 17:00:00.00000010
20607ACHN15/21/2019 19:00:00.0000001110407ACHN05/15/2019 17:00:00.00000011
1 REPLY 1
Enthusiast

Re: Link 2 tables and distribute the count

Anyone has any ideas to share please ?