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.

What `[Primary User]` should be shown if the last 5 logins are (in order) `bob, alice, charles ,alice, bob`? — Martin Smith, Sep 26 '12 at 17:26
Well, if that ever get to be the case, i guess it can be the latest one or random. — Keb, Sep 26 '12 at 17:31
And by virtual column you mean something that shows up as a column in the table as per @Norla's answer or just a column that is appended to a select query? — Martin Smith, Sep 26 '12 at 17:37
I think the best way would be not to alter the DB, so if it is possible I would like to append it to the result with a query — Keb, Sep 26 '12 at 17:42

score 3 · Accepted Answer · answered Sep 26 '12 at 17:46

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

This is perfect, thanks a lot Martin! It's making me kind of sad that I have tried a whole day to achieve something you cooked together in 5, but mostly happy :) Have a great one! — Keb, Sep 26 '12 at 18:03

score 0 · Answer 2 · edited May 23 '17 at 12:04

0

Looks like you want to define a computed column. Check out this question: Creating a computed column in SQL Server 2008

edited May 23 '17 at 12:04

Community

1
1

answered Sep 26 '12 at 17:31

Nick Vaccaro

5,428
6
38
60

Thank you for the response, I was not aware of this functionality! But I'm not sure if this approach suits my needs in this particular case, but I will definitely look more into it. Thanks again! – Keb Sep 26 '12 at 17:51
I misread "virtual column" as "I want all this jazz in a single column." Please consider using commas to separate columns. – Nick Vaccaro Sep 26 '12 at 19:06

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

2 Answers2