Avatar of dbaSQL
dbaSQLFlag for United States of America asked on

Self-Join a table to report which column value(s) have changed

I have a 'ServerAudit' table that is populated when server definition change events occur.  The 'ServerAudit' table is very simple:     


ServerName, SQLVersion, SQLPatchLevel, OS, OSBuild, Written, Active

SrvA, MSSQL 2017, SP1,Windows 10 Pro, 19042.1237, 2021-10-04, 1 


When a change event occurs (ie., PatchLevel is changed), the existing record for a server is updated to Active = 0, and a new record is written where Active is 1, with the new PatchLevel.  This all works great.  But, I need to form a statement that will output specifically what value(s) have been changed for a server.  


For example, this is the most recent active record and previously active record for SrvA, where you can see the PatchLevel has been changed from SP1 to SP4.  The PatchLevel, Written and Active data values are different:

SrvA, MSSQL 2017, SP1,Windows 10 Pro, 19042.1237, 2021-09-26, 0  
SrvA, MSSQL 2017, SP4,Windows 10 Pro, 19042.1237, 2021-10-04, 1 


The output of my statement needs to say this:

        The PatchLevel for SrvA was changed from SP1 to SP4.


Or, say the SQLPatch and the OSBuild were changed, like this:


SrvA, MSSQL 2017, SP1,Windows 10 Pro, 19042.1237,1 
SrvA, MSSQL 2017, SP4,Windows 10 Pro, 19111.1234,1


I would need something of this nature -- format could be different, I just need BOTH change events referenced in my statement:

      The PatchLevel for SrvA was changed from SP1 to SP4.

      The OSBuild for SrvA was changed from 19042.1237 to 19111.1234 .


I believe I can handle this with a self join on the ServerAudit table, but I'm not certain how to collect most current and immediately previous column name(s) that have been changed.


Any EE help?



Note, I don't need Active or Written in the output because they mean nothing to the front end.

Microsoft SQL Server* Microsoft SQL Server 2019Microsoft SQL Server 2008

Avatar of undefined
Last Comment
dbaSQL

8/22/2022 - Mon
lcohan

You mean you need something like this:

create table ServerAudit (ServerName sysname, SQLVersion sysname, SQLPatchLevel sysname, OS sysname, OSBuild sysname, Written datetime, Active bit)

insert into ServerAudit (ServerName, SQLVersion, SQLPatchLevel, OS, OSBuild, Written, Active) 
select 'SrvA', 'MSSQL 2017', 'SP1','Windows 10 Pro', '19042.1237', '2021-06-26', 0
union
select 'SrvA', 'MSSQL 2017', 'SP4','Windows 10 Pro', '19042.1237', '2021-10-04', 1
union
select 'SrvA', 'MSSQL 2017', 'SP2','Windows 10 Pro', '19042.1237', '2021-07-26', 0
union
select 'SrvA', 'MSSQL 2017', 'SP3','Windows 10 Pro', '19042.1237', '2021-08-04', 0;

declare @srvname sysname;
declare @sqlpatchold sysname;
declare @sqlpatchnew sysname;

with cte_list (ServerName,SQLVersion,SQLPatchLevel,OS,OSBuild,Written, Active)
as 
   (select top 2 * from ServerAudit 
      group by ServerName,SQLVersion,SQLPatchLevel,OS,OSBuild,Written, Active
      order by Written desc)

select @srvname = (select top 1 ServerName from cte_list where active = 1), 
      @sqlpatchold = (select top 1 SQLPatchLevel from cte_list where active = 0),
      @sqlpatchnew = (select top 1 SQLPatchLevel from cte_list where active = 1)

select 'The PatchLevel for ' + @srvname + ' was changed from ' + @sqlpatchold +' to ' + @sqlpatchnew;

Open in new window

slightwv (䄆 Netminder)

>>When a change event occurs (ie., PatchLevel is changed), the existing record for a server is updated to Active = 0

Why?  Seems like unnecessary DML.  Latest written date for the server should be 'active'.

 Anyway, you can use LEAD and LAG to peek at the next or previous row.

Here is my approach:
with cte as (
	select servername, written, sqlversion, sqlpatchlevel, os, osbuild,
		lead(sqlversion) over(partition by servername order by written desc) next_sqlversion,
		lead(sqlpatchlevel) over(partition by servername order by written desc) next_sqlpatchlevel,
		lead(os) over(partition by servername order by written desc) next_os,
		lead(osbuild) over(partition by servername order by written desc) next_osbuild
	from serveraudit 
)
select
	servername,
	'On ' + convert(varchar, written) +
	':' + 
		coalesce(nullif(
			case when sqlversion != next_sqlversion then ' SQL Version changed from ' + next_sqlversion + ' to ' + sqlversion else '' end + 
			case when sqlpatchlevel != next_sqlpatchlevel then ' SQL Patch changed from ' + next_sqlpatchlevel + ' to ' + sqlpatchlevel else '' end + 
			case when os != next_os then ' OS changed from ' + next_os + ' to ' + os else '' end + 
			case when osbuild != next_osbuild then ' OS build changed from ' + next_osbuild + ' to ' + osbuild else '' end 
		,'')
		, 'Nothing changed'
	) + ':'
