new stored procidure with interval and frequery code

 CREATE procedure  [dbo].[SP_Baxter_CSAT]                                                      

                                                     

AS                                                           

BEGIN       

    DECLARE @ExtractIdOut INT                                                              

    DECLARE @EndDt DATETIME2                            

    DECLARE @runDate DATETIME2                            

    DECLARE @RecordCount INT = 0                                                            

    DECLARE @ProcedureName SYSNAME = OBJECT_NAME(@@PROCID)                                            

 DECLARE @intErrorCode INT                                                            

    Declare @returnCode int = 0                                                            

    DECLARE @ExtractProcessControlId UniqueIdentifier                                                            

    SET NOCOUNT ON;      

                      

BEGIN TRY      

    --Log the beginning of the  load process,          

    set @runDate = Coalesce(@runDate,GetDate())                            

    EXECUTE [CKM].[dbo].[CKM_UpdateExtractLogbyID] @ExtractIdOut, @runDate, 'E', 0           

           

    --Log the beginning of the SP load           

    set @ExtractProcessControlId = NewID()                          

    EXECUTE [CKM].[dbo].[CKM_InsertExtractLog]  @ProcedureName, @runDate, @ExtractIdOut           

    OUTPUT,@ExtractProcessControlId           

           

    UPDATE CKM.DBO.CKM_Logging          

    SET AccountName = 'Baxter'          

    ,TransactionType = 'CSAT'          

    ,Datasourceid = 28          

    FROM CKM.DBO.CKM_Logging           

    WHERE ProcessControlId = @ExtractProcessControlId      

BEGIN TRAN     

    

DECLARE @startdate datetime                     

DECLARE @enddate datetime                    

DECLARE @frequency nvarchar(10)           

DECLARE @interval int     

                    

DECLARE @Last_modified_date DATETIME                                                  

SET @Last_modified_date = (SELECT dateadd(second,(SELECT Last_modified_date FROM CKM.DBO.SourceTable WITH(NOLOCK)          

WHERE Package_name = 'SP_Baxter_CSAT'),'1970-01-01'))      

    

SET @frequency = (SELECT frequency FROM CKM.DBO.SourceTable WITH(NOLOCK)                     

WHERE Package_name='SP_Baxter_CSAT')                           

                    

SET @startdate = @Last_modified_date                    

IF @frequency = 'daily'                    

SET @enddate = (SELECT @startdate+(SELECT interval FROM CKM.DBO.SOURCETABLE WITH(NOLOCK)WHERE Package_name = 'SP_Baxter_CSAT')*1)              

ELSE IF @frequency = 'weekly'                    

set @enddate = (SELECT @startdate+(SELECT interval FROM CKM.DBO.SOURCETABLE WITH(NOLOCK)WHERE Package_name = 'SP_Baxter_CSAT')*7)                 

ELSE IF @frequency = 'monthly'         

BEGIN        

SET @interval = (SELECT interval FROM CKM.DBO.SOURCETABLE WITH(NOLOCK) WHERE Package_name = 'SP_Baxter_CSAT')        

SET @enddate = (SELECT DATEADD(MONTH, @interval, EOMONTH(@startdate)))     

SET @enddate = (SELECT @startdate + (SELECT datediff(dd,@startdate,@enddate+1)))          

END    

                                                

UPDATE A                                                    

SET LoadStartTime=GETDATE()                                                      

FROM CKM.[dbo].[SourceTable] A WITH(NOLOCK)                                                      

WHERE Package_name = 'SP_Baxter_CSAT'                         

                      

DELETE FROM CKM.dbo.CKM_CSAT            

WHERE datasourceid=28          

            

INSERT INTO CKM.dbo.CKM_CSAT            

(      

SurveyReceived        

,FirstName        

,LastName        

,ClosedDate        

,AssignedGroup        

,AssigneeName        

,SubmitterSite        

,ProductName        

,Submitter        

,ResolvedBy        

,CallHandlingCategorization        

,HelpdeskResolvable        

,SLA        

,Q1Comment        

,Q2Comment        

,Q3Comment        

,Q4Comment        

,Q5Comment        

,SurveryCompDate        

,ResolverGroup        

,AverageScore        

,Verbatim        

,Q6Comment        

,Q7Comment        

,Q8Comment        

,Q9Comment        

,Q10Comment        

,DateOrder        

,SurveyCompletedOrder        

,GroupOwner        

,YearMonthShort        

,ReportedDate        

,SurveyInvitationDate        

,CorporateID        

,ContactType        

,ResolutionCode        

,CustomerCountry        

,UISProdCatHDR        

,UISServiceCatHDR        

,SubmitterRemedyLogin        

,Manager        

,ResolverDepartment        

,Country        

,ResolverCountry        

,[Type]        

,WorkOrderID        

,TaskID        

,DataSourceID        

)        

