1.    Drop table if exists #content_asset_mapping;
2.    
3.    Create table #content_asset_mapping as        
4.           SELECT     contskill.scdtimestamp, skillid skill_id,assetid content_id,upper(operation)operation
5.      FROM       dw.dimassetskillsmapping contskill
6.      WHERE     intpartnerid IN   (SELECT IntegrationPartnerName FROM etl.IntegrationPartner  WHERE IntegrationPartnerID = 3)
7.                   --AND rowversion  > '20180801';
8.           AND rowversion  > '${LastProcessRowversion}'
9.           AND rowversion <= '${CurrentRowversion}';
10.  
11.            Update report.standard_hierarchy set end_school_year =rec.end_school_year
12.            ,is_Active=false
13.            from ( Select   cast(case when date_part('month',contskill.scdtimestamp)>7 then date_part('year',contskill.scdtimestamp) else date_part('year',contskill.scdtimestamp)-1 end  as smallint)End_School_Year
14.                         , skill_id,content_id
15.                         from #content_asset_mapping contskill
16.            where operation='DELETE')rec
17.            where rec.content_id=report.standard_hierarchy.content_id;
18.            
19.            
20.            Insert into  report.Standard_Hierarchy(platform,framework_id,standard_id,skill_id,content_id,content_type,Start_School_Year,is_Active)
21. Select platform,framework_id,standard_id,skill_id,content_id ,isnull(upper(cont.contenttype),'') content_type
22. ,case when date_part('month',rec.eventdate)>7 then date_part('year',rec.eventdate) else date_part('year',rec.eventdate)-1 end Start_School_Year
23. ,true is_Active
24. from (q
25. Select  std.intpartnerid platform,std.frameworkid framework_id,std.standardid standard_id,contskill.skill_id ,contskill.content_id
26. ,greatest(contskill.scdtimestamp , skillstd.scdtimestamp ,std.scdtimestamp)eventdate
27. from
28. (Select cont.* from #content_asset_mapping cont where not exists
29. (Select 1 from report.Standard_Hierarchy hier where hier.content_id=cont.content_id and hier.skill_id=cont.skill_id))contskill,
30. dw.dimskillstandardmapping  skillstd,
31. dw.dimstandard std
32. where contskill.skill_id=skillstd.skillid
33. and skillstd.standardid=std.standardid
34. and framework_id <>'')rec
35. ,(Select contentassetid,contenttype from (Select contentassetid,contenttype , dense_rank() over(partition by contentassetid order by scdtimestamp desc)rnk from  dw.dimcontent
36. where lower(operation) <>'delete') where rnk=1) cont
37. where rec.content_id=cont.contentassetid(+)
38. and length(skill_id)<65
39. and length(content_id)<65
40. and length(standard_id)<65
41. and length(framework_id)<65;
42.            
43.              Drop table if exists #skill_standard_mapping;
44.    
45.    Create table #skill_standard_mapping as       
46.           SELECT     skillstd.scdtimestamp, skillid skill_id,standardid standard_id,upper(operation)operation
47.      FROM       dw.dimskillstandardmapping skillstd
48.      WHERE     intpartnerid IN   (SELECT IntegrationPartnerName FROM etl.IntegrationPartner  WHERE IntegrationPartnerID = 3)
49.                   AND rowversion  > '20180801';
50.        --   AND rowversion  > '${LastProcessRowversion}'
51.        --   AND rowversion <= '${CurrentRowversion}';
52.  
53.            Update report.standard_hierarchy set end_school_year =rec.end_school_year
54.            ,is_Active=false
55.            from ( Select   cast(case when date_part('month',skillstd.scdtimestamp)>7 then date_part('year',skillstd.scdtimestamp) else date_part('year',skillstd.scdtimestamp)-1 end  as smallint)End_School_Year
56.                         , skill_id,Standard_id
57.                         from #skill_standard_mapping skillstd
58.            where operation='DELETE')rec
59.            where rec.Standard_id=report.standard_hierarchy.Standard_id;
60.            
61.            
62.            Insert into  report.Standard_Hierarchy(platform,framework_id,standard_id, content_id,content_type,Start_School_Year,is_Active)
63. Select platform,framework_id,standard_id,skill_id,content_id ,isnull(upper(cont.contenttype),'') content_type
64. ,case when date_part('month',rec.eventdate)>7 then date_part('year',rec.eventdate) else date_part('year',rec.eventdate)-1 end Start_School_Year
65. ,true is_Active
66. from (
67. Select  std.intpartnerid platform,std.frameworkid framework_id,std.standardid standard_id,contskill.assetid content_id
68. ,greatest(contskill.scdtimestamp , skillstd.scdtimestamp ,std.scdtimestamp)eventdate
69. from
70. dw.dimassetskillsmapping contskill,
71. (Select skillst.* from #skill_standard_mapping skillst where not exists
72. (Select 1 from report.Standard_Hierarchy hier where hier.standard_id=skillst.standard_id and hier.skill_id=skillst.skill_id))  skillstd,
73. dw.dimstandard std
74. where skillstd.standard_id=std.standardid
75. and framework_id <>'')rec
76. ,(Select contentassetid,contenttype from (Select contentassetid,contenttype , dense_rank() over(partition by contentassetid order by scdtimestamp desc)rnk from  dw.dimcontent
77. where lower(operation) <>'delete') where rnk=1) cont
78. where rec.content_id=cont.contentassetid(+)
79. and length(content_id)<65
80. and length(standard_id)<65
81. and length(framework_id)<65;
82.            
83.            
84.            
85. Drop table if exists #standard;
86.    
87.    Create table #standard as     
88.           SELECT     std.scdtimestamp, standardid standard_id,frameworkid framework_id, upper(operation)operation
89.      FROM       dw.dimstandard std
90.      WHERE     intpartnerid IN   (SELECT IntegrationPartnerName FROM etl.IntegrationPartner  WHERE IntegrationPartnerID = 3)
91.                   AND rowversion  > '20180801';
92.        --   AND rowversion  > '${LastProcessRowversion}'
93.        --   AND rowversion <= '${CurrentRowversion}';
94.  
95.            Update report.standard_hierarchy set end_school_year =rec.end_school_year
96.            ,is_Active=false
97.            from ( Select   cast(case when date_part('month',std.scdtimestamp)>7 then date_part('year',std.scdtimestamp) else date_part('year',std.scdtimestamp)-1 end  as smallint)End_School_Year
98.                         , framework_id,Standard_id
99.                         from #standard std
100.            where operation='DELETE')rec
101.            where rec.framework_id=report.standard_hierarchy.framework_id
102.            and rec.Standard_id=report.standard_hierarchy.Standard_id;
103.            
104.            
105.            Insert into  report.Standard_Hierarchy(platform,framework_id,standard_id,skill_id,content_id,content_type,Start_School_Year,is_Active)
106. Select platform,framework_id,standard_id,skill_id,content_id ,isnull(upper(cont.contenttype),'') content_type
107. ,case when date_part('month',rec.eventdate)>7 then date_part('year',rec.eventdate) else date_part('year',rec.eventdate)-1 end Start_School_Year
108. ,true is_Active
109. from (
110. Select  contskill.intpartnerid platform,std.framework_id,std.standard_id,skillstd.skillid skill_id ,contskill.assetid content_id
111. ,greatest(contskill.scdtimestamp , skillstd.scdtimestamp ,std.scdtimestamp)eventdate
112. from
113. dw.dimassetskillsmapping contskill,
114. dw.dimskillstandardmapping  skillstd,
115.  
116. (Select st.* from #standard st where not exists
117. (Select 1 from report.Standard_Hierarchy hier where hier.standard_id=st.standard_id and hier.framework_id=st.framework_id)) std
118. where contskill.skillid=skillstd.skillid
119. and skillstd.standardid=std.standard_id
120. and std.framework_id <>'')rec
121. ,(Select contentassetid,contenttype from (Select contentassetid,contenttype , dense_rank() over(partition by contentassetid order by scdtimestamp desc)rnk from  dw.dimcontent
122. where lower(operation) <>'delete') where rnk=1) cont
123. where rec.content_id=cont.contentassetid(+)
124. and length(content_id)<65
125. and length(standard_id)<65
126. and length(framework_id)<65;
127.            
128.            
129.            Delete from report.standard_hierarchy where start_school_year=end_school_year;