Keeping track of added/removed items in a field

[canonquiche]

I have a server inventory table that gets updated monthly. The fields include server name, date, cost, etc.

What I want is a query that identifies any servers that have been added or removed since the previous month (added or removed in the sense of they were used or not used in the current month). It would look something like this:

current_server | previous_server | current_date | previous_date
xyz000000001 | ------------- | Jan 2016 | Dec 2015
xyz000000002 | ------------- | Jan 2016 | Dec 2015
------------ | abc0000000001 | Jan 2016 | Dec 2015

In other words, current_server indicates servers that have been added. Conversely, previous_server indicates servers that have been removed.

For the other fields in the table (like cost), I am able to achieve something like this using an inner join query to find cost changes. But I can't seem to figure out how to do the same thing for changes in the servers.

SELECT prev.server_name, prev.server_cost, cur.server_cost, (cur.server_cost-prev.server_cost) AS server_cost_increase, cur.date AS [current], prev.date AS previous
FROM Invoice_ABC AS cur INNER JOIN Invoice_ABC AS prev ON cur.server_name = prev.server_name
GROUP BY prev.server_name,, prev.server_cost, cur.server_cost, (cur.server_cost-prev.server_cost), cur.date, prev.date, cur.server_name
HAVING ((cur.date)=(SELECT Max([cur].[date]) from Invoice_ABC)) AND ((prev.date)=(SELECT Dateadd("m", -1, Max(cur.date)) FROM Invoice_ABC)
ORDER BY cur.date DESC , cur.server_name;

 

[plog]

Please post 2 sets of data to demonstrate what you would like:

A. Starting sample data from your table. Be sure to include table and field names and enough sample data to cover all cases.

B. Expected results of A. Based on the data in A, show me what you expect the query to return.

 

[canonquiche]

Sample data:

server_name | date
AAA001 Dec-15
AAA002 Dec-15
AAA003 Dec-15
AAA004 Dec-15
AAA005 Dec-15
AAA002 Jan-16
AAA003 Jan-16
AAA004 Jan-16
AAA005 Jan-16
AAA006 Jan-16


Query returns:

current_server | previous_server | current_date | previous_date
------- | AAA001 | Jan-16 | Dec-15
AAA006 | -------| Jan-16 | Dec-15

In this case, server AAA001 was removed and AAA006 was added.

 

[plog]

The problem you have is that date isn't a date. To determine previous/current you need a field you can order. With just a month code and year, you can't do that.

Do you have an actual date field, or another field you could use to determine which record is prior and which is next?

 

[canonquiche]

Date is an actual, sortable date field. Dec-15 is stored as 12/1/2015, Jan-15 is 1/1/2016, and so on. As I mentioned in my initial post, the query works fine if I want to find changes in cost. I want to find changes in server names.

The problem you have is that date isn't a date. To determine previous/current you need a field you can order. With just a month code and year, you can't do that.

Do you have an actual date field, or another field you could use to determine which record is prior and which is next?

 

[plog]

Ok, then its possible. First, you will need a subquery:

SELECT Invoice_ABC.server_name, Min(Invoice_ABC.Date) AS [current_date], Max(Invoice_ABC.Date) AS previous_date
FROM Invoice_ABC
WHERE (((Invoice_ABC.date)>=DateAdd("m",-1,DateSerial(Year(Date()),Month(Date()),1))))
GROUP BY Invoice_ABC.server_name;

Name that 'ServerInventory_sub1'. It groups the data into 1 record per server and looks at just the last 2 months of data in your table. Then to get the results you want, this is the code:

SELECT IIf([current_date]=DMax("[current_date]","ServerInventory_sub1"),[server_name]) AS current_server, IIf([previous_date]=DMin("[previous_date]","ServerInventory_sub1"),[server_name]) AS previous_server, ServerInventory_sub1.current_date, ServerInventory_sub1.previous_date
FROM ServerInventory_sub1
WHERE (((ServerInventory_sub1.current_date)=[previous_date]));

 

[canonquiche]

That works - thanks!