本人工作单位是一家橡塑产品制造企业,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] [3] 下一页 [电脑应用]教你如何远程管理MSSQL数据库 [办公软件]在sybase中插入图片、PDF、文本文件 [办公软件]安装Sybase ASE [办公软件]linux指令大全(完整篇) [办公软件]Linux新手入门常用命令大全 [办公软件]在RedHat Linux 9里安装gaim0.80 [办公软件]浅谈Linux 下Java 1.5 汉字方块问题解决方法 [办公软件]Linux程序员必读:中文化与GB18030标准 [办公软件]linux指令大全 [办公软件]制作Linux启动盘的四种方法
|