Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE caris_db;
- SET @start_date= "2016-01-01";
- SET @end_date="2016-09-30";
- select lookup_hospital.hospital_code, total,
- (m_under_1+f_under_1+m_btwn_1_and_4+f_btwn_1_and_4+m_btwn_5_and_9+f_btwn_5_and_9) as under_10,
- (m_under_1+f_under_1) as under_1,
- (m_btwn_1_and_4+f_btwn_1_and_4+m_btwn_5_and_9+f_btwn_5_and_9) as '1_9', m_btwn_10_and_14, f_btwn_10_and_14 , m_btwn_15_and_17, f_btwn_15_and_17 , m_btwn_18_and_24, f_btwn_18_and_24, m_25plus, f_25plus
- from
- (SELECT CONCAT(city_code, '/', hospital_code) AS hospital_code, hospital_code AS hcode_nocity
- FROM lookup_hospital) lookup_hospital
- LEFT JOIN
- (
- select CONCAT(city_code, '/', hospital_code) AS hospital_code, count(*) as 'total',
- SUM(CASE
- WHEN
- dob between (@start_date - interval 364 day) and @end_date
- and dob != '0000-00-00'
- and gender = 1
- THEN
- 1
- ELSE 0
- END) AS m_under_1,
- SUM(CASE
- WHEN
- dob between (@start_date - interval 364 day) and @end_date
- and dob != '0000-00-00'
- and gender = 2
- THEN
- 1
- ELSE 0
- END) AS f_under_1,
- SUM(CASE
- WHEN
- dob between (@start_date - interval 4 year + interval 1 day) and @end_date - interval 1 year
- and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>=1
- and gender = 1
- THEN
- 1
- ELSE 0
- END) AS m_btwn_1_and_4,
- SUM(CASE
- WHEN
- dob between (@start_date - interval 4 year + interval 1 day) and @end_date - interval 1 year
- and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>=1
- and gender = 2
- THEN
- 1
- ELSE 0
- END) AS f_btwn_1_and_4,
- SUM(CASE
- WHEN
- dob between (@start_date - interval 9 year + interval 1 day) and @end_date - interval 5 year
- and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>= 5
- and gender = 1
- THEN
- 1
- ELSE 0
- END) AS m_btwn_5_and_9,
- SUM(CASE
- WHEN
- dob between (@start_date - interval 9 year + interval 1 day) and @end_date - interval 5 year
- and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>= 5
- and gender = 2
- THEN
- 1
- ELSE 0
- END) AS f_btwn_5_and_9,
- SUM(CASE
- WHEN
- dob between (@start_date - interval 14 year + interval 1 day) and @end_date - interval 10 year
- and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>= 10
- and gender = 1
- THEN
- 1
- ELSE 0
- END) AS m_btwn_10_and_14,
- SUM(CASE
- WHEN
- dob between (@start_date - interval 14 year + interval 1 day) and @end_date - interval 10 year
- and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>= 10
- and gender = 2
- THEN
- 1
- ELSE 0
- END) AS f_btwn_10_and_14,
- SUM(CASE
- WHEN
- dob between (@start_date - interval 17 year + interval 1 day) and @end_date - interval 15 year
- and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>= 15
- and gender = 1
- THEN
- 1
- ELSE 0
- END) AS m_btwn_15_and_17,
- SUM(CASE
- WHEN
- dob between (@start_date - interval 17 year + interval 1 day) and @end_date - interval 15 year
- and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>= 15
- and gender = 2
- THEN
- 1
- ELSE 0
- END) AS f_btwn_15_and_17,
- SUM(CASE
- WHEN
- dob between (@start_date - interval 24 year + interval 1 day) and @end_date - interval 18 year
- and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>= 18
- and gender = 1
- THEN
- 1
- ELSE 0
- END) AS m_btwn_18_and_24,
- SUM(CASE
- WHEN
- dob between (@start_date - interval 24 year + interval 1 day) and @end_date - interval 18 year
- and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>= 18
- and gender = 2
- THEN
- 1
- ELSE 0
- END) AS f_btwn_18_and_24,
- SUM(CASE
- WHEN
- dob <= @end_date - interval 25 year and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>= 25
- and gender = 1
- THEN
- 1
- ELSE 0
- END) AS m_25plus,
- SUM(CASE
- WHEN
- dob <= @end_date - interval 25 year and dob != '0000-00-00' and timestampdiff(year, dob, @start_date)>= 25
- and gender = 2
- THEN
- 1
- ELSE 0
- END) AS f_25plus
- from
- /*
- (
- select id_patient, infant_dob as dob , infant_gender as gender
- from testing_mereenfant
- where date >="2013-10-01" and date<@start_date
- ) i , patient
- */
- (
- (
- select id_patient, infant_dob as dob , infant_gender as gender
- from testing_mereenfant
- where date >=date_add(@start_date,interval 3 month) and date<@end_date
- )
- union
- (
- select id_patient, dob , gender
- from patient LEFT JOIN tracking_infant
- ON tracking_infant.id_patient = patient.id
- and
- (
- (
- ( kids_club_program =1
- or id_patient in (select id from tracking_followup where date>=@start_date AND date<@end_date )
- )
- AND id_patient not in (select id_patient from testing_mereenfant where date >=date_add(@start_date,interval 3 month) and date<@end_date )
- )
- OR
- (
- id_patient in (
- select id from patient where
- (
- created_at >=@start_date and created_at<=@end_date
- and id not in
- (
- select id_patient from testing_mereenfant where date >=date_add(@start_date,interval 3 month) and date<@end_date
- )
- )
- or id in
- (
- select id_patient from testing_specimen where date_add(date_blood_taken,interval 3 month) >=@start_date and date_blood_taken <@end_date
- )
- or id in
- (
- select id_patient from tracking_regime where start_date >=@start_date and start_date<=@end_date
- and id_patient in (select id from patient where linked_to_id_patient=0)
- )
- or id in
- (
- select id_patient from tracking_followup where date >=@start_date and date<=@end_date
- and id_patient in (select id from patient where linked_to_id_patient=0)
- )
- or id in
- (
- select id_patient from session left join club_session on session.id_club_session = club_session.id
- left join club on club.id = club_session.id_club
- where club_session.date >=@start_date and club_session.date<=@end_date and club.club_type != 1
- and id_patient in (select id from patient where linked_to_id_patient=0)
- )
- or id in
- (
- select id_patient from tracking_infant where positive_pcr_1 >=@start_date and positive_pcr_1 <@end_date or
- positive_pcr_2 >=@start_date and positive_pcr_2 <@end_date and id_patient in (select id from patient where linked_to_id_patient=0)
- )
- )
- )
- )
- ) ) i , patient
- where
- #kids_club_program =1
- #and
- patient.id = i.id_patient
- #and datediff(@start_date,dob) /365 <15
- group by hospital_code
- ) table_for_left_join_with_lookup_hospital
- ON table_for_left_join_with_lookup_hospital.hospital_code = lookup_hospital.hospital_code
- order by hcode_nocity
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement