BMS-HOSxP Community
HOSxP => การเขียน SQL Script => ข้อความที่เริ่มโดย: kampa ที่ มิถุนายน 17, 2016, 12:05:30 PM
-
begin
GetDateRangeDialog(d1, d2);
ds1:= FormatDateTime('YYYY-MM-DD', d1);
ds2:= FormatDateTime('YYYY-MM-DD', d2);
c_name:= GetMultipleList('select s.name from spclty s ,ovst o ,opitemrece op '
+'where o.spclty=s.spclty and op.vn=o.vn '
+'and o.vstdate between "'+ds1+'" and "'+ds2+'" '
+'group by s.name ');
ShowMessage(c_name);
ChangeReportSQL('select op.vn ,op.hn ,p.pname ,p.fname ,p.lname ,op.vstdate ,op.vsttime ,op.pttype , '
+'pt.name as ptt_name ,sum(op.sum_price) as price ,v.pdx ,ic.name as pdx_name ,ic.tname , '
+'o.spclty ,s.name '
+'from opitemrece op '
+'left outer join ovst o on o.vn = op.vn '
+'left outer join patient p on p.hn = op.hn '
+'left outer join vn_stat v on v.vn = op.vn '
+'left outer join spclty s on s.spclty = o.spclty '
+'left outer join icd101 ic on ic.code = v.pdx '
+'left outer join pttype pt on pt.pttype = op.pttype '
+'where op.vstdate between "'+ds1+'" and "'+ds2+'" '
+'and o.spclty = "01" '
{+'and s.name = "'+c_name+'" ' }
+'and op.an is null '
+'group by op.vn '
+'order by op.vn asc ' );
end;
พอทดสอบรายงานแล้ว ERROR ครับ
-
ลองเปลี่ยนตารางหลัก ก็ไม่ได้ครับ
GetDateRangeDialog(d1, d2);
ds1:= FormatDateTime('YYYY-MM-DD', d1);
ds2:= FormatDateTime('YYYY-MM-DD', d2);
c_name:= GetMultipleList('select s.name from spclty s ,ovst o ,opitemrece op '
+'where o.spclty=s.spclty and o.vn=op.vn '
+'and o.vstdate between "'+ds1+'" and "'+ds2+'" '
+'group by s.name ');
ShowMessage(c_name);
ChangeReportSQL('select o.vn ,o.hn ,o.an ,p.pname ,p.fname ,p.lname ,o.vstdate ,o.vsttime , '
+'o.spclty ,s.name as spclty_name , v.pdx , ic.name as pdx_name , ic.tname , '
+'o.pttype ,pt.name as ptt_name ,sum(op.sum_price) as price '
+'from ovst o '
+'left outer join patient p on p.hn = o.hn '
+'left outer join pttype pt on pt.pttype = o.pttype '
+'left outer join spclty s on s.spclty = o.spclty '
+'left outer join opitemrece op on op.vn = o.vn '
+'left outer join vn_stat v on v.vn = o.vn '
+'left outer join icd101 ic on ic.code = v.pdx '
+'where o.vstdate between "'+ds1+'" and "'+ds2+'" '
+'and o.an is null '
{+'and o.spclty = "01" ' }
+'and o.spclty = "'+c_name+'" '
+'group by o.vn ' );
-
+'and o.spclty in ('+c_name+') '
-
ขอบคุณครับ ไม่ error แล้วครับ
แต่ภาพ preview เป็นกระดาษขาวไม่มีข้อมูลแสดงออกมาครับ รบกวนหน่อยนะครับ
-
+'and o.spclty in ('+c_name+') '
ได้แล้วครับ ขอบคุณมาก ๆ ครับ
-
ใช้ได้แล้วครับผม
GetDateRangeDialog(d1, d2);
ds1:= FormatDateTime('YYYY-MM-DD', d1);
ds2:= FormatDateTime('YYYY-MM-DD', d2);
c_name:= GetMultipleList('select s.name from spclty s ,ovst o ,opitemrece op '
+'where s.spclty=o.spclty and o.vn=op.vn '
+'and o.vstdate between "'+ds1+'" and "'+ds2+'" '
+'group by s.name ');
ShowMessage(c_name);
ChangeReportSQL('select o.vn ,o.hn ,o.an ,p.pname ,p.fname ,p.lname ,o.vstdate ,o.vsttime , '
+'o.spclty ,s.name as spclty_name ,v.pdx ,ic.name as pdx_name ,ic.tname , '
+'o.pttype ,pt.name as ptt_name ,sum(op.sum_price) as price '
+'from ovst o '
+'left outer join patient p on p.hn = o.hn '
+'left outer join pttype pt on pt.pttype = o.pttype '
+'left outer join spclty s on s.spclty = o.spclty '
+'left outer join opitemrece op on op.vn = o.vn '
+'left outer join vn_stat v on v.vn = o.vn '
+'left outer join icd101 ic on ic.code = v.pdx '
+'where o.vstdate between "'+ds1+'" and "'+ds2+'" '
+'and o.an is null '
+'and s.name in ('+c_name+') '
+'group by o.vn ' );