Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Prometeo
Starting Member
3 Posts |
 Posted - 2003-08-12 : 18:57:41
|
| Tobj_key File Date Qty Rej_Qty TechR_Qty WorkR_QtyFault Process S#147279 104620 14-Apr NULL NULL NULL NULL NULL Assembly 01153722 104620 15-Apr NULL NULL NULL NULL Tech TXRX_Test 01153722 104620 15-Apr NULL NULL NULL NULL Tech TXRX_Test 01 153722 104620 15-Apr NULL NULL NULL NULL Work TXRX_Test 01153992 104620 10-Jul NULL NULL NULL NULL NULL Debug 01 243515 104620 15-Jul NULL NULL NULL NULL NULL 2nd_Assy 01244506 104620 16-Jul NULL NULL NULL NULL NULL Test 01246409 104620 21-Jul NULL NULL NULL NULL NULL Cycling 01246431 104620 21-Jul NULL NULL NULL NULL NULL CloseUnit 01 Hello All,my scenario is this--Above, I copied a sample of the table for one S#--what I need to do is select or UPDATE the table such that I assign a Qty of 1 to each unique process ( the problem is with TXRX_Test)has three rows Also, whenever Fault is not NULL (Tech or Work) assign 1 to the Columns TechR_Qty or WorkR_Qty respectively (But only 1 for each individual process) Basically want I'm looking is the result table to look like this: Is this possible to do in one query?Thanks.Tobj_key File Date Qty Rej_Qty TechR_Qty WorkR_QtyFault Process S#147279 104620 14-Apr 1 NULL NULL NULL NULL Assembly 01153722 104620 15-Apr 1 1 1 NULL Tech TXRX_Test 01153722 104620 15-Apr NULL NULL NULL NULL Tech TXRX_Test 01 153722 104620 15-Apr NULL NULL NULL 1 Work TXRX_Test 01153992 104620 10-Jul 1 NULL NULL NULL NULL Debug 01 243515 104620 15-Jul 1 NULL NULL NULL NULL 2nd_Assy 01244506 104620 16-Jul 1 NULL NULL NULL NULL Test 01246409 104620 21-Jul 1 NULL NULL NULL NULL Cycling 01246431 104620 21-Jul 1 NULL NULL NULL NULL CloseUnit 01 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-12 : 19:02:53
|
| It is very hard to read the data. Could you provide the DDL (CREATE TABLE statement) for your table as well as the DML (INSERT statements) to get the data into the table so that we can test?Tara |
 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-08-12 : 19:09:12
|
| More information is needed here; questions:1) Why did Rej_Qty get updated with '1' for row #2 in the example2) Why did row #2 get updated with new values for the Qty and TechR_Qty columns as opposed to row #3? They are identical in all respects. Is it because row #2 comes "before" row #3 (I used quotes because row position terms like "before" and "after" have no real meaning in relational databases).Jonathan{0} |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-08-12 : 20:08:00
|
| You want a '1' in the Qty column, and when Fault is not null then 1 in Rej_Qty and TechR_Qty but only for each DISTINCT process. Return all rows, without altering the row content of the duplicate process rows.Start by selecting a unique row for each processSELECT MIN(Tobj_key) as MyFlag FROM MyTable GROUP BY ProcessNow this result can be used to identify specific rows where Qty should be 1 like this:SELECT Tobj_key, File, Date, CASE WHEN MyFlag IS NOT NULL THEN 1 ELSE Qty END as Qty, CASE WHEN WorkR_QtyFlag IS NOT NULL AND MyFlag IS NOT NULL THEN 1 ELSE Rej_Qty END as Rej_Qty , CASE WHEN WorkR_QtyFlag IS NOT NULL AND MyFlag IS NOT NULL THEN 1 ELSE TechR_Qty END as TechR_Qty , WorkR_QtyFault, Process, S#FROM MyTable MTLEFT OUTER JOIN ( SELECT MIN(Tobj_key) as MyFlag FROM MyTable GROUP BY Process) F ON F.Tobj_Key = MT.Tobj_KeySam |
|
|
|
|
|
|
|
|
Tricky Query