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

sql, sql-server-2008, t-sql

Solution

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

Problem

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.

Original source

Related problems