|

Update Multi Table Conditions

ByMaki Pa

Problem

In transaction table DB_GAds there are storage of records according key Date, Sesion_source_medium, Transaction Id and Item Id. In cost table DB_GAds_costs there are costs according to key Date and Ses_sm. The problem is that in cost table thera missing some heneral keys from transaction table. In presented code we update cost table with missing key values.

-- PBI presentation problems, there is no visible some keys and turnover
-- preliminary select
SELECT top (100) *
       FROM [DB_GAds] with (nolock)  

-- select from transaction table
   SELECT 
       [Date]
      ,Sesion_source_medium
      ,sum([Item_ga_gross_revenue]) as Item_ga_gross_revenue

  FROM [DB_GAds] with (nolock)    
    where [Item_ga_gross_revenue] != 0 -- and [Date] = DATEFROMPARTS(2024,9,1)
  group by 
       [Date]
      ,Sesion_source_medium
  order by 1,2

-- select from cost table
    select [Date], [Ses_sm]
       from [DB_GAds_costs] as a 
    order by 1

-- transaction tab linking with cost tab
with t1 as (
   SELECT 
       [Date]
      ,Sesion_source_medium
      ,sum([Item_ga_gross_revenue]) as Item_ga_gross_revenue

  FROM [DB_GAds] with (nolock)    
    where [Item_ga_gross_revenue] != 0 -- and [Date] = DATEFROMPARTS(2024,9,1)
  group by 
       [Date]
      ,Sesion_source_medium
--  order by 1,2
  )
  select t1.*, c.* 
     from t1
     left join [DB_GAds_costs] as c on (t1.Date = c.Date) and (t1.Sesion_source_medium = c.Ses_sm) 
     

-- przygotowanie do insert

where c.Ses_sm is null


-- approach to insert

insert into [DB_GAds_costs] 
   ([Date],	Ses_sm,	Cost_value,	ModDate)
   select ... 
   
;
GO

-- rekonfiguracja select 

   SELECT 
       t1.[Date]
      ,t1.Sesion_source_medium
      ,sum([Item_ga_gross_revenue]) as Item_ga_gross_revenue

  FROM [DB_GAds] t1 with (nolock) 
     left join [DB_GAds_costs] as c with (nolock)  on (t1.Date = c.Date) and (t1.Sesion_source_medium = c.Ses_sm) 
    where [Item_ga_gross_revenue] != 0 and c.Ses_sm is null
  group by 
       t1.[Date]
      ,Sesion_source_medium

--
insert into [DB_GAds_costs] 
   ([Date],	Ses_sm,	Cost_value,	ModDate)
  
  SELECT 
       t1.[Date] Date
      ,t1.Sesion_source_medium Ses_sm
      ,0 Cost_value
      ,GETUTCDATE() ModDate
      --,sum([Item_ga_gross_revenue]) as Item_ga_gross_revenue

  FROM [DB_GAds] t1 with (nolock) 
     left join [DB_GAds_costs] as c with (nolock)  on (t1.Date = c.Date) and (t1.Sesion_source_medium = c.Ses_sm) 
    where [Item_ga_gross_revenue] != 0 and c.Ses_sm is null
  group by 
       t1.[Date]
      ,Sesion_source_medium

However this is update based on transaction table, but what to do when you update transaction table with monthly date. Here is a proposal of current update.

declare @total_rev_sum as decimal(18,2)
declare @dt_filter as date

SELECT @dt_filter = max([Date]) FROM [DB_GAds_temp]
SELECT @total_rev_sum = SUM([Item_ga_gross_revenue]) FROM [DB_GAds_temp] where [Date] = @dt_filter


insert into [DB_GAds_costs] (
       [Date]
      ,[Ses_sm]
      ,[Cost_value]
      ,[ModDate]) 

SELECT a.[Date]
      ,a.[Ses_sm]
      ,0 as [Cost_value]
      ,GETUTCDATE() as [ModDate]
--      ,COUNT(*) trans_count
--      ,SUM([Item_ga_gross_revenue]) rev_sum
--      ,round(100*(SUM([Item_ga_gross_revenue])/@total_rev_sum),2) Proc_in_tot
--      ,COUNT(c.Date) c_rec_count
  FROM [DB_GAds_temp] as a 
    left join [DB_GAds_costs] as c on a.Date = c.Date and a.Ses_sm=c.Ses_sm
  where [Item_ga_gross_revenue] > 0 --and [Ses_sm] like '%cpc%'
  group by a.[Date]
      ,a.[Ses_sm]
  having SUM([Item_ga_gross_revenue])/@total_rev_sum > 0.01 and COUNT(c.Date)=0
order by 3 desc

Similar Posts