Multiple CTE's and group by

Question

I am trying to calculate the difference between 2 dates using the below query

with cte (clientid, calls)
as
(
select clientid, max(isnull(cast(calls as int),0)) as calls from STATS
where clientid != 0
group by CLIENTID), 
cteb (minCal, calls, clientid) 
as
(
select calendar as minCal, (cast(calls as int)) as calls, CLIENTID from STATS
)
select min(CONVERT(datetime, convert(varchar(10), minCalendar))) as dayHighCall, cte.clientid, max(cte.calls) as highestCall from cte
join cteb 
on cteb.clientid = cte.clientid
where cteb.tot = cte.totalcalls
group by cte.clientid)

It returns this output

dayHighCall             clientid    highestCall
2017-11-27 00:00:00.000 2200        2
2017-11-17 00:00:00.000 2500        65

I wanted it to return another date, so I wrote this as an extension to the above query -

cted as
(
select min(CONVERT(datetime, convert(varchar(10), CALENDAR))) as mincal from STATS
group by CLIENTID
)

Now I want my result with the below query -

select cte.clientid,
    DATEDIFF(day,
    ctec.dayHighCall,
    mincal)
    as datedifference
 from cte, ctec, cted
 group by cte.clientid

This throws an error on ctec.dayHighCall and mincal because they are neither aggregates or part of a sub query. How do I modify this query, so I can get clientid, datediff and calls as a result.

Answer 1

If you are using SQL Server 2012 or Newer you don't need a CTE, you can solve this with FIRST_VALUE function using the proper partitions

SELECT DISTINCT 
    ClientID, 
    FIRST_VALUE(Calendar) OVER (PARTITION BY ClientID ORDER BY Calls DESC) AS dayHighCall, 
    MAX(Calls) OVER (PARTITION BY ClientID) AS highestCall,
    DATEDIFF(DAY, MIN(Calendar) OVER (), FIRST_VALUE(Calendar) OVER (PARTITION BY ClientID ORDER BY Calls DESC)) AS datedifference
FROM STATS
ORDER BY ClientID

If you are using an older SQL Server version, you can use CTEs and CROSS APPLY

;WITH CTE AS (
    SELECT DISTINCT ClientID, MAX(Calls) OVER (Partition BY ClientID) AS Calls, MIN(Calendar) OVER () AS minCalendar
    FROM STATS
),
SELECT 
    ClientID, 
    dayHighCall.Calendar AS dayHighCall,
    Calls AS highestCall,
    DATEDIFF(day, minCalendar, dayHighCall.Calendar) datedifference
FROM CTE
CROSS APPLY (
    SELECT TOP 1 Calendar FROM STATS S WHERE S.ClientID = CTE.ClientID AND S.Calls = CTE.Calls 
) dayHighCall
ORDER BY ClientID

PS. I am assuming that your minCal is for the whole table, if you also need it for ClientID, you can add the Partition By into any of both Over() clauses

Answer 2

I guess you have the STATS table with at most one row for each client (CLIENTID) and date (calendar).

I had to re-write your code with clearer names and comments (fixing the mismatching names, which made the query invalid, in the process) to understand what you were trying to do:

with daily_stats as (
  select clientid
       , isnull(cast(calls as int),0) as num_calls
       -- whatever the format of "calendar" is, you made sure this converted it to datetime properly
       , convert(datetime, convert(varchar(10), calendar)) as date
  from STATS
  where clientid != 0
)
, max_calls_for_client as ( -- this was "cte"
  select clientid, max(calls) as max_daily_calls from daily_stats group by clientid
)
, date_with_highest_calls as ( -- this was "ctec", presumably - the query for which you provided an example result
  select a.clientid
       , min(b.date) as dayHighCall -- the first day with the highest number of calls
       , max(a.max_daily_calls) as max_daily_calls
  from max_calls_for_client a
  join daily_stats b
    on a.clientid = b.clientid
   and a.max_daily_calls = b.num_calls -- get the date(s) with the maximum number of calls
  group by a.clientid
)
, first_call_date as ( -- "cted"
  select clientid, min(date) as min_call_date from daily_stats group by clientid
)
select max_calls_for_client.clientid
     , DATEDIFF(day,
                date_with_highest_calls.dayHighCall,
                first_call_date.min_call_Date) as datedifference
from max_calls_for_client
   , date_with_highest_calls
   , first_call_date
group by max_calls_for_client.clientid

If I guessed correctly, your problem with the last bit is easily solved by joining on the client ID: you have three "tables", each grouped by client ID, which you need to combine, so this should work:

from max_calls_for_client a
join date_with_highest_calls b on a.clientid = b.clientid
join first_call_date c on a.clientid = c.clientid

Also note that you can do this in two steps instead of four:

1. You can merge first_call_date and max_calls_for_client into one (let's call it client_stats), since both are simple aggregations on clientid.

2. You can then calculate the number of days in the same query where you find the date with the highest number of calls:

   select c.clientid
        , min(d.date) as dayHighCall -- the first day with the highest number of calls
        -- aggregations are no-op here, as `client_stats` has a single value for each clientid
        , max(c.min_call_date) as max_daily_calls
        , max(c.max_daily_calls) as max_daily_calls
        , DATEDIFF(day,
                   min(d.date), -- the first day with the highest number of calls
                   max(c.min_call_date)
                  ) as datedifference
   
   from client_stats c
   join daily_stats d
     on c.clientid = d.clientid
    and c.max_daily_calls = d.num_calls
   group by c.clientid