Any other way to make the query shorter? [closed]

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center. Closed 10 years ago.

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

---

Looks like you're creating a reporting table that does three things. 1: compares registration to activation by number of days from 0 to 7 (not sure why datediff would be less than 0, but maybe as an error check?), within 14 days, 21 days, 28 days, and 28 or more. 2: reports the total number of registrations that activate or doesn't activate, and 3: ... not sure what subquery Q is supposed to be reporting by "port out date".

Why not stage your data first, a la:

create table #t 
( regYear smallint, regMonth smallint
, reg_activationDate dateTime, dayDiff smallint 
)
insert #t select year( reg_date ), month( reg_date )
, reg_activationDate, datediff( day, reg_date, reg_activationDate )
from dailyregistration

Then do a much simpler crosstab query:

select regYear, regMonth, sum( case when dayDiff < 0 then 1 else 0 end ) [lt 0]
, sum ( case when dayDiff = 0 then 1 else 0 end ) [eq 0]
, ...
, sum( case when dayDiff > 28 then 1 else 0 end ) [gt 28]
, sum( case when reg_activationDate is not null then 1 else 0 end ) [Total Activated]
, sum( case when reg_activationDate is null then 1 else 0 end ) [Total Not Activated]
from #t
group by regYear, regMonth

Still not sure about the final condition.

---

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