SELECT DISTINCT            

CSAT.TicketNumber as [Survey Received]              

,INCA_Assignee.FIRST_NAME  as [first name]          

,INCA_Assignee.LAST_NAME   as [last name]           

,I.Closed_Date  as [Closed date]          

,INCA_Supportgroup.SUPPORT_GROUP_NAME AS [Assigned Group]              

,INCA_Assignee.FULL_NAME as [Assignee Name]              

,Submitter_site.site AS [Submitter Site]              

,Product_Name AS [Product Name]              

,Submitter.Submitter as [Submitter]              

,ISH.RESOLVED_USER AS [Resolved By]              

,I.Call_Handling_Categorization as [Call Handling Categorization]              

,HelpDesk_Resolvable as [Helpdesk Resolvable]            

,CASE          

WHEN S.Progress = 'False' THEN 'NO'          

WHEN S.Progress = 'true' THEN 'YES'          

ELSE NULL END AS [SLA]       

,CSAT.Q1Score as [Q1 Score]              

,CSAT.Q2Score as [Q2 Score]              

,CSAT.Q3Score as [Q3 Score]              

,CSAT.Q4Score as [Q4 Score]              

,CSAT.Q5Score as [Q5 Score]              

,CSAT.SurveyCompleted as 'Survery Comp Date'            

,INCA_Supportgroup.SUPPORT_GROUP_NAME  as[Resolver Group]              

,CSAT.AverageScore as 'Average Score'              

,null as [Verbatim]              

,CSAT.Q6Score as [Q6 Score]              

,CSAT.Q7Score as [Q7 Score]              

,CSAT.Q8Score as [Q8 Score]              

,CSAT.Q9Score as [Q9 Score]              

,CSAT.Q10Score as [Q10 Score]              

,CSAT.SurveyInvitationDate as [Date Order]              

,CSAT.SurveyCompleted as [Survey Completed Order]              

,null as [GroupOwner]              

,null as [YearMonthShort]              

,I.Reported_Date  as [Reported Date]              

,CSAT.SurveyInvitationDate as [Survey Invitation Date]              

,INCA_Assignee.CORPORATE_ID as [CORPORATE ID]              

,I.Reported_Source as [Contact Type]            

 ,REPLACE(REPLACE(REPLACE(CAST(I.Resolution AS NVARCHAR(4000)) ,'|',''), CHAR(10),''), CHAR(13), '')  AS  [Resolution code]            

,Customer_Site.Country as [Customer Country]              

,I.UIS_ProdCat_HDR as [UIS ProdCat HDR]              

,I.UIS_ServiceCat_HDR as [UIS ServiceCat HDR]              

,INCA_Assignee.REMEDY_LOGIN_ID as [Submitter Remedy Login]              

,INCA_Supportgroup.GROUP_MANAGER as [Manager]              

,INCA_Assignee.DEPARTMENT as [Resolver Department]              

,Customer_Site.Country as [Country]              

,Customer_Site.Country as [Resolver Country]              

,CSAT.TicketType as [Type]              

,null as [Work Order ID]              

,CSAT.TicketNumber as [Task ID]              

,'28' as datasourceid               

FROM  ODS.dbo.ITIL_Transaction ITIL WITH(NOLOCK)               

Left Outer Join ODS.dbo.person Submitter WITH(NOLOCK)               

ON (ITIL.Submitter_Person_SK=Submitter.PERSON_SK and ITIL.Datasourceid=Submitter.Datasourceid)              

Left outer join ODS.dbo.SITE  Submitter_site WITH(NOLOCK)             

ON (Submitter.SITE_SK=Submitter_site.SITE_SK and Submitter.Datasourceid=Submitter_site.DatasourceId)      

Left JOIN ODS.dbo.COMPANY Customer_Company WITH(NOLOCK)            

ON (Customer_Company.COMPANY_SK=ITIL.COMPANY_SK AND Customer_Company.DataSourceid=ITIL.DataSourceid)      

Left OUTER JOIN ODS.dbo.Incident I WITH(NOLOCK)               

