Выгрузка из Медиалога (стр. 5 )

UNION ALL

SELECT COUNT (DISTINCT        PacientVisit. Polis)

FROM PacientVisit

               INNER JOIN ReestrMedOrgDb ON PacientVisit. ReestrMedOrgID = ReestrMedOrgDb. ID

WHERE

((OsobSluch = 1) OR (OsobSluch = 22)) AND (PacientVisit. HelpEnd >= '01.01.2015' AND PacientVisit. HelpEnd <= '31.12.2015') AND ReestrMedOrgDb. NameWithId LIKE 'Реестр%' AND datediff(year, PacientVisit. DR,'31.12.2015') IN (21, 24, 27, 30, 33, 36, 78, 84, 90, 96) AND PacientVisit. W = '2'

UNION ALL

SELECT COUNT (DISTINCT        PacientVisit. Polis)

FROM PacientVisit

               INNER JOIN ReestrMedOrgDb ON PacientVisit. ReestrMedOrgID = ReestrMedOrgDb. ID

WHERE

((OsobSluch = 1) OR (OsobSluch = 22)) AND (PacientVisit. HelpEnd >= '01.01.2015' AND PacientVisit. HelpEnd <= '31.12.2015') AND ReestrMedOrgDb. NameWithId LIKE 'Реестр%' AND datediff(year, PacientVisit. DR,'31.12.2015') IN (42, 72) AND PacientVisit. W = '2'

UNION ALL

SELECT COUNT (DISTINCT        PacientVisit. Polis)

FROM PacientVisit

               INNER JOIN ReestrMedOrgDb ON PacientVisit. ReestrMedOrgID = ReestrMedOrgDb. ID

WHERE

((OsobSluch = 1) OR (OsobSluch = 22)) AND (PacientVisit. HelpEnd >= '01.01.2015' AND PacientVisit. HelpEnd <= '31.12.2015') AND ReestrMedOrgDb. NameWithId LIKE 'Реестр%' AND datediff(year, PacientVisit. DR,'31.12.2015') IN (48, 54, 60, 66, 81, 87, 93, 99) AND PacientVisit. W = '2'

UNION ALL

SELECT COUNT (DISTINCT        PacientVisit. Polis)

FROM PacientVisit

               INNER JOIN ReestrMedOrgDb ON PacientVisit. ReestrMedOrgID = ReestrMedOrgDb. ID

WHERE

((OsobSluch = 1) OR (OsobSluch = 22)) AND (PacientVisit. HelpEnd >= '01.01.2015' AND PacientVisit. HelpEnd <= '31.12.2015') AND ReestrMedOrgDb. NameWithId LIKE 'Реестр%' AND datediff(year, PacientVisit. DR,'31.12.2015') IN (39, 45, 69, 75) AND PacientVisit. W = '2'

UNION ALL

SELECT COUNT (DISTINCT        PacientVisit. Polis)

FROM PacientVisit

               INNER JOIN ReestrMedOrgDb ON PacientVisit. ReestrMedOrgID = ReestrMedOrgDb. ID

WHERE

((OsobSluch = 1) OR (OsobSluch = 22)) AND (PacientVisit. HelpEnd >= '01.01.2015' AND PacientVisit. HelpEnd <= '31.12.2015') AND ReestrMedOrgDb. NameWithId LIKE 'Реестр%' AND datediff(year, PacientVisit. DR,'31.12.2015') IN (51, 57, 63) AND PacientVisit. W = '2'

4) Терпаветы

SELECT DoctorBd. FioWithId AS Фамилия,

sum (case when ReestrMedOrgID = '45' then 1 else 0 end) AS Январь,

sum (case when ReestrMedOrgID = '50' then 1 else 0 end) AS Февраль,

sum (case when ReestrMedOrgID = '54' then 1 else 0 end) AS Март,

sum (case when ReestrMedOrgID = '59' then 1 else 0 end) AS Апрель,

sum (case when ReestrMedOrgID = '61' then 1 else 0 end) AS Май,

sum (case when ReestrMedOrgID = '65' then 1 else 0 end) AS Июнь,

sum (case when ReestrMedOrgID = '69' then 1 else 0 end) AS Июль,

