Find All Record unchanged in last 4 updates 1

[Daytona00432]

I am trying to create a view into a table that contains weekly updates of scan of server file shares. I want to find those shares that are inactive and haven't changed in the last, say 4, updates.

The fields are HostName, ShareName, Date, NumberofDirectories, NunmberOfFiles, TotalbytesScanned.

How can this be done? If the only thing that has changed between scans is the Date field. And I only want to see the stale shares with at least four updates. I am using Server 2005.

 

[RiverGuy]

Does as a record get inserted if there are no updates? For example, if you have a share with zero activity, will it still be recorded with the same NumberOfDirectories, NumberOfFiles and TotalBytesScanned each day?

 

[Daytona00432]

Yes. Every week a record is created for every share that exists.

 

[RiverGuy]

I would suggest using a temporary table or table variable in this to break it down into manageable pieces.

--Get the Latest 4 records for each group
SELECT a.*
INTO #Temp1
FROM YourTable a
WHERE [Date] BETWEEN  (SELECT TOP 1 [Date] FROM (SELECT TOP 4 [Date] FROM YourTable b WHERE a.HostName = b.HostName AND a.ShareName = b.ShareName ORDER BY [Date] DESC) c ORDER BY [Date] ASC)
AND (SELECT TOP 1 [Date] FROM YourTable c WHERE a.HostName = c.HostName AND a.ShareName = c.ShareName ORDER BY [Date] DESC)

Then, look for groups without differences in the data

SELECT * 
FROM #Temp1 a
WHERE NOT EXISTS
	(SELECT * FROM #Temp1 b WHERE a.HostName = b.HostName AND a.ShareName = b.ShareName AND (a.NumberofDirectories <> b.NumberofDirectories OR a.NunmberOfFiles <> b.NunmberOfFiles OR a.TotalbytesScanned <> b.TotalbytesScanned))

Finally, drop the temp table

DROP TABLE #Temp1

Obviously, if you could guarantee that every group will have a record for each date, then you could use date logic and just subtract 4 weeks, but the code I provided should account for any interruptions.

 

[Daytona00432]

I'm getting a syntax error near the word ORDER. Here is my query:

SELECT     dbo.Hosts.ComputerName, dbo.CreationTime.ShareName, dbo.CreationTime.Date, dbo.CreationTime.NumberofDirectories, 
                      dbo.CreationTime.NumberofFiles, dbo.CreationTime.TotalScannedMBytes, dbo.CreationTime.CreationTime30, dbo.CreationTime.CreationTime30Size, 
                      dbo.CreationTime.CreationTime60, dbo.CreationTime.CreationTime60Size, dbo.CreationTime.CreationTime90, dbo.CreationTime.CreationTime90Size, 
                      dbo.CreationTime.CreationTime180, dbo.CreationTime.CreationTime180Size, dbo.CreationTime.CreationTime365, 
                      dbo.CreationTime.CreationTime365Size, dbo.CreationTime.CreationTime2yrs, dbo.CreationTime.CreationTime2yrsSize, 
                      dbo.CreationTime.CreationTime3yrs, dbo.CreationTime.CreationTime3yrsSize, dbo.CreationTime.CreationTime4yrs, 
                      dbo.CreationTime.CreationTime4yrsSize, dbo.CreationTime.CreationTime5yrs, dbo.CreationTime.CreationTime5yrsSize, 
                      dbo.CreationTime.CreationTimeover5, dbo.CreationTime.CreationTimeover5Size
INTO            #Temp1
FROM         dbo.CreationTime INNER JOIN
                      dbo.Hosts ON dbo.CreationTime.ComputerNameID = dbo.Hosts.ID a
WHERE     [Date] BETWEEN
                          (SELECT     TOP 1 [Date]
                            FROM          (SELECT     TOP 4 [Date]
                                                    FROM          dbo.CreationTime b
                                                    WHERE      a.Computername = b.ComputerName AND a.ShareName = b.ShareName
                                                    ORDER BY [Date] DESC) c
                            ORDER BY [Date] ASC) AND
                          (SELECT     TOP 1 [Date]
                            FROM          dbo.CreationTime c
                            WHERE      a.ComputerName = c.ComputerName AND a.ShareName = c.ShareName
                            ORDER BY [Date] DESC)

 

[RiverGuy]

I think you just had the table alias in the incorrect spot.


FROM dbo.CreationTime a INNER JOIN dbo.Hosts ON dbo.CreationTime.ComputerNameID = dbo.Hosts.ID (NO "a" here)

 

[Daytona00432]

Hello, RiverGuy,

You were right! I am now seeing everything as I want but with one caveat. Can I prevent the view from showing me records that do not return at least 4 results? I am seeing some newer shares that have not existed long enough to have been scanned 4 times. And thank you for your help. I hope I can be as helpful to someone in the future.

 

[RiverGuy]

There are several ways to accomplish that. The easiest to code would be this.

Right after you've created the #Temp table,

DELETE a
FROM #Temp1 a
INNER JOIN
	(SELECT KeyColumn(s),
	COUNT(*) AS Ct
	FROM #Temp1 b
	GROUP BY KeyColumns(s)
	HAVING COUNT(*) < 4) c
ON a.KeyColumns(s) = c.KeyColumn(s)

A better approach but somewhat longer to code and debug would be join a derived table similar to what I posted above to your query which creates the temporary table. But I'm feeling lazy right now.

 

[Daytona00432]

Looks like I partied a little too soon. I am seeing records that have changes in number of directories and/or number of files. Starting back from scratch I see I am also getting an error related to the INTO clause. Specifically, it is telling me that the INTO clause cannot be used in a VIEW or function.

 

[Daytona00432]

Ignore my last post. I found out that I needed to do all this as a stored procedure and not as a View. You are a life saver!