select a.产品代号, a.产品名称, a.产品规格,
b.期初结存数量,c.本期进库数量,c.本期出库数量,
isnull(b.期初结存数量,0)+isnull(c.本期进库数量,0)-isnull(c.本期出库数量) as 期未结存
from table2 a left join
(select 产品代号,sum(数量) as 期初结存数量 from table1
group by 产品代号 where convert(char(8),时间,112)<@cdt1) b
on a.产品代号=b.产品代号
left join
(select 产品代号,sum(case when 数量>=0 then 数量 else 0 end ) as 本期进库数量,sum(case when 数量<0 then abs(数量) else 0 end ) as 本期本期出库数量 from table1
group by 产品代号 where convert(char(8),时间,112) between @cdt1 and @cdt2) c
on a.产品代号=b.产品代号
order by a.产品代号
------------------------------------------
@cdt1,@cdt2 是形如‘19991231’的char型时间条件
b.期初结存数量,c.本期进库数量,c.本期出库数量,
isnull(b.期初结存数量,0)+isnull(c.本期进库数量,0)-isnull(c.本期出库数量) as 期未结存
from table2 a left join
(select 产品代号,sum(数量) as 期初结存数量 from table1
group by 产品代号 where convert(char(8),时间,112)<@cdt1) b
on a.产品代号=b.产品代号
left join
(select 产品代号,sum(case when 数量>=0 then 数量 else 0 end ) as 本期进库数量,sum(case when 数量<0 then abs(数量) else 0 end ) as 本期本期出库数量 from table1
group by 产品代号 where convert(char(8),时间,112) between @cdt1 and @cdt2) c
on a.产品代号=b.产品代号
order by a.产品代号
------------------------------------------
@cdt1,@cdt2 是形如‘19991231’的char型时间条件
table2=>产品库存表
1001 aa 1 2002-04-09 -2 200204091002
怎么能得到结果1001 aa 1 100 9 5 104 100可能是你忘了写,但这个5从那里来
好好看一下狼兄的语句,结果里有的地方出现NULL就加一些ISNULL(...,0),应该能解决你的问题的。
Server: Msg 174, Level 15, State 1, Line 2
The isnull function requires 2 arguments.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'where'.
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'where'.
a.数量 as 期初结存数量,isnull(c.本期进库数量) as 本期进库数量,isnull(c.本期出库数量) as 本期出库数量,
a.数量+isnull(c.本期进库数量,0)-isnull(c.本期出库数量,0) as 期未结存
from table2 a left join
(select 产品代号,sum(case when 数量>=0 then 数量 else 0 end ) as 本期进库数量,sum(case when 数量<0 then abs(数量) else 0 end ) as 本期本期出库数量 from table1
group by 产品代号 where convert(char(8),时间,112) between @cdt1 and @cdt2) c
on a.产品代号=b.产品代号
order by a.产品代号
这种类型的报表在速达上有的!
isnull(b.期初结存数量,0) as 期初结存数量,isnull(c.本期进库数量,0) as 本期进库数量,isnull(c.本期出库数量,0) as 本期出库数量,
isnull(b.期初结存数量,0)+isnull(c.本期进库数量,0)-isnull(c.本期出库数量,0) as 期未结存
from table2 a left join
(select 产品代号,sum(数量) as 期初结存数量 from table1
where convert(char(8),时间,112)<@cdt1 group by 产品代号 ) b
on a.产品代号=b.产品代号
left join
(select 产品代号,sum(case when 数量>=0 then 数量 else 0 end ) as 本期进库数量,sum(case when 数量<0 then abs(数量) else 0 end ) as 本期本期出库数量 from table1
where convert(char(8),时间,112) between @cdt1 and @cdt2 group by 产品代号 ) c
on a.产品代号=b.产品代号
order by a.产品代号我的也跟着错了,应该改为:select a.产品代号, a.产品名称, a.产品规格,
a.数量 as 期初结存数量,isnull(c.本期进库数量) as 本期进库数量,isnull(c.本期出库数量) as 本期出库数量,
a.数量+isnull(c.本期进库数量,0)-isnull(c.本期出库数量,0) as 期未结存
from table2 a left join
(select 产品代号,sum(case when 数量>=0 then 数量 else 0 end ) as 本期进库数量,sum(case when 数量<0 then abs(数量) else 0 end ) as 本期本期出库数量 from table1
where convert(char(8),时间,112) between @cdt1 and @cdt2 group by 产品代号 ) c
on a.产品代号=c.产品代号
order by a.产品代号
group by code
where convert(char(8),stock_date,112)<'20010401'执行时为什么会有以下错误:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'where'.stock_date是datetime型的数据
where convert(char(8),stock_date,112)<'20010401'
group by code以上回复已修改这个错误。