SQL query to bring the last occurrence

Question

I have a query that performs data filtering through codes, adding the existing values, only I need to bring in the "Marked" column, only the last occurrence of a phone number, Ex:

I cannot change the structure of how the query generates, because I am a standardized report. the code:

select
PhoneNumber
,DataContato            = cast(a.contactDate as Date)
,HoraContato            = cast(left(cast(dateadd(minute,(datediff(minute,0,a.ContactDate)/30)*30,0)  as time),5) as varchar(5))
,Base                   = Count(a.PhoneNumber)
,Marcados = sum(case when StatusCode in('Cod_1, Cod_2, Cod_3, Cod_4') then 1 else 0 end) 

from OdsAspect.dbo.ContactIntraday a
    where 
    cast(contactDate as Date) between '2021-04-01' and '2021-04-11'
    and [Serviceid] IN (274) 
    group by 
        cast(a.contactDate as Date)
        ,cast(left(cast(dateadd(minute,(datediff(minute,0,a.ContactDate)/30)*30,0)  as time),5) as varchar(5))
        ,AgentId,
        PhoneNumber
    order by 1,2

Answer 1

To perform this query, think of set theory where you already have an initial set and need to select specific items from that set. I suggest two solutions:

1. Using sub-consumption based on the result already obtained;
2. Use the clause HAVING.

Using sub-consumption based on the result already obtained;

To solve the problem, I considered the result of your query as the table resultado since you already have this initial result. Later, I will show another solution option using the clause HAVING.

Situation has been tested in the SQL Fiddle and posted codes and results below:

MS SQL Server 2017 Schema Setup:

CREATE TABLE resultado(
    [PhoneNumber] [varchar](20) NULL,
    [DataContato] [date] NULL,
    [HoraContato] [varchar](5) NULL,
    [Base] [int] NULL,
    [Marcados] [varchar](1) NULL
)

insert into resultado values (7,'2021-04-01','11:00','1','1')
insert into resultado values (7,'2021-04-01','15:00','1','0')
insert into resultado values (7,'2021-04-01','11:00','1','0')
insert into resultado values ('06','2021-04-01','09:00','1','0')
insert into resultado values ('06','2021-04-01','16:00','1','1')
insert into resultado values ('06','2021-04-06','11:00','1','0')
insert into resultado values ('06','2021-04-06','16:30','1','1')

insert into resultado values ('83','2021-04-01','11:00','1','1')
insert into resultado values ('83','2021-04-01','13:00','1','0')
insert into resultado values ('83','2021-04-06','13:30','1','0')

insert into resultado values ('1','2021-04-01','11:00','1','1')
insert into resultado values ('1','2021-04-01','11:30','1','0')
insert into resultado values ('1','2021-04-06','12:30','1','0')

Query 1:

With resultado_agrupado AS
    (
       select * from resultado
    )

    Select ra.*
      from resultado_agrupado ra
      where cast(cast(ra.DataContato as varchar) + ' ' + ra.HoraContato+':00' as datetime) = 
        (   Select max(cast(cast(ra2.DataContato as varchar) + ' ' + ra2.HoraContato+':00' as datetime)) dataContato_Ultimo
              from resultado_agrupado ra2
             where ra2.PhoneNumber = ra.PhoneNumber 
        )

Results:

Phonenumber	Datacontact	Horacontacto	Groundwork	Marked
06	2021-04-06	16:30	1	1
1	2021-04-06	12:30	1	0
7	2021-04-01	15:00	1	0
83	2021-04-06	13:30	1	0

I used the excerpt to abstract the complexity of your original query

With resultado_agrupado AS
    (
       select * from resultado
    )

Replacing the select * from resultado for your consultation, would be:

With resultado_agrupado AS
    (
       select
        PhoneNumber
        ,DataContato            = cast(a.contactDate as Date)
        ,HoraContato            = cast(left(cast(dateadd(minute,(datediff(minute,0,a.ContactDate)/30)*30,0)  as time),5) as varchar(5))
        ,Base                   = Count(a.PhoneNumber)
        ,Marcados = sum(case when StatusCode in('Cod_1, Cod_2, Cod_3, Cod_4') then 1 else 0 end) 

        from OdsAspect.dbo.ContactIntraday a
            where 
            cast(contactDate as Date) between '2021-04-01' and '2021-04-11'
            and [Serviceid] IN (274) 
            group by 
                cast(a.contactDate as Date)
                ,cast(left(cast(dateadd(minute,(datediff(minute,0,a.ContactDate)/30)*30,0)  as time),5) as varchar(5))
                ,AgentId,
                PhoneNumber
    )

