sql 月库存查询有关问题_Sql Server

sql 月库存查询问题

compid是项目部 item-no是材料名称 item-num 是结存库存量请问如何求的任意月中每中材料的库存量

每种材料的本月结存是各个项目部最大日期的这笔材料的和

求大牛帮忙

------解决方案--------------------

SQL code

select item_no,convert(varchar(7),make_date,120),sum(item_num) from(select * from tb a where not exists(select 1 from tb where comp_id=a.comp_id and item_no=a.item_no and convert(varchar(7),make_date,120)=convert(varchar(7),a.make_date,120) and make_date>a.make_date))t group by item_no,convert(varchar(7),make_date,120)
------解决方案--------------------
SQL codeselect a.item-no,sum(item-num) 'item-num'from tab ainner join(select compid,item-no,max(Make_date) maxMake_datefrom tabwhere datepart(m,Make_date)=[指定的月]group by compid,item-no) bon a.compid=b.compid and a.item-no=b.item-no and a.Make_date=b.maxMake_dategroup by a.item-no