按时间段查询,按客户和产品以及业务员汇总数量和金额的报表

select f.fname 业务员 ,d.fnumber 客户代码,d.fname 客户名称,a.Fdate 日期,a.FBillNo 单据号码,
c.FNumber 产品编码,c.FName 产品名称,c.FModel 规格型号,e.FName 计量单位,b.fauxqty 实收数量,
b.fauxprice 单价,b.Famount 金额
from ICStockBill a
inner join ICStockBillEntry b on a.FInterID=b.FInterID
left join t_ICItem c on c.FItemID=b.FItemID
left join t_Organization d on d.FItemID=a.FSupplyID
left join t_MeasureUnit e on e.FItemID=b.FUnitID
left join t_Emp f on a.FEmpID=f.FItemID
where FTranType=21 and FDate >='********' and FDate<='########'
union
select f.fname,d.fnumber,d.fname,null,null,c.FNumber+'(产品小计)',c.FName,c.FModel,e.FName,
sum(b.fauxqty),null,sum(b.Famount)
from ICStockBill a
inner join ICStockBillEntry b on a.FInterID=b.FInterID
left join t_ICItem c on c.FItemID=b.FItemID
left join t_Organization d on d.FItemID=a.FSupplyID
left join t_MeasureUnit e on e.FItemID=b.FUnitID
left join t_Emp f on f.FItemID=a.FEmpID
where FTranType=21 and FDate >='********' and FDate<='########'
group by f.fname,d.fnumber,d.fname,c.FNumber,c.FName,c.FModel,e.FName
--order by d.FNumber,c.FNumber
union
select f.fname,d.fnumber+'（客户小计）',d.fname,null,null,null,null,null,null,
sum(b.fauxqty),null,sum(b.Famount)
from ICStockBill a
inner join ICStockBillEntry b on a.FInterID=b.FInterID
left join t_ICItem c on c.FItemID=b.FItemID
left join t_Organization d on d.FItemID=a.FSupplyID
left join t_MeasureUnit e on e.FItemID=b.FUnitID
left join t_Emp f on f.FItemID=a.FEmpID
where FTranType=21 and FDate >='********' and FDate<='########'
group by f.fname,d.fnumber,d.fname
union
select f.fname+'(小计)',null,null,null,null,null,null,null,null,
sum(b.fauxqty),null,sum(b.Famount)
from ICStockBill a
inner join ICStockBillEntry b on a.FInterID=b.FInterID
left join t_ICItem c on c.FItemID=b.FItemID
left join t_Organization d on d.FItemID=a.FSupplyID
left join t_MeasureUnit e on e.FItemID=b.FUnitID
left join t_Emp f on f.FItemID=a.FEmpID
where FTranType=21 and FDate >='********' and FDate<='########'
group by f.fname
union
select '总 计' ,null ,null,null,null,null,null,null,null,sum(b.fauxqty),null,sum(b.Famount)
from ICStockBill a
inner join ICStockBillEntry b on a.FInterID=b.FInterID
left join t_ICItem c on c.FItemID=b.FItemID
left join t_Organization d on d.FItemID=a.FSupplyID
left join t_MeasureUnit e on e.FItemID=b.FUnitID
left join t_Emp f on f.FItemID=a.FEmpID
where FTranType=21 and FDate >='********' and FDate<='########'
order by f.FName, d.FNumber,c.FNumber