1212

   

  

--====================================================================================      

--Created Date  06/05/2021  Author: Jeevan S Shekhar    

--Descrition: Snapshot of Open Task on daily Basis.    

    

--Note: We have handlded to run this SP once a day,     

   --if there is any back track request we should delete the current days data and then back track    

      --Back track can be handled only current day.    

    

--=====================================================================================      

       

CREATE PROCEDURE [dbo].[CW_Open_Task_Snapshot_SP]           

            

AS                

          

--================= Delete from data older than 2 months =================                    

              

--Delete from  [ODS_CW].dbo.open_Task_snapshot               

--Where Snapshot_Date < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 3, 0)             

          

--=================Check if the Load is completed for the day =================                    

                  

Declare @Confirm INT = 0                  

Set @Confirm = (                  

Select Case when Max(Snapshot_Date) = CONVERT(date, getdate()) then 0 else 1 end                  

from SYN_CW_OPEN_TASK_SNAPSHOT)                  

                  

if @Confirm=1                  

                  

          

          

--=====================================================================================                    

                  

BEGIN                    

DECLARE @RecordCount INT = 0                    

SET NOCOUNT ON;                    

BEGIN TRY                    

                  

                  

BEGIN TRAN                    

                

                  

--=================LOADING DATA INTO STAGE=================       

    

Insert into SYN_CW_OPEN_TASK_SNAPSHOT     

(    

TaskId,    

Taskname,    

Summary,    

Tasktype,    

Priority,    

Status,    

Assignee,    

Assignee_Support_Group,    

Company,    

Region,    

Site_Group,    

Site,    

Submited_Date,    

Actual_Start_Date_Time,    

Actual_End_Date_Time,    

Scheduled_Start_Date_Time,    

Scheduled_End_Date_Time,    

Operational_Categorization_Tier_1,    

Operational_Categorization_Tier_2,    

Modified_Date,    

Assignee_Support_Organization,    

Workorder_Number,    

Task_Group,    

Submitter,    

Affected_User,    

Affected_User_Email,    

Last_Modified_By,    

Business_Group,    

Reassignment_Count,    

Urgency,    

Impact,    

Detailed_Description,    

Snapshot_Date,    

Snapshot_Datetime,  

datasourceid  

)    

SELECT    

 WO_Task.Task_Number as TaskId,    

 WO_Task.TASKNAME as Taskname,    

 WOO_Transaction.Headline as Summary,    

 WO_Task.TASKTYPE as Tasktype,    

 WOO_Transaction.Priority as Priority,    

 WO_Task.STATUS as Status,    

 WOT_Assignee.FULL_NAME as Assignee,    

 WOT_Assigneegroup.SUPPORT_GROUP_NAME as Assignee_Support_Group,    

 'Cushman & Wakefield' as Company,    

 WO_Customer.REGION as Region,    

 WO_Customer.SITE_GROUP as Site_Group,    

 WO_Customer_Site.SITE as Site,    

 WO_Task.SUBMIT_DATE as Submited_Date,    

 WO_Task.ACTUAL_START_DATE as Actual_Start_Date_Time,    

 WO_Task.ACTUAL_END_DATE as Actual_End_Date_Time,    

 WO_Task.SCHEDULED_START_DATE as Scheduled_Start_Date_Time,    

 WO_Task.SCHEDULED_END_DATE as Scheduled_End_Date_Time,    

 WOO_Transaction.Categorization_Tier_1 as Operational_Categorization_Tier_1,    

 WOO_Transaction.Categorization_Tier_2 as Operational_Categorization_Tier_2,    

 WO_Task.LAST_MODIFIED_DATE as Modified_Date,    

 WOT_Assigneegroup.SUPPORT_ORGANIZATION as Assignee_Support_Organization,    

 Workorder.WorkOrder_Number as Workorder_Number,    

 WO_Task.TaskGroup_Name as Task_Group,    

 WO_Task.SUBMITTER as Submitter,    

 WO_Customer.FULL_NAME as Affected_User,    

 WO_Customer.REMEDY_LOGIN_ID as Affected_User_Email,    

 WO_Task.LAST_MODIFIED_BY as Last_Modified_By,    

 WOT_Assigneegroup.Business_Group as Business_Group,    

 WO_Task.ESCALATION_Count as Reassignment_Count,    

 WOO_Transaction.Urgency as Urgency,    

 WOO_Transaction.Impact as Impact,    

 WOO_Transaction.Description as Detailed_Description,    

 CONVERT(date, getdate()) as Snapshot_Date,        

 Getdate() as Snapshot_Datetime,  

 WO_Task.datasourceid as datasourceid  

FROM  

  

  

SYN_CW_TASK WO_Task (nolock)  

LEFT OUTER JOIN SYN_ODS_CW_ITIL_TRANSACTION  WOO_Transaction (nolock)

 ON (WO_Task.Transaction_ID=WOO_Transaction.Transaction_ID and WO_Task.DataSourceID=WOO_Transaction.DataSourceID)  