Use the clause HAVING

You use the having to restrict records after processing the GROUP BY.

Note that it is necessary that the field used in having is also present in group by, for this reason, I have included the contactDate as a last criterion of group by. If your query cannot include the field contactDate in the group by you will need to do the casts necessary to obtain the equivalent information in the main consultation after the having and in the sub-consumption as required, I will give an example with casts at the end of this reply.

See how your query would look with the use of HAVING:

--Se tivesse o contactDate no Group By        

select
PhoneNumber
,DataContato            = cast(a.contactDate as Date)
,HoraContato            = cast(left(cast(dateadd(minute,(datediff(minute,0,a.ContactDate)/30)*30,0)  as time),5) as varchar(5))
,Base                   = Count(a.PhoneNumber)
,Marcados
from ContactIntraday a
    where 
    cast(contactDate as Date) between '2021-04-01' and '2021-04-11'
    and [Serviceid] IN (274) 
    group by 
        cast(a.contactDate as Date)
        ,cast(left(cast(dateadd(minute,(datediff(minute,0,a.ContactDate)/30)*30,0)  as time),5) as varchar(5))
        ,AgentId,
        ,PhoneNumber
        ,Marcados
        ,contactDate
    having contactDate = (select max(a2.contactDate) from ContactIntraday a2 where a2.PhoneNumber = a.PhoneNumber)
    order by 1,2

Having without adding contactDate and using casts

    select
    PhoneNumber
    ,DataContato            = cast(a.contactDate as Date)
    ,HoraContato            = cast(left(cast(dateadd(minute,(datediff(minute,0,a.ContactDate)/30)*30,0)  as time),5) as varchar(5))
    ,Base                   = Count(a.PhoneNumber)
    ,Marcados
    from ContactIntraday a
        where 
        cast(contactDate as Date) between '2021-04-01' and '2021-04-11'
        and [Serviceid] IN (274) 
        group by 
            cast(a.contactDate as Date)
            ,cast(left(cast(dateadd(minute,(datediff(minute,0,a.ContactDate)/30)*30,0)  as time),5) as varchar(5))
            ,AgentId
            ,PhoneNumber
            ,Marcados
        having cast(cast(a.contactDate as Date) as varchar) +' '+ cast(left(cast(dateadd(minute,(datediff(minute,0,a.ContactDate)/30)*30,0)  as time),5) as varchar(5)) = 
        (
            select max(
                   cast(cast(cast(a2.contactDate as Date) as varchar) + ' ' + cast(left(cast(dateadd(minute,(datediff(minute,0,a2.ContactDate)/30)*30,0)  as time),5) as varchar(5)) as datetime))
               from ContactIntraday a2 where a2.PhoneNumber = a.PhoneNumber
        )
        order by 1,2

To test the having with the table resultado proposal, would be like this:

    Select PhoneNumber, DataContato,    HoraContato,    Base,   Marcados
      from resultado r
      group by PhoneNumber, DataContato,    HoraContato,    Base,   Marcados
      having Cast(cast(DataContato as varchar) + ' ' + HoraContato as datetime) = 
      (
          Select Max(Cast(cast(DataContato as varchar) + ' ' + HoraContato as datetime)) 
             from resultado r2
             where r2.PhoneNumber = r.PhoneNumber
       )

Answer 2

From what I understand... You can try to bring the 'Marked' column using a subquerie that brings the value of the highest date/time of the phone number. An idea you can try

Something like:

marcado = 
 select 'sua_coluna' from OdsAspect.dbo.ContactIntraday a 
Where PhoneNumber = 'seu_numero' and a.contactDate = (select max(a.contactDate) from from OdsAspect.dbo.ContactIntraday a Where PhoneNumber = 'seu_numero' )