tein_type=1 -> shinki/keizoku   tein_type=2 -> dansei/josei			event_contact_status=1 -> yoyaku  event_contact_status=2 -> nyukin

1. yoyaku & shinki/keizoku  0/0

SELECT mycount, (shinki_yoyaku_no +keizoku_yoyaku_no+sai_yoyaku_no) AS total_yoyaku, teiin, tein_type, teiin_sub1, teiin_sub2, id_sub1 AS course_id, shinki_toroku_no, keizoku_toroku_no, shinki_yoyaku_no, keizoku_yoyaku_no, dansei_toroku_no, josei_toroku_no, dansei_yoyaku_no, josei_yoyaku_no, sai_toroku_no, sai_yoyaku_no
FROM smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contacts.deleted=0 AND smsevent_sub1.nenki='201501' 
		AND event_contact_status=1 GROUP BY event_id
) AS my_ec
WHERE smsevent_sub1.nenki='201501' AND smsevent_sub1.id_sub1=smsevent_sub3.id_sub3 AND my_ec.event_id=id_sub1 AND tein_type=1
	AND mycount!=(shinki_yoyaku_no +keizoku_yoyaku_no+sai_yoyaku_no)

2. yoyaku & dansei/josei 0/0 

SELECT mycount, (dansei_yoyaku_no +josei_yoyaku_no) AS total_yoyaku, teiin, tein_type, teiin_sub1, teiin_sub2, id_sub1 AS course_id, shinki_toroku_no, keizoku_toroku_no, shinki_yoyaku_no, keizoku_yoyaku_no, dansei_toroku_no, josei_toroku_no, dansei_yoyaku_no, josei_yoyaku_no, sai_toroku_no, sai_yoyaku_no
FROM smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contacts.deleted=0 AND smsevent_sub1.nenki='201501' 
		AND event_contact_status=1 GROUP BY event_id
) AS my_ec
WHERE smsevent_sub1.nenki='201501' AND smsevent_sub1.id_sub1=smsevent_sub3.id_sub3 AND my_ec.event_id=id_sub1 AND tein_type=2
	AND mycount!=(dansei_yoyaku_no +josei_yoyaku_no)

3. nyukin & shinki/keizoku  1/1,197  --  7db5c180-1e58-9e1e-3692-543f3f471c89

SELECT mycount, (shinki_toroku_no +keizoku_toroku_no+saikai_toroku_no) AS total_toroku, teiin, tein_type, teiin_sub1, teiin_sub2, id_sub1 AS course_id, shinki_toroku_no, keizoku_toroku_no, shinki_yoyaku_no, keizoku_yoyaku_no, dansei_toroku_no, josei_toroku_no, dansei_yoyaku_no, josei_yoyaku_no, sai_toroku_no, sai_yoyaku_no, saikai_toroku_no, saikai_yoyaku_no
FROM smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contacts.deleted=0 AND smsevent_sub1.nenki='201501' 
		AND event_contact_status=2 GROUP BY event_id
) AS my_ec
WHERE smsevent_sub1.nenki='201501' AND smsevent_sub1.id_sub1=smsevent_sub3.id_sub3 AND my_ec.event_id=id_sub1 AND tein_type=1
	AND mycount!=(shinki_toroku_no +keizoku_toroku_no+saikai_toroku_no)

4. nyukin & dansei/josei  0/8  

SELECT mycount, (dansei_toroku_no +josei_toroku_no) AS total_toroku, teiin, tein_type, teiin_sub1, teiin_sub2, id_sub1 AS course_id, shinki_toroku_no, keizoku_toroku_no, shinki_yoyaku_no, keizoku_yoyaku_no, dansei_toroku_no, josei_toroku_no, dansei_yoyaku_no, josei_yoyaku_no, sai_toroku_no, sai_yoyaku_no, saikai_toroku_no, saikai_yoyaku_no
FROM smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contacts.deleted=0 AND smsevent_sub1.nenki='201501' 
		AND event_contact_status=2 GROUP BY event_id
) AS my_ec
WHERE smsevent_sub1.nenki='201501' AND smsevent_sub1.id_sub1=smsevent_sub3.id_sub3 AND my_ec.event_id=id_sub1 AND tein_type=2
	AND mycount!=(dansei_toroku_no +josei_toroku_no)