from cte

Open in new window


Working example:
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=3a148c8c4e90a716d6d114bac7862270
Scott Pletcher


CREATE TABLE #ServerAudit (
    ServerName varchar(100) NOT NULL,
    SQLVersion varchar(50) NOT NULL,
    SQLPatchLevel varchar(20) NOT NULL,
    OS varchar(50) NOT NULL,
    OSBuild varchar(20) NOT NULL,
    Written date NOT NULL,
    Active char(1) NOT NULL
    );
TRUNCATE TABLE #ServerAudit;
INSERT INTO #ServerAudit VALUES
    ('SrvA',' MSSQL 2017',' SP1','Windows 10 Pro','19042.1237','2021-09-26','0'),
    ('SrvA',' MSSQL 2017',' SP4','Windows 10 Pro','19042.1237','2021-10-04','1'),
    ('SrvB',' MSSQL 2017',' SP1','Windows 10 Pro','19042.1237','2021-09-27','0'),
    ('SrvB',' MSSQL 2017',' SP4','Windows 10 Pro','19111.1234','2021-10-05','1')

SELECT ca2.*
FROM #ServerAudit SA1
CROSS APPLY (
    SELECT
        SA1.ServerName,  SA1.SQLVersion,  SA1.SQLPatchLevel,  SA1.OS,  SA1.OSBuild,  SA1.Written,  SA1.Active,
        SA2.SQLVersion AS SQLVersion2, SA2.SQLPatchLevel AS SQLPatchLevel2,
        SA2.OS AS OS2, SA2.OSBuild AS OSBuild2, SA2.Written AS Written2, SA2.Active AS Active2
    FROM (
        SELECT TOP (1) ServerName, SQLVersion, SQLPatchLevel, OS, OSBuild, Written, Active
        FROM #ServerAudit SA2
        WHERE SA2.ServerName = SA1.ServerName AND SA2.Written < SA1.Written
        ORDER BY SA1.Written DESC  
    ) AS SA2
) AS ca1
CROSS APPLY (
    SELECT 'The SQLVersion for ' + ca1.ServerName + ' has changed from ' + ca1.SQLVersion + ' to ' + ca1.SQLVersion2 + '.' AS Message
    WHERE ca1.SQLVersion <> ca1.SQLVersion2
    UNION ALL
    SELECT 'The SQLPatchLevel for ' + ca1.ServerName + ' has changed from ' + ca1.SQLPatchLevel + ' to ' + ca1.SQLPatchLevel2 + '.' AS Message
    WHERE ca1.SQLPatchLevel <> ca1.SQLPatchLevel2
    UNION ALL
    SELECT 'The OS for ' + ca1.ServerName + ' has changed from ' + ca1.OS + ' to ' + ca1.OS2 + '.' AS Message
    WHERE ca1.OS <> ca1.OS2
    UNION ALL
    SELECT 'The OSBuild for ' + ca1.ServerName + ' has changed from ' + ca1.OSBuild + ' to ' + ca1.OSBuild2 + '.' AS Message
    WHERE ca1.OSBuild <> ca1.OSBuild2
) AS ca2
WHERE SA1.Active = '1'

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
dbaSQL

Thank you all three very much.  I will be able to test your suggestions in just a few hours, and I will send an update soon after.  Thank you again!
ASKER
dbaSQL

I am working each of the suggestions now and I realized I did not ask one thing -- what if NEW servers are entered that did not pre-exist?  Will any of your suggestions account for the new values, or will they only function if there is an old and a new to compare?
slightwv (䄆 Netminder)

The beauty of relational tables is, new rows are added and queries, 'normally', pick up the new data.

I put 'normally' in quotes because like any other language, you can do bad things...

If you break things up into small test cases like the examples or my fiddle, you can add random new data and observe the  results.

For example, since I had a fiddle that I could easily change, add a new server:
insert into ServerAudit values
('SrvXXX', 'MSSQL 2017', 'SP4','Windows 10 Pro', '19042.1237', '2021-02-02', 1);

Open in new window



The fiddle with the new row:
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=38fa85ec73cea4864bb76f3824b6076c
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
dbaSQL

Excellent.  Thank you, slightwv.  I am still testing but I will send an update as soon as I have one.
Scott Pletcher

I believe that I wrote my code so that it will handle any new data.

