CODEWITHSUNDEEP
sqloracle-databasepowershellteradata
gabuglc
gabuglc

Reputation: 43

Retroactive update monthly

Let me explain what I want by explaining my tables.

Month 1 -

Table IMEI_SALES - This Table updates monthly. The sale_date column is the min(sale_date) of all the table. So there cannot be a another IMEI with the same sale_date. 

imei              sale_date   month   tfm  tfm1 tmf2 tfm3 
------            ---------   -----   ---- ---- ---- ----
354205080163741   01/08/2017  201708  null null null null
354205080163742   01/08/2017  201708  null null null null     
354205080163743   13/08/2017  201708  null null null null

Current Month-

Traffic TABLE - Here I got the Traffic. Again there cannot be a imei with the same date. This tables creates and then drops monthly.

imei              traffic_date tr_month   
------            ---------   -----   
354205080163741   01/08/2017  201708  
354205080163742   01/08/2017  201708     
354205080163743   13/08/2017  201708

So at then end month. I have my IMEI_SALES TABLE and the traffic TABLE. What I need to do is to update the tfm's colums with the traffic. I can do this manually. So at then end of August Table IMEI_SALES should look like this.

  imei              sale_date   month    tfm       tfm1 tmf2 tfm3 
  ------            ---------   -----   ----       ---- ---- ----
  354205080163741   01/08/2017  201708  01/08/2017 null null null
  354205080163742   01/08/2017  201708  01/08/2017 null null null     
  354205080163743   13/08/2017  201708  13/08/2017 null null null

The limit is current month+3. Let's see what happens next month.

Next Month -

Table IMEI_SALES. New rows here from September.

imei              sale_date   month   tfm        tfm1 tmf2 tfm3 
------            ---------   -----   ----       ---- ---- ----
354205080163741   01/08/2017  201708  01/08/2017 null null null
354205080163742   01/08/2017  201708  01/08/2017 null null null     
354205080163743   13/08/2017  201708  13/08/2017 null null null
354205080163741   01/09/2017  201709  null       null null null
354205080132131   01/09/2017  201709  null       null null null
354205080163425   13/09/2017  201709  null       null null null

Traffic TABLE - Previous one is dropped.

IMEI 354205080163741,354205080163742, 354205080163743 match with the same date and within 3 months from the sale_date.

IMEI 354205080132131 matches with the different date and is within 3 months from the sale_date.

IMEI 354205080163425 no match in the traffic table.

imei              traffic_date tr_month   
------            ---------   -----   
354205080163741   01/09/2017  201709
354205080163742   01/09/2017  201709
354205080163743   01/09/2017  201709  
354205080132131   02/09/2017  201709     
354243774312421   14/09/2017  201709

When updating the IMEI_SALES Table, it should look like this.

imei              sale_date   month   tfm        tfm1       tmf2 tfm3 
------            ---------   -----   ----       ----       ---- ----
354205080163741   01/08/2017  201708  01/08/2017 01/09/2017 null null
354205080163742   01/08/2017  201708  01/08/2017 01/09/2017 null null     
354205080163743   13/08/2017  201708  13/08/2017 01/09/2017 null null
354205080163741   01/09/2017  201709  01/09/2017 null       null null
354205080132131   01/09/2017  201709  02/09/2017 null       null null
354205080163425   13/09/2017  201709  null       null       null null

So for example, Lets say I'am in December with same sales and traffic tables from september to December. You will have to match

I need to do this with Teradata sql and Powershell to automate it. Oracle is welcomed too. Is it possible with the limitations I just mentioned?

Thanks a lot!

Upvotes: 0

Views: 202

Answers (1)

Paweł Zaraś
Paweł Zaraś

Reputation: 28

How are you currently modifying tables? Any PL/SQL procedure? Can add on a trigger, which will make additional updates when modifications, or to update your current mechanism to update tfm, tfm1 field

Upvotes: 0

Related Questions