How to clone and modify rows in SQL table?

Question

I use MSSQL 2012.

I have this table called SensorMeasure:

| Id | SensorUnitId| MeasureDate            | AlertType | MeasureValue|   
| 1  |   3379      | 2016-06-05 00:01:34.000|         1 |     4       | 
| 3  |   3381      | 2016-09-18 11:26:33.000|         6 |     5       |
| 4  |   3381      | 2017-03-03 21:46:18.000|         3 |     3       |
| 5  |   3382      | 2016-09-18 11:26:33.000|         5 |     8       |
| 6  |   3381      | 2017-05-03 21:46:18.000|         3 |     0       |

I need to gruop the rows by SensorUnitId column then after groupping by SensorUnitId I need to clone rows which has latest date, set AlertType cell to 6 and set current date and time to MeasureDate cell and insert them into the table.

Here the result I expect:

| Id | SensorUnitId| MeasureDate            | AlertType | MeasureValue|   
| 1  |   3379      | 2016-06-05 00:01:34.000|         1 |     4       | 
| 3  |   3381      | 2016-09-18 11:26:33.000|         6 |     5       |
| 4  |   3381      | 2017-03-03 21:46:18.000|         4 |     3       |
| 5  |   3382      | 2016-09-18 11:26:33.000|         5 |     8       |
| 6  |   3381      | 2016-11-03 21:46:18.000|         3 |     0       |
| 7  |   3379      | 2016-12-12 15:30:34.000|         6 |     4       | 
| 8  |   3381      | 2016-12-12 15:30:34.000|         6 |     0       |
| 9  |   3382      | 2016-12-12 15:30:34.000|         6 |     8       |

Here how I group the rows:

SELECT  Max(MeasureDate) as Date, MAX(AlertType) as AlertType,SensorUnitId
FROM SensorsMeasure
GROUP BY SensorUnitId

But I don't know how after grouping I modify MeasureDate to current date and time and how to set AlertType to 6 and insert those rows to the table.

UPDATE

As I already wrote I have table named SensorMeasure.I need to group all records in the table by column SensorUnitId.

After records were grouped I need to fetch from each gruop record with the latest date.If the fetched record has AlertType value 6 I need to filter it(ignore it) if record has AlertType value not 6 I need to clone this row set AlertType cell to 6 and set current date and time to MeasureDate cell and insert them into the table SensorMeasure.

For example I have this table:

| Id | SensorUnitId|      MeasureDate       | AlertType | MeasureValue|   
| 1  |   3379      | 2016-06-05 00:01:34.000|         1 |     4       | 
| 3  |   3381      | 2016-09-18 11:26:33.000|         6 |     5       |
| 4  |   3381      | 2016-12-03 21:46:18.000|         3 |     3       |
| 5  |   3382      | 2016-09-18 11:26:33.000|         6 |     8       |

The desired table is:

| Id | SensorUnitId|      MeasureDate       | AlertType | MeasureValue|   
| 1  |   3379      | 2016-06-05 00:01:34.000|         1 |     4       | 
| 3  |   3381      | 2016-09-01 11:26:33.000|         6 |     5       |
| 4  |   3381      | 2016-12-03 21:46:18.000|         3 |     3       |
| 5  |   3382      | 2016-09-18 11:26:33.000|         6 |     8       |
| 6  |   3379      | 2016-12-12 21:20:34.000|         6 |     4       | 
| 7  |   3381      | 2016-12-12 21:20:34.000|         6 |     3       |

Answer 1

Latest update

This is my rephrasing of the question as I understand it:

If the most recent entry (by MeasureDate) for each SensorUnitId does not have AlertType = 6, I need to insert a new row into that table for that SensorUnitId with the current date, a new AlertType, and the most recent MeasurementValue.

I believe the easiest way to do this is with cross apply.

rextester: http://rextester.com/VBP95454

