My work unit is a rubber-plastic product manufacturing enterprise. In February 2005, the leaders of the unit demanded that I developed a production process data checklist, the accuracy of the audit process data, the basic principles of the report were as follows:
Follow-up today's authentic number subsequent process Today's number of waste = this process is today's genuine number this process is reissued today - this process remains yesterday
After understanding the principles, start working immediately, and consider the temporary table for the first time:
select distinct LEFT (productid, 4) '00' as productid into #productid from yuancaiFlow_viewwhere productiondate = '2004-3-1'and productid like' 01 ____ 'goselect 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_DIFMERENCE, ISNULL (S.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_DIFMERENCE, ISNULL (Ph.GoodQty, 0) AS PH_GOOD, ISNULL (PH. BadQty, 0) As pH_bad, isnull (phoday.qty, 0) as pH_today.Qty, 0) ph_yesterday, isnull (ph.goodqty, 0) - isnull (phthoday.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' * = hjyest Erday.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.productidid 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 '* = phoday.productid and phocoday.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' godrop table #ProductIDGO Preparation with ASP Rendering Report The result of the Recordset old reminder of the ASP end. I have to use the function:
- ============================================= - Create Table function (TF) - ============================================ == if exists (SELECT * from sysobjects where name = n'fn_getglsubsistencelist ') Drop Function FN_GETGLSUBSISTENCELISTGO
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) ASBEGIN DECLARE @ProductIDList TABLE (ProductID nvarchar (6)) DECLARE @YesterdayOfCheckDate smalldatetime SET @YesterdayOfCheckDate = DATEADD (day, -1, @CheckDate) - save the date of production of the product ID number INSERT @ProductIDList select distinct LEFT (productid, 4) '00' as productid from yua Ncaiflow_view where produter = @Checkdate and productidIke '01____'
- Normal process is hj-> sx-> jt-> ph-> zj INSERT @flowsubsistencelist select 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, 0 - (isnull (hj.goodqty, 0) - isnull (hjtoday.qty, 0) isnull (hjyesterday.qty, 0) - isnull (sx.goodqty, 0) - Isnull (S.Badqty, 0)) AS HJ_DIFMERENCE, 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, 0 - (ISNULL (SX.GoodQty, 0) - Isnull (SXTODAY.QTY, 0) ISNULL (SxyesterDay.Qty, 0) - Isnull (Jt.goodQty, 0) - Isnull (JT.Badqty , 0) AS SX_DIFMERENCE, 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, 0 - (ISNULL (Jt.GoodQty, 0) - Isnull (JTTODAY.QTY, 0) ISNULL (JTYesterDay.Qty, 0) - Isnull (Ph.good Qty, 0) - Isnull (ph.badqty, 0)) AS JT_DIFMERENCE, ISNULL (Ph.GoodQty, 0) AS Ph_good, ISNULL (Ph.Badqty, 0) as pH_bad, isnull (phoday.qty, 0) as pH_Today, Isnull (phyesterday.qty, 0) Ph_yesterday, 0 - (isnull (ph.goodqty, 0) - isnull (phoday.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 @
ProductIDList 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 = @CheckDate 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 = @CheckDate and left (a.productid, 4) '06' * = hjyesterday.productid and hjyesterday. Checkdate = @YesterDayOfcheckdate --sx and sproductionDate = @Checkdate 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 = @CheckDate and left (a.productid, 4) '08' * = sxyesterday.productid and sxyesterday.checkdate = @YesterdayOfCheckDate --jt and jt.productiondate = @CheckDate 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 = @
CheckDate and left (a.productid, 4) '07' * = jtyesterday.productid and jtyesterday.checkdate = @YesterdayOfCheckDate --ph and ph.productiondate = @CheckDate 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 = @CheckDate and left (a.productid, 4) '09 '* = phyesterday.productid and phyesterday.checkdate = @YesterdayOfCheckDate --zj and zj.productiondate = @CheckDate and left (a.productid, 4) * = left (zj.productid, 4) and zj.productid like' 01__11 ' The RETURN Endgoasp end is unpacked in a way to the SQL end:
Dim rsCheckListSet rsCheckList = Server.CreateObject ( "ADODB.Recordset") rsCheckList.ActiveConnection = oConnrsCheckList.Open "select * from dbo.fn_GetGLSubsistenceList ( '" & datCheckDate & "')"
This programming practice makes me get a new idea for ASP development: use view function package database end, ASP ends only call function, so the biggest benefit is that the platform transplant is more convenient, and the ASP programmer's database knowledge requirements Reduced.
