Insert into temporary table an iterate an ID column

Question

I have a select statement that inserts data into a #temptable, it looks like this:

select null as ID, Name, AnotherId, date into #TempTable from Table.

The resulting #temptable looks like this:

|  Id  |  Name  |  AnotherId  |     Datetime      |
---------------------------------------------------
| null | Login  |    10       |2016-01-01 15:00:00|
| null | Command|    10       |2016-01-01 15:00:01|
| null | Login  |    20       |2016-01-01 15:01:00|
| null | Command|    10       |2016-01-01 15:01:00|
| null | Logout |    10       |2016-01-01 15:01:01|
| null | Command|    20       |2016-01-01 15:01:02|
| null | Logout |    20       |2016-01-01 15:02:00|

I would like to put in the Id column a unique ID but with some conditions as:

• When there is a Login assign a unique Id (eg for the 1st Login give Id = 1)
• Then for the next Login have Id = 2
• For the Commands between a Login and a Logout that have the same AnotherId then put the corresponding Id (eg for AnotherId = 10 I should have all the rows that have AnotherId = 10 -> Id = 1)

How should I proceed? Any help appreciated.

Edit: The results I want:

|  Id  |  Name  |  AnotherId  |     Datetime      |
| 1    | Login  |    10       |2016-01-01 15:00:00|
| 1    | Command|    10       |2016-01-01 15:00:01|
| 2    | Login  |    20       |2016-01-01 15:01:00|
| 1    | Command|    10       |2016-01-01 15:01:00|
| 1    | Logout |    10       |2016-01-01 15:01:00|
| 2    | Command|    20       |2016-01-01 15:01:02|
| 2    | Logout |    20       |2016-01-01 15:02:00

Answer 1

If I understand correctly, you want logins to have incremental ids, with all rows in-between having the same id.

Another way of expressing this is that the id is the number of logins on or before a given row.

In SQL Server 2012+, you can do this using ANSI standard cumulative sum functionality:

select sum(case when name = 'login' then 1 else 0 end) over
           (partition by anotherId order by datetime) as ID,
       Name, AnotherId, date
into #TempTable
from Table;

In earlier versions of SQL Server you can do this using outer apply.

EDIT:

The above (although useful) was not a complete understanding of the question. Instead:

select (case when name = 'login' then ID
             else max(ID) over (partition by AnotherId order by DateTime)
        end) as Id,
       Name, AnotherId, date
into #TempTable
from (select sum(case when name = 'login' then 1 else 0 end) over
                 (order by datetime) as ID,
             Name, AnotherId, date
      from Table
     ) t;