sum (case when ReestrMedOrgID = '71' then 1 else 0 end) AS Август,

sum (case when ReestrMedOrgID = '74' then 1 else 0 end) AS Сентябрь,

sum (case when ReestrMedOrgID = '76' then 1 else 0 end) AS Октябрь,

sum (case when ReestrMedOrgID = '78' then 1 else 0 end) AS Ноябрь

FROM PacientVisit

  INNER JOIN DoctorBd ON PacientVisit. Doctor = DoctorBd. id

WHERE

(DoctorBd. FioWithId LIKE '99  %'  AND ((OsobSluch = 1) OR (OsobSluch = 22) OR (OsobSluch = 2) OR (OsobSluch = 23))) OR

(DoctorBd. FioWithId LIKE '207  %'  AND ((OsobSluch = 1) OR (OsobSluch = 22) OR (OsobSluch = 2) OR (OsobSluch = 23))) OR

(DoctorBd. FioWithId LIKE '21  %'  AND ((OsobSluch = 1) OR (OsobSluch = 22) OR (OsobSluch = 2) OR (OsobSluch = 23))) OR

(DoctorBd. FioWithId LIKE '38  %'  AND ((OsobSluch = 1) OR (OsobSluch = 22) OR (OsobSluch = 2) OR (OsobSluch = 23))) OR

(DoctorBd. FioWithId LIKE '83  %'  AND ((OsobSluch = 1) OR (OsobSluch = 22) OR (OsobSluch = 2) OR (OsobSluch = 23))) OR

(DoctorBd. FioWithId LIKE '35  %'  AND ((OsobSluch = 1) OR (OsobSluch = 22) OR (OsobSluch = 2) OR (OsobSluch = 23))) OR

(DoctorBd. FioWithId LIKE '315  %'  AND ((OsobSluch = 1) OR (OsobSluch = 22) OR (OsobSluch = 2) OR (OsobSluch = 23))) OR

(DoctorBd. FioWithId LIKE '321  %'  AND ((OsobSluch = 1) OR (OsobSluch = 22) OR (OsobSluch = 2) OR (OsobSluch = 23))) OR

(DoctorBd. FioWithId LIKE '72  %'  AND ((OsobSluch = 1) OR (OsobSluch = 22) OR (OsobSluch = 2) OR (OsobSluch = 23))) OR

(DoctorBd. FioWithId LIKE '205  %'  AND ((OsobSluch = 1) OR (OsobSluch = 22) OR (OsobSluch = 2) OR (OsobSluch = 23))) OR

(DoctorBd. FioWithId LIKE '373  %'  AND ((OsobSluch = 1) OR (OsobSluch = 22) OR (OsobSluch = 2) OR (OsobSluch = 23))) OR

(DoctorBd. FioWithId LIKE '377  %'  AND ((OsobSluch = 1) OR (OsobSluch = 22) OR (OsobSluch = 2) OR (OsobSluch = 23))) OR

(DoctorBd. FioWithId LIKE '378  %'  AND ((OsobSluch = 1) OR (OsobSluch = 22) OR (OsobSluch = 2) OR (OsobSluch = 23))) OR

(DoctorBd. FioWithId LIKE '387  %'  AND ((OsobSluch = 1) OR (OsobSluch = 22) OR (OsobSluch = 2) OR (OsobSluch = 23))) OR

(DoctorBd. FioWithId LIKE '381  %'  AND ((OsobSluch = 1) OR (OsobSluch = 22) OR (OsobSluch = 2) OR (OsobSluch = 23)))

GROUP BY DoctorBd. FioWithId;

Исправляем ошибку «некорректное выставление признака детского профиля (не заполнено поле)

update dbo. PacientVisit

set PacientVisit. DetProfil = 1

WHERE PacientVisit. ReestrMedOrgID = 106

AND datediff(year, PacientVisit. DR, PacientVisit. HelpEnd) < 18

AND PacientVisit. DetProfil is null

Затем

update dbo. PacientVisit

set PacientVisit. DetProfil = 0

WHERE PacientVisit. ReestrMedOrgID = 106

AND PacientVisit. DetProfil is null