CORRECTION to existing code:
...
        SA2.OS AS OS2, SA2.OSBuild AS OSBuild2, SA2.Written AS Written2, SA2.Active AS Active2
    FROM (
        SELECT TOP (1) ServerName, SQLVersion, SQLPatchLevel, OS, OSBuild, Written, Active
        FROM #ServerAudit SA2
        WHERE SA2.ServerName = SA1.ServerName AND SA2.Written < SA1.Written
        ORDER BY SA2.Written DESC  --<<-- change SA1.Written to SA2.Written in the ORDER BY
    ) AS SA2
) AS ca1
...


ASKER
dbaSQL

Still testing, but I've just been told that I need to include BuildDate into the process flow.  I've added the column into ServerAudit as a DATETIME and loaded the data.  I have also tried to add it into this process but I am having problems due to the ':' in the timestamp.

For example, slightwv, I've added these two pieces into your statement:

LEAD(BuildDate) OVER(PARTITION BY servername ORDER BY Written DESC) next_BuildDate

CASE WHEN BuildDate != next_BuildDate THEN ' BuildDate changed from ' + CONVERT(VARCHAR(50),CONVERT(DATE,next_BuildDate,103),103) + ' to ' + CONVERT(VARCHAR(50),CONVERT(DATE,BuildDate ,103),103) ELSE '' END

It compiles fine, but at runtime it fails with this:

Msg 245, Level 16, State 1, Line 29
Conversion failed when converting the varchar value ':' to data type int.

Possibly I am converting the datetime incorrectly.  slightwv, would you suggest a different method for passing a datetime value into this from serveraudit?


lcohan and Scott, still testing yours.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
dbaSQL

Scott, I've added the BuildDate into yours and it fails with this:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.


I should note, I tested all three suggestions exactly as you've posted successfully.  I'm just trying to add BuildDate into it, and the column is full DATETIME.  The conversion is causing problems.
ASKER
dbaSQL

Actually, Scott, I suppose it also could be Written.   You've used DATE values in your example with Written, but it is DATETIME with my data.  Because multiple changes can occur in a single day, I need to include the mm:ss with the value output in the message.
Scott Pletcher

YOU used date in YOUR sample data.
SrvA, MSSQL 2017, SP1,Windows 10 Pro, 19042.1237, 2021-09-26, 0  
SrvA, MSSQL 2017, SP4,Windows 10 Pro, 19042.1237, 2021-10-04, 1
I can only go by what you tell us.  Obviously WE know NOTHING about YOUR data.

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
dbaSQL

lcohan, I thought yours was working, but it is only returning change event details for the most recently recorded change event.  When there are multiple changes recorded in the table for different servernames, it only returns data for the most recent change.  I could be wrong, but I believe it is due to this line:      select @srvname = (select top 1 ServerName from cte_list where active = 1),

slightwv and scott, yours both worked well until I added BuildDate into the process.  Now I am receiving a similar error for both, but I cannot seem to resolve either.  It is exactly as you have each posted, only with BuildDate DATETIME in the table and I've included it within each of your suggested statements.

slightwv, yours fails with this now that I've added BuildDate into the process.  The colon is coming from the datetime values:
Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value ':' to data type int.

scott, yours fails with this now that I've added BuildDate into the process:
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.
ASKER
dbaSQL

>>I can only go by what you tell us.  Obviously WE know NOTHING about YOUR data.
Very true.  I am sorry for that confusion, Scott.  I remember doing that in the initial post because it was so large that it spread to multiple lines, and I was trying to keep the post clean and easy to read.    That was my mistake and I apologize for the inconvenience.
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
slightwv (䄆 Netminder)

Mine:
with cte as (
	select servername, written, sqlversion, sqlpatchlevel, os, osbuild, builddate,
		lead(sqlversion) over(partition by servername order by written desc) next_sqlversion,
		lead(sqlpatchlevel) over(partition by servername order by written desc) next_sqlpatchlevel,
		lead(os) over(partition by servername order by written desc) next_os,
		lead(osbuild) over(partition by servername order by written desc) next_osbuild,
		lead(builddate) over(partition by servername order by written desc) next_builddate
	from serveraudit 
)
select
	servername,
	'On ' + convert(varchar, written) +
	':' + 
		coalesce(nullif(
			case when sqlversion != next_sqlversion then ' SQL Version changed from ' + next_sqlversion + ' to ' + sqlversion else '' end + 
			case when sqlpatchlevel != next_sqlpatchlevel then ' SQL Patch changed from ' + next_sqlpatchlevel + ' to ' + sqlpatchlevel else '' end + 
			case when os != next_os then ' OS changed from ' + next_os + ' to ' + os else '' end + 
			case when osbuild != next_osbuild then ' OS build changed from ' + next_osbuild + ' to ' + osbuild else '' end +
			CASE WHEN BuildDate != next_BuildDate THEN ' BuildDate changed from ' + CONVERT(VARCHAR(50),CONVERT(DATE,next_BuildDate,103),103) + ' to ' + CONVERT(VARCHAR(50),CONVERT(DATE,BuildDate ,103),103) ELSE '' END

		,'')
		, 'Nothing changed'
	) + ':'
