asp+| programming
I work unit is a rubber and plastic products manufacturing enterprises, February 05 unit leaders asked me to develop a production process data checklist, used to audit the accuracy of the process data, the basic principles of its statements are as follows:
Follow-up process today's number of products + follow-up process today's waste number = Today's number of authentic products + this process today retention number-this operation yesterday retained
After understanding the principle immediately starts, considers the temporary table for the first time:
SELECT distinct left (ProductID, 4) + ' as ProductID to #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) + ' *= ' Hjtoday.productid
and hjtoday.checkdate = ' 2004-3-1 '
and left (A.productid, 4) + ' *= ' 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) + ' *= ' Sxtoday.productid
and sxtoday.checkdate = ' 2004-3-1 '
and left (A.productid, 4) + ' *= ' 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) + ' modified ' *= Jttoday.productid
and jttoday.checkdate = ' 2004-3-1 '
and left (A.productid, 4) + ' modified ' *= Jtyesterday.productid
and jtyesterday.checkdate = ' 2004-2-29 '
--ph
and Ph.productiondate = ' 2004-
