Note: The double and enclosed variables must be constant before query execution, for example, & previous day &
Select a. billdate as fdate, max (Year (A. billdate) as fyear,
Max (month (A. billdate) as fperiod, 'record' as fgroupid, '1' as fnumber,
'2014. 02 'As faccountnum, 'waste paper goods (Project Accounting)' As faccountname,
'RMB' as fcurrencynum, 'RMB' as fcurrencyname, sum (A. Total)
As famountfor, sum (A. Total) as fdebit, 0 as fcredit, 'tong Meihua 'As fpreparerid,
'None' as fcheckerid, 'none' as fapproveid, 'none' as fcashierid,
''As fhandler, '*' As fsettletypeid, ''as fsettleno,
Max (I. billcode + ', quantity:' + ltrim (STR (A. qty, 15, 3 ))
+ 'Ton; '+ I. Comment +'; '+ ltrim (I. Explain) as fexplanation, 0 as fquantity,
'*' As fmeasureunitid, 0 as funitprice, ''as freference, max (A. billdate)
As ftransdate, ''as ftransno, 0 as fattachments, 999 as fserialnum,
''As fobjectname, ''as fparameter, 1 as fexchangerate, 0 as fentryid,
Max ('department --- '+ Left (K. usercode, 6) +' --- '+ Left (K. fullname, 5)
+ '| Location ---' + right (ltrim (K. usercode), 2) + '---' + right (ltrim (K. fullname), 3 ))
As fitem, 0 as fposted, ''as finternalind,'' as fcashflow, K. usercode as expr1,
Max (K. fullname) as expr2, B. usercode, max (B. fullname) as expr3, I. billcode
From inoutstocktable a inner join
Btype B on B. typeid = A. btypeid inner join
Stock K on K. typeid = A. ktypeid inner join
Billindex I on I. billnumberid = A. billnumberid
Where (A. billtype = 34) or (A. billtype = 6) and (A. billdate = '& previous day &') and (I. redword = 0)
Group by A. billdate, K. usercode, B. usercode, I. billcode
Union all
Select a. billdate as fdate, max (Year (A. billdate) as fyear,
Max (month (A. billdate) as fperiod, 'record' as fgroupid, '1' as fnumber,
'2014. 02 'As faccountnum, 'waste paper payable' As faccountname,
'RMB' as fcurrencynum, 'RMB' as fcurrencyname, sum (A. Total) as famountfor,
0 as fdebit, sum (A. Total) as fcredit, 'tong Meihua 'As fpreparerid,
'None' as fcheckerid, 'none' as fapproveid, 'none' as fcashierid,
''As fhandler, '*' As fsettletypeid, ''as fsettleno,
Max (I. billcode + ', quantity:' + ltrim (STR (A. qty, 15, 3 ))
+ 'Ton; '+ I. Comment +'; '+ ltrim (I. Explain) as fexplanation, 0 as fquantity,
'*' As fmeasureunitid, 0 as funitprice, ''as freference, max (A. billdate)
As ftransdate, ''as ftransno, 0 as fattachments, 999 as fserialnum,
''As fobjectname, ''as fparameter, 1 as fexchangerate, 0 as fentryid,
Max ('department --- '+ Left (K. usercode, 6) +' --- '+ Left (K. fullname, 5)
+ '| Supplier ---' + ltrim (B. usercode) + '---' + ltrim (B. fullname) as fitem,
0 as fposted, ''as finternalind,'' as fcashflow, K. usercode as expr1,
Max (K. fullname) as expr2, B. usercode, max (B. fullname) as expr3, I. billcode
From inoutstocktable a inner join
Btype B on B. typeid = A. btypeid inner join
Stock K on K. typeid = A. ktypeid inner join
Billindex I on I. billnumberid = A. billnumberid
Where (A. billtype = 34) or (A. billtype = 6) and (A. billdate = '& previous day &') and (I. redword = 0)
Group by A. billdate, K. usercode, B. usercode, I. billcode
Order by A. billdate, K. usercode, B. usercode, I. billcode