insert into ckrjz
select CGMX.CPBH,Convert(varchar(10),KCRKD.RKRQ,111),sum(CGMX.jhs) --设jhs在表CGMX中
from cgmx
inner join CGYSD on CGYSD.DDBH=CGMX.DDBH
inner join KCRKD on KCRKD.JHDH=CGYSD.YSDH
where Convert(varchar(10),KCRKD.RKRQ,111)='指定日期' --格式'YYYY/MM/DD'
group by CGMX.CPBH,Convert(varchar(10),KCRKD.RKRQ,111)
select CGMX.CPBH,Convert(varchar(10),KCRKD.RKRQ,111),sum(CGMX.jhs) --设jhs在表CGMX中
from cgmx
inner join CGYSD on CGYSD.DDBH=CGMX.DDBH
inner join KCRKD on KCRKD.JHDH=CGYSD.YSDH
where Convert(varchar(10),KCRKD.RKRQ,111)='指定日期' --格式'YYYY/MM/DD'
group by CGMX.CPBH,Convert(varchar(10),KCRKD.RKRQ,111)
select A.CPBH, '2003-06-06', count(*) as JHS from CGMX A
left join CGYSD B on A.DDBH = B.DDBH
left join KCRKD C on B.YSDH = C.YSDH
where convert(char(10), C.RKRQ, 120) = '2003-06-06'
group by A.CPBH
select A.CPBH, '2003-06-06', sum(A.JHS) as JHS from CGMX A
left join CGYSD B on A.DDBH = B.DDBH
left join KCRKD C on B.YSDH = C.YSDH
where convert(char(10), C.RKRQ, 120) = '2003-06-06'
group by A.CPBH
SELECT a.CPBH,'日期', SUM(a.DHSL)
FROM CGMX a LEFT JOIN CGYSD b ON a.DDBH=b.DDBH
LEFT JOIN KCRKD c ON b.YSDH=c.JHDH
WHERE convert ( char(8),c.RKRQ,112)='日期'
GROUP BY a.CPBH
--你的表缺少实际收货数量字段
合理的应该是
采购明细表CGMX(编号,订单编号DDBH,产品编号CPBH,订货数量DHSL)
再考虑仔细的话,如果实际收货数量和订货数量不一致,就要在验收表上加上实际收货数量字段
统计时取实际收获数量
Select CPBH, @TheDay, Count(编号) From CGMX
Where DDBH in (Select Distinct DDBH From CGYSD
Where YSDH IN (Select JHDH From KCRKD
Where RKRQ = @TheDay
)
)
Group By CPBH
--注:您的数据库可能有这样的问题
--你得保证同一张订单的相关业务在同一张验收单上完成。
--否则将有可能出现重复计数的情况。