Any other way to make the query shorter?

Question

select A.regYear, A.regMonth, isnull(A.regCount,0), isnull(B.regCount,0), isnull(C.regCount,0), isnull(D.regCount,0),
isnull(E.regCount,0), isnull(F.regCount,0),
isnull(G.regCount,0),isnull(H.regCount,0),isnull(I.regCount,0),isnull(J.regCount,0),
isnull(K.regCount,0),isnull(L.regCount,0),isnull(M.regCount,0),isnull(N.regCount,0),
isnull(O.regCount,0),isnull(P.regCount,0),isnull(Q.regCount,0)
from (

select Year(reg_date) as regYear, Month(reg_Date) as RegMonth, count(*) as RegCount 
from dailyregistration
group by Year(reg_date) , Month(reg_Date)) A

left join 

(select Year(reg_date) as regYear, Month(reg_Date) as RegMonth,
count(1) as RegCount from dailyregistration
where datediff(day, reg_date, reg_activationdate) < 0 
group by Year(reg_date) , Month(reg_Date) ) B on A.RegYear = B.regYear and A.RegMonth = B.RegMonth

left join

(select Year(reg_date) as regYear, Month(reg_Date) as RegMonth,
count(1) as RegCount from dailyregistration
where datediff(day, reg_date, reg_activationdate) = 0
group by Year(reg_date) , Month(reg_Date)) C on A.RegYear = C.regYear and A.RegMonth = C.RegMonth

left join

(select Year(reg_date) as regYear, Month(reg_Date) as RegMonth,
count(1) as RegCount from dailyregistration
where datediff(day, reg_date, reg_activationdate) = 1
group by Year(reg_date) , Month(reg_Date)) D on A.RegYear = D.regYear and A.RegMonth = D.RegMonth

left join

(select Year(reg_date) as regYear, Month(reg_Date) as RegMonth,
count(1) as RegCount from dailyregistration
where datediff(day, reg_date, reg_activationdate) = 2
group by Year(reg_date) , Month(reg_Date)) E on A.RegYear = E.regYear and A.RegMonth = E.RegMonth

left join

(select Year(reg_date) as regYear, Month(reg_Date) as RegMonth,
count(1) as RegCount from dailyregistration
where datediff(day, reg_date, reg_activationdate) = 3
group by Year(reg_date) , Month(reg_Date)) F on A.RegYear = F.regYear and A.RegMonth = F.RegMonth

left join

(select Year(reg_date) as regYear, Month(reg_Date) as RegMonth,
count(1) as RegCount from dailyregistration
where datediff(day, reg_date, reg_activationdate) = 4
group by Year(reg_date) , Month(reg_Date)) G on A.RegYear = G.regYear and A.RegMonth = G.RegMonth


left join

(select Year(reg_date) as regYear, Month(reg_Date) as RegMonth,
count(1) as RegCount from dailyregistration
where datediff(day, reg_date, reg_activationdate) = 5
group by Year(reg_date) , Month(reg_Date)) H on A.RegYear = H.regYear and A.RegMonth = H.RegMonth

left join

(select Year(reg_date) as regYear, Month(reg_Date) as RegMonth,
count(1) as RegCount from dailyregistration
where datediff(day, reg_date, reg_activationdate) = 6
group by Year(reg_date) , Month(reg_Date)) I on A.RegYear = I.regYear and A.RegMonth = I.RegMonth

left join

(select Year(reg_date) as regYear, Month(reg_Date) as RegMonth,
count(1) as RegCount from dailyregistration
where datediff(day, reg_date, reg_activationdate) = 7
group by Year(reg_date) , Month(reg_Date)) J on A.RegYear = J.regYear and A.RegMonth = J.RegMonth

left join

(select Year(reg_date) as regYear, Month(reg_Date) as RegMonth,
count(1) as RegCount from dailyregistration
where datediff(day, reg_date, reg_activationdate) = 14
group by Year(reg_date) , Month(reg_Date)) K on A.RegYear = K.regYear and A.RegMonth = K.RegMonth

left join

(select Year(reg_date) as regYear, Month(reg_Date) as RegMonth,
count(1) as RegCount from dailyregistration
where datediff(day, reg_date, reg_activationdate) = 21
group by Year(reg_date) , Month(reg_Date)) L on A.RegYear = L.regYear and A.RegMonth = L.RegMonth

left join

(select Year(reg_date) as regYear, Month(reg_Date) as RegMonth,
count(1) as RegCount from dailyregistration
where datediff(day, reg_date, reg_activationdate) = 28
group by Year(reg_date) , Month(reg_Date)) M on A.RegYear = M.regYear and A.RegMonth = M.RegMonth
left join

(select Year(reg_date) as regYear, Month(reg_Date) as RegMonth,
count(1) as RegCount from dailyregistration
where datediff(day, reg_date, reg_activationdate) > 28
group by Year(reg_date) , Month(reg_Date)) N on A.RegYear = N.regYear and A.RegMonth = N.RegMonth


left join 

(select Year(reg_date) as regYear, Month(reg_Date) as RegMonth, count(*) as RegCount 
from dailyregistration 
where reg_activationdate is not null
group by Year(reg_date) ,Month(reg_Date)) O on A.RegYear = O.regYear and A.RegMonth = O.RegMonth

left join