/* testing setup */
create table SensorsMeasure (Id int,SensorUnitId int,MeasureDate datetime,AlertType int,MeasureValue int)
insert into  SensorsMeasure (Id,SensorUnitId,MeasureDate,AlertType,MeasureValue) values (1,3379,'2016-06-05 00:01:34.000',1,4),(3,3381,'2016-09-18 11:26:33.000',6,5),(4,3381,'2016-12-03 21:46:18.000',3,3),(5,3382,'2016-09-18 11:26:33.000',6,8)
declare @NewAlertType int   ; set @NewAlertType    = 6;
declare @ExceptAlertType int; set @ExceptAlertType = 6;

select * from SensorsMeasure;

--**`cross [apply][4]`** version:

--insert into SensorsMeasure (SensorUnitId, MeasureDate, AlertType, MeasureValue)
select distinct
    SensorUnitId
  , MeasureDate  =   getdate()
  , AlertType    =  @NewAlertType
  , MeasureValue = x.MeasureValue
from SensorsMeasure sm
  cross apply (select top 1 MeasureValue, AlertType
                      from SensorsMeasure i
                      where i.SensorUnitId = sm.SensorUnitId
                      order by MeasureDate desc) as x
where x.AlertType != @ExceptAlertType;

You can also do this with top with ties (without needing to use cross or outer apply in this instance).

rextester: http://rextester.com/JWT74538

--insert into SensorsMeasure (SensorUnitId, MeasureDate, AlertType, MeasureValue)
select top 1 with ties
      sm.SensorUnitId
    , MeasureDate = getdate() 
    , AlertType   = @NewAlertType
    , sm.MeasureValue
  from  SensorsMeasure sm
  where sm.AlertType != @ExceptAlertType
  order by row_number() over (partition by sm.SensorUnitId order by sm.MeasureDate desc);

---

Previous Update:

It looks like I made an assumption that it was the max alert type based on the example data. Instead you could just use AlertType = 6.

Updated to the reflect updated question :

I need to ignore recordes after group by that has AlertType = 6.

Which I interpret to mean ignore SensorUnitIds that have had an AlertType of 6

rextester link for test code: http://rextester.com/CLA47054

/* testing setup */
create table SensorsMeasure (Id int,SensorUnitId int,MeasureDate datetime,AlertType int,MeasureValue int)
insert into  SensorsMeasure (Id,SensorUnitId,MeasureDate,AlertType,MeasureValue) values (1,3379,'2016-06-05 00:01:34.000',1,4),(3,3381,'2016-09-18 11:26:33.000',6,5),(4,3381,'2017-03-03 21:46:18.000',3,3),(5,3382,'2016-09-18 11:26:33.000',5,8),(6,3381,'2017-05-03 21:46:18.000',3,0);
declare @NewAlertType    int; set @NewAlertType    = 7;
declare @ExceptAlertType int; set @ExceptAlertType = 6;

correlated supquery version:

--insert into SensorsMeasure (SensorUnitId, MeasureDate, AlertType, MeasureValue)
  select
      SensorUnitId
    , MeasureDate  = getdate()
    , AlertType    = @NewAlertType
    , MeasureValue = (select top 1 MeasureValue
                        from SensorsMeasure i
                        where i.SensorUnitId = sm.SensorUnitId
                        order by MeasureDate desc)
  from SensorsMeasure sm
  where not exists (
    select 1
      from SensorsMeasure e
      where e.SensorUnitId = sm.SensorUnitId
        and e.AlertType    = @ExceptAlertType
        )
  group by SensorUnitId;

common table expression with row_number() version:

;with LatestMeasureValue as (
  select
      SensorUnitId
    , MeasureValue
    , rn=row_number() over (partition by SensorUnitId order by MeasureDate desc)
    from SensorsMeasure sm
    where not exists (
    select 1
      from SensorsMeasure e
      where e.SensorUnitId = sm.SensorUnitId
        and e.AlertType    = @ExceptAlertType
        )
)
--insert into SensorsMeasure (SensorUnitId, MeasureDate, AlertType, MeasureValue)
  select
      lmv.SensorUnitId
    , MeasureDate  = getdate()
    , AlertType    = @NewAlertType
    , MeasureValue = lmv.MeasureValue
  from LatestMeasureValue lmv
  where lmv.rn=1

