--fer

SELECT *
FROM (
	SELECT value AS region
	FROM cmn_setting_value
	WHERE setting_id = 'cz.atria.common.settings.impl.ProductOwnerInfoSettings.regionCode'
	) AS REGION
	,(
		SELECT count(DISTINCT (org_id)) AS CLINICS
		FROM sr_res_group srg
		JOIN fer_se_res_group fsrg ON fsrg.id = srg.id
		WHERE srg.is_system = false
		) AS "CLINICS"
	,(
		SELECT count(*) AS RESGROUPS
		FROM fer_se_res_group
		) AS "RESGROUPS"
	,(
		SELECT count(*) AS APP_total
		FROM md_appointment a
		JOIN sr_srv_rendered ssr ON ssr.id = a.srv_rendered_id
		JOIN fer_se_res_group fg ON fg.id = ssr.res_group_id
		JOIN sr_session_ticket st ON st.ticket_id = a.id
		WHERE a.state_id NOT IN (
				1
				,4
				)
			AND NOT EXISTS (
				SELECT 1
				FROM sr_session_source_na na
				WHERE na.session_id = st.session_id
					AND na.source_id = 2
				)
			AND a.bdatetime BETWEEN '2015-01-01'
				AND CURRENT_DATE
		) AS APP_total
	,(
		SELECT count(*) AS APP_01
		FROM md_appointment a
		JOIN sr_srv_rendered ssr ON ssr.id = a.srv_rendered_id
		JOIN fer_se_res_group fg ON fg.id = ssr.res_group_id
		JOIN sr_session_ticket st ON st.ticket_id = a.id
		WHERE a.state_id NOT IN (
				1
				,4
				)
			AND NOT EXISTS (
				SELECT 1
				FROM sr_session_source_na na
				WHERE na.session_id = st.session_id
					AND na.source_id = 2
				)
			AND a.bdatetime BETWEEN '2015-01-01'
				AND '2015-01-31'
		) AS APP_01
	,(
		SELECT count(*) AS APP_02
		FROM md_appointment a
		JOIN sr_srv_rendered ssr ON ssr.id = a.srv_rendered_id
		JOIN fer_se_res_group fg ON fg.id = ssr.res_group_id
		JOIN sr_session_ticket st ON st.ticket_id = a.id
		WHERE a.state_id NOT IN (
				1
				,4
				)
			AND NOT EXISTS (
				SELECT 1
				FROM sr_session_source_na na
				WHERE na.session_id = st.session_id
					AND na.source_id = 2
				)
			AND a.bdatetime BETWEEN '2015-02-01'
				AND '2015-02-28'
		) AS APP_02
	,(
		SELECT count(*) APP_03
		FROM md_appointment a
		JOIN sr_srv_rendered ssr ON ssr.id = a.srv_rendered_id
		JOIN fer_se_res_group fg ON fg.id = ssr.res_group_id
		JOIN sr_session_ticket st ON st.ticket_id = a.id
		WHERE a.state_id NOT IN (
				1
				,4
				)
			AND NOT EXISTS (
				SELECT 1
				FROM sr_session_source_na na
				WHERE na.session_id = st.session_id
					AND na.source_id = 2
				)
			AND a.bdatetime BETWEEN '2015-03-01'
				AND '2015-03-31'
		) AS APP_03
	,(
		SELECT count(*) APP_04
		FROM md_appointment a
		JOIN sr_srv_rendered ssr ON ssr.id = a.srv_rendered_id
		JOIN fer_se_res_group fg ON fg.id = ssr.res_group_id
		JOIN sr_session_ticket st ON st.ticket_id = a.id
		WHERE a.state_id NOT IN (
				1
				,4
				)
			AND NOT EXISTS (
				SELECT 1
				FROM sr_session_source_na na
				WHERE na.session_id = st.session_id
					AND na.source_id = 2
				)
			AND a.bdatetime BETWEEN '2015-04-01'
				AND '2015-04-30'
		) AS APP_04
	,(
		SELECT count(*) APP_05
		FROM md_appointment a
		JOIN sr_srv_rendered ssr ON ssr.id = a.srv_rendered_id
		JOIN fer_se_res_group fg ON fg.id = ssr.res_group_id
		JOIN sr_session_ticket st ON st.ticket_id = a.id
		WHERE a.state_id NOT IN (
				1
				,4
				)
			AND NOT EXISTS (
				SELECT 1
				FROM sr_session_source_na na
				WHERE na.session_id = st.session_id
					AND na.source_id = 2
				)
			AND a.bdatetime BETWEEN '2015-05-01'
				AND '2015-05-31'
		) AS APP_05;

















--iehr


SELECT *
FROM (
	SELECT value AS region
	FROM cmn_setting_value
	WHERE setting_id = 'cz.atria.common.settings.impl.ProductOwnerInfoSettings.regionCode'
	) AS REGION
	,(
		SELECT count(*) AS rmis_cases_2014
		FROM mc_case
		WHERE create_date BETWEEN '2014-01-01'
				AND '2014-12-31'
		) AS rmis_cases_2014
	,(
		SELECT count(*) AS rmis_cases_2015
		FROM mc_case
		WHERE create_date BETWEEN '2015-01-01'
				AND CURRENT_DATE
		) AS rmis_cases_2015
	,(
		SELECT count(*) AS iehr_cases_total
		FROM iehr_cda
		) AS iehr_cases_total
	,(
		SELECT count(*) AS iehr_cases_mar
		FROM iehr_cda
		WHERE creation_time BETWEEN '2015-03-01'
				AND '2015-03-31'
		) AS iehr_cases_mar
	,(
		SELECT count(*) AS iehr_cases_apr
		FROM iehr_cda
		WHERE creation_time BETWEEN '2015-04-01'
				AND '2015-04-30'
		) AS iehr_cases_apr
	,(
		SELECT count(*) AS iehr_cases_may
		FROM iehr_cda
		WHERE creation_time BETWEEN '2015-05-01'
				AND '2015-05-31'
		) AS iehr_cases_may
	,(
		SELECT count(*) AS iehr_cases_june
		FROM iehr_cda
		WHERE creation_time BETWEEN '2015-06-01'
				AND CURRENT_DATE
		) AS iehr_cases_june