(select Year(reg_date) as regYear, Month(reg_Date) as RegMonth, count(*) as RegCount 
from dailyregistration 
where reg_activationdate is null
group by Year(reg_date) ,Month(reg_Date)) P on A.RegYear = P.regYear and A.RegMonth = P.RegMonth

left join 

(select Year(prt_completed) as regYear, Month(prt_completed) as RegMonth, count(*) as RegCount 
from dailyPortOut 
where prt_status = 'COMPLETED'
group by Year(prt_completed) ,Month(prt_completed)) Q on A.RegYear = Q.regYear and A.RegMonth = Q.RegMonth 

order by A.regYear, A.regMonth

Answer 1

This is a prime example for Common Table Expression use:

WITH daily_reg AS (
  SELECT YEAR(dr.reg_date) as regYear, 
         MONTH(dr.reg_Date) as RegMonth, 
         COUNT(*) as RegCount 
    FROM DAILYREGISTRATION dr
GROUP BY YEAR(dr.reg_date) , MONTH(dr.reg_Date)),
   daily_portout AS (
  SELECT YEAR(d.reg_date) as regYear, 
         MONTH(d.reg_Date) as RegMonth, 
         COUNT(*) as RegCount 
    FROM DAILYPORTOUT d
   WHERE d.prt_status = 'COMPLETED'
GROUP BY YEAR(d.prt_completed), MONTH(d.prt_completed))
   SELECT a.regyear, 
          a.regmonth, 
          ISNULL(a.regCount,0), 
          ISNULL(b.regCount,0), 
          ISNULL(d.regCount,0), 
          ISNULL(d.regCount,0),
          ISNULL(e.regCount,0), 
          ISNULL(f.regCount,0),
          ISNULL(g.regCount,0),
          ISNULL(h.regCount,0),
          ISNULL(i.regCount,0),
          ISNULL(j.regCount,0),
          ISNULL(k.regCount,0),
          ISNULL(l.regCount,0),
          ISNULL(m.regCount,0),
          ISNULL(n.regCount,0),
          ISNULL(o.regCount,0),
          ISNULL(p.regCount,0),
          ISNULL(q.regCount,0)
     FROM daily_reg a
LEFT JOIN daily_reg b ON b.regyear = a.regyear 
                     AND b.regmonth = a.regmonth
LEFT JOIN daily_reg c ON c.regyear = a.regyear 
                     AND c.regmonth = a.regmonth
LEFT JOIN daily_reg d ON d.regyear = a.regyear 
                     AND d.regmonth = a.regmonth
LEFT JOIN daily_reg e ON e.regyear = a.regyear 
                     AND e.regmonth = a.regmonth
LEFT JOIN daily_reg f ON f.regyear = a.regyear 
                     AND f.regmonth = a.regmonth
LEFT JOIN daily_reg g ON g.regyear = a.regyear 
                     AND g.regmonth = a.regmonth
LEFT JOIN daily_reg h ON h.regyear = a.regyear 
                     AND h.regmonth = a.regmonth
LEFT JOIN daily_reg i ON i.regyear = a.regyear 
                     AND i.regmonth = a.regmonth
LEFT JOIN daily_reg j ON j.regyear = a.regyear 
                     AND j.regmonth = a.regmonth
LEFT JOIN daily_reg k ON k.regyear = a.regyear 
                     AND k.regmonth = a.regmonth
LEFT JOIN daily_reg l ON l.regyear = a.regyear 
                     AND l.regmonth = a.regmonth
LEFT JOIN daily_reg m ON m.regyear = a.regyear 
                     AND m.regmonth = a.regmonth
LEFT JOIN daily_reg n ON n.regyear = a.regyear 
                     AND n.regmonth = a.regmonth
LEFT JOIN daily_reg o ON o.regyear = a.regyear 
                     AND o.regmonth = a.regmonth
LEFT JOIN daily_reg p ON p.regyear = a.regyear 
                     AND p.regmonth = a.regmonth
LEFT JOIN daily_portout dp ON dp.regyear = a.regyear
                          AND dp.regmonth = a.regmonth
    WHERE DATEDIFF(day, b.reg_date, b.reg_activationdate) < 0 
      AND DATEDIFF(day, c.reg_date, c.reg_activationdate) = 0
      AND DATEDIFF(day, d.reg_date, d.reg_activationdate) = 1
      AND DATEDIFF(day, e.reg_date, e.reg_activationdate) = 2
      AND DATEDIFF(day, f.reg_date, f.reg_activationdate) = 3
      AND DATEDIFF(day, g.reg_date, g.reg_activationdate) = 4
      AND DATEDIFF(day, h.reg_date, h.reg_activationdate) = 5
      AND DATEDIFF(day, i.reg_date, i.reg_activationdate) = 6
      AND DATEDIFF(day, j.reg_date, j.reg_activationdate) = 7
      AND DATEDIFF(day, k.reg_date, k.reg_activationdate) = 14
      AND DATEDIFF(day, l.reg_date, l.reg_activationdate) = 21
      AND DATEDIFF(day, m.reg_date, m.reg_activationdate) = 28
      AND DATEDIFF(day, n.reg_date, n.reg_activationdate) > 28
      AND o.reg_activationdate IS NOT NULL
      AND p.reg_activationdate IS NULL
 ORDER BY a.regyear, a.regmonth