cross apply version:

--insert into SensorsMeasure (SensorUnitId, MeasureDate, AlertType, MeasureValue)
select distinct
    SensorUnitId
  , MeasureDate  =   getdate()
  , AlertType    =  @NewAlertType
  , MeasureValue = x.MeasureValue
from SensorsMeasure sm
  cross apply (select top 1 MeasureValue
                      from SensorsMeasure i
                      where i.SensorUnitId = sm.SensorUnitId
                      order by MeasureDate desc) as x
  where not exists (
    select 1
      from SensorsMeasure e
      where e.SensorUnitId = sm.SensorUnitId
        and e.AlertType    = @ExceptAlertType
        )

---

old answer:

;with LatestMeasureValue as (
  select
      SensorUnitId
    , MeasureValue
    , rn=row_number() over (partition by SensorUnitId order by MeasureDate desc)
    from SensorsMeasure
)
insert into SensorsMeasure (SensorUnitId, MeasureDate, AlertType, MeasureValue)
  select
      lmv.SensorUnitId
    , MeasureDate = getdate()
    , mat.MaxAlertType
    , lmv.MeasureValue
  from LatestMeasureValue lmv
    cross join (
      select
        MaxAlertType=max(AlertType)
      from SensorsMeasure
      ) as mat
  where lmv.rn=1

This uses a common table expression to get the latest MeasureValue for each SensorUnitId, and cross joins a query to get the max(AlertType) to add the greatest AlertType to each of the latest values, and finally uses getdate() to serve as the new MeasureDate

You can also do this some other ways.

This method just uses a subquery and a correlated subquery:

insert into SensorsMeasure (SensorUnitId, MeasureDate, AlertType, MeasureValue)
  select
      SensorUnitId
    , MeasureDate  = getdate()
    , AlertType    = (select max(AlertType) from SensorsMeasure)
    , MeasureValue = (select top 1 MeasureValue
                        from SensorsMeasure i
                        where i.SensorUnitId = sm.SensorUnitId
                        order by MeasureDate desc)
  from SensorsMeasure sm
  group by SensorUnitId;

Answer 2

One more way using TOP WITH TIES + ROW_NUMBER:

DECLARE  @alerttype int = 6

--INSERT INTO SensorMeasure
SELECT top 1 with ties
        s.SensorUnitId,
        GETDATE() as MeasureDate,
        @alerttype as AlertType,
        s.MeasureValue
FROM SensorMeasure S
left join (select distinct SensorUnitId from SensorMeasure where AlertType =6) s1 
on s1.SensorUnitId = s.SensorUnitId 
WHERE s1.SensorUnitId is null
ORDER BY ROW_NUMBER() OVER (PARTITION BY s.SensorUnitId ORDER BY s.MeasureDate DESC)

Output:

SensorUnitId MeasureDate             AlertType   MeasureValue
------------ ----------------------- ----------- ------------
3379         2016-12-12 17:06:35.953 6           4
3382         2016-12-12 17:06:35.953 6           8

(2 row(s) affected)

EDIT

If you need only latest and not with AlertType = 6 you can add some OUTER APPLY

DECLARE  @alerttype int = 6

--INSERT INTO SensorMeasure
SELECT top 1 with ties
        s.SensorUnitId,
        GETDATE() as MeasureDate,
        @alerttype as AlertType,
        s.MeasureValue
FROM SensorMeasure S
OUTER APPLY (
    SELECT TOP 1 * 
    FROM SensorMeasure 
    WHERE SensorUnitId = s.SensorUnitId 
    ORDER BY MeasureDate DESC
        ) s1 
WHERE s1.AlertType != @alerttype
ORDER BY ROW_NUMBER() OVER (PARTITION BY s.SensorUnitId ORDER BY s.MeasureDate DESC)

Output:

SensorUnitId MeasureDate             AlertType   MeasureValue
------------ ----------------------- ----------- ------------
3379         2016-12-13 10:05:39.377 6           4
3381         2016-12-13 10:05:39.377 6           3

(2 row(s) affected)