LEFT OUTER JOIN SYN_PERSON  WO_Customer (nolock) ON (WO_Customer.PERSON_SK=WOO_Transaction.PERSON_SK AND WO_Customer.DataSourceID=WOO_Transaction.DataSourceID)  

LEFT OUTER JOIN SYN_CW_WORKORDER  Workorder (nolock)ON (WO_Task.DataSourceID=WORKORDER.DataSourceID and WO_Task.ROOTREQUESTID=WORKORDER.Transaction_ID)   

LEFT OUTER JOIN SYN_SUPPORTGROUP  WOT_Assigneegroup (nolock) ON (WOO_Transaction.DataSourceID=WOT_Assigneegroup.DataSourceID and WOO_Transaction.ASSIGNED_GROUP_ID=WOT_Assigneegroup.SUPPORT_GROUP_ID)  

LEFT OUTER JOIN SYN_PERSON  WOT_Assignee (nolock) ON (WOT_Assignee.DataSourceID=WOO_Transaction.DataSourceID and WOT_Assignee.PERSON_SK=WOO_Transaction.Assignee_Login_ID)  

LEFT OUTER JOIN SYN_ODS_CW_SITE  WO_Customer_Site (nolock) ON (WO_Customer.SITE_SK=WO_Customer_Site.SITE_SK AND WO_Customer.DataSourceID=WO_Customer_Site.DataSourceID)  

WHERE  

  (    

     WO_Task.STATUS  NOT IN  ( 'Closed' ,'Closed Complete','Closed Incomplete','Closed Skipped','Cancelled','Completed','Closed Successful','Closed Cancelled' ,'Closed Rejected')  

    AND WO_Task.SUBMIT_DATE  >= '2020-10-29'  

 and WOO_Transaction.Transaction_Type = 'Catalog Task'  

     )  

    

  

  

COMMIT TRAN                    

                  

SELECT ReturnCode=0                    

 END TRY                    

                    

BEGIN CATCH                     

 ROLLBACK TRAN                    

                    

 DECLARE @ERROR_SEVERITY INT = ERROR_SEVERITY()                    

 DECLARE @ERROR_STATE INT = ERROR_STATE()                    

 DECLARE @ERROR_NUMBER INT = ERROR_NUMBER()                    

 DECLARE @ERROR_LINE INT = ERROR_LINE()                    

 DECLARE @ERROR_MESSAGE NVARCHAR(4000) = ERROR_MESSAGE()                     

                  

                    

 SELECT ReturnCode=1                    

                    

END CATCH                    

                  

                  

END   

Comments

Post a Comment

Popular posts from this blog

java chapter11 practice question on abstruct class and interfaces

  /* Chapter 11 - Practise Set 1... Create an abstract class Pen with methods Write () and refill 1) as abstract methods 2... Use the Pen Class from Q1 to create a Concrete class fountain Pen with additional method change Nibl) 3...Create a class Monkey with jump () and bitel) methods. Create a class Human which inherits this Monkey class and implements Basic Animalinterface with eati) and sleep methods. 4.... Greate a class Telephone with ring() liftus and disconnect ( methods as abstract methods Create another class Smart Telephone and demonstratepolymorphism 5.....Demonstrate polymorphism Msing monkey class from Quess 3. 6.......Create an Interface TV Remote and use it to inheritanother Interface Smart Tv Remote 7...... Create a Class Tv which implements Tv Remote interface qsn 6 */ package com.company ; //programe 1 abstract class Pen { abstract void Write (); abstract void Refill (); } //programe 2 class FountainPen extends Pen { void ...
Read more

DAY 12 -AZURE DP900(Microsoft Azure Data Fundamentals: Explore non-relational data in Azure)

Image
  Microsoft Azure Data Fundamentals: Explore non-relational data in Azure 1. Explore non-relational data offerings in Azure 2. Explore provisioning and deploying non-relational data services in Azure 3. Manage non-relational data stores in Azure Explore non-relational data offerings in Azure---------- Introduction Completed 100 XP 1 minute Data comes in all shapes and sizes, and can be used for a large number of purposes. Many organizations use relational databases to store this data. However, the relational model might not be the most appropriate schema. The structure of the data might be too varied to easily model as a set of relational tables. For example, the data might contain items such as video, audio, images, temporal information, large volumes of free text, encrypted information, or other types of data that aren't inherently relational. Additionally, the data processing requirements might not be best suited by attempting to convert this data into the relational format. In ...
Read more

java exercise4

  //implement a library using java class library //methods: issuebook,returnbook,showavailablebooks //properties:to store available books,store issued books //exercise 4 package com.company ; class library { String [] books ; int no_of_books ; library () { this . books = new String[ 100 ]; this . no_of_books = 0 ; } void addbook ( String book) { this . books [ no_of_books ] = book; no_of_books ++; System . out .println(book + ".... has been added....." ); } void showavailablebooks () { System . out .println( "available books are :" ); for ( String book : this . books ) { if ( book == null ) { //break;------//issue book jdi null hoye jy tale porer books shor krbe na tai continue continue ; } System . out .println( "*..." + book ); } } void issuebook ( String book) { //for (String b:th...
Read more