It is likely that many of us working in the automation space and/or working with PowerShell will need to write SQL queries. We aren’t SQL developers by any means, but we do what we can to get by. When developing a recent piece of automation I ended up writing this query that I thought demonstrated a useful technique.
The Scenario
I had a table of devices assigned to users. The query has to return all of the users devices, except for the most recently assigned device. The query needs to be able to handle devices with a NULL assigned date and consider these older than anything with a date.
Let’s set up a sample table:
CREATE TABLE [dbo].[AMTestTable](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[UserGuid] UNIQUEIDENTIFIER,
[DeviceGuid] UNIQUEIDENTIFIER,
[DeviceName] [nvarchar](256) NOT NULL,
[UserName] [nvarchar](256) NOT NULL,
[AssignedDate] [datetime] NULL
)
…and insert some sample data:
INSERT INTO [dbo].[AMTestTable] (
UserGuid,
DeviceGuid,
DeviceName,
UserName,
AssignedDate
)
VALUES
('f6b011de-83e3-463f-bdcc-f58da0229e03','0af13215-01ee-4d36-b50e-8e25e8d2e791','LAPTOP01','John Wick',GETDATE() -1),
('f6b011de-83e3-463f-bdcc-f58da0229e03','b2869028-1326-486a-a11a-ad0ca4f9a50d','LAPTOP02','John Wick',GETDATE() -2),
('f6b011de-83e3-463f-bdcc-f58da0229e03','deabf242-961c-4113-a5fc-8b913a7fee03','LAPTOP03','John Wick',GETDATE() -3),
('f4f13ddc-0b5f-4275-8bb2-1e9d9d6cbf00','2deeefa0-1450-448f-821d-e75099407828','LAPTOP04','Jack Bauer',GETDATE() -1),
('f4f13ddc-0b5f-4275-8bb2-1e9d9d6cbf00','c2a1a77b-b594-41aa-a7ed-f6c2bbd2e353','LAPTOP05','Jack Bauer',NULL),
('f4f13ddc-0b5f-4275-8bb2-1e9d9d6cbf00','eab954d3-a335-4777-af54-2db90e7ed7c4','LAPTOP06','Jack Bauer',GETDATE() -5)
LAPTOP01, 02 and 03 belong to John Wick. The query should return LAPTOP02 and 03, since 01 is the most recently assigned.
Looking at Jack Bauer who has LAPTOP04, 05 and 06, we have a NULL assigned date thrown into the mix. In this instance we want the query to return LAPTOP05 and 06, given that NULL should be considered older than any assigned date.
Let’s look at a query that will do this for us:
SELECT
D2.id,
D2.UserGuid,
D2.DeviceGuid,
D2.DeviceName,
D2.UserName,
D2.AssignedDate
FROM
(
SELECT
UserGuid,
MAX(ISNULL(AssignedDate,'1900-01-01 00:00:00.000')) as max_AssignedDate
FROM [dbo].[AMTestTable]
GROUP BY UserGuid
) D1
INNER JOIN [dbo].[AMTestTable] D2
ON D1.UserGuid = D2.UserGuid
AND D1.max_AssignedDate > ISNULL(D2.AssignedDate,'1900-01-01 00:00:00.000')
ORDER BY id
In essence, we are JOINing the table back to itself using 2 aliases, D1 (the thing we don’t want, the device with the latest date) and D2 (everything else that isn’t this).
The SELECT statement for the D1 alias is selecting the device with the latest date. We accommodate for NULLs here by using the ISNULL function. If the AssignedDate is not null, then return the date. If it’s null, then return ‘1900-01-01’ an obviously impossible date for someone to have had a laptop assigned, ensuring that on any comparison it will be older 😊 The MAX function picks the latest available date from the devices. The punchline is that the D1 alias returns the latest assigned device for the user.
We then JOIN to the D2 alias, but with the condition that the row must have an assigned date less than that of the row selected in D1 (i.e. everything else that isn’t the latest device). Again we use the ISNULL function here to return ‘1900-01-01’ for NULLs helping us with our comparison.
The resultant output is:
….which looks all good 👍
As a non-SQL person I’m very pleased with the results 😊 I had never considered using 2 aliases of the same table with a JOIN statement. If you are a SQL person reading this, then let me know if there was a better way of achieving this.
.The Dot Source 