ON (ITIL.Transaction_ID=I.Transaction_ID and ITIL.DataSourceID=I.DataSourceID)              

Left OUTER JOIN ODS.dbo.CSAT_Data CSAT WITH(NOLOCK)               

ON (I.Incident_Number=CSAT.TicketNumber and I.datasourceid=CSAT.datasourceid)              

LEFT OUTER JOIN ODS.dbo.SUPPORTGROUP INCA_Supportgroup WITH(NOLOCK)               

ON (ITIL.DataSourceID=INCA_Supportgroup.DataSourceID and          

ITIL.ASSIGNED_GROUP_ID=INCA_Supportgroup.SUPPORT_GROUP_ID)               

LEFT OUTER JOIN ODS.dbo.PERSON Customer WITH(NOLOCK)               

ON (ITIL.PERSON_SK=Customer.PERSON_SK and ITIL.DatasourceId=Customer.DatasourceId)              

LEFT OUTER JOIN ODS.dbo.PERSON  INCA_Assignee WITH(NOLOCK)            

ON (ITIL.DataSourceID=INCA_Assignee.DataSourceID and ITIL.Assignee_Login_ID=INCA_Assignee.PERSON_SK)      

LEFT OUTER JOIN ODS.dbo.Incident_StatusHistory ISH WITH(NOLOCK)              

ON (ISH.datasourceid = CSAT.datasourceid and ISH.Transaction_ID =CSAT.TicketNumber)           

LEFT OUTER JOIN ODS.dbo.SITE Customer_Site WITH(NOLOCK)            

ON (Customer.SITE_SK=Customer_Site.SITE_SK)              

Left outer join ODS.dbo.incident_customerspecific Reopn WITH(NOLOCK)               

ON (Reopn.datasourceid = CSAT.datasourceid and Reopn.Incident_ID = CSAT.TicketNumber )          

LEFT OUTER JOIN ods.dbo.slm_measurement S WITH(NOLOCK)            

ON (I.Incident_id = S.Transaction_id  and I.DatasourceId=S.DatasourceId and S.[status]='completed')          

WHERE ITIL.transaction_type IN ('Incident')              

AND CSAT.datasourceid ='28'          

AND CSAT.SurveyInvitationDate BETWEEN @startdate AND @enddate          

            

SET @RecordCount = @@ROWCOUNT             

              

UPDATE SourceTable                                                 

set Last_Modified_Date = (SELECT Datediff(Second,'1970-01-01',(SELECT MAX(SurveyInvitationDate)-1       

FROM ODS.dbo.CSAT_Data WITH(NOLOCK) WHERE datasourceid ='28')))                                      

where Package_name = 'SP_Baxter_CSAT'                                                

                                                      

UPDATE A                                                    

SET LoadEndTime=GETDATE()                                                      

FROM CKM.[dbo].[SourceTable] A WITH (NOLOCK)                                                      

WHERE Package_name = 'SP_Baxter_CSAT'      

      

  -- Log the Procedure Completion Time                   

  SET @EndDt = GETDATE()                                    

  EXECUTE [CKM].[dbo].[CKM_updateextractlogbyid] @ExtractIdOut, @EndDt, 'E', @RecordCount      

                                                   

                                             

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()       

       

 --Log errormessage detail           

 EXECUTE [CKM].[dbo].[CKM_updateextractlogbyid] @ExtractIdOut, @EndDt,'F',@RecordCount , @ERROR_MESSAGE      

                                                                                 

RAISERROR (@ERROR_MESSAGE,@ERROR_SEVERITY,@ERROR_STATE);                                                                     

  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 Writ
Read more

java practice set 8

  questions: /* 1. Create a class Employee with the following properties and methods: Salary (property) (int) getSalary (method returning int) name (property) (String) getName (method returning String) setName (method changing name) 2. Create a class cellphone with methods to print “ringing…”, “vibrating…”, etc. 3. Create a class Square with a method to initialize its side, calculating area, perimeter etc. 4. Create a class Rectangle & problem 3. 5. Create a class TommyVecetti for Rockstar Games capable of hitting (print hitting…), running, firing, etc. 6. Repeat problem 4 for a circle. */ answers package com.company; class Employee{ int salary; String name; public int getSalary(){ return salary; } public String getName(){ return name; } public void setName(String n){ name = n; } } class CellPhone{ public void ring(){ System.out.print
Read more