2

I have two queries in SQL Server 2016 that produce summarized data and would like to combine them into one query that with both sets of results. The first brings in data for a specific election_period, the second returns data for the current month. I'd like to get a single result set that contains both election_period and Current Month.

    I've tried Union to put the two queries together however there could 
    be duplicate members and I would like to count each member one time.

I have tried the union statement however since I want the results in separate columns this method does not work.

    Query 1
select d.note  
      ,isnull(count(distinct d.member_id),0) as '2019 EOY'  
from diary_tab d (nolock)  
left join member_tab m (nolock)  
     on m.member_id = d.member_id  
where year(m.effective_dt) = '2019'  
  and m.election_period = 'EOY'  
group by d.note  

    Query 2  
select d.note  
      ,isnull(count(distinct d.member_id),0) as 'Current MTD'  
from diary_tab d (nolock)  
group by d.note  



    currently I get these results:  
    note      EOY  
    -----     ----  
    Note1       20  
    Note2        5  

    Note      MTD  
    -----     -----  
    Note1       6  
    Note2       2  

    I would like to get this:  

    Note      EOY      MTD  
    -----     ----     -----  
    Note1       20       6  
    Note2        5       2
TT.
  • 15,774
  • 6
  • 47
  • 88
JoeB
  • 21
  • 2

1 Answers1

0

You could use conditional aggregation:

select d.note  
  ,[2019 EOY] = isnull(count(distinct CASE WHEN year(m.effective_dt) = 2019 AND m.election_period = 'EOY' THEN d.member_id END),0)
  ,[Current MTD] = isnull(count(distinct d.member_id),0)
from diary_tab d
left join member_tab m 
  on m.member_id = d.member_id  
group by d.note;

(NOLOCK) is not a turbo boost button and could lead to false results.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • Thank you Lukas Szozda. This is the solution I've been looking for! – JoeB Aug 08 '19 at 17:30
  • @JoeB Great to hear that. Please consider [accepting the answer](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) – Lukasz Szozda Aug 08 '19 at 19:48