from cte

Open in new window


Fiddle:
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=3fd3f58cfe5a5d12074c193c20fe6e93
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
dbaSQL

slightwv, I am unsure exactly what I am missing, but I am still receiving exactly the same error.  I will keep at it and let you know the end result after I've identified the problem.

scott, yours works perfectly.  I've made numerous changes on multiple servers, and they've all been picked up and returned from the statement properly.  This is great -- but, I am immediately reminded that I still need to pump this into the html body of an email notification.  Because the number of change notifications will never be known, I was thinking about dumping them into a temp table and then querying any table input into the sp_send_dbmail, kind of like what I posted below.  I'm about ready to test it but I wanted to know your thoughts.

			DECLARE 
				@xml NVARCHAR(MAX),
				@body NVARCHAR(MAX),
				@sub VARCHAR(155),
				@srvname VARCHAR(35);

			SET @srvname = (SELECT @@SERVERNAME) -- for email subject line

			SET @xml = CAST(( 
				SELECT [Message] AS 'td'
				FROM #RESULTS --<<< populated from your script which i've wrapped into a procedure
				FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)
				)

			SET @body ='<html><body><H3>Server change events have occurred. </H3>
			<table border = "1" CELLSPACING = "3" CELLPADDING = "4"> 
			<tr>
			<th> Message </th></tr>'    

			SET @body = @body + @xml +'</table></body></html>'
			SET @sub = 'Server change events have occured on ' + @srvname + '. Please review.'

			-- send email w/change messages in body
			EXEC msdb.dbo.sp_send_dbmail
				@profile_name = 'profilename',
				@body = @body,
				@body_format ='HTML',
				@recipients ='recipients',
				@subject = @sub ;

Open in new window

slightwv (䄆 Netminder)

>>I will keep at it and let you know the end result after I've identified the problem.

If the fiddle I provided isn't realistic enough to help you out, you can always create a fiddle set up for us so we can test against what you want us to test against instead of making up our own test case loosely based on what we've read.
ASKER
dbaSQL

scott, while everything else seems to be exactly what I need, I've just noticed that the changed from and changed to values are reversed.  It is always listing the newer data value for changed from, and then the older value for changed to.  

This is the code:

SELECT 'The BuildDate for ' + ca1.ServerName + ' has changed from ' + CONVERT(VARCHAR(30),ca1.BuildDate,120) + ' to ' + CONVERT(VARCHAR(30),ca1.BuildDate2,120) + '.' AS Message
WHERE ca1.BuildDate<> ca1.BuildDate

This is the returned data, where you can see the changed from value is more recent than the changed to value.  They are swapped.  It is the same for each of the other columns returned as well, but I am using this as an example because the newer datetime vs older datetime is easier to see.

The BuildDate for  SQLServerNameXYZ has changed from 2021-10-06 13:55:50 to 2021-10-06 12:04:34.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Pletcher

Oops, you're quite right.  Need to flip them in the APPLY:
...
CROSS APPLY (
    SELECT 'The SQLVersion for ' + ca1.ServerName + ' has changed from ' + ca1.SQLVersion2 + ' to ' + ca1.SQLVersion + '.' AS Message
    WHERE ca1.SQLVersion <> ca1.SQLVersion?
    UNION ALL
    SELECT 'The SQLPatchLevel for ' + ca1.ServerName + ' has changed from ' + ca1.SQLPatchLevel2 + ' to ' + ca1.SQLPatchLevel + '.' AS Message
    WHERE ca1.SQLPatchLevel <> ca1.SQLPatchLevel2
    UNION ALL
    SELECT 'The OS for ' + ca1.ServerName + ' has changed from ' + ca1.OS2 + ' to ' + ca1.OS + '.' AS Message
    WHERE ca1.OS <> ca1.OS2
    UNION ALL
    SELECT 'The OSBuild for ' + ca1.ServerName + ' has changed from ' + ca1.OSBuild2 + ' to ' + ca1.OSBuild + '.' AS Message
    WHERE ca1.OSBuild <> ca1.OSBuild2
    UNION ALL
    SELECT 'The BuildDate for ' + ca1.ServerName + ' has changed from ' + CONVERT(varchar(30), ca1.BuildDate2, 120) + ' to ' +
        CONVERT(varchar(30), ca1.BuildDate, 120) + '.' AS Message --<<-- use format code 1?1 if you want the ms in the times
    WHERE ca1.OSBuild <> ca1.OSBuild2
) AS ca2
...
ASKER
dbaSQL