4. yoyaku & type not set  1/40 using dansei/josei  -- 1f373afe-d6b6-6386-ca49-547c3040097b

SELECT mycount, (shinki_yoyaku_no +keizoku_yoyaku_no+saikai_yoyaku_no) AS total_yoyaku, teiin, tein_type, teiin_sub1, teiin_sub2, id_sub1 AS course_id, shinki_toroku_no, keizoku_toroku_no, shinki_yoyaku_no, keizoku_yoyaku_no, dansei_toroku_no, josei_toroku_no, dansei_yoyaku_no, josei_yoyaku_no, sai_toroku_no, sai_yoyaku_no
FROM smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contacts.deleted=0 AND smsevent_sub1.nenki='201501' 
		AND event_contact_status=1 GROUP BY event_id
) AS my_ec
WHERE smsevent_sub1.nenki='201501' AND smsevent_sub1.id_sub1=smsevent_sub3.id_sub3 AND my_ec.event_id=id_sub1 AND tein_type IS NULL
	AND mycount!=(shinki_yoyaku_no +keizoku_yoyaku_no+saikai_yoyaku_no)
	
4.2 yoyaku & type not set  1/41  using shinki/keizoku  --   1f373afe-d6b6-6386-ca49-547c3040097b

SELECT mycount, (shinki_yoyaku_no +keizoku_yoyaku_no+saikai_yoyaku_no) AS total_yoyaku, (dansei_yoyaku_no +josei_yoyaku_no) AS total_danjo, teiin, tein_type, teiin_sub1, teiin_sub2, id_sub1 AS course_id, shinki_toroku_no, keizoku_toroku_no, shinki_yoyaku_no, keizoku_yoyaku_no, dansei_toroku_no, josei_toroku_no, dansei_yoyaku_no, josei_yoyaku_no, sai_toroku_no, sai_yoyaku_no, saikai_toroku_no, saikai_yoyaku_no
FROM smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contacts.deleted=0 AND smsevent_sub1.nenki='201501' 
		AND event_contact_status=1 GROUP BY event_id
) AS my_ec
WHERE smsevent_sub1.nenki='201501' AND smsevent_sub1.id_sub1=smsevent_sub3.id_sub3 AND my_ec.event_id=id_sub1 AND tein_type IS NULL
	AND mycount!= (shinki_yoyaku_no +keizoku_yoyaku_no+saikai_yoyaku_no)
			
5. nyukin & type not set  1/2,822  using shinki/keizoku --  d2b78aba-7977-fcee-71d3-544f06ddceea

SELECT mycount, (shinki_toroku_no +keizoku_toroku_no+saikai_toroku_no) AS total_toroku, (dansei_toroku_no +josei_toroku_no) AS total_danjo, teiin, tein_type, teiin_sub1, teiin_sub2, id_sub1 AS course_id, shinki_toroku_no, keizoku_toroku_no, shinki_yoyaku_no, keizoku_yoyaku_no, dansei_toroku_no, josei_toroku_no, dansei_yoyaku_no, josei_yoyaku_no, sai_toroku_no, sai_yoyaku_no
FROM smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contacts.deleted=0 AND smsevent_sub1.nenki='201501' 
		AND event_contact_status=2 GROUP BY event_id
) AS my_ec
WHERE smsevent_sub1.nenki='201501' AND smsevent_sub1.id_sub1=smsevent_sub3.id_sub3 AND my_ec.event_id=id_sub1 AND tein_type IS NULL
	AND mycount!=(shinki_toroku_no +keizoku_toroku_no+saikai_toroku_no)
	
5.1 nyukin & type not set  2/2,822  using dansei/josei  --   252010   &   260631    =>  import data (only nenki has changed?)

SELECT mycount, (shinki_toroku_no +keizoku_toroku_no) AS total_toroku, (dansei_toroku_no +josei_toroku_no) AS total_danjo, teiin, tein_type, teiin_sub1, teiin_sub2, id_sub1 AS course_id, shinki_toroku_no, keizoku_toroku_no, shinki_yoyaku_no, keizoku_yoyaku_no, dansei_toroku_no, josei_toroku_no, dansei_yoyaku_no, josei_yoyaku_no, sai_toroku_no, sai_yoyaku_no
FROM smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contacts.deleted=0 AND smsevent_sub1.nenki='201501' 
		AND event_contact_status=2 GROUP BY event_id
) AS my_ec
WHERE smsevent_sub1.nenki='201501' AND smsevent_sub1.id_sub1=smsevent_sub3.id_sub3 AND my_ec.event_id=id_sub1 AND tein_type IS NULL
	AND mycount!=(dansei_toroku_no +josei_toroku_no)
	
