Database

PIEBALDconsult wrote:

If you're going to teach a man to fish, do it right

UPDATE BEN
SET Start_date=B.Start_date
FROM BEN A
INNER JOIN 
(
  SELECT EmpId
  , Start_date 
  FROM BEN 
  WHERE Ben_code=1
) B
ON A.EmpId=B.EmpID
WHERE A.Ben_code<>1 -- optional

Name     Part     Passed     Finished     Scrap
-----------------------------------------------
John     P-ONE    8          5            7
Jane     P-ONE    4          6            10
Jane     P-TWO    9          2            3
Jack     P-THREE  10         12           6

SELECT UserId,ProductNum,Passed,Finished,Scrapped
FROM
(SELECT UserId, ProductNum, Qty, 'passed' as status
FROM    Passed
UNION
SELECT UserId, ProductNum, Qty, 'finished' as status
FROM   finished
UNION
SELECT UserId, ProductNum, Qty, 'scrapped' as status
FROM   scrapped
) AS SourceTable
PIVOT
(
sum(qty)
FOR status IN ('passed', 'finished','scrapped')
) AS PivotTable;

Select Name, Part, 1 Passed,0 Finished,0 Scrap
from PassedTable
UNION 
Select Name, Part, 0 Passed,1 Finished,0 Scrap
from FinishedTable
UNION
Select Name, Part, 0 Passed,0 Finished,1 Scrap
from ScrapTable

Matt U. wrote:

How is it not normalized

UserId
ProductNum
Status
Qty

CREATE VIEW Finished AS
 SELECT UserId,ProductNum,Qty
 FROM mytable
 WHERE status = 'finished'

Mycroft Holmes wrote:

SQL

Select Name, Part, 1 Qty Passed,0 Finished,0 Scrap
from PassedTable
UNION 
Select Name, Part, 0 Passed,1 Qty Finished,0 Scrap
from FinishedTable
UNION
Select Name, Part, 0 Passed,0 Finished,1 Qty Scrap
from ScrapTable