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