-- 2014-12-18  on test site
	
UPDATE smsevent_sub3 SET shinki_toroku_no=shinki_toroku_no+saikai_toroku_no WHERE 1;
UPDATE smsevent_sub3 SET shinki_yoyaku_no=shinki_yoyaku_no+saikai_yoyaku_no WHERE 1;


-- 3 koza
SELECT mycount, (shinki_yoyaku_no +keizoku_yoyaku_no) AS total_yoyaku, teiin, tein_type, teiin_sub1, teiin_sub2, id_sub1 AS course_id, shinki_toroku_no, keizoku_toroku_no, shinki_yoyaku_no, keizoku_yoyaku_no, dansei_toroku_no, josei_toroku_no, dansei_yoyaku_no, josei_yoyaku_no, sai_toroku_no, sai_yoyaku_no
FROM smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contacts.deleted=0 AND smsevent_sub1.nenki='201501' 
		AND event_contact_status=1 GROUP BY event_id
) AS my_ec
WHERE smsevent_sub1.nenki='201501' AND smsevent_sub1.id_sub1=smsevent_sub3.id_sub3 AND my_ec.event_id=id_sub1 AND mycount!=(shinki_yoyaku_no +keizoku_yoyaku_no)

-- 17 koza
SELECT mycount, (shinki_toroku_no +keizoku_toroku_no) AS total_toroku, (dansei_toroku_no +josei_toroku_no) AS total_danjo, teiin, tein_type, teiin_sub1, teiin_sub2, id_sub1 AS course_id, shinki_toroku_no, keizoku_toroku_no, shinki_yoyaku_no, keizoku_yoyaku_no, dansei_toroku_no, josei_toroku_no, dansei_yoyaku_no, josei_yoyaku_no, sai_toroku_no, sai_yoyaku_no
FROM smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contacts.deleted=0 AND smsevent_sub1.nenki='201501' 
		AND event_contact_status=2 GROUP BY event_id
) AS my_ec
WHERE smsevent_sub1.nenki='201501' AND smsevent_sub1.id_sub1=smsevent_sub3.id_sub3 AND my_ec.event_id=id_sub1 AND mycount!=(shinki_toroku_no +keizoku_toroku_no)


-- 0 
SELECT mycount, sai_yoyaku_no, teiin, tein_type, teiin_sub1, teiin_sub2, id_sub1 AS course_id, shinki_toroku_no, keizoku_toroku_no, shinki_yoyaku_no, keizoku_yoyaku_no, dansei_toroku_no, josei_toroku_no, dansei_yoyaku_no, josei_yoyaku_no, sai_toroku_no, sai_yoyaku_no
FROM smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contacts.deleted=0 AND smsevent_sub1.nenki='201501' 
		AND event_contact_status=1 AND ec_shinki_keizoku=3 GROUP BY event_id
) AS my_ec
WHERE smsevent_sub1.nenki='201501' AND smsevent_sub1.id_sub1=smsevent_sub3.id_sub3 AND my_ec.event_id=id_sub1 AND mycount!=sai_yoyaku_no


UPDATE  smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contacts.deleted=0 AND smsevent_sub1.nenki='201501' 
		AND event_contact_status=1 AND ec_shinki_keizoku=3 GROUP BY event_id
) AS my_ec
SET sai_yoyaku_no=mycount WHERE smsevent_sub1.nenki='201501' AND smsevent_sub1.id_sub1=smsevent_sub3.id_sub3 AND my_ec.event_id=id_sub1 AND mycount!=sai_yoyaku_no


