example click
update TEST
SET EncryptCol=EncryptByKey(Key_GUID('s_key'),convert(varbinary(MAX),XML_DATA2))
CLOSE SYMMETRIC KEY S_Key
-------------------------------------------------------------------------------------
71365118
71472146
71472132
71472158
71207434
71247204
71173686
71270633
globalaccountid = '0044100730'
GlobalAccountID in ('0042100372','0043200131','0043203872','0044100131','0044100162','0044100730'
,'0044111022'
,'0044111322'
,'0044133532'
,'0044200171'
,'0045100371'
,'0047201271'
,'0049100472')
We have discussed in the call regarding Click mapping document to
identify the sample data for these GlobalaccountId for the Dellpii Columns.
I attached the Mapping document please identity the Sample data for these
2 GlobalaccountId 0045100371 and 0044100730
use [UNNCLICKDBT]
select customer,* from W6TASKS where GlobalAccountID='0044100730'
select * from W6TASKS where GlobalAccountID='0044100730'
--call idaccordingng to global account id
55663622
55664649
select countryid,* from W6TASKS Where callid ='55663622' and globalaccountid = '0044100730'
select countryid,* from W6TASKS Where callid ='55664649' and globalaccountid = '0044100730'
-----------------------------------------------------------------------------------------------------------------------
select customer,* from W6TASKS where GlobalAccountID='0045100371'
select * from W6TASKS where GlobalAccountID='0045100371'
-- callid for 2nd GlobalAccountID
55669247
55668402
55668432
select contactphonenumber2,* from W6TASKS Where callid ='55669247' and globalaccountid = '0045100371'
select contactphonenumber2,* from W6TASKS Where callid ='55668402' and globalaccountid = '0045100371'
select contactphonenumber2,* from W6TASKS Where callid ='55668432' and globalaccountid = '0045100371'
-----------------------------------------------------------------------------------------
use [UNNCLICKDBT]
select * from W6TASKS
select customer,* from W6TASKS where GlobalAccountID='0044100730'where GlobalAccountID='0044100730'
select customer,* from W6TASKS where GlobalAccountID='0044100730'
--call idaccordingng to global account id
--55663622
--55664649
select serialnumber,* from W6TASKS Where callid ='55663622' and globalaccountid = '0044100730'
select serialnumber,* from W6TASKS Where callid ='55664649' and globalaccountid = '0044100730'
----------------------------------------------------------------------------------------------------------------------
select * from W6TASKS where GlobalAccountID='0045100371'
select customer,* from W6TASKS where GlobalAccountID='0045100371'
select customer,* from W6TASKS where GlobalAccountID='0045100371'
-- callid for 2nd GlobalAccountID
--55668452
--55668402
---55668432
select serialnumber,* from W6TASKS Where callid ='55668452' and globalaccountid = '0045100371'
select serialnumber,* from W6TASKS Where callid ='55668402' and globalaccountid = '0045100371'
select serialnumber,* from W6TASKS Where callid ='55668432' and globalaccountid = '0045100371'
USE crct filters and we can use joins
send me the query I will see the output
----------------------------------------------------------------------------------------------
select A.city
from W6ENGINEERS A inner join W6TASKS B
On A.Branch = B.Branch and B.globalaccountid = '0045100371' and B.callid ='55668432'
------------------------------------------------------------
use [UNNCLICKDBT]
select * from W6TASKS
select customer,* from W6TASKS where GlobalAccountID='0044100730'where GlobalAccountID='0044100730'
select customer,* from W6TASKS where GlobalAccountID='0044100730'
--call idaccordingng to global account id
--55663622
--55664649
select serialnumber,* from W6TASKS Where callid ='55663622' and globalaccountid = '0044100730'
select serialnumber,* from W6TASKS Where callid ='55664649' and globalaccountid = '0044100730'
----------------------------------------------------------------------------------------------------------------------
select * from W6TASKS where GlobalAccountID='0045100371'
select customer,* from W6TASKS where GlobalAccountID='0045100371'
select customer,* from W6TASKS where GlobalAccountID='0045100371'
-- callid for 2nd GlobalAccountID
--55668452
--55668402
---55668432
select serialnumber,* from [stg_UNNCLICKDBT].[dbo].W6TASKS Where callid ='55668452' and globalaccountid = '0045100371'
select serialnumber,* from W6TASKS Where callid ='55668402' and globalaccountid = '0045100371'
select serialnumber,* from W6TASKS Where callid ='55668432' and globalaccountid = '0045100371'
sp_help 'W6ENGINEERS'
select A.city
from W6ENGINEERS A inner join W6TASKS B
On A.Branch = B.Branch and B.globalaccountid = '0045100371' and B.callid ='55668432'
-------------------------------------------------------------------------------------------------------------------
--click configuration in .sql file
use [DellPII]
select *from sys.objects where type='u'
--insert into the excel files data
select * from PIIDBConfig
select * from DataSecure
select * from PIIConfig
select * from PIIConfig where fieldid=1
select * from PIIConfig where fieldid=1
select * from [dbo].[PIIClientParent]
--configuration data
select * from [dbo].[PIIClientChild]
select * from PIIConfig where fieldid=1 and fieldname='Serialnumber'
--and clientid=0043200131
--[dbo].[PIIClientParent]
--[dbo].[PIIClientChild]
-----------------------------------------------------------------------------------------------------------------------------
USE [DellPII]
GO
INSERT INTO [dbo].[PIIDBConfig]
([DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID])
VALUES
('UNNCLICKDBT'
,'W6TASKS'
,'contactname'
,36
,'UcareAdmin',
,GETDATE()
,2
,3)
GO
----------------------------------------------------------------------------------------
USE [DellPII]
GO
INSERT INTO [dbo].[PIIDBConfig]
([DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID])
VALUES
('UNNCLICKDBT'
,'W6ENGINEERS '
,'city'
,33
,'UcareAdmin'
,GETDATE()
,1
,3)
GO
----------------------------------------
USE [DellPII]
GO
INSERT INTO [dbo].[PIIDBConfig]
([DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID])
VALUES
('UNNCLICKDBT'
,'W6TASKS'
,'city'
,33
,'UcareAdmin'
,GETDATE()
,1
,3)
GO
-----------------------------------------
USE [DellPII]
GO
INSERT INTO [dbo].[PIIDBConfig]
([DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID])
VALUES
('UNNCLICKDBT'
,'W6TASKS'
,'contactname'
//primary contact name
,36
,'UcareAdmin',
,GETDATE()
,2
,3)
GO
-----------------------------------
USE [DellPII]
GO
INSERT INTO [dbo].[PIIDBConfig]
([DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID])
VALUES
('UNNCLICKDBT'
,'W6TASKS'
,'contactname'
//contact name
,41
,'UcareAdmin'
,GETDATE()
,2
,3)
GO
---------------------------------
USE [DellPII]
GO
INSERT INTO [dbo].[PIIDBConfig]
([DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID])
VALUES
('UNNCLICKDBT'
,'W6TASKS'
,'contactname'
//customer primary name
,31
,'UcareAdmin'
,GETDATE()
,1
,3)
GO
---------------------------------
USE [DellPII]
GO
INSERT INTO [dbo].[PIIDBConfig]
([DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID])
VALUES
('UNNCLICKDBT'
,'W6TASKS'
,'contactname2'
//customer alternative name
,32
,'UcareAdmin'
,GETDATE()
,1
,3)
GO
--------------------------------------
USE [DellPII]
GO
INSERT INTO [dbo].[PIIDBConfig]
([DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID])
VALUES
('UNNCLICKDBT'
,'W6TASKS'
,'ContactPhoneNumber'
//primary contact phone
,38
,'UcareAdmin'
,GETDATE()
,2
,3)
GO
---------------------------------------
USE [DellPII]
GO
INSERT INTO [dbo].[PIIDBConfig]
([DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID])
VALUES
('UNNCLICKDBT'
,'W6TASKS'
,'ContactPhoneNumber2'
//secondary phone number
,39
,'UcareAdmin'
,GETDATE()
,2
,3)
GO
--------------------------------------------------
USE [DellPII]
GO
INSERT INTO [dbo].[PIIDBConfig]
([DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID])
VALUES
('UNNCLICKDBT'
,'W6TASKS'
,'customer'
//company name
,42
,'UcareAdmin'
,GETDATE()
,2
,3)
GO
---------------------------------------------------
USE [DellPII]
GO
INSERT INTO [dbo].[PIIDBConfig]
([DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID])
VALUES
('UNNCLICKDBT'
,'W6Mission'
,'CustomerEmail'
//primary contact emailid
,37
,'UcareAdmin'
,GETDATE()
,2
,3)
GO
-----------------------------------------------
USE [DellPII]
GO
INSERT INTO [dbo].[PIIDBConfig]
([DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID])
VALUES
('UNNCLICKDBT'
,'W6CUSTOMERSURVEY'
,'CustomerEmail'
//primary contact email id
,37
,'UcareAdmin'
,GETDATE()
,2
,3)
GO
-----------------------------------------------
USE [DellPII]
GO
INSERT INTO [dbo].[PIIDBConfig]
([DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID])
VALUES
('UNNCLICKDBT'
,'W6PREMISSION'
,'CustomerEmail'
//primary contact email id
,37
,'UcareAdmin'
,GETDATE()
,2
,3)
GO
-------------------------------------------------
USE [DellPII]
GO
INSERT INTO [dbo].[PIIDBConfig]
([DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID])
VALUES
('UNNCLICKDBT'
,'W6TASKS'
,'CustomerEmail'
//primary contact email id
,37
,'UcareAdmin'
,GETDATE()
,2
,3)
GO
-----------------------------------------------
USE [DellPII]
GO
INSERT INTO [dbo].[PIIDBConfig]
([DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID])
VALUES
('UNNCLICKDBT'
,'W6CustomerSurvey'
,'comments',
//customer mood
,40
,'UcareAdmin'
,GETDATE()
,2
,3)
GO
----------------------------------------------
USE [DellPII]
GO
INSERT INTO [dbo].[PIIDBConfig]
([DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID])
VALUES
('UNNCLICKDBT'
,'W6CustomerSurvey'
,'Question1',
//customer mood
,40
,'UcareAdmin'
,GETDATE()
,2
,3)
GO
-------------------------------------------
USE [DellPII]
GO
INSERT INTO [dbo].[PIIDBConfig]
([DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID])
VALUES
('UNNCLICKDBT'
,'W6WORKINGAREA'
,'Description'
//product description
,43
,'UcareAdmin'
,GETDATE()
,1
,3)
GO
-------------------------------------
USE [DellPII]
GO
INSERT INTO [dbo].[PIIDBConfig]
([DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID])
VALUES
('UNNCLICKDBT'
,'W6ENGINEERS'
,'postcode'
//postal code
,35
,'UcareAdmin'
,GETDATE()
,1
,3)
GO
-----------------------------------------
USE [DellPII]
GO
INSERT INTO [dbo].[PIIDBConfig]
([DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID])
VALUES
('UNNCLICKDBT'
,'W6TASKS'
,'postcode'
//postal code
,35
,'UcareAdmin'
,GETDATE()
,1
,3)
GO
--------------------------------------------
USE [DellPII]
GO
INSERT INTO [dbo].[PIIDBConfig]
([DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID])
VALUES
('UNNCLICKDBT'
,'W6TASKS'
,'SerialNumber'
//serial number
,30
,'UcareAdmin'
,GETDATE(),
,1
,3)
GO
--------------------------------------------
USE [DellPII]
GO
INSERT INTO [dbo].[PIIDBConfig]
([DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID])
VALUES
('UNNCLICKDBT'
,'W6ENGINEERS'
,'state'
//statecode
,34
,'UcareAdmin'
,GETDATE()
,1
,3)
GO
----------------------------------------------
USE [DellPII]
GO
INSERT INTO [dbo].[PIIDBConfig]
([DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID])
VALUES
('UNNCLICKDBT'
,'W6TASKS'
,'state'
//state code
,34
,'UcareAdmin'
,GETDATE()
,1
,3)
GO
---------------------------------------------------
select * from [dbo].[PIIClientParent]
select * from PIIDBConfig
select * from DataSecure
select * from PIIConfig
I have been working on Click configurations .Inserting sample data for click.
---------------------------------------------------------
insert into PIIconfig (FieldName,LastUpdatedBy,LastModifiedDate,ClientParentID,ISActive,Comments)
values ('Serial Number','UcareAdmin',GETDATE(),3,1,NULL)
---------------------------------------------------
INSERT INTO [dbo].[PIIDBConfig] (
[DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID]
)
VALUES
('UNNCLICKDBT'
,'W6ENGINEERS '
,'city'
,33
,'UcareAdmin'
,GETDATE()
,1
,3),
('UNNCLICKDBT'
,'W6TASKS'
,'city'
,33
,'UcareAdmin'
,GETDATE()
,1
,3),
('UNNCLICKDBT'
,'W6TASKS'
,'contactname'
//primary contact name
,36
,'UcareAdmin',
,GETDATE()
,2
,3),
('UNNCLICKDBT'
,'W6TASKS'
,'contactname'
//contact name
,41
,'UcareAdmin'
,GETDATE()
,2
,3),
('UNNCLICKDBT'
,'W6TASKS'
,'contactname'
//customer primary name
,31
,'UcareAdmin'
,GETDATE()
,1
,3) ,
//customer alternative name
,32
,'UcareAdmin'
,GETDATE()
,1
,3),
('UNNCLICKDBT'
,'W6TASKS'
,'ContactPhoneNumber'
//primary contact phone
,38
,'UcareAdmin'
,GETDATE()
,2
,3),
('UNNCLICKDBT'
,'W6TASKS'
,'ContactPhoneNumber2'
//secondary phone number
,39
,'UcareAdmin'
,GETDATE()
,2
,3),
('UNNCLICKDBT'
,'W6TASKS'
,'customer'
//company name
,42
,'UcareAdmin'
,GETDATE()
,2
,3),
('UNNCLICKDBT'
,'W6Mission'
,'CustomerEmail'
//primary contact emailid
,37
,'UcareAdmin'
,GETDATE()
,2
,3) ,
('UNNCLICKDBT'
,'W6CUSTOMERSURVEY'
,'CustomerEmail'
//primary contact email id
,37
,'UcareAdmin'
,GETDATE()
,2
,3),
('UNNCLICKDBT'
,'W6PREMISSION'
,'CustomerEmail'
//primary contact email id
,37
,'UcareAdmin'
,GETDATE()
,2
,3) ,
('UNNCLICKDBT'
,'W6TASKS'
,'CustomerEmail'
//primary contact email id
,37
,'UcareAdmin'
,GETDATE()
,2
,3),
('UNNCLICKDBT'
,'W6CustomerSurvey'
,'comments',
//customer mood
,40
,'UcareAdmin'
,GETDATE()
,2
,3),
('UNNCLICKDBT'
,'W6CustomerSurvey'
,'Question1',
//customer mood
,40
,'UcareAdmin'
,GETDATE()
,2
,3),
('UNNCLICKDBT'
,'W6WORKINGAREA'
,'Description'
//product description
,43
,'UcareAdmin'
,GETDATE()
,1
,3),
('UNNCLICKDBT'
,'W6ENGINEERS'
,'postcode'
//postal code
,35
,'UcareAdmin'
,GETDATE()
,1
,3),
('UNNCLICKDBT'
,'W6TASKS'
,'postcode'
//postal code
,35
,'UcareAdmin'
,GETDATE()
,1
,3),
('UNNCLICKDBT'
,'W6TASKS'
,'SerialNumber'
//serial number
,30
,'UcareAdmin'
,GETDATE(),
,1
,3),
('UNNCLICKDBT'
,'W6ENGINEERS'
,'state'
//statecode
,34
,'UcareAdmin'
,GETDATE()
,1
,3),
('UNNCLICKDBT'
,'W6TASKS'
,'state'
//state code
,34
,'UcareAdmin'
,GETDATE()
,1
,3);
GO
------------------------------------------------------------------
INSERT INTO [dbo].[PIIDBConfig] (
[DBName]
,[TableName]
,[FieldName]
,[FieldIdFK]
,[LastUpdatedBy]
,[LastModifiedDate]
,[SecureID]
,[ClientParentID]
)
VALUES
('UNNCLICKDBT'
,'W6ENGINEERS '
,'city'
,33
,'UcareAdmin'
,GETDATE()
,1
,3),
('UNNCLICKDBT'
,'W6TASKS'
,'city'
,33
,'UcareAdmin'
,GETDATE()
,1
,3),
('UNNCLICKDBT'
,'W6TASKS'
,'contactname'
//primary contact name
,36
,'UcareAdmin',
,GETDATE()
,2
,3),
('UNNCLICKDBT'
,'W6TASKS'
,'contactname'
//contact name
,41
,'UcareAdmin'
,GETDATE()
,2
,3),
('UNNCLICKDBT'
,'W6TASKS'
,'contactname'
//customer primary name
,31
,'UcareAdmin'
,GETDATE()
,1
,3) ,
//customer alternative name
,32
,'UcareAdmin'
,GETDATE()
,1
,3),
('UNNCLICKDBT'
,'W6TASKS'
,'ContactPhoneNumber'
//primary contact phone
,38
,'UcareAdmin'
,GETDATE()
,2
,3),
('UNNCLICKDBT'
,'W6TASKS'
,'ContactPhoneNumber2'
//secondary phone number
,39
,'UcareAdmin'
,GETDATE()
,2
,3),
('UNNCLICKDBT'
,'W6TASKS'
,'customer'
//company name
,42
,'UcareAdmin'
,GETDATE()
,2
,3),
('UNNCLICKDBT'
,'W6Mission'
,'CustomerEmail'
//primary contact emailid
,37
,'UcareAdmin'
,GETDATE()
,2
,3) ,
('UNNCLICKDBT'
,'W6CUSTOMERSURVEY'
,'CustomerEmail'
//primary contact email id
,37
,'UcareAdmin'
,GETDATE()
,2
,3),
('UNNCLICKDBT'
,'W6PREMISSION'
,'CustomerEmail'
//primary contact email id
,37
,'UcareAdmin'
,GETDATE()
,2
,3) ,
('UNNCLICKDBT'
,'W6TASKS'
,'CustomerEmail'
//primary contact email id
,37
,'UcareAdmin'
,GETDATE()
,2
,3),
('UNNCLICKDBT'
,'W6CustomerSurvey'
,'comments',
//customer mood
,40
,'UcareAdmin'
,GETDATE()
,2
,3),
('UNNCLICKDBT'
,'W6CustomerSurvey'
,'Question1',
//customer mood
,40
,'UcareAdmin'
,GETDATE()
,2
,3),
('UNNCLICKDBT'
,'W6WORKINGAREA'
,'Description'
//product description
,43
,'UcareAdmin'
,GETDATE()
,1
,3),
('UNNCLICKDBT'
,'W6ENGINEERS'
,'postcode'
//postal code
,35
,'UcareAdmin'
,GETDATE()
,1
,3),
('UNNCLICKDBT'
,'W6TASKS'
,'postcode'
//postal code
,35
,'UcareAdmin'
,GETDATE()
,1
,3),
('UNNCLICKDBT'
,'W6TASKS'
,'SerialNumber'
//serial number
,30
,'UcareAdmin'
,GETDATE(),
,1
,3),
('UNNCLICKDBT'
,'W6ENGINEERS'
,'state'
//statecode
,34
,'UcareAdmin'
,GETDATE()
,1
,3),
('UNNCLICKDBT'
,'W6TASKS'
,'state'
//state code
,34
,'UcareAdmin'
,GETDATE()
,1
,3);
GO
----------------------------
use [UNNCLICKDBT]
sp_help 'W6ENGINEERS'
--1ST
select A.postcode
from W6ENGINEERS A inner join W6TASKS B
On A.Branch = B.Branch and B.globalaccountid = '0045100371' and B.callid ='55668452'
----------------------------------------------
--2ND
select A.postcode
from W6ENGINEERS A inner join W6TASKS B
On A.Branch = B.Branch and B.globalaccountid = '0045100371' and B.callid ='55668402'
--------------------------------------
--3RD
select A.postcode
from W6ENGINEERS A inner join W6TASKS B
On A.Branch = B.Branch and B.globalaccountid = '0045100371' and B.callid ='55668432'
--------------------------------------------
---------------------------------------------
select city,* from W6TASKS where globalaccountid = '0044100730' and callid ='55663622'
select city,* from W6TASKS where globalaccountid = '0044100730' and callid ='55663622'
select distinct city from W6ENGINEERS
--1ST
select A.city
from W6ENGINEERS A inner join W6TASKS B
On A.city = B.city and B.globalaccountid = '0044100730' and B.callid ='55663622'
------------------------------------------------
--2ND
select A.postcode
from W6ENGINEERS A inner join W6TASKS B
On A.Branch = B.Branch and B.globalaccountid = '0044100730' and B.callid ='55664649'
-------------------------------------------------------------------------------------------------------------------------------------------
use [UNNCLICKDBT]
sp_help 'W6ENGINEERS'
--1ST
select A.postcode
from W6ENGINEERS A inner join W6TASKS B
On A.Branch = B.Branch and B.globalaccountid = '0045100371' and B.callid ='55668452'
----------------------------------------------
--2ND
select A.postcode
from W6ENGINEERS A inner join W6TASKS B
On A.Branch = B.Branch and B.globalaccountid = '0045100371' and B.callid ='55668402'
--------------------------------------
--3RD
select A.postcode
from W6ENGINEERS A inner join W6TASKS B
On A.Branch = B.Branch and B.globalaccountid = '0045100371' and B.callid ='55668432'
--------------------------------------------
---------------------------------------------
select city,* from W6TASKS where globalaccountid = '0044100730' and callid ='55663622'
select city,* from W6TASKS where globalaccountid = '0044100730' and callid ='55663622'
select distinct city from W6ENGINEERS
--1ST
select A.city
from W6ENGINEERS A inner join W6TASKS B
On A.city = B.city and B.globalaccountid = '0044100730' and B.callid ='55663622'
------------------------------------------------
--2ND
select A.postcode
from W6ENGINEERS A inner join W6TASKS B
On A.Branch = B.Branch and B.globalaccountid = '0044100730' and B.callid ='55664649'
-------------------------------------------------------------------------------------------------------------------------------------------
person
company
Site
please don't config rows for stg tables(srms)--SRMS_BAIR_History
//insert into [DellPII].[dbo].[PIIDBConfig]
(DBName ,TableName ,FieldName, FieldIdFK, LastUpdatedBy, LastModifiedDate ,SecureID, ClientParentID ,ISActive)
values('ODS','Incident_SRMS_Specific','Call_type','20','UcareAdmin',GETDATE(),'2','1','1')
insert into [DellPII].[dbo].[PIIDBConfig]
(DBName ,TableName ,FieldName, FieldIdFK, LastUpdatedBy, LastModifiedDate ,SecureID, ClientParentID ,ISActive)
values('ODS','Incident_SRMS_Specific','Call_type','20','UcareAdmin',GETDATE(),'2','1','1')
"
------------------------------------------------------------
use DELLPII
select * from PIIDBConfig
select * from PIIConfig
--select * from PIIConfig where fieldid=1
select * from [dbo].[PIIClientParent]
select * from [dbo].[PIIClientChild]
select * from [dbo].[DataSecure]
select SecureID ,* from [DellPII].[dbo].[PIIDBConfig]
--[dbo].[SP_EncryptFields]
--[dbo].[SP_Redact]
--[dbo].[SP_DecryptFields]
--[dbo].[SP_ConfigLoad]
select top 10 Override_Install_Site
FROM Incident_SRMS_Specific_TEST
WHERE DataSourceID=42 AND National_Account_ID='43200131' AND Last_Scheduled_DateTime<=DATEADD(d,-90, getdate())
------------------------------------------
select top 10 Override_Install_Site
from Incident_SRMS_Specific_TEST A inner join W6TASKS B
On A.Branch = B.Branch and B.globalaccountid = '0045100371' and B.callid ='55668452'
--------------------------------------------------------------------------
use DELLPII
select * from PIIDBConfig
select * from PIIConfig
select * from PIIConfig where fieldid=1
select * from [dbo].[PIIClientParent]
select * from [dbo].[PIIClientChild]
-------------------------------------------
####INSERT SYSTEM##########
insert into [DellPII].[dbo].[PIIDBConfig]
(DBName ,TableName ,FieldName, FieldIdFK, LastUpdatedBy, LastModifiedDate ,SecureID, ClientParentID ,ISActive)
values('ODS_stg_omega','SRMS_BAIR_History','Bair_Caller','12','UcareAdmin',GETDATE(),'2','1','1')
--ODS_stg_omega.SRMS_BAIR_History.Bair_Caller
---------------------------------------------
#################
--42 ODS Person REMEDY_LOGIN_ID 12 UcareAdmin 2022-02-15 09:05:10.577 2 1 1
---------------------------------------------
####UPDATE STATEMENT
UPDATE [DellPII].[dbo].[PIIDBConfig]
SET SECUREID=1
WHERE DBName='ODS_stg_omega' AND TableName='SRMS_BAIR_History'AND FieldName='Bair_ProbDesc'
------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------
select [DellPII].[dbo].[PIIDBConfig]
--SET SECUREID=1
WHERE DBName='ODS' AND TableName='Incident_SRMS_Specific'AND FieldName='Customer_Phone_Provided_1st' AND FieldIdFK='14' AND SecureID='1'
---ODS Incident_SRMS_Specific Customer_Phone_Provided_1st 14 1
-------------------------------------------------------------------
select * from [DellPII].[dbo].[PIIDBConfig]
-------------------------------------------------------
##################WE DONT TOUCH
person
company
Site
ODS_stg_omega(SRMS_BAIR_History,SRMS_BAIR)
----------------------------------------------------
use DELLPII
select* from PIIDBConfig where DBConfigId in(42,43)
Delete from PIIDBConfig where DBConfigId in(72,73)
select* from PIIDBConfig where DBName='ODS_stg_omega'
Delete from PIIDBConfig where DBName='ODS_stg_omega'
select * from PIIDBConfig
--------------------------------------------------------------------
###########################LOOPING UPDATE STATEMENT################3
UPDATE Incident_SRMS_Specific_TEST
SET Reported_Style=EncryptByKey (Key_GUID('SymKey_test'),Reported_Style)
FROM Incident_SRMS_Specific_TEST
WHERE DataSourceID=@Datasourceid AND National_Account_ID='43200131' AND Last_Scheduled_DateTime<=DATEADD(d,-90, getdate())
UPDATE Incident_SRMS_Attention Vendor_Style
SET Vendor_Style=EncryptByKey (Key_GUID('SymKey_test'),Vendor_Style)
FROM Incident_SRMS_Attention
WHERE DataSourceID=@Datasourceid AND National_Account_ID='43200131' AND Last_Scheduled_DateTime<=DATEADD(d,-90, getdate())
------------------------------------------------------
select * from [sourcetables]
-------------------------------------------------------------------------------------------------------------------------------------------------
insert into [DellPII].[dbo].[sourcetables]
(TableName , FieldName, DataSourceID, DataSourceName ,Is_Active)
values('ITIL_Transaction','Headline','42','SRMS','1')
-------------------------------------------------------------------------------------------------------------------------------------------------
--ID TableName FieldName StartTime EndTime Last_Modified_Date DataSourceID DataSourceName Is_Active Row_Count
-- TableName, FieldName, DataSourceID ,DataSourceName ,Is_Active
--------------------------------------------------------------------------------------------------------------------------------------------------
insert into [DellPII].[dbo].[sourcetables]
(TableName ,FieldName, StartTime, EndTime ,Last_Modified_Date, DataSourceID, DataSourceName ,Is_Active, Row_Count)
values('Incident_SRMS_Attention','Vendor_Style','NULL','NULL','NULL','42','SRMS','1','NULL')
---------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[sourcetables](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[TableName] [nvarchar](200) NOT NULL,
[FieldName] [nvarchar](200) NOT NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[Last_Modified_Date] [numeric](15, 0) NULL,
[DataSourceID] [int] NOT NULL,
[DataSourceName] [nvarchar](20) NULL,
[Is_Active] [bit] NULL,
[Row_Count] [int] NULL,
-------------------------------------------------------------------------------------------------------
insert into [DellPII].[dbo].[sourcetables](TableName,FieldName,DataSourceID,DataSourceName,Is_Active)
values('ITIL_Transaction','Headline','42','SRMS','1')
------------------------------------------------------------------------------------------------------
INSERT INTO [DellPII].[dbo].[sourcetables]
(
,[TableName]
,[FieldName]
,[DataSourceID]
,[DataSourceName]
,[Is_Active]
)
VALUES
('Incident_SRMS_Attention'
,'Vendor_Style'
,'42'
,SRMS
,'1'
),
-----------------------------------------
INSERT INTO [dbo].[sourcetables]
([TableName]
,[FieldName]
,[StartTime]
,[EndTime]
,[Last_Modified_Date]
,[DataSourceID]
,[DataSourceName]
,[Is_Active]
,[Row_Count])
VALUES
('Incident_SRMS_Attention'
,'Vendor_Style'
,<StartTime, datetime,>
,<EndTime, datetime,>
,<Last_Modified_Date, numeric(15,0),>
,'42'
,SRMS
,1
,<Row_Count, int,>)
,
('Incident_SRMS_Specific'
,'Reported_Style'
,<StartTime, datetime,>
,<EndTime, datetime,>
,<Last_Modified_Date, numeric(15,0),>
,'42'
,SRMS
,1
,<Row_Count, int,>)
,
('Incident_SRMS_Attention'
,'Vendor_OEMSerialNumber'
,<StartTime, datetime,>
,<EndTime, datetime,>
,<Last_Modified_Date, numeric(15,0),>
,'42'
,SRMS
,1
,<Row_Count, int,>)
,
('Incident_SRMS_Specific '
,'Override_Install_Site'
,<StartTime, datetime,>
,<EndTime, datetime,>
,<Last_Modified_Date, numeric(15,0),>
,'42'
,SRMS
,1
,<Row_Count, int,>)
,
('Incident_SRMS_NOTES '
,'NOTETEXT '
,<StartTime, datetime,>
,<EndTime, datetime,>
,<Last_Modified_Date, numeric(15,0),>
,'42'
,SRMS
,1
,<Row_Count, int,>)
,
('Incident_SRMS_NOTES '
,'NOTETEXT '
,<StartTime, datetime,>
,<EndTime, datetime,>
,<Last_Modified_Date, numeric(15,0),>
,'42'
,SRMS
,1
,<Row_Count, int,>)
,
('Incident_SRMS_NOTES '
,'NOTETEXT '
,<StartTime, datetime,>
,<EndTime, datetime,>
,<Last_Modified_Date, numeric(15,0),>
,'42'
,SRMS
,1
,<Row_Count, int,>)
,
('Incident_SRMS_NOTES '
,'NOTETEXT '
,<StartTime, datetime,>
,<EndTime, datetime,>
,<Last_Modified_Date, numeric(15,0),>
,'42'
,SRMS
,1
,<Row_Count, int,>)
,
('Incident_SRMS_NOTES '
,'NOTETEXT '
,<StartTime, datetime,>
,<EndTime, datetime,>
,<Last_Modified_Date, numeric(15,0),>
,'42'
,SRMS
,1
,<Row_Count, int,>)
,
('ITIL_Transaction '
,'Headline'
,<StartTime, datetime,>
,<EndTime, datetime,>
,<Last_Modified_Date, numeric(15,0),>
,'42'
,SRMS
,1
,<Row_Count, int,>)
GO
--------------------------------
select * from [sourcetables]
--truncate table [sourcetables]
insert into [DellPII].[dbo].[sourcetables]
(DBName ,TableName , FieldName, DataSourceID, DataSourceName ,Is_Active)
values('ODS','ITIL_Transaction','Headline','42','SRMS','1')
################################----
SET NOCOUNT ON
DECLARE @DBConfigId INT
DECLARE @DBName NVARCHAR(MAX)
DECLARE @TableName NVARCHAR(MAX)
DECLARE @FieldName NVARCHAR(MAX)
DECLARE @SecureID INT
DECLARE @ClientParentID INT
DECLARE @ISActive INT
DECLARE @FieldIdFK INT
--DECLARE @EMP_CITY NVARCHAR(MAX)
DECLARE PIIDBConfig_CURSOR CURSOR
LOCAL FORWARD_ONLY FOR
SELECT * FROM PIIDBConfig
OPEN PIIDBConfig_CURSOR
FETCH NEXT FROM PIIDBConfig_CURSOR INTO @DBConfigId,@DBName,@TableName,@FieldName, @SecureID,@ClientParentID,@ISActive,@FieldIdFK
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT 'EMP_ID: ' + CONVERT(NVARCHAR(MAX),@EMP_ID)+ ' EMP_NAME '+@EMP_NAME +' EMP_SALARY ' +CONVERT(NVARCHAR(MAX),@EMP_SALARY) + ' EMP_CITY ' +@EMP_CITY
UPDATE Incident_SRMS_Specific_TEST SET Reported_Style=EncryptByKey (Key_GUID('SymKey_test'),Reported_Style)
WHERE DataSourceID=@Datasourceid AND National_Account_ID=(select clientID from dbo.[PIIClientChild]) AND Last_Scheduled_DateTime<=DATEADD(d,-90, getdate())
--UPDATE product_table SET unit_price = 52WHERE product_id = 105;
--DBConfigId DBName TableName FieldName FieldIdFK LastUpdatedBy LastModifiedDate SecureID ClientParentID ISActive
--1 ODS Incident_SRMS_Specific Reported_Style 1 UcareAdmin 2022-02-03 14:54:31.570 1 1 1
FETCH NEXT FROM PIIDBConfig_CURSOR INTO @DBConfigId,@DBName,@TableName,@FieldName, @SecureID,@ClientParentID,@ISActive,@FieldIdFK
END
CLOSE PIIDBConfig_CURSOR
DEALLOCATE PIIDBConfig_CURSOR
----------------------------------------
INSERT INTO [dbo].[sourcetables]
([DBName]
,[TableName]
,[FieldName]
,[StartTime]
,[EndTime]
,[Last_Modified_Date]
,[DataSourceID]
,[DataSourceName]
,[Is_Active]
,[Row_Count])
VALUES
('ODS'
,'Incident_SRMS_Attention'
,'Vendor_Style'
,'42'
,SRMS
,1
)
,
('ODS'
,'Incident_SRMS_Specific'
,'Reported_Style'
,'42'
,'SRMS
,1)
,
('ODS'
,'Incident_SRMS_Attention'
,'Vendor_OEMSerialNumber'
,'42'
,SRMS
,1)
,
('ODS'
,'Incident_SRMS_Specific '
,'Override_Install_Site'
,'42'
,SRMS
,1)
,
('ODS'
,'Incident_SRMS_NOTES '
,'NOTETEXT '
,'42'
,SRMS
,1)
,
('ODS'
,'Incident_SRMS_NOTES '
,'NOTETEXT '
,'42'
,SRMS
,1)
,
('ODS'
,'Incident_SRMS_NOTES '
,'NOTETEXT '
,'42'
,SRMS
,1
)
,
('ODS'
,'Incident_SRMS_NOTES '
,'NOTETEXT '
,'42'
,SRMS
,1)
,
('ODS'
,'Incident_SRMS_NOTES '
,'NOTETEXT '
,'42'
,SRMS
,1)
,
('ODS'
,'ITIL_Transaction '
,'Headline'
,'42'
,SRMS
,1)
GO
--------
CREATE TRIGGER SampleTrigger ON Sample
after INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON;
insert into SampleLog
(ID,Name,Date,UserName,Type)
SELECT
D.ID, D.NAME, GETDATE(), SYSTEM_USER,
CASE WHEN I.ID IS NULL THEN 'D' ELSE 'U' END
FROM
DELETED D
LEFT JOIN
INSERTED I ON D.ID = I.ID
UNION ALL
SELECT
I.ID, I.NAME, GETDATE(), SYSTEM_USER, 'I'
FROM
INSERTED I
LEFT JOIN
DELETED D ON D.ID = I.ID
WHERE
D.ID IS NULL
GO---
-------------------------------
Execute sp_dynamicupdate1 incident_test1,Resolution_Product_Name,1,3,Incident_Number
EXECUTE (SPNAME,TABLE NAME,COLUMN NAME,client id,decryption,JOIN TABLE)
1-encryption
2-reduct
3-decryption
-------------------------------------
select top 100 Resolution_Product_Name,* from [dellpii].[dbo].[incident_test1]
where incident_number in(select incident_number from [dbo].[vw_dellpii_SRMS_TXNS])
---------------------------------------------
VALUE TO VALUE TO MAPPING
ALSO NEED TABLE NAME
-------------------
insert into [DellPII].[dbo].[PIIDBConfig]
(DBName ,TableName ,FieldName, FieldIdFK, LastUpdatedBy, LastModifiedDate ,SecureID, ClientParentID ,ISActive)
values('ODS','Incident_Srms_Backlog','EMAIL1','13','UcareAdmin',GETDATE(),'2','1','1')
insert into [DellPII].[dbo].[PIIDBConfig]
(DBName ,TableName ,FieldName, FieldIdFK, LastUpdatedBy, LastModifiedDate ,SecureID, ClientParentID ,ISActive)
values('ODS','Incident_Srms_Backlog','EMAIL2','13','UcareAdmin',GETDATE(),'2','1','1')
update [DellPII].[dbo].[PIIDBConfig]
set FieldName='GEOADDRESS12' where FieldName='FIRSTNAME1' and TableName='Incident_Srms_Backlog' and FieldIdFK='4'
update [DellPII].[dbo].[PIIDBConfig]
set TableName='Incident_Srms_Backlog' and FieldName='GEOADDRESS12' and FieldIdFK ='4'
where TableName='Incident_Srms_Backlog' and FieldName='FIRSTNAME1' and FieldIdFK ='22'
--------------------------------
SELECT * FROM SOURCETABLES
-----------------------------------------------
update [DellPII].[dbo].[sourcetables]
set FieldName='CUSTOMERIDENTIFIER'
where TableName='Fs_Serviceincidents' and FieldName='CURRENTPROVIDERNAME' and ID='32'
--------------------------------------------------------------------
insert into [DellPII].[dbo].[sourcetables]
(DBName ,TableName ,FieldName, DataSourceID ,DataSourceName,Is_Active)
values('ODS','Incident_Srms_Backlog','EMAIL2','42','SRMS','1')
insert into [DellPII].[dbo].[sourcetables]
(DBName ,TableName ,FieldName, DataSourceID ,DataSourceName,Is_Active)
values('ODS','Incident_Srms_Backlog','COUNTRY2','42','SRMS','1')
insert into [DellPII].[dbo].[sourcetables]
(DBName ,TableName ,FieldName, DataSourceID ,DataSourceName,Is_Active)
values('ODS','Incident_Srms_Backlog','ORGANIZATIONNAME2','42','SRMS','1')
insert into [DellPII].[dbo].[sourcetables]
(DBName ,TableName ,FieldName, DataSourceID ,DataSourceName,Is_Active)
values('ODS','Incident_Srms_Backlog','COMPANY2','42','SRMS','1')
insert into [DellPII].[dbo].[sourcetables]
(DBName ,TableName ,FieldName, DataSourceID ,DataSourceName,Is_Active)
values('ODS','Incident_Srms_Backlog','VOICEPHONE2','42','SRMS','1')
insert into [DellPII].[dbo].[sourcetables]
(DBName ,TableName ,FieldName, DataSourceID ,DataSourceName,Is_Active)
values('ODS','Incident_Srms_Backlog','ALTERNATEPHONE2','42','SRMS','1')
insert into [DellPII].[dbo].[sourcetables]
(DBName ,TableName ,FieldName, DataSourceID ,DataSourceName,Is_Active)
values('ODS','Incident_Srms_Backlog','FAXPHONE2','42','SRMS','1')
--------
click configuration
------------------------------------
71365118
71472146
71472132
71472158
71207434
71247204
71173686
71270633
globalaccountid = '0044100730'
GlobalAccountID in ('0042100372','0043200131','0043203872','0044100131','0044100162','0044100730'
,'0044111022'
,'0044111322'
,'0044133532'
,'0044200171'
,'0045100371'
,'0047201271'
,'0049100472')
We have discussed in the call regarding Click mapping document to
identify the sample data for these GlobalaccountId for the Dellpii Columns.
I attached the Mapping document please identity the Sample data for these
2 GlobalaccountId 0045100371 and 0044100730
use [UNNCLICKDBT]
select customer,* from W6TASKS where GlobalAccountID='0044100730'
select * from W6TASKS where GlobalAccountID='0044100730'
--call idaccordingng to global account id
55663622
55664649
select countryid,* from W6TASKS Where callid ='55663622' and globalaccountid = '0044100730'
select countryid,* from W6TASKS Where callid ='55664649' and globalaccountid = '0044100730'
-----------------------------------------------------------------------------------------------------------------------
select customer,* from W6TASKS where GlobalAccountID='0045100371'
select * from W6TASKS where GlobalAccountID='0045100371'
-- callid for 2nd GlobalAccountID
55669247
55668402
55668432
select contactphonenumber2,* from W6TASKS Where callid ='55669247' and globalaccountid = '0045100371'
select contactphonenumber2,* from W6TASKS Where callid ='55668402' and globalaccountid = '0045100371'
select contactphonenumber2,* from W6TASKS Where callid ='55668432' and globalaccountid = '0045100371'
---------------------------------------------------------------------------------------------------------
use [UNNCLICKDBT]
select customer,* from W6TASKS where GlobalAccountID='0044100730'
select * from W6TASKS where GlobalAccountID='0044100730'
--call idaccordingng to global account id
select customerEmail from W6TASKS where callid = '55664649' and GlobalAccountID='0044100730'
---------------------------------
Comments
Post a Comment