CODEWITHSUNDEEP
sql
Aaron
Aaron

Reputation: 1455

Combine queries with different where clause conditions

How do I combine these queries, so I can use dayCall from the second query in the first one?

First query

select clientid, 
DATEDIFF(
        DAY,
        CONVERT(datetime, convert(varchar(10), min(calendar))),
        MIN(dayCall)     
        )
from
Stats 
group by clientid

Second query

SELECT  
    clientid, 
    MIN(calendar)    
    as dayCall
FROM (
    SELECT 
        clientid, 
        CONVERT(datetime, convert(varchar(10), calendar)) calendar,
        lead(calls) over (partition by clientid order by calendar) as total_1,
        lead(calls, 2) over (partition by clientid order by calendar) as total_2
    FROM STATS

) a
where calls = 0 and total_1 = 0 and total_2 = 0
GROUP BY clientid

Upvotes: 0

Views: 61

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Use conditional aggregation:

select clientid, 
       min(case when calls = 0 and total_1 = 0 and total_2 = 0 then calendar end) as dayCall,
       datediff(day
                convert(datetime, convert(varchar(10), min(calendar))),
                min(dayCall)     
               )
from (select s.*, 
             convert(datetime, convert(varchar(10), calendar)) calendar,
                     lead(calls) over (partition by clientid order by calendar) as total_1,
                     lead(calls, 2) over (partition by clientid order by calendar) as total_2
      from STATS s
     ) s
group by clientid

Upvotes: 0

Icculus018
Icculus018

Reputation: 1066

You can join the tables together. I see clientID is common in both, but should do full outer join to make sure you get all results even if there is no common clientID between the two tables:

SELECT * FROM 
(
select clientid, 
DATEDIFF(
        DAY,
        CONVERT(datetime, convert(varchar(10), min(calendar))),
        MIN(dayCall)     
        )
from
Stats 
group by clientid
) tableA
FULL OUTER JOIN 
(
SELECT  
    clientid, 
    MIN(calendar)    
    as dayCall
FROM (
    SELECT 
        clientid, 
        CONVERT(datetime, convert(varchar(10), calendar)) calendar,
        lead(calls) over (partition by clientid order by calendar) as total_1,
        lead(calls, 2) over (partition by clientid order by calendar) as total_2
    FROM STATS

) a
where calls = 0 and total_1 = 0 and total_2 = 0
GROUP BY clientid
) tableB
ON tableA.clientid = tableB.clientid

Upvotes: 3

Related Questions