>>I believe that I wrote my code so that it will handle any new data.
Hey Scott, I hate to come back to this, and I can open a new one, if need be.  But.  I wanted to ask, do you have any idea how to change the output in the message for the new server entries to something like 'The 'XXXXX' new server was created on XX-XX-XXXX xx:xx:xx'  ?
Scott Pletcher

Let me take another look at it later today, refresh my mind.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
dbaSQL

Thank you very much.  I put it into place on several servers and tested it numerous times per server.  All change events are being reported except the addition of new servers and the deletion of existing servers.
Scott Pletcher

A new server is fairly easy.  But how would we know when a server has been deleted?  That server has NO active row at all??

DROP TABLE #ServerAudit;
GO
CREATE TABLE #ServerAudit (
    ServerName varchar(100) NOT NULL,
    SQLVersion varchar(50) NOT NULL,
    SQLPatchLevel varchar(20) NOT NULL,
    OS varchar(50) NOT NULL,
    OSBuild varchar(20) NOT NULL,
    Written date NOT NULL,
    BuildDate datetime NOT NULL,
    Active char(1) NOT NULL
    );
TRUNCATE TABLE #ServerAudit;
INSERT INTO #ServerAudit VALUES
    ('SrvA',' MSSQL 2017',' SP1','Windows 10 Pro','19042.1237','2021-09-26','2021-09-26 01:11', '0'),
    ('SrvA',' MSSQL 2017',' SP4','Windows 10 Pro','19042.1237','2021-10-04','2021-10-04 02:22','1'),
    ('SrvB',' MSSQL 2017',' SP1','Windows 10 Pro','19042.1237','2021-09-27','2021-09-27 03:33','0'),
    ('SrvB',' MSSQL 2017',' SP4','Windows 10 Pro','19111.1234','2021-10-05','2021-10-05 04:44','1'),
    ('SrvC',' MSSQL 2017',' SP4','Windows 10 Pro','19111.1234','2021-10-05','2021-10-05 05:55','1')

SELECT ca2.*
FROM #ServerAudit SA1
OUTER APPLY (
    SELECT
        SA1.ServerName,  SA1.SQLVersion,  SA1.SQLPatchLevel,  SA1.OS,  SA1.OSBuild,  SA1.Written,  SA1.Active, SA1.BuildDate,
        SA2.SQLVersion AS SQLVersion2, SA2.SQLPatchLevel AS SQLPatchLevel2, SA2.OS AS OS2,
        SA2.OSBuild AS OSBuild2, SA2.Written AS Written2, SA2.Active AS Active2, SA2.BuildDate AS BuildDate2
    FROM (
        SELECT TOP (1) *
        FROM (
            SELECT ServerName, SQLVersion, SQLPatchLevel, OS, OSBuild, Written, BuildDate, Active
            FROM #ServerAudit SA2
            WHERE SA2.ServerName = SA1.ServerName AND SA2.Written < SA1.Written
            UNION ALL
            SELECT TOP (1) NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
        ) AS SA3
        ORDER BY Written DESC  
    ) AS SA2
) AS ca1
CROSS APPLY (
    SELECT CASE WHEN ca1.SQLVersion2 IS NOT NULL THEN NULL
        ELSE 'The ' + ca1.ServerName + ' new server was created on ' + CONVERT(varchar(30), ca1.BuildDate, 120) END AS Message
    UNION ALL
    SELECT 'The SQLVersion for ' + ca1.ServerName + ' has changed from ' + ca1.SQLVersion2 + ' to ' + ca1.SQLVersion + '.' AS Message
    WHERE ca1.SQLVersion <> ca1.SQLVersion2
    UNION ALL
    SELECT 'The SQLPatchLevel for ' + ca1.ServerName + ' has changed from ' + ca1.SQLPatchLevel2 + ' to ' + ca1.SQLPatchLevel + '.' AS Message
    WHERE ca1.SQLPatchLevel <> ca1.SQLPatchLevel2
    UNION ALL
    SELECT 'The OS for ' + ca1.ServerName + ' has changed from ' + ca1.OS2 + ' to ' + ca1.OS + '.' AS Message
    WHERE ca1.OS <> ca1.OS2
    UNION ALL
    SELECT 'The OSBuild for ' + ca1.ServerName + ' has changed from ' + ca1.OSBuild2 + ' to ' + ca1.OSBuild + '.' AS Message
    WHERE ca1.OSBuild <> ca1.OSBuild2
    UNION ALL
    SELECT 'The BuildDate for ' + ca1.ServerName + ' has changed from ' + CONVERT(varchar(30), ca1.BuildDate2, 120) + ' to ' +
        CONVERT(varchar(30), ca1.BuildDate, 120) + '.' AS Message --<<-- use format code 1?1 if you want the ms in the times
    WHERE ca1.OSBuild <> ca1.OSBuild2
) AS ca2
WHERE SA1.Active = '1' AND ca2.Message <> ''

