SQL - Create "virtual" column based on data from other columns

Question

I have a some issues with a SQL query I'm working on, I'm sorry that I don't have any work-in-progress to show because nothing that I have tried until now have worked out too well, so I am hoping that someone will be able to point me in the right direction.

Tables:

Computers:

[SN][PN][ComputerName][Model][OS][Architecture][RAM][CPU]

Logons:

[SN][Username][Timestamp]

Info:

It works this way, every time a user logs on to a computer the computer info gets updated to the computer table and the username and timestamp gets inserted to the logons table.

Result

The result I am trying to acheive is the following:

[SN][PN][ComputerName][Model][OS][Architecture][RAM][CPU]**[Primary User]**

It should be only one row for each computer

The Primary User field should be based from the 5 latest logons and being the username with the most recurrences in those 5.

So I think that wraps It up, I hope someone here is able to at least point me in the right direction as every result google have to offer now show up as red.

Answer 1

It's a bit RBAR but something like the following should do it.

SELECT [SN],
       [PN],
       [ComputerName],
       [Model],
       [OS],
       [Architecture],
       [RAM],
       [CPU],
       O.[Username] AS [Primary User]
FROM   Computers C
       OUTER APPLY (SELECT TOP 1 [Username]
                    FROM   (SELECT TOP (5) *
                            FROM   Logons L
                            WHERE  L.[SN] = C.[SN]
                            ORDER  BY [Timestamp] DESC) Last5Users
                    GROUP  BY [Username]
                    ORDER  BY Count(*) DESC,
                              Max([Timestamp]) DESC) O