Skip to content

Instantly share code, notes, and snippets.

@zv0r

zv0r/Inquiry-report-to-upda

Created March 2, 2015 17:33
Show Gist options
  • Save zv0r/df00575307e3597326df to your computer and use it in GitHub Desktop.
Save zv0r/df00575307e3597326df to your computer and use it in GitHub Desktop.
Download ZIP
Отчет по обращениям граждан
Raw
Inquiry-report-to-upda
SELECT
(SELECT COUNT(*) FROM inquiries2015 inq INNER JOIN v_status st ON st.id = inq.status_id WHERE st.legal = 0 AND inq.reg_date >= '2015-02-01' AND inq.reg_date <= '2015-02-28') AS 'С4', -- всего физлиц
(SELECT COUNT(*) FROM inquiries2015 inq INNER JOIN v_status st ON st.id = inq.status_id WHERE st.legal = 1 AND inq.reg_date >= '2015-02-01' AND inq.reg_date <= '2015-02-28') AS 'D4', -- всего юрлиц
(SELECT COUNT(*) FROM inquiries2015 inq WHERE inq.reg_date >= '2015-02-01' AND inq.reg_date <= '2015-02-28') AS 'E4', -- всего
(SELECT COUNT(*) FROM inquiries2015 inq INNER JOIN v_status st ON st.id = inq.status_id WHERE st.legal = 0 AND (inq.source_id = 1 OR inq.source_id = 6) AND inq.reg_date >= '2015-02-01' AND inq.reg_date <= '2015-02-28') AS 'С5', -- физлиц очно
(SELECT COUNT(*) FROM inquiries2015 inq INNER JOIN v_status st ON st.id = inq.status_id WHERE st.legal = 1 AND (inq.source_id = 1 OR inq.source_id = 6) AND inq.reg_date >= '2015-02-01' AND inq.reg_date <= '2015-02-28') AS 'D5', -- юрлиц очно
(SELECT COUNT(*) FROM inquiries2015 inq WHERE (inq.source_id = 1 OR inq.source_id = 6) AND inq.reg_date >= '2015-02-01' AND inq.reg_date <= '2015-02-28') AS 'E5', -- всего очно
(SELECT COUNT(*) FROM inquiries2015 inq INNER JOIN v_status st ON st.id = inq.status_id WHERE st.legal = 0 AND inq.source_id = 8 AND inq.reg_date >= '2015-02-01' AND inq.reg_date <= '2015-02-28') AS 'С6', -- физлиц ЕПГУ
(SELECT COUNT(*) FROM inquiries2015 inq INNER JOIN v_status st ON st.id = inq.status_id WHERE st.legal = 1 AND inq.source_id = 8 AND inq.reg_date >= '2015-02-01' AND inq.reg_date <= '2015-02-28') AS 'D6', -- юрлиц ЕПГУ
(SELECT COUNT(*) FROM inquiries2015 inq WHERE inq.source_id = 8 AND inq.reg_date >= '2015-02-01' AND inq.reg_date <= '2015-02-28') AS 'E6', -- всего ЕПГУ
(SELECT COUNT(*) FROM inquiries2015 inq INNER JOIN v_status st ON st.id = inq.status_id WHERE st.legal = 0 AND (inq.source_id = 3 OR inq.source_id = 5 OR inq.source_id = 7) AND inq.reg_date >= '2015-02-01' AND inq.reg_date <= '2015-02-28') AS 'С7', -- физлиц эл. вид
(SELECT COUNT(*) FROM inquiries2015 inq INNER JOIN v_status st ON st.id = inq.status_id WHERE st.legal = 1 AND (inq.source_id = 3 OR inq.source_id = 5 OR inq.source_id = 7) AND inq.reg_date >= '2015-02-01' AND inq.reg_date <= '2015-02-28') AS 'D7', -- юрлиц эл. вид
(SELECT COUNT(*) FROM inquiries2015 inq WHERE (inq.source_id = 3 OR inq.source_id = 5 OR inq.source_id = 7) AND inq.reg_date >= '2015-02-01' AND inq.reg_date <= '2015-02-28') AS 'E7', -- всего эл. вид
(SELECT COUNT(*) FROM inquiries2015 inq INNER JOIN v_status st ON st.id = inq.status_id WHERE st.legal = 0 AND (inq.source_id = 2 OR inq.source_id = 4) AND inq.reg_date >= '2015-02-01' AND inq.reg_date <= '2015-02-28') AS 'С8', -- физлиц почта
(SELECT COUNT(*) FROM inquiries2015 inq INNER JOIN v_status st ON st.id = inq.status_id WHERE st.legal = 1 AND (inq.source_id = 2 OR inq.source_id = 4) AND inq.reg_date >= '2015-02-01' AND inq.reg_date <= '2015-02-28') AS 'D9', -- юрлиц почта
(SELECT COUNT(*) FROM inquiries2015 inq WHERE (inq.source_id = 2 OR inq.source_id = 4) AND inq.reg_date >= '2015-02-01' AND inq.reg_date <= '2015-02-28') AS 'E9' -- всего почта
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment