WITH BranchSale(BranchID,WorkDate,Qty,Amount,AmountKB,PriceIn,AmountCB,AgentCost)
AS(
Select top 80 A.BranchID, A.WorkDate,Sum(A.Qty)AS Qty,Sum(A.Amount)As Amount,Sum(A.AmountKB)As AmountKB,(Case When Sum(A.Qty)=0 Then 0 Else Sum(A.Qty*B.InPrice)/Sum(A.Qty) End) As PriceIn,Sum(A.Qty*B.InPrice) As AmountCB,Sum(IsNull(C.BatchPrice,0)*A.Qty)As AgentCost
From (
Select A.BranchID, A.WorkDate,B.GoodsID,Sum((Case B.ReturnFlag When 0 Then 1 Else -1 End)*B.Qty)AS Qty,Sum(B.Amount)AS Amount ,Sum(B.Amount*B.DiscountKB)AS AmountKB
From DBO.Retail A Inner Join DBO.RetailD B On A.PKID=B.PKID And A.BranchID=B.BranchID Inner Join DBO.BaseBranch Branch On A.BranchID=Branch.BranchID
Where Branch.BranchID in ('0001','0002','0003','0004','0006','0007','0008','0010','0013','0015','0016','0017','0018','0019','0021','0022','0024','0025','0026','0027','0029','0030','0031','0032','0034','0035','0036','0037','0038','0052','0053','0054') And Convert(varchar(12),A.WorkDate,112) Between '20141001' and '20141223' And A.DelFlag=0 And A.DoneFlag=1 And A.RetailType=0
Group By A.BranchID, A.WorkDate,B.GoodsID )A Left Join (Select GoodsID,InPrice From DBO.BaseGoods )B On A.GoodsID=B.GoodsID Left Join DBO.BaseGoods C On A.GoodsID=C.GoodsID Group By A.BranchID, A.WorkDate Order By A.BranchID, A.WorkDate)
Select a.BranchDesc as BranchDesc,sum(b.Qty) as qty,sum(b.Amount) as amount,sum(b.AmountCB) as aountcb
from BaseBranch as a Inner Join BranchSale as b on a.BranchID=B.BranchID
group by branchdesc
order by Amount DESC
注意语句里的 between ‘20141001’ and '20141223' 这个时间段 以这个时间段查询出的数据中有一行，如图
如果改条件 between '20141220' and '20141223' 查询出的数据完全正常
经过反复的测试这两个间隔天数值不能超过4，超过4就会出现查询数据不全。
各位老大遇到过这个问题没有？ sql server 版本已打印出！
------解决思路----------------------
目测是这个20141220时间点以前的某个栏位数据有问题。。你可以分步把结果查来看看就知了
------解决思路----------------------
不知道你的数据结构,试试把
And Convert(varchar(12),A.WorkDate,112) Between '20141001' and '20141223'
改成
And Ltrim(Convert(varchar(12),A.WorkDate,112)) Between '20141001' and '20141223'