alenan2013 alenan2013 - 5 months ago 7
SQL Question

How can I optimize these T-SQL queries?

I have four sql variables that were calculated in the exact same way, except one condition on each query :

C.CenterID = @CenterID
C.CenterNo IN (SELECT CenterNo from Registry.Vol1_5)
C.CenterNo IN (SELECT CenterNo from Registry.Vol6_10)
C.CenterNo IN (SELECT CenterNo from Registry.Vol11)


My code is here:

DECLARE @MyCenterAllComplications INT;
DECLARE @Total5AllComplications INT;
DECLARE @Total6_10AllComplications INT;
DECLARE @Total11AllComplications INT;


SET @MyCenterAllComplications = (SELECT count(*) FROM ECLS.Runs R

INNER JOIN Registry.Patients P on R.PatientId = P.PatientId
INNER JOIN Registry.CenterPatients CP ON CP.PatientId = P.PatientId
INNER JOIN Registry.Centers C on C.CenterId = CP.CenterId

where 1 = 1

AND NOT R.RunId IN (select RD.RunId from ECLS.RunDetails RD
INNER JOIN ECLS.ModeCodes MC ON RD.Mode = MC.Code AND MC.Abbreviation IN ('AVCO2R', 'VVCO2R')
)
AND C.CenterID = @CenterID
AND R.SupportType = @SupportType
AND NOT R.CompletedBy IS NULL
AND NOT R.TimeOn IS NULL
AND DATEDIFF(year, R.timeOn, GETDATE()) < 10
AND DATEDIFF(day, P.Birthdate, R.timeOn) <= 30
);

SET @Total5AllComplications = (SELECT count(*) FROM ECLS.Runs R

INNER JOIN Registry.Patients P on R.PatientId = P.PatientId
INNER JOIN Registry.CenterPatients CP ON CP.PatientId = P.PatientId
INNER JOIN Registry.Centers C on C.CenterId = CP.CenterId

where 1 = 1

AND NOT R.RunId IN (select RD.RunId from ECLS.RunDetails RD
INNER JOIN ECLS.ModeCodes MC ON RD.Mode = MC.Code AND MC.Abbreviation IN ('AVCO2R', 'VVCO2R')
)
AND C.CenterNo IN (SELECT CenterNo from Registry.Vol1_5)
AND R.SupportType = @SupportType
AND NOT R.CompletedBy IS NULL
AND NOT R.TimeOn IS NULL
AND DATEDIFF(year, R.timeOn, GETDATE()) < 10
AND DATEDIFF(day, P.Birthdate, R.timeOn) <= 30
);

SET @Total6_10AllComplications = (SELECT count(*) FROM ECLS.Runs R

INNER JOIN Registry.Patients P on R.PatientId = P.PatientId
INNER JOIN Registry.CenterPatients CP ON CP.PatientId = P.PatientId
INNER JOIN Registry.Centers C on C.CenterId = CP.CenterId

where 1 = 1

AND NOT R.RunId IN (select RD.RunId from ECLS.RunDetails RD
INNER JOIN ECLS.ModeCodes MC ON RD.Mode = MC.Code AND MC.Abbreviation IN ('AVCO2R', 'VVCO2R')
)
AND C.CenterNo IN (SELECT CenterNo from Registry.Vol6_10)
AND R.SupportType = @SupportType
AND NOT R.CompletedBy IS NULL
AND NOT R.TimeOn IS NULL
AND DATEDIFF(year, R.timeOn, GETDATE()) < 10
AND DATEDIFF(day, P.Birthdate, R.timeOn) <= 30
);

SET @Total11AllComplications = (SELECT count(*) FROM ECLS.Runs R

INNER JOIN Registry.Patients P on R.PatientId = P.PatientId
INNER JOIN Registry.CenterPatients CP ON CP.PatientId = P.PatientId
INNER JOIN Registry.Centers C on C.CenterId = CP.CenterId

where 1 = 1

AND NOT R.RunId IN (select RD.RunId from ECLS.RunDetails RD
INNER JOIN ECLS.ModeCodes MC ON RD.Mode = MC.Code AND MC.Abbreviation IN ('AVCO2R', 'VVCO2R')
)
AND C.CenterNo IN (SELECT CenterNo from Registry.Vol11)
AND R.SupportType = @SupportType
AND NOT R.CompletedBy IS NULL
AND NOT R.TimeOn IS NULL
AND DATEDIFF(year, R.timeOn, GETDATE()) < 10
AND DATEDIFF(day, P.Birthdate, R.timeOn) <= 30
);


How can I calculate all these variables

@MyCenterAllComplications,
@Total5AllComplications,
@Total6_10AllComplications,
@Total11AllComplications


At the same time, by using one query? Thank you in advance for any help.

Answer
SELECT count(cID.ID)
     , count(Registry.Vol1_5.CenterNo)
     , count(Registry.Vol6_10.CenterNo)
     , count(on Registry.Vol11.CenterNo)
FROM ECLS.Runs R
INNER JOIN Registry.Patients P 
        on R.PatientId = P.PatientId
       AND R.SupportType = @SupportType
       AND NOT R.CompletedBy IS NULL
       AND NOT R.TimeOn IS NULL
       AND DATEDIFF(year, R.timeOn, GETDATE()) < 10
       AND DATEDIFF(day, P.Birthdate, R.timeOn) <= 30
       AND NOT R.RunId IN (select RD.RunId 
                           from ECLS.RunDetails RD 
                           INNER JOIN ECLS.ModeCodes MC 
                            ON RD.Mode = MC.Code 
                           AND MC.Abbreviation IN ('AVCO2R', 'VVCO2R')
                          )
INNER JOIN Registry.CenterPatients CP ON CP.PatientId = P.PatientId
INNER JOIN Registry.Centers C on C.CenterId = CP.CenterId 
LEFT JOIN (select @CenterID as ID
          ) cID
             on cID.ID                   = C.CenterNo 
LEFT JOIN Registry.Vol1_5
            on Registry.Vol1_5.CenterNo  = C.CenterNo 
LEFT JOIN Registry.Vol6_10
            on Registry.Vol6_10.CenterNo = C.CenterNo 
LEFT JOIN Registry.Vol11
            on Registry.Vol11.CenterNo   = C.CenterNo 
Comments