Updating target table with source data
-
It's almost 2AM here so obviously I'm not thinking very well at the moment.
I have a source (orange border) and target table (green border)
What I need to do is add the records from the source table into the target table but the to_date in the target table needs to be updated with the from_date - 1 from the source table. I just can't figure out how to do this.
If anyone can offer suggestions I would be most grateful.
Sample data below:
create table source_table (Uarn bigint, Asst_ref bigint, VO_Ref bigint, total_value int, from_date datetime, to_Date datetime)insert into source_table (Uarn, Asst_ref, VO_Ref, total_value, from_date, to_Date)
select 6705290000, 14673893000, 22898762212, 3475, '2013-07-22', NULL UNION ALL
select 6705290000, 14673284000, 22573281212, 2893, '2013-07-19', '2013-07-21' union all
select 100381173, 14664965000, 22815438212, 17607, '2013-07-18', NULL
create table target_table (Uarn bigint, Asst_ref bigint, VO_Ref bigint, total_value int, from_date datetime, to_Date datetime)
insert into target_table (Uarn, Asst_ref, VO_Ref, total_value, from_date, to_Date)
select 6705290000, 11045507000, 15243111182, 3319, '2010-04-01', NULL union all
select 100381173, 14528777000, 22500073212, 16727, '2013-04-05', NULL union all
select 100381173, 9245349000, 11945621182, 17607, '2010-04-01', '2013-04-04'
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
Hi
Would something like this do the trick?
update utset to_date = a.min_source_date
from target_table ut
cross apply (
select s.uarn, dateadd(d,-1,min(from_date)) min_source_date
from source_table s
where ut.uarn = s.uarn
and s.from_date > ut.from_date -- may not be required if your source table is truncated after insert
group by s.uarn
) a
where to_date is null
insert into target_table (Uarn, Asst_ref, VO_Ref, total_value, from_date, to_Date)
select s.Uarn, s.Asst_ref, s.VO_Ref, s.total_value, s.from_date, s.to_Date
from source_table s
where not exists (select 1 from #target_table t where s.from_date <= t.from_date) -- ditto on the comment above
-
I think this may work as well...not 100%
UPDATE target_table
SET to_Date = (T2.from_date-1)
FROM target_table T1
LEFT JOIN source_table T2 ON T1.Uarn = T2.Uarn
WHERE t1.to_Date IS NULL
INSERT target_table
SELECT DISTINCT T1.Uarn
,T1.Asst_ref
,T1.VO_Ref
,T1.total_value
,T1.from_date
,T1.to_Date
FROM source_table T1
LEFT JOIN target_table T2 ON T1.Uarn = T2.Uarn
"To be 'The Man,' you gotta beat the man!" ~The Nature Boy
-
Sorry, after reviewing mickyT's code and results i realize the dates should be "contiguous" if i'm understanding correctly.(in which mickyT's does just that). Just wanted to post an update to my last post with the correct logic.
Does the same thing but different approach...
SELECT MIN(T2.from_date) - 1 AS end_date
,T2.Uarn
INTO #temp
FROM source_table T2
INNER JOIN target_table T1 ON T2.Uarn = T1.Uarn
GROUP BY T2.UARN
UPDATE target_table
SET to_Date = T3.end_date
FROM target_table T1
LEFT JOIN source_table T2 ON T1.Uarn = T2.Uarn
INNER JOIN temp T3 ON T1.Uarn = T3.Uarn
WHERE t1.to_Date IS NULL
INSERT target_table
SELECT DISTINCT T1.Uarn
,T1.Asst_ref
,T1.VO_Ref
,T1.total_value
,T1.from_date
,T1.to_Date
FROM source_table T1
LEFT JOIN target_table T2 ON T1.Uarn = T2.Uarn
DROP TABLE #temp
"To be 'The Man,' you gotta beat the man!" ~The Nature Boy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply