/* select from inv1 T3 */
declare @Date1 as datetime
set @Date1=/* T3.docdate */ '[%0]'
declare @Date2 as datetime
set @Date2=/* T3.docdate */ '[%1]'
declare @whs as nvarchar(20)
set @whs=/* T3.whscode */ '[%2]'
select T0.docEntry,T0.itemcode,T0.dscription,T0.whscode,T0.Price,sum(T0.Quantity) as 'SQ',T0.StockPrice
into #Temp1
From inv1 T0 inner join oinv T1 on T0.DocEntry=T1.DocEntry
where (T1.U_docdate<@Date2 or @Date2='') and (T1.U_docdate>@Date1 or @Date1='')
and (T0.whscode=@whs or @whs='0')
group by T0.docEntry,T0.itemcode,T0.dscription,T0.Price,T0.StockPrice ,T0.whscode select T0.baseEntry,T0.Itemcode,sum(T0.Quantity) as 'RQ'
into #Temp2
from RIN1 T0 group by T0.baseEntry,T0.Itemcode select T0.DocEntry as '定单号',T0.ItemCode as '物料编号',T0.dscription as '物料名称',T3.Suppcatnum as '生产厂家',
isnull(T0.SQ,0)-isnull(T2.RQ,0) as '销售数量', T0.price as '单价',T0.Price*(isnull(T0.SQ,0)-isnull(T2.RQ,0)) as '总金额',
T0.stockPrice as '成本价',(T0.price-T0.stockPrice)*(isnull(T0.SQ,0)-isnull(T2.RQ,0)) as '毛利',
case when T0.Price=0 then '0' else
(T0.price-T0.stockPrice)/T0.Price*100 end as '毛利率'
From #Temp1 T0 left join #temp2 T2 on T0.Itemcode=T2.Itemcode and T0.docEntry=T2.baseEntry left join oitm T3 on T0.itemcode=T3.itemcode
and itemcode<>'1000001'
drop table #temp1,#Temp2
declare @Date1 as datetime
set @Date1=/* T3.docdate */ '[%0]'
declare @Date2 as datetime
set @Date2=/* T3.docdate */ '[%1]'
declare @whs as nvarchar(20)
set @whs=/* T3.whscode */ '[%2]'
select T0.docEntry,T0.itemcode,T0.dscription,T0.whscode,T0.Price,sum(T0.Quantity) as 'SQ',T0.StockPrice
into #Temp1
From inv1 T0 inner join oinv T1 on T0.DocEntry=T1.DocEntry
where (T1.U_docdate<@Date2 or @Date2='') and (T1.U_docdate>@Date1 or @Date1='')
and (T0.whscode=@whs or @whs='0')
group by T0.docEntry,T0.itemcode,T0.dscription,T0.Price,T0.StockPrice ,T0.whscode select T0.baseEntry,T0.Itemcode,sum(T0.Quantity) as 'RQ'
into #Temp2
from RIN1 T0 group by T0.baseEntry,T0.Itemcode select T0.DocEntry as '定单号',T0.ItemCode as '物料编号',T0.dscription as '物料名称',T3.Suppcatnum as '生产厂家',
isnull(T0.SQ,0)-isnull(T2.RQ,0) as '销售数量', T0.price as '单价',T0.Price*(isnull(T0.SQ,0)-isnull(T2.RQ,0)) as '总金额',
T0.stockPrice as '成本价',(T0.price-T0.stockPrice)*(isnull(T0.SQ,0)-isnull(T2.RQ,0)) as '毛利',
case when T0.Price=0 then '0' else
(T0.price-T0.stockPrice)/T0.Price*100 end as '毛利率'
From #Temp1 T0 left join #temp2 T2 on T0.Itemcode=T2.Itemcode and T0.docEntry=T2.baseEntry left join oitm T3 on T0.itemcode=T3.itemcode
and itemcode<>'1000001'
drop table #temp1,#Temp2
解决方案 »
- 在MSSQL2005的维护计划里,能否往MYSQL数据库表里写入记录?
- 求采购未交的SQL语句!
- 插入时,条件中包含对表格自身的统计作息可不可以
- 创建SQL server认证登录用户的问题
- 表A ---10万记录,表B ---100万记录,哪种查询快?
- 再问一个SQL语句
- 请大家来讨论一下SQL里的错误处理
- 请问:我要做一个页面用来从sql server里面调出数据,如何能让实现定时对数据库进行查询,以刷新页面中的数据呢?
- 如何使用SQL语句查询一个表中 第m条-第n条 的一个记录段?(m<n)
- 小问题,但我不会,各位兄弟指教!
- C#如何执行这条SQL语句str,以实现恢复数据库:str = @"RESTORE DATABASE tDB ......
- 求教一个触发器写法
union all
...可以外加一个表表达式..
另外添下报错信息