1
การเขียน SQL Script / ขอคำแนะนำสร้างรายงานรูปแบบรายงาน Multiplelist
« เมื่อ: มิถุนายน 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 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 ครับ