-- 4 out of 6 
SELECT mycount, sai_toroku_no, teiin, tein_type, teiin_sub1, teiin_sub2, id_sub1 AS course_id, shinki_toroku_no, keizoku_toroku_no, shinki_yoyaku_no, keizoku_yoyaku_no, dansei_toroku_no, josei_toroku_no, dansei_yoyaku_no, josei_yoyaku_no, sai_toroku_no, sai_toroku_no
FROM smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contacts.deleted=0 AND smsevent_sub1.nenki='201501' 
		AND event_contact_status=2 AND ec_shinki_keizoku=3 GROUP BY event_id
) AS my_ec
WHERE smsevent_sub1.nenki='201501' AND smsevent_sub1.id_sub1=smsevent_sub3.id_sub3 AND my_ec.event_id=id_sub1 AND mycount!=sai_toroku_no


UPDATE  smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contacts.deleted=0 AND smsevent_sub1.nenki='201501' 
		AND event_contact_status=2 AND ec_shinki_keizoku=3 GROUP BY event_id
) AS my_ec
SET sai_toroku_no=mycount WHERE smsevent_sub1.nenki='201501' AND smsevent_sub1.id_sub1=smsevent_sub3.id_sub3 AND my_ec.event_id=id_sub1 AND mycount!=sai_toroku_no


-- 2014-12-19

SELECT mycount, (shinki_yoyaku_no + keizoku_yoyaku_no + shinki_toroku_no + keizoku_toroku_no) AS total_toroku, teiin, tein_type, teiin_sub1, teiin_sub2, id_sub1 AS course_id, shinki_toroku_no, keizoku_toroku_no, shinki_yoyaku_no, keizoku_yoyaku_no, dansei_toroku_no, josei_toroku_no, dansei_yoyaku_no, josei_yoyaku_no, sai_toroku_no, sai_yoyaku_no
FROM smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contacts.deleted=0 AND smsevent_sub1.nenki IN ('201501', '201504')
		AND event_contact_status IN ('1', '2') GROUP BY event_id
) AS my_ec
WHERE smsevent_sub1.nenki IN ('201501', '201504') AND smsevent_sub1.id_sub1=smsevent_sub3.id_sub3 AND my_ec.event_id=id_sub1 AND mycount!=(shinki_yoyaku_no + keizoku_yoyaku_no + shinki_toroku_no + keizoku_toroku_no)


SELECT mycount, (shinki_yoyaku_no + keizoku_yoyaku_no + shinki_toroku_no + keizoku_toroku_no) AS total_toroku, teiin, tein_type, teiin_sub1, teiin_sub2, id_sub1 AS course_id, shinki_toroku_no, keizoku_toroku_no, shinki_yoyaku_no, keizoku_yoyaku_no, dansei_toroku_no, josei_toroku_no, dansei_yoyaku_no, josei_yoyaku_no, sai_toroku_no, sai_yoyaku_no
FROM smsevent_sub1, smsevent_sub3, (
	SELECT count(contact_id) AS mycount, event_id FROM event_contacts, smsevent_sub1 	WHERE event_contacts.event_id=smsevent_sub1.id_sub1 AND event_contacts.deleted=0 AND smsevent_sub1.nenki IN ('201501', '201504')
		AND event_contact_status IN ('1', '2') GROUP BY event_id
) AS my_ec
WHERE smsevent_sub1.nenki IN ('201501', '201504') AND smsevent_sub1.id_sub1=smsevent_sub3.id_sub3 AND my_ec.event_id=id_sub1 AND (shinki_yoyaku_no + keizoku_yoyaku_no + shinki_toroku_no + keizoku_toroku_no)!=0



SELECT (shinki_yoyaku_no + keizoku_yoyaku_no + shinki_toroku_no + keizoku_toroku_no) AS total_toroku, teiin, tein_type, teiin_sub1, teiin_sub2, id_sub1, shinki_toroku_no, keizoku_toroku_no, shinki_yoyaku_no, keizoku_yoyaku_no, dansei_toroku_no, josei_toroku_no, dansei_yoyaku_no, josei_yoyaku_no, sai_toroku_no, sai_yoyaku_no 
FROM smsevent_sub1, smsevent_sub3 LEFT JOIN event_contacts ON id_sub3=event_id AND event_contact_status IN ('1', '2') AND event_contacts.deleted=0 
WHERE smsevent_sub1.nenki IN ('201501', '201504') AND id_sub1=id_sub3 AND (shinki_yoyaku_no + keizoku_yoyaku_no + shinki_toroku_no + keizoku_toroku_no)!=0 AND event_contacts.id IS NULL