Скрипт выгрузки льготников (чернобыльцев), получивших стационарную помощь

Материал из Wiki МИАЦ ВО
Перейти к навигации Перейти к поиску
"\\TS\MySQL\bin\mysql.exe" --host=10.35.0.93 --default-character-set cp1251 --character-sets-dir "\\TS\MySQL\bin\share\charsets " --database  DB-uUser -pPasswd < "C:\client\my_scripts\Talanova\1kv2015\stac_1_kv2015_norm.sql" >stac_31-03-2015n.res
SELECT people.UNICOD, people.birthDate,tmp.id,CASE WHEN tmp.numberPolis <> '' THEN tmp.numberPolis ELSE 'Не найдено'  END AS Полис,
  people_document.NAME, office.name AS 'ЛПУ', privelege.name AS 'Наименование льготы',  privelege.codOut,  privelege.cod,
  dynpeople.sDate AS 'Дата начала лечения',
  dynpeople.eDate  AS 'Дата окончания лечения', dynpeople.idlpu AS IDLPU,  dynpeople.codDS
FROM dynpeople
  LEFT OUTER JOIN (SELECT
      people_polis_1.numberPolis,
      people_polis_1.sDatePolis,
      people_polis_1.eDatePolis,
      people_polis_1.id,
      people_polis_1.idlpu
    FROM people_polis people_polis_1) tmp
    ON tmp.id = dynpeople.idPeople AND tmp.idlpu = dynpeople.idlpu AND tmp.sDatePolis < dynpeople.sDate AND tmp.eDatePolis >= dynpeople.sDate
  RIGHT OUTER JOIN people_lgot
    ON people_lgot.id = tmp.id AND people_lgot.idlpu = tmp.idlpu
  LEFT OUTER JOIN privelege
    ON people_lgot.idLgot = privelege.unicod
  RIGHT OUTER JOIN people
    ON people_lgot.idlpu = people.idlpu AND people_lgot.id = people.unicod AND dynpeople.idPeople = people.unicod AND dynpeople.idlpu = people.idlpu
  LEFT OUTER JOIN people_document
    ON people.idlpu = people_document.idlpu AND people.unicod = people_document.id
LEFT OUTER JOIN office
  ON office.idlpu=tmp.idlpu AND office.statusLPU=1
WHERE (dynpeople.NHist IS NOT NULL) AND privelege.codOut = '010' AND (year(dynpeople.edate) = 2015) AND (month(dynpeople.edate) < 4)
ORDER BY people.UNICOD;