Database

mrfalk wrote:

one production time fix and won't be running in production

PIEBALDconsult wrote:

So is this how the responder writes production code when it matters

PIEBALDconsult wrote:

Perfect practice makes perfect. Do it the right way every time.

PIEBALDconsult wrote:

I see too much bad SQL being written. It should be nipped in its bud.

PIEBALDconsult wrote:

Won't that rescan the table/index for each id?

Michael Potter wrote:

increasing the code complexity

Michael Potter wrote:

lower the lookups by a 5th

PIEBALDconsult wrote:

may think that the provided code is good for all situations.

PIEBALDconsult wrote:

If you're going to teach a man to fish, do it right. Sure, in a pinch you can use a shotgun, but you shouldn't be teaching newbies to fish with a shotgun.

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;