ASKER
dbaSQL

>> But how would we know when a server has been deleted?  That server has NO active row at all??
Yes.  There is a 'Active' column.  Have I understood you correctly?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Pletcher

Not really.

You said you wanted to report if a server had been deleted.  How would one know, looking at the data, that a server had been deleted so that it could be reported?  What in the data "tells" me that a server has been deleted?
ASKER
dbaSQL

Got it.  When a server is deleted, the previously existing record where Active = 1 is updated to Active = 0, and a new record with Active = 1 is not written to the table.  So you could see the previous history for all entries of this server, but there is no current Active entry.  KInd of like this, where SrvA was deleted:


example.png
Scott Pletcher

Ah, I did guess right, no active row at all.


DROP TABLE #ServerAudit;
GO
CREATE TABLE #ServerAudit (
    ServerName varchar(100) NOT NULL,
    SQLVersion varchar(50) NOT NULL,
    SQLPatchLevel varchar(20) NOT NULL,
    OS varchar(50) NOT NULL,
    OSBuild varchar(20) NOT NULL,
    Written date NOT NULL,
    BuildDate datetime NOT NULL,
    Active char(1) NOT NULL
    );
TRUNCATE TABLE #ServerAudit;
INSERT INTO #ServerAudit VALUES
    ('SrvA',' MSSQL 2017',' SP1','Windows 10 Pro','19042.1237','2021-09-26','2021-09-26 01:11', '0'),
    ('SrvA',' MSSQL 2017',' SP4','Windows 10 Pro','19042.1237','2021-10-04','2021-10-04 02:22','1'),
    ('SrvB',' MSSQL 2017',' SP1','Windows 10 Pro','19042.1237','2021-09-27','2021-09-27 03:33','0'),
    ('SrvB',' MSSQL 2017',' SP4','Windows 10 Pro','19111.1234','2021-10-05','2021-10-05 04:44','1'),
    ('SrvC',' MSSQL 2017',' SP4','Windows 10 Pro','19111.1234','2021-10-05','2021-10-05 05:55','1'),
    ('SrvD',' MSSQL 2017',' SP4','Windows 10 Pro','19111.1234','2021-10-05','2021-10-06 06:06','0')

SELECT ca2.*
FROM #ServerAudit SA1
OUTER APPLY (
    SELECT
        SA1.ServerName,  SA1.SQLVersion,  SA1.SQLPatchLevel,  SA1.OS,  SA1.OSBuild,  SA1.Written,  SA1.Active, SA1.BuildDate,
        SA2.SQLVersion AS SQLVersion2, SA2.SQLPatchLevel AS SQLPatchLevel2, SA2.OS AS OS2,
        SA2.OSBuild AS OSBuild2, SA2.Written AS Written2, SA2.Active AS Active2, SA2.BuildDate AS BuildDate2
    FROM (
        SELECT TOP (1) *
        FROM (
            SELECT ServerName, SQLVersion, SQLPatchLevel, OS, OSBuild, Written, BuildDate, Active
            FROM #ServerAudit SA2
            WHERE SA2.ServerName = SA1.ServerName AND SA2.Written < SA1.Written
            UNION ALL
            SELECT TOP (1) NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
        ) AS SA3
        ORDER BY Written DESC  
    ) AS SA2
) AS ca1
CROSS APPLY (
    SELECT CASE WHEN ca1.SQLVersion2 IS NOT NULL THEN NULL
        ELSE 'The ' + ca1.ServerName + ' new server was created on ' + CONVERT(varchar(30), ca1.BuildDate, 120) END AS Message
    UNION ALL
    SELECT 'The SQLVersion for ' + ca1.ServerName + ' has changed from ' + ca1.SQLVersion2 + ' to ' + ca1.SQLVersion + '.' AS Message
    WHERE ca1.SQLVersion <> ca1.SQLVersion2
    UNION ALL
    SELECT 'The SQLPatchLevel for ' + ca1.ServerName + ' has changed from ' + ca1.SQLPatchLevel2 + ' to ' + ca1.SQLPatchLevel + '.' AS Message
    WHERE ca1.SQLPatchLevel <> ca1.SQLPatchLevel2
    UNION ALL
    SELECT 'The OS for ' + ca1.ServerName + ' has changed from ' + ca1.OS2 + ' to ' + ca1.OS + '.' AS Message
    WHERE ca1.OS <> ca1.OS2
    UNION ALL
    SELECT 'The OSBuild for ' + ca1.ServerName + ' has changed from ' + ca1.OSBuild2 + ' to ' + ca1.OSBuild + '.' AS Message
    WHERE ca1.OSBuild <> ca1.OSBuild2
    UNION ALL
    SELECT 'The BuildDate for ' + ca1.ServerName + ' has changed from ' + CONVERT(varchar(30), ca1.BuildDate2, 120) + ' to ' +
        CONVERT(varchar(30), ca1.BuildDate, 120) + '.' AS Message --<<-- use format code 1?1 if you want the ms in the times
    WHERE ca1.OSBuild <> ca1.OSBuild2
    UNION ALL
    SELECT 'The ' + ServerName + ' old server was deleted on ' + CONVERT(varchar(30), MAX(BuildDate), 120)
    FROM #ServerAudit
    GROUP BY ServerName
    HAVING MAX(Active) = '0'
) AS ca2
WHERE SA1.Active = '1' AND ca2.Message <> ''

