March 5, 2015 at 3:39 am
Morning,
Ok – After the mess I did explaining myself – lets start from scratch -
To create the audit table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[co_audit_trail2](
[user_name] [varchar](60) NULL,
[date_of_change] [datetime] NULL,
[time_of_change] [varchar](16) NULL,
[bi] [varchar](8000) NULL,
[ai] [varchar](8000) NULL,
[modified_table] [varchar](60) NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
To Insert the audit data –
INSERT INTO [dbo].[co_audit_trail2]
(user_name, date_of_change, time_of_change, bi, ai, modified_table)
SELECT 'ryank','Feb 25 2015 9:08AM','09:07:58:913','01ªRC1ªMrªRªCottissª ª4231ª0ª0ª0ª0ª1ª0ª0ªCustomer Experienceª0ªRickyª ªricky.cottiss@rrr.org.ukª0ª0ª0ª ª0ª ª ª ª ªCustomer Service Advisorª0ª0ª0ª0.00ª ª ªª444ª444ª0ªª ªª ª0ª','01ªRC1ªMrªRªCottissªª4231ª0ª0ª0ª0ª1ª0ª0ªAsset Managementª0ªRickyªªricky.cottiss@rrr.org.ukª0ª0ª0ªª0ªª ª ª ªPolicy & Data Co-Ordinatorª0ª0ª0ª0.00ª ª ªª444ª444ª0ª25/02/2015ª ªª ª0ª','ih_officer' UNION ALL
SELECT 'adamb','Feb 25 2015 1:24PM','13:24:16:993','01ªAD6ªMrªAªDayªª4174ª0ª0ª0ª0ª1ª0ª0ªResponsive Repairsª0ªAlainªªAlain.Day@rrr.org.ukª0ª0ª0ªª0ªªªªªWork Experience - Repairsª0ª0ª0ª0.00ªªªªª744ª0ªªAB6ª25/02/2015ª13:24:17ª0ª','01ªAD6ªMrªAªDayªª4174ª0ª0ª0ª0ª1ª0ª0ªResponsive Repairsª0ªAlainªªAlain.Day@rrr.org.ukª0ª0ª0ªª0ªªªªªWork Experience - Repairsª0ª0ª0ª0.00ªªªª744ª744ª0ªªAB6ª25/02/2015ª13:24:17ª0ª','ih_officer' UNION ALL
SELECT 'adamb','Feb 25 2015 1:24PM','13:24:17:853','null','01ªAD6ªMrªAªDayªª4174ª0ª0ª0ª0ª1ª0ª0ªResponsive Repairsª0ªAlainªªAlain.Day@rrr.org.ukª0ª0ª0ªª0ªªªªªWork Experience - Repairsª0ª0ª0ª0.00ªªªªª744ª0ªªAB6ª25/02/2015ª13:24:17ª0ª','ih_officer' UNION ALL
SELECT 'ryank','Feb 27 2015 4:00PM','16:00:43:407','01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analystª0ª0ª0ª0.00ªªªª660ª660ª0ª19/08/2014ªJB7ª12/08/2014ª14:37:18ª0ª','01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analyst 1ª0ª0ª0ª0.00ªªªª660ª660ª0ª27/02/2015ªJB7ª12/08/2014ª14:37:18ª0ª','ih_officer' UNION ALL
SELECT 'ryank','Feb 27 2015 4:02PM','16:02:15:227','01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analyst 1ª0ª0ª0ª0.00ªªªª660ª660ª0ª27/02/2015ªJB7ª12/08/2014ª14:37:18ª0ª','01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analyst 2ª0ª0ª0ª0.00ªªªª660ª660ª0ª27/02/2015ªJB7ª12/08/2014ª14:37:18ª0ª','ih_officer' UNION ALL
SELECT 'joannab','Feb 27 2015 4:07PM','16:07:12:003','01ªJD7ªMrsªJªDobinsonª01634 83194ªª0ª0ª1ª0ª1ª0ª0ªFoyersª0ªJulieªªjulie.dobinson@rrr.org.ukª0ª0ª0ªª0ªªªªªSupport Officerª0ª0ª0ª0.00ªªªª738ª738ª0ªªAB6ª23/12/2014ª11:28:25ª0ª','01ªJD7ªMrsªJªDobinsonª01634 83194ªª1ª0ª1ª0ª1ª1ª0ªFoyersª0ªJulieªªjulie.dobinson@rrr.org.ukª0ª0ª0ªª0ªªªªªSupport Officerª0ª0ª0ª0.00ªªªª738ª738ª0ª27/02/2015ªAB6ª23/12/2014ª11:28:25ª0ª','ih_officer' UNION ALL
SELECT 'ryank','Mar 2 2015 9:35AM','09:35:57:170','01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analyst 2ª0ª0ª0ª0.00ªªªª660ª660ª0ª27/02/2015ªJB7ª12/08/2014ª14:37:18ª0ª','01ªCO-RKªMrªRªKeastªªª1ª1ª1ª1ª1ª1ª0ªICTª0ªRyanªªª0ª0ª0ªª0ªªªªªICT Systems Analystª0ª0ª0ª0.00ªªªª660ª660ª0ª02/03/2015ªJB7ª12/08/2014ª14:37:18ª0ª','ih_officer' UNION ALL
SELECT 'jennyr','Mar 4 2015 3:23PM','15:23:13:777','01ªIL2ªMrªIªLongª01634 35ª4199ª0ª0ª0ª0ª0ª0ª0ªCorporate Servicesª0ªIanª07921537823ªian.long@rrr.org.ukª0ª0ª0ªª0ªªªªªCommunity Investment Managerª0ª0ª0ª0.00ªªªªª745ª0ª04/03/2015ªJR6ª04/03/2015ª15:23:14ª0ª','01ªIL2ªMrªIªLongª01634 35ª4199ª0ª0ª0ª0ª0ª0ª0ªCorporate Servicesª0ªIanª07921537823ªian.long@rrr.org.ukª0ª0ª0ªª0ªªªªªCommunity Investment Managerª0ª0ª0ª0.00ªªªª745ª745ª0ª04/03/2015ªJR6ª04/03/2015ª15:23:14ª0ª','ih_officer' UNION ALL
SELECT 'jennyr','Mar 4 2015 3:23PM','15:23:14:600','null','01ªIL2ªMrªIªLongª01634 35ª4199ª0ª0ª0ª0ª0ª0ª0ªCorporate Servicesª0ªIanª07921537823ªian.long@rrr.org.ukª0ª0ª0ªª0ªªªªªCommunity Investment Managerª0ª0ª0ª0.00ªªªªª745ª0ª04/03/2015ªJR6ª04/03/2015ª15:23:14ª0ª','ih_officer'
To Create the CRM table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CRM2](
[PersonRef] [varchar](60) NULL,
[StaffName] [varchar](60) NULL,
[LoggedDateTime] [datetime] NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
To Insert the CRM DATA
INSERT INTO [dbo].[CRM2]
(PersonRef, StaffName, LoggedDateTime)
SELECT '009545','Ryan Keast','2015-02-27 15:56:17.000' UNION ALL
SELECT '009545','Ryan Keast','2015-02-27 16:01:06.000'UNION ALL
SELECT '009545','Ryan Keast','2015-02-27 16:02:31.000'
So below the CRM Data shows you that I have made three entries on “the system” and the times I made them.
The Audit data shows me any changes to the “Job Title” (Item Number 29) that has taken place.
I need a script that will pull me back the correct job title as of entry of the crm entry – hope that now makes sense?
--Crm Script--
select StaffName, PersonRef,LoggedDateTime
from crm2
--Audit Script--
select CONCAT([Forename],' ' ,[Surname]) AS 'StaffName', date_of_change + time_of_change as 'changedate', [Job Title Before], [Job Title After]
from co_audit_trail2
cross apply (select Item AS 'Job Title Before' FROM mhsInsight.dbo.DelimitedSplit8K(bi,'ª') WHERE ItemNumber = 29) o --Job TitleBEFORE--
cross apply (select Item AS 'Job Title After' FROM mhsInsight.dbo.DelimitedSplit8K(ai,'ª') WHERE ItemNumber = 29) o1 --Job TitleAFTER--
cross apply (select Item AS 'Forename' FROM mhsInsight.dbo.DelimitedSplit8K(ai,'ª') WHERE ItemNumber = 17) o2 --Forename--
cross apply (select Item AS 'Surname' FROM mhsInsight.dbo.DelimitedSplit8K(ai,'ª') WHERE ItemNumber = 5) o3 --Surname--
where [Surname] = 'Keast'
Thank you for your patience and any help would be wonderful.
March 5, 2015 at 4:19 am
Final script you provided seems to work fine, I just cant see the connect between that and the CRM table.
What is the data set you are looking to return.
As you have three rows in the CRM table and 3 in the output, but there doesn't seem to be anything in common, except for the Staffname,
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 5, 2015 at 4:51 am
This is the issue I think I have - I only have the Staff Name in both tables that's unique.
So the only way I can think is to join from the CRM to AUDIT using the Officer Name and then some sort of CASE statement that for the 3 CRM entries only pull back the Job Title that was "active" at point of crm entry.
March 5, 2015 at 5:03 am
A Change to the audit script
--Audit Script--
select CONCAT([Forename],' ' ,[Surname]) AS 'StaffName', date_of_change as 'changedate', [Job Title Before], [Job Title After]
from co_audit_trail2
cross apply (select Item AS 'Job Title Before' FROM mhsInsight.dbo.DelimitedSplit8K(bi,'ª') WHERE ItemNumber = 29) o --Job TitleBEFORE--
cross apply (select Item AS 'Job Title After' FROM mhsInsight.dbo.DelimitedSplit8K(ai,'ª') WHERE ItemNumber = 29) o1 --Job TitleAFTER--
cross apply (select Item AS 'Forename' FROM mhsInsight.dbo.DelimitedSplit8K(ai,'ª') WHERE ItemNumber = 17) o2 --Forename--
cross apply (select Item AS 'Surname' FROM mhsInsight.dbo.DelimitedSplit8K(ai,'ª') WHERE ItemNumber = 5) o3 --Surname--
where [Surname] = 'Keast'
So I made three changes to the officer of Ryan Keast
2015-02-27 16:00:00.000
2015-02-27 16:02:00.000
2015-03-02 09:35:00.000
Before the first change I logged a CRM entry
at 2015-02-27 15:56:17.000
After my first audit change I logged another at
2015-02-27 16:01:06.000
Then a final audit change at
2015-02-27 16:02:31.000
As I need to report on what the job title was at the time of the crm entry - all I have is this horrendous audit table.
So for the CRM entry logged on 2015-02-27 15:56:17.000 the job title was ICT Systems Analyst
The second entry at 2015-02-27 16:01:06.000 was ICT Systems Analyst 1
The Final on at 2015-02-27 16:02:31.000 was ICT Systems Analyst 2
Does that make any sense?
March 5, 2015 at 6:26 am
This might help you get started
NOTE : Tables changed to TEMP tables for ease, also using my own String splitter
Reporting.SplitParam_Test (Effectively Jeff Modens).
;WITH CTE_GetAudit
AS
(
select
CONCAT([Forename],' ' ,[Surname]) AS 'StaffName'
, date_of_change + time_of_change as 'changedate'
, [Job Title Before]
, [Job Title After]
from #co_audit_trail2
cross apply (select Item AS 'Job Title Before' FROM [Reporting].[SplitParam_test](bi,'ª') WHERE ItemNumber = 29) o --Job TitleBEFORE--
cross apply (select Item AS 'Job Title After' FROM [Reporting].[SplitParam_test](ai,'ª') WHERE ItemNumber = 29) o1 --Job TitleAFTER--
cross apply (select Item AS 'Forename' FROM [Reporting].[SplitParam_test](ai,'ª') WHERE ItemNumber = 17) o2 --Forename--
cross apply (select Item AS 'Surname' FROM [Reporting].[SplitParam_test](ai,'ª') WHERE ItemNumber = 5) o3 --Surname--
where [Surname] = 'Keast'
)
SELECT
CRMLog.Staffname
,ChangeDate
,LoggedDateTime
, [Job Title Before]
, [Job Title After]
FROM CTE_GetAudit
JOIN
(
Select
StaffName
, LoggedDateTime
, ISNULL
(
LEAD(LoggedDateTime,1)
OVER (PARTITION BY StaffName ORDER BY LoggedDateTime)
,'01-Jan-2900'
)EffectiveToDateEffectiveToDate
from #CRM2
) CRMLog ON CTE_GetAudit.StaffName=CRMLog.StaffName
WHERE ChangeDate>=CRMLog.LoggedDateTime and ChangeDate<CRMLog.EffectiveToDate
The way this works is that we create a set from the CRM table that has an effective to and effective from based on the next log entry for that staff member (LEAD function).
This can then be joined to the Ouput set of the original query using ChangeDate between.
Giving this result set
StaffnameChangeDateLoggedDateTimeJob Title BeforeJob Title After
Ryan Keast2015-02-28 08:00:43.4072015-02-27 16:02:00.000ICT Systems AnalystICT Systems Analyst 1
Ryan Keast2015-02-28 08:04:15.2272015-02-27 16:02:00.000ICT Systems Analyst 1ICT Systems Analyst 2
Ryan Keast2015-03-02 19:10:57.1702015-03-02 09:35:00.000ICT Systems Analyst 2ICT Systems Analyst
Hope this helps.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 5, 2015 at 6:54 am
Theres a big logic flaw in yourscript, you add the Time to a DATETIME that already has a time component, which is why my results look a little weird.
as per the previous script but the changedate column doesn't have the TIME component added.
;WITH CTE_GetAudit
AS
(
select
CONCAT([Forename],' ' ,[Surname]) AS 'StaffName'
, date_of_change as 'changedate'
, [Job Title Before]
, [Job Title After]
from #co_audit_trail2
cross apply (select Item AS 'Job Title Before' FROM [Reporting].[SplitParam_test](bi,'ª') WHERE ItemNumber = 29) o --Job TitleBEFORE--
cross apply (select Item AS 'Job Title After' FROM [Reporting].[SplitParam_test](ai,'ª') WHERE ItemNumber = 29) o1 --Job TitleAFTER--
cross apply (select Item AS 'Forename' FROM [Reporting].[SplitParam_test](ai,'ª') WHERE ItemNumber = 17) o2 --Forename--
cross apply (select Item AS 'Surname' FROM [Reporting].[SplitParam_test](ai,'ª') WHERE ItemNumber = 5) o3 --Surname--
where [Surname] = 'Keast'
)
SELECT
CRMLog.Staffname
,ChangeDate
,LoggedDateTime
, [Job Title Before]
, [Job Title After]
FROM CTE_GetAudit
JOIN
(
Select
StaffName
, LoggedDateTime
, ISNULL
(
LEAD(LoggedDateTime,1)
OVER (PARTITION BY StaffName ORDER BY LoggedDateTime)
,'01-Jan-2900'
)EffectiveToDate
from #CRM2
) CRMLog ON CTE_GetAudit.StaffName=CRMLog.StaffName
WHERE ChangeDate>=CRMLog.LoggedDateTime and ChangeDate<CRMLog.EffectiveToDate
Results set
StaffnameChangeDateLoggedDateTimeJob Title BeforeJob Title After
Ryan Keast2015-02-27 16:00:00.0002015-02-27 16:00:00.000ICT Systems AnalystICT Systems Analyst 1
Ryan Keast2015-02-27 16:02:00.0002015-02-27 16:02:00.000ICT Systems Analyst 1ICT Systems Analyst 2
Ryan Keast2015-03-02 09:35:00.0002015-03-02 09:35:00.000ICT Systems Analyst 2ICT Systems Analyst
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 11, 2015 at 3:55 am
Hi,
Changing tact on this one. The supplier of the software has now supplied a officer history table.
So I am now using a new tsql as below -
select officer_code
,department
,eff_date
,LEAD (eff_date-1, 1, 0) OVER (PARTITION BY officer_code ORDER BY eff_date DESC) expiry_date
from ih_officer_hist
where officer_code = 'RC1'
This gives me the two rows below -
officer_code department eff_date expiry_date
------------ ---------------------------------------- ----------------------- -----------------------
RC1 Customer Experience 2005-03-01 00:00:00.000 2015-01-04 00:00:00.000
RC1 Asset Management 2015-01-05 00:00:00.000 1900-01-01 00:00:00.000
(2 row(s) affected)
Is there any way that I can get the expiry date to be today's date instead of 1900-01-01 00:00:00.000, if no other Officers start with with RC1?
Thanks
March 11, 2015 at 4:07 am
Worked it out -
select officer_code
,department
,eff_date
,CASE WHEN LEAD (eff_date-1, 1, 0) OVER (PARTITION BY officer_code ORDER BY eff_date) = '1900-01-01 00:00:00.000' THEN GETDATE() ELSE LEAD (eff_date-1, 1, 0) OVER (PARTITION BY officer_code ORDER BY eff_date) END AS expiried_date
from ih_officer_hist
where officer_code = 'RC1'
March 11, 2015 at 4:42 am
TSQL Tryer (3/11/2015)
Worked it out -
select officer_code,department
,eff_date
,CASE WHEN LEAD (eff_date-1, 1, 0) OVER (PARTITION BY officer_code ORDER BY eff_date) = '1900-01-01 00:00:00.000' THEN GETDATE() ELSE LEAD (eff_date-1, 1, 0) OVER (PARTITION BY officer_code ORDER BY eff_date) END AS expiried_date
from ih_officer_hist
where officer_code = 'RC1'
The third parameter for the LEAD function is the default value to return when the value of eff_date at selected offset (in this case 1, i.e. one row ahead) is NULL. You've entered 0 for this value, which is implicitly converted to datetime '1900-01-01 00:00:00.000'. Instead of using 0 as the default value, try GETDATE() like this:
LEAD(eff_date-1, 1, GETDATE()) OVER (PARTITION BY officer_code ORDER BY eff_date)
https://msdn.microsoft.com/en-us/library/hh213125.aspx
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 11, 2015 at 6:11 am
That works Chris, and makes sense.
Thanks
March 12, 2015 at 2:53 am
Chris,
I keep forgetting that the LEAD/LAG window functions have the 'default' value field, and have been using ISNULL (as per my previous example!!!) :w00t:
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply