BMS-HOSxP Community
HOSxP => Report Exchange => ข้อความที่เริ่มโดย: golf_win ที่ มกราคม 17, 2009, 20:03:02 PM
-
พี่ขวดครับผมดูผิดจริงๆ เป็นรายงานที่ผสมกันระหว่าง SQL กับ Variable ครับ ;D ;D
-
อีกอย่างเป็นรายงานที่ตัดยอดแบบ realtime ถ้ามี colum วันที่บันทึกข้อมูลในตาราง person น่าจะทำให้ดึงรายงานย้อนหลังได้นะครับ ;D ;D
-
เอาไปใช้ด้วย ;D
-
;D ;D ;D ;D ;D ;D
-
... ลองใช้คำสั่งข้างล่าง RUN ใน HOSxP ดูนะครับ ...
... กลุ่มอายุจะใช้วันเกิดมาคำนวณ ไม่ได้ใช้ age_y จากตาราง person ก็จะทำให้ดึงข้อมูลมาดูเมื่อไรก็ได้ โดยไม่ต้องไปสั่ง Update Age ก่อน...
...ถ้าต้องการเปลี่ยนแปลงเงื่อนไขที่ต้องการดู ก็แก้ไขเพิ่มเติมตรง where ได้เลยครับ .... :D ....
select p.village_id, v.village_moo as "หมู่ที่",v.village_name as "ชื่อหมู่บ้าน",
count(pt.person_id) as "รวมทั้งหมด",
count(pm.person_id) as "รวม(ช.)", count(pf.person_id) as "รวม(ญ.)",
count(p1.person_id) as "ต่ำกว่า 1 ปี(ช.)", count(p2.person_id) as "ต่ำกว่า 1 ปี(ญ.)",
count(p3.person_id) as "1-4 ปี(ช.)", count(p4.person_id) as "1-4 ปี(ญ.)",
count(p5.person_id) as "5-9 ปี(ช.)", count(p6.person_id) as "5-9 ปี(ญ.)",
count(p7.person_id) as "10-14 ปี(ช.)", count(p8.person_id) as "10-14 ปี(ญ.)",
count(p9.person_id) as "15-19 ปี(ช.)", count(p10.person_id) as "15-19 ปี(ญ.)",
count(p11.person_id) as "20-24 ปี(ช.)", count(p12.person_id) as "20-24 ปี(ญ.)",
count(p13.person_id) as "25-29 ปี(ช.)", count(p14.person_id) as "25-29 ปี(ญ.)",
count(p15.person_id) as "30-34 ปี(ช.)", count(p16.person_id) as "30-34 ปี(ญ.)",
count(p17.person_id) as "35-39 ปี(ช.)", count(p18.person_id) as "35-39 ปี(ญ.)",
count(p19.person_id) as "40-44 ปี(ช.)", count(p20.person_id) as "40-44 ปี(ญ.)",
count(p21.person_id) as "45-49 ปี(ช.)", count(p22.person_id) as "45-49 ปี(ญ.)",
count(p23.person_id) as "50-54 ปี(ช.)", count(p24.person_id) as "50-54 ปี(ญ.)",
count(p25.person_id) as "55-59 ปี(ช.)", count(p26.person_id) as "55-59 ปี(ญ.)",
count(p27.person_id) as "60-64 ปี(ช.)", count(p28.person_id) as "60-64 ปี(ญ.)",
count(p29.person_id) as "65-69 ปี(ช.)", count(p30.person_id) as "65-69 ปี(ญ.)",
count(p31.person_id) as "70-74 ปี(ช.)", count(p32.person_id) as "70-74 ปี(ญ.)",
count(p33.person_id) as "75-79 ปี(ช.)", count(p34.person_id) as "75-79 ปี(ญ.)",
count(p35.person_id) as "80-84 ปี(ช.)", count(p36.person_id) as "80-84 ปี(ญ.)",
count(p37.person_id) as "85 ปีขึ้นไป(ช.)", count(p38.person_id) as "85 ปีขึ้นไป(ญ.)",
count(p39.person_id) as "ไม่ทราบอายุ(ช.)", count(p40.person_id) as "ไม่ทราบอายุ(ญ.)"
from person p
left outer join village v on v.village_id = p.village_id
left outer join person pt on pt.person_id = p.person_id
left outer join person pm on pm.person_id = p.person_id and p.sex = '1'
left outer join person pf on pf.person_id = p.person_id and p.sex = '2'
left outer join person p1 on p1.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) < '1' and p.sex = '1'
left outer join person p2 on p2.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) < '1' and p.sex = '2'
left outer join person p3 on p3.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '1' and '4' and p.sex = '1'
left outer join person p4 on p4.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '1' and '4' and p.sex = '2'
left outer join person p5 on p5.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '5' and '9' and p.sex = '1'
left outer join person p6 on p6.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '5' and '9' and p.sex = '2'
left outer join person p7 on p7.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '10' and '14' and p.sex = '1'
left outer join person p8 on p8.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '10' and '14' and p.sex = '2'
left outer join person p9 on p9.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '15' and '19' and p.sex = '1'
left outer join person p10 on p10.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '15' and '19' and p.sex = '2'
left outer join person p11 on p11.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '20' and '24' and p.sex = '1'
left outer join person p12 on p12.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '20' and '24' and p.sex = '2'
left outer join person p13 on p13.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '25' and '29' and p.sex = '1'
left outer join person p14 on p14.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '25' and '29' and p.sex = '2'
left outer join person p15 on p15.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '30' and '34' and p.sex = '1'
left outer join person p16 on p16.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '30' and '34' and p.sex = '2'
left outer join person p17 on p17.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '35' and '39' and p.sex = '1'
left outer join person p18 on p18.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '35' and '39' and p.sex = '2'
left outer join person p19 on p19.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '40' and '44' and p.sex = '1'
left outer join person p20 on p20.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '40' and '44' and p.sex = '2'
left outer join person p21 on p21.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '45' and '49' and p.sex = '1'
left outer join person p22 on p22.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '45' and '49' and p.sex = '2'
left outer join person p23 on p23.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '50' and '54' and p.sex = '1'
left outer join person p24 on p24.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '50' and '54' and p.sex = '2'
left outer join person p25 on p25.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '55' and '59' and p.sex = '1'
left outer join person p26 on p26.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '55' and '59' and p.sex = '2'
left outer join person p27 on p27.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '60' and '64' and p.sex = '1'
left outer join person p28 on p28.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '60' and '64' and p.sex = '2'
left outer join person p29 on p29.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '65' and '69' and p.sex = '1'
left outer join person p30 on p30.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '65' and '69' and p.sex = '2'
left outer join person p31 on p31.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '70' and '74' and p.sex = '1'
left outer join person p32 on p32.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '70' and '74' and p.sex = '2'
left outer join person p33 on p33.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '75' and '79' and p.sex = '1'
left outer join person p34 on p34.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '75' and '79' and p.sex = '2'
left outer join person p35 on p35.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '80' and '84' and p.sex = '1'
left outer join person p36 on p36.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) between '81' and '84' and p.sex = '2'
left outer join person p37 on p37.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) > '84' and p.sex = '1'
left outer join person p38 on p38.person_id = p.person_id
and timestampdiff(year,p.birthdate,curdate()) > '84' and p.sex = '2'
left outer join person p39 on p39.person_id = p.person_id
and (p.birthdate is null or p.birthdate = '') and p.sex = '1'
left outer join person p40 on p40.person_id = p.person_id
and (p.birthdate is null or p.birthdate = '') and p.sex = '2'
where p.village_id <> '1' and p.citizenship = '99'
group by p.village_id
order by p.village_id
-
เป็น script SQL ที่ยาวมากเลยครับ
อ.ขวด ยาวมากเลยครับ :D ;D
สุดยอดจริงๆ ยังไม่เคยเห็นยาวขนาดนี้มาก่อน ;D :D ;D
-
... อันนี้เป็นตัวรายงานที่พี่ลองทำดู เป็นรายงานที่ใช้คำสั่ง sql ล้วนๆ เลย รูปแบบรายงานลอกมาจาก HCIS ...
... และถ้าต้องการเปลี่ยนแปลงเงื่อนไขของข้อมูลที่ต้องการดู ก็แก้ไขจากคำสั่งของรายงานได้เลย ไม่ต้องแก้ที่ตัวรายงาน ลองทดสอบดูนะครับ ... :D ....
-
สุดยอดครับพี่ khuad ขยันจริงๆๆๆๆ
-
เป็น script SQL ที่ยาวมากเลยครับ
อ.ขวด ยาวมากเลยครับ :D ;D
สุดยอดจริงๆ ยังไม่เคยเห็นยาวขนาดนี้มาก่อน ;D :D ;D
.... จริงๆ แล้ว ป๋า jdadmin1 กะ ป๋า udomchok 2 คนน่ะยาวกว่าพี่อีกนะน้องกิจ ....
.... อายุ กะ สายตา นะ 2 ป๋าข้างบนน่ะยาวกว่าพี่เยอะเลย .... ;D ;D ;D ;D ;D ......
-
อ.ขวด ยาวมากเลยครับ :D ;D
สุดยอดจริงๆ ยังไม่เคยเห็นยาวขนาดนี้มาก่อน ;D :D ;D
แหม...อ่านแค่นี้แล้วรู้สึก..... ไม่อยากจะเชื่อเลยว่า อ.ขวด ตัวแต่นี้ แต่....ยาววววววววววจริง ๆ 555555555555