Open in new window


Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
dbaSQL

Hi Scott.  I am trying to implement your changes, but failing to compile with this error:

Msg 4101, Level 15, State 1, Line 103
Aggregates on the right side of an APPLY cannot reference columns from the left side.

I want to post the code up here, but what I have posted thus far is an incredibly generic version of what I am really running.  I know that is sometimes received poorly, but I just had to do that for confidentiality.  So, it's difficult for me to post what I am actually running -- which I am sure you'd probably need to help me determine the cause of that error.  Is that correct?   (really hoping the cause of this error is an easy/obvious fat finger on my end)
Scott Pletcher

Yeah, if you need aggregates on the APPLY side, that method won't work.  My code above doesn't need aggregates.

ASKER
dbaSQL

>>Yeah, if you need aggregates on the APPLY side, that method won't work.  My code above doesn't need aggregates.
Unfortunately, I don't know if I do or not.  I've never used the cross/outer apply to the extent that we are here.  Embarrassed to ask th is question, but would I find the 'aggregates on the apply side' ?

I am unsure if this is the relevant portion of the code, but this is where I had to alter your suggestion slightly:

    FROM #ServerAudit
    GROUP BY ServerName
    HAVING MAX(Active) = '0'
) AS ca2
WHERE SA1.Active = '1'
AND ca2.Message <> ''

This is what I've used in my code for the very same:

    FROM dbo.tablename
    GROUP BY ServerName
    HAVING MAX(CAST(Active AS TINYINT)) = 0
) AS ca2
WHERE lsp1.Active = 1
AND ca2.Message <> ''

I had to do that CAST on the Active because it's a BIT.  And your version failed with this:

Msg 8117, Level 16, State 1, Line 107
Operand data type bit is invalid for max operator.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Pletcher

Sorry, I overlooked that.  I didn't use bit type for Active when I created my sample code.
ASKER
dbaSQL

That's not possibly the cause of this -- is it?


Msg 4101, Level 15, State 1, Line 103
Aggregates on the right side of an APPLY cannot reference columns from the left side.
_agx_

That's not possibly the cause of this -- is it? 

Scott knows his example best, but ... I don't think that's the problem since his example worked fine WITH those two changes i.e. Changing the "Active" column to type BIT and adding the cast to tinyint

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=d34442ba57a0a0a6a6aa6639c70775cb





This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
dbaSQL

Thank you, agx.  As I said before, my expertise with cross/outer applies is minimal.  I will review my code vs Scott's and see if I can find it.
_agx_

Gotcha. If you can't find the issue, perhaps you could modify the existing "demo" fiddle with any other changes you made, at least enough to reproduce the error you're seeing. Worth a shot anyway :)
_agx_

See this thread for an explanation of the "..cannot reference columns from the left side"error
Cross apply works at line level.  
"For each line on the left, we apply a function on the right".
Your aggregation has no meaning on per line bases.

My guess is one of your APPLY's is using an aggregate with the wrong column alias.  Example:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e2ea6eb70af07f7c6b4015f4c5e66477







Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
dbaSQL

Thank you for your suggestions, ag.  I still cannot find it.   I want to upload the code so you guys can help me find it, but It is a completely different version of my original post.  I just posted a generic version of my construct because I cannot put it up here for public viewing.  Is there any way I can email it to yourself or to Mr. Pletcher?
slightwv (䄆 Netminder)

>>because I cannot put it up here for public viewing.

How complex are the tables and data you are working with?

Capture everything in a single SQL file, global search/replace to change all the column names and proprietary data in the sample and post away?

Masking "real" data isn't all that difficult.  The real is knowing you have the original column called Salary and you changed it to Bob when posting it.  You will need to remember that Bob means Salary when porting the code back.
ASKER
dbaSQL

>>How complex are the tables and data you are working with?
Not complex at all.  Just much larger than the Servers table I posted.  

