Return results with the max datetime

Transact-SQL
1

Good morning, I'm trying to get the campaign name with the max time so I'm only showing one record per CampaignName. Max doesn't seem to be working probably because the filter name is different.

Can anyone help me with this? I suspect I will need to do a subquery of some sort

SELECT a.CampaignName, b.Filtername, MAX(calldisconnectedtimeUTC) as MaxTime
FROM I3_IC.ININ_DIALER_40.CallHistory a
Inner Join I3_IC.ININ_DIALER_40.CampaignFilterDefinition b On a.CampaignFilterID = b.CampaignFilterID
Where CampaignName like 'GBR%' and datediff(day,calldisconnectedtimeUTC, getdate()) = 0 and a.CampaignFilterID > 0
GROUP BY CampaignName, b.Filtername
Order By CampaignName

Capture

Thanks in advance

David

2

use

row number partition by [campaign name] order by [max time] desc

then
from the results

choose where row number = 1

3

Thanks, I'm almost there but I've noticed that for the campaign name GBR Collections it's returning me the filter 'GBR - Defaults' instead of 'GBR - Priority Collections' but I'm unsure why because the MaxTime is greater for the 'GBR - Priority Collections' filter.

Have I gone wrong somewhere?

With CTE as
(
SELECT
a.CampaignName, b.Filtername,
row_number() over (Partition By a.CampaignName order by MAX(calldisconnectedtimeUTC)) as RowNumber,
Sum(Case When a.campaignfilterid > 0 Then 1 Else 0 End) as Dials,
MAX(calldisconnectedtimeUTC) as MaxTime
FROM I3_IC.ININ_DIALER_40.CallHistory a
Inner Join I3_IC.ININ_DIALER_40.CampaignFilterDefinition b On a.CampaignFilterID = b.CampaignFilterID
Where CampaignName like 'GBR%' and datediff(day,calldisconnectedtimeUTC, getdate()) = 0 and a.CampaignFilterID > 0
GROUP BY CampaignName, b.Filtername)
--Order By CampaignName)
Select * from cte where RowNumber = 1

Query Results

Query Results

Table

Table

4

you have to take the first CTE without Row Num

add Row Num in the next cte

finally where Row Num =1

; with cte as ( select without Row Num )
, cte_rownum as ( add rownum to cte )
select * from cte_rownum where rownum =1

5

Thanks for your help on this, but how do I add the cte_rownum ? I am new to CTE so apologies.

"cte_rownum as ( add rownum to cte )"

row_number() over (Partition By a.CampaignName order by MAX(calldisconnectedtimeUTC)) as RowNumber

6 
;
WITH cte AS
(
           SELECT     a.campaignname,
                      b.filtername,
                      Sum(
                      CASE
                                 WHEN a.campaignfilterid > 0 THEN 1
                                 ELSE 0
                      END)                         AS dials,
                      Max(calldisconnectedtimeutc) AS maxtime
           FROM       i3_ic.inin_dialer_40.callhistory a
           INNER JOIN i3_ic.inin_dialer_40.campaignfilterdefinition b
           ON         a.campaignfilterid = b.campaignfilterid
           WHERE      campaignname LIKE 'GBR%'
           AND        Datediff(day, calldisconnectedtimeutc, Getdate()) = 0
           AND        a.campaignfilterid > 0
           GROUP BY   campaignname,
                      b.filtername )
, cte_rownum AS
(
         SELECT   Row_number() OVER ( partition BY a.campaignname ORDER BY maxtime DESC ) as rownumber, * FROM cte 
)
SELECT     *
FROM   cte_rownum
WHERE  rownumber = 1
7

Thanks, I'm getting an error on line 21 now, do you know what causing this?

Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'as'.

Line 21

SELECT Row_number() OVER ( partition BY a.campaignname ORDER BY maxtime DESC )) as rownumber, * FROM cte

8

change

SELECT Row_number() OVER ( partition BY a.campaignname ORDER BY maxtime DESC )) as rownumber, * FROM cte

to

SELECT Row_number() OVER ( partition BY a.campaignname ORDER BY maxtime DESC ) as rownumber, * FROM cte

9

Hi I did try this but got the following error

Msg 4104, Level 16, State 1, Line 21
The multi-part identifier "a.campaignname" could not be bound.

WITH cte AS
(
SELECT a.campaignname,
b.filtername,
Sum(
CASE
WHEN a.campaignfilterid > 0 THEN 1
ELSE 0
END) AS dials,
Max(calldisconnectedtimeutc) AS maxtime
FROM i3_ic.inin_dialer_40.callhistory a
INNER JOIN i3_ic.inin_dialer_40.campaignfilterdefinition b
ON a.campaignfilterid = b.campaignfilterid
WHERE campaignname LIKE 'GBR%'
AND Datediff(day, calldisconnectedtimeutc, Getdate()) = 0
AND a.campaignfilterid > 0
GROUP BY campaignname,
b.filtername )
, cte_rownum AS
(
SELECT Row_number() OVER ( partition BY a.campaignname ORDER BY maxtime DESC ) as rownumber, * FROM cte
)
SELECT *
FROM cte_rownum
WHERE rownumber = 1

10

Try this ..

WITH cte AS
(
SELECT a.campaignname,
b.filtername,
Sum(
CASE
WHEN a.campaignfilterid > 0 THEN 1
ELSE 0
END) AS dials,
Max(calldisconnectedtimeutc) AS maxtime
FROM i3_ic.inin_dialer_40.callhistory a
INNER JOIN i3_ic.inin_dialer_40.campaignfilterdefinition b
ON a.campaignfilterid = b.campaignfilterid
WHERE campaignname LIKE 'GBR%'
AND Datediff(day, calldisconnectedtimeutc, Getdate()) = 0
AND a.campaignfilterid > 0
GROUP BY campaignname,
b.filtername )
, cte_rownum AS
(
SELECT Row_number() OVER ( partition BY campaignname ORDER BY maxtime DESC ) as rownumber, * FROM cte
)
SELECT *
FROM cte_rownum
WHERE rownumber = 1

2 Likes
11

That's done it. Many thanks for your support today, its really appreciated,

David