| 1234567891011121314 |
- declare @siteid varchar(20)
- set @siteid = $siteid$
- select t1.fsonum,t2.frownum,fpayagentnum,t1.frecagentnum,pp_hr.displayname,t2.fitemname,t2.fspec,t2.fmodel,tunit.fname,t2.fqty,t2.fprice,t2.frateamount,(t2.frateamount-isnull(t2.fwriteoffamount,0)) fwriteoffamount,t1.checkdate,SAINVOICE.fdate,t3.fmaterial,t3.fdevice,t3.fspecalnote,t3.fitemno,t3.fnotes,TPROJECT.fprojectname,t4.kaipiaodate,(t2.frateamount-isnull(t2.finvoiceamount,0)) finvoiceamount from saorder t1
- inner join saorderdetail t2 on t1.siteid=t2.siteid and t1.saorderid=t2.fparentid
- inner join pp_hr on pp_hr.hrid=t1.fsaler and pp_hr.siteid=@siteid
- inner join tunit on tunit.FUNITID=t2.FUNITID
- left join SAINVOICEDETAIL on SAINVOICEDETAIL.FSOURCEID=t2.SAORDERDETAILID and SAINVOICEDETAIL.siteid=t2.siteid
- inner join SAINVOICE on SAINVOICEDETAIL.fparentid=SAINVOICE.sainvoiceid and SAINVOICEDETAIL.siteid=SAINVOICE.siteid
- inner join titem t3 on t2.fitemno=t3.fitemno and t2.siteid=t3.siteid
- left join TPROJECT on TPROJECT.fprojectnum=t1.fprojectnum and TPROJECT.siteid=t1.siteid
- left join (select saorderdetail.SAORDERDETAILID,max(TINVOICEAPPLY.checkdate) kaipiaodate from saorderdetail left join TINVOICEAPPLYORDER on saorderdetail.SAORDERDETAILID=TINVOICEAPPLYORDER.SAORDERDETAILID and saorderdetail.siteid=TINVOICEAPPLYORDER.siteid
- left join TINVOICEAPPLY on TINVOICEAPPLY.TINVOICEAPPLYID=TINVOICEAPPLYORDER.TINVOICEAPPLYID and TINVOICEAPPLY.siteid=TINVOICEAPPLYORDER.siteid where TINVOICEAPPLY.checkdate is not null group by saorderdetail.SAORDERDETAILID ) t4 on t2.saorderdetailid=t4.saorderdetailid
- where t1.fstatus in('ÉóºË','¹Ø±Õ') and t1.siteid=@siteid and DateDiff(dd,t1.checkdate,getdate())<=365 and t1.fagentnum=$fagentnum$ order by t1.fsonum desc,t2.frownum asc
|
