|
本人工作单位是一家橡塑产品制造企业，05年2月单位领导要求我开发一个生产流程数据核对表，用来稽核流程数据的准确性，其报表基本原理如下：
后续工序今日正品数 + 后续工序今日废品数 = 本工序今日正品数 + 本工序今日留存数 - 本工序昨日留存数
了解了原理后马上开工，第一次考虑用临时表：
select distinct LEFT(productid, 4) + ''''00'''' as productid into #productid from yuancaiFlow_view
where productiondate = ''''2004-3-1''''
and productid like ''''01____''''
go
select a.productid,
b.productname,
isnull(hj.GoodQty, 0) as hj_good,
isnull(hjtoday.qty, 0) as hj_today,
isnull(hjyesterday.qty, 0) hj_yesterday,
isnull(hj.GoodQty, 0) - isnull(hjtoday.qty, 0) + isnull(hjyesterday.qty, 0) - isnull(sx.GoodQty, 0) - isnull(sx.BadQty, 0) as hj_difference,
isnull(sx.GoodQty, 0) as sx_good,
isnull(sx.BadQty, 0) as sx_bad,
isnull(sxtoday.qty, 0) as sx_today,
isnull(sxyesterday.qty, 0) sx_yesterday,
isnull(sx.GoodQty, 0) - isnull(sxtoday.qty, 0) + isnull(sxyesterday.qty, 0) - isnull(jt.GoodQty, 0) - isnull(jt.BadQty, 0) as sx_difference,
isnull(jt.GoodQty, 0) as jt_good,
isnull(jt.BadQty, 0) as jt_bad,
isnull(jttoday.qty, 0) as jt_today,
isnull(jtyesterday.qty, 0) jt_yesterday,
isnull(jt.GoodQty, 0) - isnull(jttoday.qty, 0) + isnull(jtyesterday.qty, 0) - isnull(ph.GoodQty, 0) - isnull(ph.BadQty, 0) as jt_difference,
isnull(ph.GoodQty, 0) as ph_good,
isnull(ph.BadQty, 0) as ph_bad,
isnull(phtoday.qty, 0) as ph_today,
isnull(phyesterday.qty, 0) ph_yesterday,
isnull(ph.GoodQty, 0) - isnull(phtoday.qty, 0) + isnull(phyesterday.qty, 0) - isnull(zj.GoodQty, 0) - isnull(zj.BadQty, 0) as ph_difference,
isnull(zj.GoodQty, 0) as zj_good,
isnull(zj.BadQty, 0) as zj_bad
from #productid as a,
finishedproduct as b,
yuancaiFlow_view as hj,
basflowsubsistence as hjtoday,
basflowsubsistence as hjyesterday,
yuancaiFlow_view as sx,
basflowsubsistence as sxtoday,
basflowsubsistence as sxyesterday,
yuancaiFlow_view as jt,
basflowsubsistence as jttoday,
basflowsubsistence as jtyesterday,
yuancaiFlow_view as ph,
basflowsubsistence as phtoday,
basflowsubsistence as phyesterday,
yuancaiFlow_view as zj
where a.productid = b.productid
--hj
and hj.productiondate = ''''2004-3-1''''
and left(a.productid, 4) *= left(hj.productid, 4)
and hj.productid like ''''____06''''
and left(a.productid, 4) + ''''06'''' *= hjtoday.productid
and hjtoday.checkdate = ''''2004-3-1''''
and left(a.productid, 4) + ''''06'''' *= hjyesterday.productid
and hjyesterday.checkdate = ''''2004-2-29''''
--sx
and sx.productiondate = ''''2004-3-1''''
and left(a.productid, 4) *= left(sx.productid, 4)
and sx.productid like ''''____08''''
and left(a.productid, 4) + ''''08'''' *= sxtoday.productid
and sxtoday.checkdate = ''''2004-3-1''''
and left(a.productid, 4) + ''''08'''' *= sxyesterday.productid
and sxyesterday.checkdate = ''''2004-2-29''''
--jt
and jt.productiondate = ''''2004-3-1''''
and left(a.productid, 4) *= left(jt.productid, 4)
and jt.productid like ''''____07''''
and left(a.productid, 4) + ''''07'''' *= jttoday.productid
and jttoday.checkdate = ''''2004-3-1''''
and left(a.productid, 4) + ''''07'''' *= jtyesterday.productid
and jtyesterday.checkdate = ''''2004-2-29''''
--ph
and ph.productiondate = ''''2004-3-1''''
and left(a.productid, 4) *= left(ph.productid, 4)
and ph.productid like ''''____09''''
and left(a.productid, 4) + ''''09'''' *= phtoday.productid
and phtoday.checkdate = ''''2004-3-1''''
and left(a.productid, 4) + ''''09'''' *= phyesterday.productid
and phyesterday.checkdate = ''''2004-2-29''''
--zj
and zj.productiondate = ''''2004-3-1''''
and left(a.productid, 4) *= left(zj.productid, 4)
and zj.productid like ''''01__11''''
go
drop table #productid
go
准备用ASP呈现报表，结果ASP端的Recordset老提示出错。只好改用函数：
-- =============================================
-- Create table function (TF)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N''''fn_GetGLSubsistenceList'''')
DROP FUNCTION fn_GetGLSubsistenceList
GO
CREATE FUNCTION fn_GetGLSubsistenceList
(@CheckDate smalldatetime)
RETURNS @FlowSubsistenceList TABLE
(ProductID nvarchar(6),
ProductName nvarchar(32),
HJGoodQty real,
HJTodayQty real,
HJYesterdayQty real,
HJDefference real,
SXGoodQty real,
SXBadQty real,
SXTodayQty real,
SXYesterdayQty real,
SXDefference real,
JTGoodQty real,
JTBadQty real,
JTTodayQty real,
JTYesterdayQty real,
JTDefference real,
PHGoodQty real,
PHBadQty real,
PHTodayQty real,
PHYesterdayQty real,
PHDefference real,
ZJGoodQty real,
ZJBadQty real)
AS
BEGIN
DECLARE @ProductIDList TABLE
&n [1] [2] 下一页 |
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 