>>Capture everything in a single SQL file, global search/replace to change all the column names and proprietary data in the sample and post away?
Thank you very much, slightwv.  Doing it now.  Will upload post as soon as I have it.  The table and the script.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
slightwv (䄆 Netminder)

>>Thank you very much, Scott.

FYI:  I'm not Scott.
ASKER
dbaSQL

Yep.  Sorry about that.
ASKER
dbaSQL

Ok.  I resolved the aggregates on the right side of the APPLY error, and agx was correct.  It was this:

      SELECT 'The ' + ServerName + ' old server was deleted on ' + CONVERT(varchar(30), MAX(ca1.Written), 120) AS Message

I changed it to this and no longer receive the error on the right side of the APPLY:

      SELECT 'The ' + ServerName + ' old server was deleted on ' + CONVERT(varchar(30), ca1.Written, 120) AS Message


I have added a new server and the output included a single record reporting the addition as intended -->
    The XXXX new server was created on 2021-11-01 19:01:52.

Then I deleted that server to test the notification on server removals, but I received 10 records for the notification, rather than one -->

    The XXXX old server was deleted on 2021-11-01 19:06:39.
    The XXXX old server was deleted on 2021-11-01 19:06:39.
    The XXXX old server was deleted on 2021-11-01 19:06:39.
    The XXXX old server was deleted on 2021-11-01 19:06:39.
    The XXXX old server was deleted on 2021-11-01 19:06:39.
    The XXXX old server was deleted on 2021-11-01 19:06:39.
    The XXXX old server was deleted on 2021-11-01 19:06:39.
    The XXXX old server was deleted on 2021-11-01 19:06:39.

Note, there were 11 records in the table with the addition of the new server, and 10 after I deleted the new one.   Looks like I received a notification for every remaining servername entry in the table.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
dbaSQL

>>Capture everything in a single SQL file, global search/replace to change all the column names and proprietary data in the sample and post away?

Trying many different ways to fix it, and now I've lost my new server addition notification.  Rather than futz it up further, I am following you recommendation, slightwv.  The table and statement are attached.  As I said before, not overly complicated.  Just much larger than the table definition that I posted initially.  Exactly as before, any changes to the table column values are reported correctly.  It is the addition of new server records or the deletion of existing records that I am not properly reporting.  Please let me know if anybody sees it.
genericized.sql
_agx_

> SELECT 'The ' + ServerName + ' old server was deleted on '
> + CONVERT(varchar(30), MAX(ca1.Written), 120) AS Message
                                   
My bad!  The original example did use the aggregate MAX. However the column reference is still wrong. Get rid of the alias ca1.  You want the local column, i.e.:


    SELECT 'The ' + ServerName + ' old server was deleted on ' + CONVERT(varchar(30), MAX(Written), 120) AS Message
    FROM    dbo.Servers
    GROUP BY LnkSrvName
    HAVING MAX(CAST(Active AS TINYINT)) = 0
    ) AS ca2
WHERE sa1.Active = 1
AND ca2.Message <> ''



ASKER
dbaSQL

It almost works, agx.  It just returns 10 of them.  Seems to be one for every servername in the table.  I need a condition to filter that to only the one that was deleted.  I may be wrong, but I think the final condition on the ca2 may be doing this:

) AS ca2
WHERE SA1.Active = '1' AND ca2.Message <> ''


The SRVNAME server was deleted on 2021-11-01 20:11:19
The SRVNAME server was deleted on 2021-11-01 20:11:19
The SRVNAME server was deleted on 2021-11-01 20:11:19
The SRVNAME server was deleted on 2021-11-01 20:11:19
The SRVNAME server was deleted on 2021-11-01 20:11:19
The SRVNAME server was deleted on 2021-11-01 20:11:19
The SRVNAME server was deleted on 2021-11-01 20:11:19
The SRVNAME server was deleted on 2021-11-01 20:11:19
The SRVNAME server was deleted on 2021-11-01 20:11:19
The SRVNAME server was deleted on 2021-11-01 20:11:19



When I create a new server, it is reported correctly:

The SRVNAME2 server was created at 2021-11-01 22:15:40
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
dbaSQL

Any ideas on the multiple returns for server deletions?
slightwv (䄆 Netminder)

>>The table and statement are attached.

Not much good without sample data and the expected results from that sample data.

We need to set up a test case where we can execute the select on our side to make sure any SQL we provide will return the desired results you give us.
or
We can continue to guess and this question can continue for another month.
ASKER
dbaSQL

The problem was because the Written timestamp was identical for all Active server entires in the table.  This made my MAX(Written) mute.
When I added a DISTINCT to the outermost query, it returned only a single record.  


CONVERT(varchar(30), MAX(Written), 120) AS Message

Thank you everyone for all of the input.  Even after the ticket was closed.  Very helpful!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.