none
Copiar Datos de unas celdas a otras celdas en la misma tabla. RRS feed

  • Pregunta

  • cuando la columna DefectDesc diga Retest y en el siguiente registro tenga datos capturados por un diagnostico, copiar los datos que se capturaron a la fila donde dice Retest.
    viernes, 18 de octubre de 2019 1:25

Respuestas

  • Hola Giovani Silonzochilt:

    Espero haberte entendido, porque no me ha quedado muy claro lo que tú esperas como siguiente.

    He metido el resultado de tu select en una variable de Tabla para poder trabajar mucho más fácil.

    Dejo primero el código de la variable de tabla y de la inserción de la misma, para que pueda servir también a quien quiera aportar o cambiar algo.

    declare @seriales table 
    (
    	serialNumber varchar(20), 
    	StationType varchar(20), 
    	Station varchar(100),
    	TestStatus varchar(15), 
    	TestGeneration int, 
    	TestTime datetime, 
    	DefectDesc varchar(10), 
    	DefectDesc2 varchar(100),
    	DefectCode varchar(100), 
    	Location varchar(100), 
    	Comment varchar(100), 
    	Debug DateTime, 
    	DebugUser varchar(100)
    )
    insert into @seriales
    (serialNumber,
     StationType,
     Station,
     TestStatus,
     TestGeneration,
     TestTime,
     DefectDesc,
     DefectDesc2,
     DefectCode,
     Location,
     Comment,
     Debug,
     DebugUser
    )
    VALUES
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'FAIL',3,'20191017 14:20:47',
    	NULL,'Missing Comp.'			,'Comp. Faltante'	,'R505' ,
    	'Pad volado'												 ,'20191017 16:31:45','gdjjuvaz [Juan Vazquez]'),
    	-- fila 1
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'FAIL',3,'20191017 14:20:47',
    	NULL,'Missing Comp.'			,'Comp. Faltante'	,'RE29' ,
    	'Pad volado'												 ,'20191017 16:31:45','gdjjuvaz [Juan Vazquez]'),
    	-- fila2
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'FAIL',3,'20191017 14:20:47',
    	NULL,'Physically Damaged Comp.','Comp. Daño Fisico','RE28' ,
    	''															 ,'20191017 16:31:45','gdjjuvaz [Juan Vazquez]'),
    	-- fila3
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'PASS',3,'20191015 22:04:49',
    	NULL,'Passed by Retest'		,NULL				,NULL	,
    	NULL														 ,NULL				 ,NULL					   ),
    	-- fila4
    ('SUB0602G01HS','F5-VALIDATION-REPAIR','F5-VALIDATION-REPAIR (MDS)','PASS',3,'20191015 21:51:29',
    	NULL,'Passed by Retest'		,NULL				,NULL	,
    	NULL														 ,NULL				 ,NULL					   ),
    	-- fila5
    ('SUB0602G01HS','F5-ESS'			  ,'F5-ESS (TOP-SUBASSY)'	   ,'FAIL',3,'20191014 17:55:19',
    	NULL,'Pin Doblado - Bent Pin'	,'Com. 13'			,'J6'	,
    	'pin ddr3_mem_a[12] (j6.174) esta doblado, cambiar conector','20191015 01:38:32','1095345 [Oscar Herrera]'),
    	-- fila6
    ('SUB0602G01HS','F5-ESS'			  ,'F5-ESS (TOP-SUBASSY)'	   ,'FAIL',3,'20191014 16:54:26',
    	NULL,'Retest'					,NULL				,nULL	,
    	NULL	,NULL				 ,NULL					   ),
    	-- fila7
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'PASS',3,'20191014 08:31:42',
    	NULL,'Passed by Retest'		,NULL				,NULL	,
    	NULL														 ,NULL				 ,NULL					   );
    	-- fila8
    

    Adicionalmente he cambiado la forma en que pones las fechas, por el estándar yyyyMMdd

    La query queda así. Ojo que va junto a lo que ya te he pegado

    declare @seriales table 
    (
    	serialNumber varchar(20), 
    	StationType varchar(20), 
    	Station varchar(100),
    	TestStatus varchar(15), 
    	TestGeneration int, 
    	TestTime datetime, 
    	DefectDesc varchar(10), 
    	DefectDesc2 varchar(100),
    	DefectCode varchar(100), 
    	Location varchar(100), 
    	Comment varchar(100), 
    	Debug DateTime, 
    	DebugUser varchar(100)
    )
    insert into @seriales
    (serialNumber,
     StationType,
     Station,
     TestStatus,
     TestGeneration,
     TestTime,
     DefectDesc,
     DefectDesc2,
     DefectCode,
     Location,
     Comment,
     Debug,
     DebugUser
    )
    VALUES
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'FAIL',3,'20191017 14:20:47',
    	NULL,'Missing Comp.'			,'Comp. Faltante'	,'R505' ,
    	'Pad volado'												 ,'20191017 16:31:45','gdjjuvaz [Juan Vazquez]'),
    	-- fila 1
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'FAIL',3,'20191017 14:20:47',
    	NULL,'Missing Comp.'			,'Comp. Faltante'	,'RE29' ,
    	'Pad volado'												 ,'20191017 16:31:45','gdjjuvaz [Juan Vazquez]'),
    	-- fila2
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'FAIL',3,'20191017 14:20:47',
    	NULL,'Physically Damaged Comp.','Comp. Daño Fisico','RE28' ,
    	''															 ,'20191017 16:31:45','gdjjuvaz [Juan Vazquez]'),
    	-- fila3
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'PASS',3,'20191015 22:04:49',
    	NULL,'Passed by Retest'		,NULL				,NULL	,
    	NULL														 ,NULL				 ,NULL					   ),
    	-- fila4
    ('SUB0602G01HS','F5-VALIDATION-REPAIR','F5-VALIDATION-REPAIR (MDS)','PASS',3,'20191015 21:51:29',
    	NULL,'Passed by Retest'		,NULL				,NULL	,
    	NULL														 ,NULL				 ,NULL					   ),
    	-- fila5
    ('SUB0602G01HS','F5-ESS'			  ,'F5-ESS (TOP-SUBASSY)'	   ,'FAIL',3,'20191014 17:55:19',
    	NULL,'Pin Doblado - Bent Pin'	,'Com. 13'			,'J6'	,
    	'pin ddr3_mem_a[12] (j6.174) esta doblado, cambiar conector','20191015 01:38:32','1095345 [Oscar Herrera]'),
    	-- fila6
    ('SUB0602G01HS','F5-ESS'			  ,'F5-ESS (TOP-SUBASSY)'	   ,'FAIL',3,'20191014 16:54:26',
    	NULL,'Retest'					,NULL				,nULL	,
    	NULL	,NULL				 ,NULL					   ),
    	-- fila7
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'PASS',3,'20191014 08:31:42',
    	NULL,'Passed by Retest'		,NULL				,NULL	,
    	NULL														 ,NULL				 ,NULL					   );
    	-- fila8
    
    
    ;with cte as (
    	select	
    			row_number() over (partition by serialNumber order by testTime desc) as row,
    			serialNumber,
    			StationType,
    			Station,
    			TestStatus,
    			TestGeneration,
    			TestTime,
    			DefectDesc,
    			DefectDesc2,
    			DefectCode,
    			LEAD(DefectCode,1)over(partition by serialNumber order by testtime desc) as DefectCodeSig,
    			Location,
    			LEAD(Location,1)over(partition by serialNumber order by testtime desc) as LocationSig,
    			Comment,
    			LEAD(Comment,1)over(partition by serialNumber order by testtime desc) as CommentSig,
    			Debug,
    			LEAD(Debug,1)over(partition by serialNumber order by testtime desc) as DebugSig,
    			DebugUser,
    			LEAD(DebugUser,1)over(partition by serialNumber order by testtime desc) as DebugUserSig
    				from @seriales
    	)
    	select  serialNumber,
    			StationType,
    			Station,
    			TestStatus,
    			TestGeneration,
    			TestTime,
    			DefectDesc,
    			DefectDesc2,
    			Coalesce(DefectCode,DefectCodeSig) as DefectCode,
    			Coalesce(Location,LocationSig) as Location,
    			Coalesce(Comment,CommentSig) as Comment,
    			Coalesce(Debug,DebugSig) as Debug,
    			Coalesce(DebugUser ,DebugUserSig) as DebugUser
    				from cte
    

    La trampa es utilizar las funciones de desplazamiento, en este caso LEAD para obtener el siguiente valor para la columna mencionada. Se hace una partición por numero de serie.

    Salida

    Lag/Lead

    https://javifer2.wordpress.com/2018/09/11/lag-y-lead-siguiente-y-anterior/

    viernes, 18 de octubre de 2019 18:52

Todas las respuestas

  • cuando la columna DefectDesc diga Retest y en el siguiente registro tenga datos capturados por un diagnostico, copiar los datos que se capturaron a la fila donde dice Retest. Este es el codigo que tengo:

    IF OBJECT_ID('tempdb..#A') IS NOT NULL
        DROP TABLE #A
    IF OBJECT_ID('tempdb..#B') IS NOT NULL
        DROP TABLE #B
    
    
    select distinct t.ID TestID ,sn.Value SerialNumber, st.Description StationType,s.Description Station, ISNULL(t.Status, 'PASS') TestStatus,
    tg.Generation TestGeneration,ROW_NUMBER() over(partition by t.UnitID,s.StationTypeID order by t.CreationTime desc) RowNumber ,t.CreationTime TestTime,
    
    case
    
    when (df.Description is not null or df.DefectCode is not null) and (tg.Generation = 1 and t.Status = 'Fail') then df.Description
    when (df.Description is not null or df.DefectCode is not null) and (tg.Generation = 2 and t.Status = 'FAIL') then df.Description
    when (df.Description is not null or df.DefectCode is not null) and (tg.Generation = 3 and t.Status = 'FAIL') then df.Description
    
    when ROW_NUMBER() over(partition by t.unitid,s.stationtypeid Order by t.creationtime desc)=2 and tg.Generation = 1 and t.Status = 'FAIL' then 'Retest'
    when ROW_NUMBER() over(partition by t.UnitID,s.StationTypeID order by t.CreationTime desc)=1 and tg.Generation = 1 and t.Status = 'FAIL' then 'Pending to Debug'
    when ROW_NUMBER() over(partition by t.UnitID,s.StationTypeID order by t.CreationTime desc)>2 and tg.Generation = 1 and t.Status = 'FAIL' then 'Pending to Debug'
    
    when ROW_NUMBER() over(partition by t.unitid,s.stationtypeid Order by t.creationtime desc)=2 and tg.Generation = 2 and t.Status = 'FAIL' then 'Retest'
    when ROW_NUMBER() over(partition by t.UnitID,s.StationTypeID order by t.CreationTime desc)=1 and tg.Generation = 2 and t.Status = 'FAIL' then 'Pending to Debug'
    when ROW_NUMBER() over(partition by t.UnitID,s.StationTypeID order by t.CreationTime desc)>2 and tg.Generation = 2 and t.Status = 'FAIL' then 'Pending to Debug'
    
    when ROW_NUMBER() over(partition by t.unitid,s.stationtypeid Order by t.creationtime desc)=2 and tg.Generation = 3 and t.Status = 'FAIL' then 'Retest'
    when ROW_NUMBER() over(partition by t.UnitID,s.StationTypeID order by t.CreationTime desc)=1 and tg.Generation = 3 and t.Status = 'FAIL' then 'pending to Debug'
    when ROW_NUMBER() over(partition by t.UnitID,s.StationTypeID order by t.CreationTime desc)>2 and tg.Generation = 3 and t.Status = 'FAIL' then 'Pending to Debug'
    
    when tg.Generation = 2 and t.Status = 'PASS' then 'Passed by Retest'
    when tg.Generation = 3 and t.Status = 'PASS' then 'Passed by Retest'
    
    else 'Next Process' end DefectDesc, 
    
    df.DefectCode, d.Location, d.Comment,d.Time DebugTime, e.UserID + ' [' + e.Firstname + ' ' + e.Lastname + ']' DebugUser
    into #A
    
    from ffTest t   left join
    ffUnit u on u.ID = t.UnitID left join
    ffStation s on s.ID = t.StationID left join
    ffSerialNumber sn on sn.UnitID = t.UnitID and sn.SerialNumberTypeID = 0 left join
    ffDebug d on d.TestID = t.ID left join 
    ffEmployee e on e.ID = d.EmployeeID left join
    luDefect df on df.ID = d.DefectID left join
    rvwTestGeneration tg on tg.ID=t.ID left join
    ffStationType st on st.ID = s.StationTypeID 
    
    order by t.CreationTime desc
    
    Select a.* into #B from #A a 
    where (a.DefectCode is not null or a.Location is not null or a.Comment is not null or a.DebugTime is not null or a.DebugUser is not null)
    order by a.TestTime desc
    
    
    
    Select distinct a.SerialNumber, a.StationType, a.Station, a.TestStatus, a.TestGeneration,a.TestTime,
    
    case when a.DebugTime is null and a.DefectDesc = 'Retest' then b.DefectDesc else null end DefectDesc,
    
    a.DefectDesc, a.DefectCode, a.Location, a.Comment, a.DebugTime, a.DebugUser
    from #A a left join
    #B b on b.TestID = a.TestID
    
    where (a.TestTime >= GETDATE() -4) and a.SerialNumber = 'SUB0602G01HS' 
    order by a.TestTime desc
    

    Espero y me puedan ayudar.

    Gracias.

    viernes, 18 de octubre de 2019 1:29
  • Hola Giovanni Silonzochilt:

    y en el siguiente registro tenga datos capturados por un diagnostico

    ¿Puedes poner unas filas de ejemplo de lo que puede contener la tabla temporal? 

    Y el resultado deseado, ya que hay varias incógnitas, que por el escenario no quedan claras y pueden variar mucho el sentido de la consulta.

    ¿Qué versión de SQL Server tienes?

    viernes, 18 de octubre de 2019 3:55
  • Hola Javi Fernández 

    SerialNumber StationType Station TestStatus TestGeneration TestTime DefectDesc DefectDesc2 DefectCode Location Comment DebugTime DebugUser
    SUB0602G01HS F5-PRE-ESS F5-PRE-ESS (TOP-SUBASSY) FAIL 3 17/10/2019 14:20:47 NULL Missing Comp. Comp. Faltante R505  Pad volado 17/10/2019 16:31:45 gdjjuvaz [Juan Vazquez]
    SUB0602G01HS F5-PRE-ESS F5-PRE-ESS (TOP-SUBASSY) FAIL 3 17/10/2019 14:20:47 NULL Missing Comp. Comp. Faltante RE29  Pad volado 17/10/2019 16:31:45 gdjjuvaz [Juan Vazquez]
    SUB0602G01HS F5-PRE-ESS F5-PRE-ESS (TOP-SUBASSY) FAIL 3 17/10/2019 14:20:47 NULL Physically Damaged Comp. Comp. Daño Fisico RE28    17/10/2019 16:31:45 gdjjuvaz [Juan Vazquez]
    SUB0602G01HS F5-PRE-ESS F5-PRE-ESS (TOP-SUBASSY) PASS 3 15/10/2019 22:04:49 NULL Passed by Retest NULL NULL NULL NULL NULL
    SUB0602G01HS F5-VALIDATION-REPAIR F5-VALIDATION-REPAIR (MDS) PASS 3 15/10/2019 21:51:29 NULL Passed by Retest NULL NULL NULL NULL NULL
    SUB0602G01HS F5-ESS F5-ESS (TOP-SUBASSY) FAIL 3 14/10/2019 17:55:19 NULL Pin Doblado - Bent Pin Com. 13 J6  pin ddr3_mem_a[12] (j6.174) esta doblado, cambiar conector 15/10/2019 01:38:32 1095345 [Oscar Herrera]
    SUB0602G01HS F5-ESS F5-ESS (TOP-SUBASSY) FAIL 3 14/10/2019 16:54:26 NULL Retest NULL NULL NULL NULL NULL
    SUB0602G01HS F5-PRE-ESS F5-PRE-ESS (TOP-SUBASSY) PASS 3 14/10/2019 08:31:42 NULL Passed by Retest NULL NULL NULL NULL NULL

    Esta es la tabla que arroja, lo que quiero es que cuando en DefectDesc diga Retest y en su siguiente prueba tenga Datos capturados, me copie los datos capturados a donde estan los NULL.

    Saludos.

    viernes, 18 de octubre de 2019 14:18
  • Hola Giovanni:

    Dos dudas.

    1º Select @@version

    2º El retest te refieres al de las 16:54 y el siguiente es el de las 17:55, ya que siguiente es un concepto complicado (siguiente en fecha, en fila)

    viernes, 18 de octubre de 2019 16:39
  • Hola Javi

    la duda 1° no la entiendo.

    y a la duda 2°: si el retest de las 16:54 y en cuestion de siguiente es al siguiente test que se realiza al seria, se puede tomar por la fecha. Ya que estamos filtandro ese serial. Porque una vez que se quite el filtro va haber diferentes seriales.

    Saludos.

    viernes, 18 de octubre de 2019 16:45
  • Necesito saber tu versión de sql server para saber si puedo utilizar para siguiente LAG/LEAD porque eso viene con 2012 o superior.

    Si no tienes 2012 entonces las consultas se aplican de otra manera

    viernes, 18 de octubre de 2019 16:57
  • Hola Javi.

    Tengo la ultima version del SQL

    Saludos...

    viernes, 18 de octubre de 2019 17:49
  • Hola Giovani Silonzochilt:

    Espero haberte entendido, porque no me ha quedado muy claro lo que tú esperas como siguiente.

    He metido el resultado de tu select en una variable de Tabla para poder trabajar mucho más fácil.

    Dejo primero el código de la variable de tabla y de la inserción de la misma, para que pueda servir también a quien quiera aportar o cambiar algo.

    declare @seriales table 
    (
    	serialNumber varchar(20), 
    	StationType varchar(20), 
    	Station varchar(100),
    	TestStatus varchar(15), 
    	TestGeneration int, 
    	TestTime datetime, 
    	DefectDesc varchar(10), 
    	DefectDesc2 varchar(100),
    	DefectCode varchar(100), 
    	Location varchar(100), 
    	Comment varchar(100), 
    	Debug DateTime, 
    	DebugUser varchar(100)
    )
    insert into @seriales
    (serialNumber,
     StationType,
     Station,
     TestStatus,
     TestGeneration,
     TestTime,
     DefectDesc,
     DefectDesc2,
     DefectCode,
     Location,
     Comment,
     Debug,
     DebugUser
    )
    VALUES
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'FAIL',3,'20191017 14:20:47',
    	NULL,'Missing Comp.'			,'Comp. Faltante'	,'R505' ,
    	'Pad volado'												 ,'20191017 16:31:45','gdjjuvaz [Juan Vazquez]'),
    	-- fila 1
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'FAIL',3,'20191017 14:20:47',
    	NULL,'Missing Comp.'			,'Comp. Faltante'	,'RE29' ,
    	'Pad volado'												 ,'20191017 16:31:45','gdjjuvaz [Juan Vazquez]'),
    	-- fila2
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'FAIL',3,'20191017 14:20:47',
    	NULL,'Physically Damaged Comp.','Comp. Daño Fisico','RE28' ,
    	''															 ,'20191017 16:31:45','gdjjuvaz [Juan Vazquez]'),
    	-- fila3
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'PASS',3,'20191015 22:04:49',
    	NULL,'Passed by Retest'		,NULL				,NULL	,
    	NULL														 ,NULL				 ,NULL					   ),
    	-- fila4
    ('SUB0602G01HS','F5-VALIDATION-REPAIR','F5-VALIDATION-REPAIR (MDS)','PASS',3,'20191015 21:51:29',
    	NULL,'Passed by Retest'		,NULL				,NULL	,
    	NULL														 ,NULL				 ,NULL					   ),
    	-- fila5
    ('SUB0602G01HS','F5-ESS'			  ,'F5-ESS (TOP-SUBASSY)'	   ,'FAIL',3,'20191014 17:55:19',
    	NULL,'Pin Doblado - Bent Pin'	,'Com. 13'			,'J6'	,
    	'pin ddr3_mem_a[12] (j6.174) esta doblado, cambiar conector','20191015 01:38:32','1095345 [Oscar Herrera]'),
    	-- fila6
    ('SUB0602G01HS','F5-ESS'			  ,'F5-ESS (TOP-SUBASSY)'	   ,'FAIL',3,'20191014 16:54:26',
    	NULL,'Retest'					,NULL				,nULL	,
    	NULL	,NULL				 ,NULL					   ),
    	-- fila7
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'PASS',3,'20191014 08:31:42',
    	NULL,'Passed by Retest'		,NULL				,NULL	,
    	NULL														 ,NULL				 ,NULL					   );
    	-- fila8
    

    Adicionalmente he cambiado la forma en que pones las fechas, por el estándar yyyyMMdd

    La query queda así. Ojo que va junto a lo que ya te he pegado

    declare @seriales table 
    (
    	serialNumber varchar(20), 
    	StationType varchar(20), 
    	Station varchar(100),
    	TestStatus varchar(15), 
    	TestGeneration int, 
    	TestTime datetime, 
    	DefectDesc varchar(10), 
    	DefectDesc2 varchar(100),
    	DefectCode varchar(100), 
    	Location varchar(100), 
    	Comment varchar(100), 
    	Debug DateTime, 
    	DebugUser varchar(100)
    )
    insert into @seriales
    (serialNumber,
     StationType,
     Station,
     TestStatus,
     TestGeneration,
     TestTime,
     DefectDesc,
     DefectDesc2,
     DefectCode,
     Location,
     Comment,
     Debug,
     DebugUser
    )
    VALUES
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'FAIL',3,'20191017 14:20:47',
    	NULL,'Missing Comp.'			,'Comp. Faltante'	,'R505' ,
    	'Pad volado'												 ,'20191017 16:31:45','gdjjuvaz [Juan Vazquez]'),
    	-- fila 1
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'FAIL',3,'20191017 14:20:47',
    	NULL,'Missing Comp.'			,'Comp. Faltante'	,'RE29' ,
    	'Pad volado'												 ,'20191017 16:31:45','gdjjuvaz [Juan Vazquez]'),
    	-- fila2
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'FAIL',3,'20191017 14:20:47',
    	NULL,'Physically Damaged Comp.','Comp. Daño Fisico','RE28' ,
    	''															 ,'20191017 16:31:45','gdjjuvaz [Juan Vazquez]'),
    	-- fila3
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'PASS',3,'20191015 22:04:49',
    	NULL,'Passed by Retest'		,NULL				,NULL	,
    	NULL														 ,NULL				 ,NULL					   ),
    	-- fila4
    ('SUB0602G01HS','F5-VALIDATION-REPAIR','F5-VALIDATION-REPAIR (MDS)','PASS',3,'20191015 21:51:29',
    	NULL,'Passed by Retest'		,NULL				,NULL	,
    	NULL														 ,NULL				 ,NULL					   ),
    	-- fila5
    ('SUB0602G01HS','F5-ESS'			  ,'F5-ESS (TOP-SUBASSY)'	   ,'FAIL',3,'20191014 17:55:19',
    	NULL,'Pin Doblado - Bent Pin'	,'Com. 13'			,'J6'	,
    	'pin ddr3_mem_a[12] (j6.174) esta doblado, cambiar conector','20191015 01:38:32','1095345 [Oscar Herrera]'),
    	-- fila6
    ('SUB0602G01HS','F5-ESS'			  ,'F5-ESS (TOP-SUBASSY)'	   ,'FAIL',3,'20191014 16:54:26',
    	NULL,'Retest'					,NULL				,nULL	,
    	NULL	,NULL				 ,NULL					   ),
    	-- fila7
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'PASS',3,'20191014 08:31:42',
    	NULL,'Passed by Retest'		,NULL				,NULL	,
    	NULL														 ,NULL				 ,NULL					   );
    	-- fila8
    
    
    ;with cte as (
    	select	
    			row_number() over (partition by serialNumber order by testTime desc) as row,
    			serialNumber,
    			StationType,
    			Station,
    			TestStatus,
    			TestGeneration,
    			TestTime,
    			DefectDesc,
    			DefectDesc2,
    			DefectCode,
    			LEAD(DefectCode,1)over(partition by serialNumber order by testtime desc) as DefectCodeSig,
    			Location,
    			LEAD(Location,1)over(partition by serialNumber order by testtime desc) as LocationSig,
    			Comment,
    			LEAD(Comment,1)over(partition by serialNumber order by testtime desc) as CommentSig,
    			Debug,
    			LEAD(Debug,1)over(partition by serialNumber order by testtime desc) as DebugSig,
    			DebugUser,
    			LEAD(DebugUser,1)over(partition by serialNumber order by testtime desc) as DebugUserSig
    				from @seriales
    	)
    	select  serialNumber,
    			StationType,
    			Station,
    			TestStatus,
    			TestGeneration,
    			TestTime,
    			DefectDesc,
    			DefectDesc2,
    			Coalesce(DefectCode,DefectCodeSig) as DefectCode,
    			Coalesce(Location,LocationSig) as Location,
    			Coalesce(Comment,CommentSig) as Comment,
    			Coalesce(Debug,DebugSig) as Debug,
    			Coalesce(DebugUser ,DebugUserSig) as DebugUser
    				from cte
    

    La trampa es utilizar las funciones de desplazamiento, en este caso LEAD para obtener el siguiente valor para la columna mencionada. Se hace una partición por numero de serie.

    Salida

    Lag/Lead

    https://javifer2.wordpress.com/2018/09/11/lag-y-lead-siguiente-y-anterior/

    viernes, 18 de octubre de 2019 18:52
  • Hola Javi.

    Si me sirve tu ejemplo, al final cambie los LEAD por LAG para que se pusiera en la fila donde esta el Retest.

    declare @seriales table 
    (
    	serialNumber varchar(20), 
    	StationType varchar(20), 
    	Station varchar(100),
    	TestStatus varchar(15), 
    	TestGeneration int, 
    	TestTime datetime, 
    	DefectDesc varchar(10), 
    	DefectDesc2 varchar(100),
    	DefectCode varchar(100), 
    	Location varchar(100), 
    	Comment varchar(100), 
    	Debug DateTime, 
    	DebugUser varchar(100)
    )
    insert into @seriales
    (serialNumber,
     StationType,
     Station,
     TestStatus,
     TestGeneration,
     TestTime,
     DefectDesc,
     DefectDesc2,
     DefectCode,
     Location,
     Comment,
     Debug,
     DebugUser
    )
    VALUES
    ('SUB0602G01HS','F5-PRE-ESS'	  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'FAIL',3,'20191017 14:20:47',
    	NULL,'Missing Comp.'			,'Comp. Faltante'	,'R505' ,
    	'Pad volado'												 ,'20191017 16:31:45','gdjjuvaz [Juan Vazquez]'),
    	-- fila 1
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'FAIL',3,'20191017 14:20:47',
    	NULL,'Missing Comp.'			,'Comp. Faltante'	,'RE29' ,
    	'Pad volado'												 ,'20191017 16:31:45','gdjjuvaz [Juan Vazquez]'),
    	-- fila2
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'FAIL',3,'20191017 14:20:47',
    	NULL,'Physically Damaged Comp.','Comp. Daño Fisico','RE28' ,
    	''															 ,'20191017 16:31:45','gdjjuvaz [Juan Vazquez]'),
    	-- fila3
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'PASS',3,'20191015 22:04:49',
    	NULL,'Passed by Retest'		,NULL				,NULL	,
    	NULL														 ,NULL				 ,NULL					   ),
    	-- fila4
    ('SUB0602G01HS','F5-VALIDATION-REPAIR','F5-VALIDATION-REPAIR (MDS)','PASS',3,'20191015 21:51:29',
    	NULL,'Passed by Retest'		,NULL				,NULL	,
    	NULL														 ,NULL				 ,NULL					   ),
    	-- fila5
    ('SUB0602G01HS','F5-ESS'			  ,'F5-ESS (TOP-SUBASSY)'	   ,'FAIL',3,'20191014 17:55:19',
    	NULL,'Pin Doblado - Bent Pin'	,'Com. 13'			,'J6'	,
    	'pin ddr3_mem_a[12] (j6.174) esta doblado, cambiar conector','20191015 01:38:32','1095345 [Oscar Herrera]'),
    	-- fila6
    ('SUB0602G01HS','F5-ESS'			  ,'F5-ESS (TOP-SUBASSY)'	   ,'FAIL',3,'20191014 16:54:26',
    	NULL,'Retest'					,NULL				,nULL	,
    	NULL	,NULL				 ,NULL					   ),
    	-- fila7
    ('SUB0602G01HS','F5-PRE-ESS'		  ,'F5-PRE-ESS (TOP-SUBASSY)'  ,'PASS',3,'20191014 08:31:42',
    	NULL,'Passed by Retest'		,NULL				,NULL	,
    	NULL														 ,NULL				 ,NULL					   );
    	-- fila8
    
    
    ;with cte as (
    	select	
    			row_number() over (partition by serialNumber order by testTime desc) as row,
    			serialNumber,
    			StationType,
    			Station,
    			TestStatus,
    			TestGeneration,
    			TestTime,
    			DefectDesc,
    			DefectDesc2,
    			DefectCode,
    			LAG(DefectCode,1)over(partition by serialNumber order by testtime desc) as DefectCodeSig,
    			Location,
    			LAG(Location,1)over(partition by serialNumber order by testtime desc) as LocationSig,
    			Comment,
    			LAG(Comment,1)over(partition by serialNumber order by testtime desc) as CommentSig,
    			Debug,
    			LAG(Debug,1)over(partition by serialNumber order by testtime desc) as DebugSig,
    			DebugUser,
    			LAG(DebugUser,1)over(partition by serialNumber order by testtime desc) as DebugUserSig
    				from @seriales
    	)
    	select  serialNumber,
    			StationType,
    			Station,
    			TestStatus,
    			TestGeneration,
    			TestTime,
    			DefectDesc,
    			DefectDesc2,
    			Coalesce(DefectCode,DefectCodeSig) as DefectCode,
    			Coalesce(Location,LocationSig) as Location,
    			Coalesce(Comment,CommentSig) as Comment,
    			Coalesce(Debug,DebugSig) as Debug,
    			Coalesce(DebugUser ,DebugUserSig) as DebugUser
    				from cte

    Obteniendo lo siguiente:

    serialNumber StationType Station TestStatus TestGeneration TestTime DefectDesc DefectDesc2 DefectCode Location Comment Debug DebugUser
    SUB0602G01HS F5-PRE-ESS F5-PRE-ESS (TOP-SUBASSY) FAIL 3 17/10/2019 14:20:47 NULL Missing Comp. Comp. Faltante R505 Pad volado 31:45.0 gdjjuvaz [Juan Vazquez]
    SUB0602G01HS F5-PRE-ESS F5-PRE-ESS (TOP-SUBASSY) FAIL 3 17/10/2019 14:20:47 NULL Missing Comp. Comp. Faltante RE29 Pad volado 31:45.0 gdjjuvaz [Juan Vazquez]
    SUB0602G01HS F5-PRE-ESS F5-PRE-ESS (TOP-SUBASSY) FAIL 3 17/10/2019 14:20:47 NULL Physically Damaged Comp. Comp. Daño Fisico RE28   31:45.0 gdjjuvaz [Juan Vazquez]
    SUB0602G01HS F5-PRE-ESS F5-PRE-ESS (TOP-SUBASSY) PASS 3 15/10/2019 22:04:49 NULL Passed by Retest Comp. Daño Fisico RE28   31:45.0 gdjjuvaz [Juan Vazquez]
    SUB0602G01HS F5-VALIDATION-REPAIR F5-VALIDATION-REPAIR (MDS) PASS 3 15/10/2019 21:51:29 NULL Passed by Retest NULL NULL NULL NULL NULL
    SUB0602G01HS F5-ESS F5-ESS (TOP-SUBASSY) FAIL 3 14/10/2019 17:55:19 NULL Pin Doblado - Bent Pin Com. 13 J6 pin ddr3_mem_a[12] (j6.174) esta doblado, cambiar conector 38:32.0 1095345 [Oscar Herrera]
    SUB0602G01HS F5-ESS F5-ESS (TOP-SUBASSY) FAIL 3 14/10/2019 16:54:26 NULL Retest Com. 13 J6 pin ddr3_mem_a[12] (j6.174) esta doblado, cambiar conector 38:32.0 1095345 [Oscar Herrera]
    SUB0602G01HS F5-PRE-ESS F5-PRE-ESS (TOP-SUBASSY) PASS 3 14/10/2019 08:31:42 NULL Passed by Retest NULL NULL NULL NULL NULL

    Ahora mi duda es como fuciono mi SELECT con el INSERT INTO y unirlo con el WHIT?, mi SELECT es:

    select distinct t.ID TestID ,sn.Value SerialNumber, st.Description StationType,s.Description Station, ISNULL(t.Status, 'PASS') TestStatus,
    tg.Generation TestGeneration, t.CreationTime TestTime,
    
    case
    
    when (df.Description is not null or df.DefectCode is not null) and (tg.Generation = 1 and t.Status = 'Fail') then df.Description
    when (df.Description is not null or df.DefectCode is not null) and (tg.Generation = 2 and t.Status = 'FAIL') then df.Description
    when (df.Description is not null or df.DefectCode is not null) and (tg.Generation = 3 and t.Status = 'FAIL') then df.Description
    
    when ROW_NUMBER() over(partition by t.unitid,s.stationtypeid Order by t.creationtime desc)=2 and tg.Generation = 1 and t.Status = 'FAIL' then 'Retest'
    when ROW_NUMBER() over(partition by t.UnitID,s.StationTypeID order by t.CreationTime desc)=1 and tg.Generation = 1 and t.Status = 'FAIL' then 'Pending to Debug'
    when ROW_NUMBER() over(partition by t.UnitID,s.StationTypeID order by t.CreationTime desc)>2 and tg.Generation = 1 and t.Status = 'FAIL' then 'Pending to Debug'
    
    when ROW_NUMBER() over(partition by t.unitid,s.stationtypeid Order by t.creationtime desc)=2 and tg.Generation = 2 and t.Status = 'FAIL' then 'Retest'
    when ROW_NUMBER() over(partition by t.UnitID,s.StationTypeID order by t.CreationTime desc)=1 and tg.Generation = 2 and t.Status = 'FAIL' then 'Pending to Debug'
    when ROW_NUMBER() over(partition by t.UnitID,s.StationTypeID order by t.CreationTime desc)>2 and tg.Generation = 2 and t.Status = 'FAIL' then 'Pending to Debug'
    
    when ROW_NUMBER() over(partition by t.unitid,s.stationtypeid Order by t.creationtime desc)=2 and tg.Generation = 3 and t.Status = 'FAIL' then 'Retest'
    when ROW_NUMBER() over(partition by t.UnitID,s.StationTypeID order by t.CreationTime desc)=1 and tg.Generation = 3 and t.Status = 'FAIL' then 'pending to Debug'
    when ROW_NUMBER() over(partition by t.UnitID,s.StationTypeID order by t.CreationTime desc)>2 and tg.Generation = 3 and t.Status = 'FAIL' then 'Pending to Debug'
    
    when tg.Generation = 2 and t.Status = 'PASS' then 'Passed by Retest'
    when tg.Generation = 3 and t.Status = 'PASS' then 'Passed by Retest'
    
    else 'Next Process' end DefectDesc, 
    
    df.DefectCode, d.Location, d.Comment,d.Time DebugTime, e.UserID + ' [' + e.Firstname + ' ' + e.Lastname + ']' DebugUser
    
    from ffTest t   left join
    ffUnit u on u.ID = t.UnitID left join
    ffStation s on s.ID = t.StationID left join
    ffSerialNumber sn on sn.UnitID = t.UnitID and sn.SerialNumberTypeID = 0 left join
    ffDebug d on d.TestID = t.ID left join 
    ffEmployee e on e.ID = d.EmployeeID left join
    luDefect df on df.ID = d.DefectID left join
    rvwTestGeneration tg on tg.ID=t.ID left join
    ffStationType st on st.ID = s.StationTypeID 
    
    order by t.CreationTime desc
    

    Saludos.

    viernes, 18 de octubre de 2019 20:27
  • Que tal Javi.

    Ya encontre la solucion.

    DECLARE @Result AS TABLE (
    	
    	TestID int,
    	SerialNumber VARCHAR(50), 
    	StationType VARCHAR(50) , 
    	Station VARCHAR(50),
    	TestStatus VARCHAR(50), 
    	TestGeneration INT,
    	TestTime DATETIME,
    	DefectDesc VARCHAR(50),
    	DefectDesc2 VARCHAR(50),
    	DefectCode VARCHAR(50),
    	[Location] VARCHAR(50),
    	Comment VARCHAR (MAX),
    	DebugTime DATETIME,
    	DebugUser VARCHAR(50)
    	   
    )
    
    
    
    insert into @Result 
    select distinct t.ID TestID ,sn.Value SerialNumber, st.Description StationType,s.Description Station, ISNULL(t.Status, 'PASS') TestStatus,
    tg.Generation TestGeneration, t.CreationTime TestTime,
    
    case
    
    when (df.Description is not null or df.DefectCode is not null) and (tg.Generation = 1 and t.Status = 'Fail') then df.Description
    when (df.Description is not null or df.DefectCode is not null) and (tg.Generation = 2 and t.Status = 'FAIL') then df.Description
    when (df.Description is not null or df.DefectCode is not null) and (tg.Generation = 3 and t.Status = 'FAIL') then df.Description
    
    when ROW_NUMBER() over(partition by t.unitid,s.stationtypeid Order by t.creationtime desc)=2 and tg.Generation = 1 and t.Status = 'FAIL' then 'Retest'
    when ROW_NUMBER() over(partition by t.UnitID,s.StationTypeID order by t.CreationTime desc)=1 and tg.Generation = 1 and t.Status = 'FAIL' then 'Pending to Debug'
    when ROW_NUMBER() over(partition by t.UnitID,s.StationTypeID order by t.CreationTime desc)>2 and tg.Generation = 1 and t.Status = 'FAIL' then 'Pending to Debug'
    
    when ROW_NUMBER() over(partition by t.unitid,s.stationtypeid Order by t.creationtime desc)=2 and tg.Generation = 2 and t.Status = 'FAIL' then 'Retest'
    when ROW_NUMBER() over(partition by t.UnitID,s.StationTypeID order by t.CreationTime desc)=1 and tg.Generation = 2 and t.Status = 'FAIL' then 'Pending to Debug'
    when ROW_NUMBER() over(partition by t.UnitID,s.StationTypeID order by t.CreationTime desc)>2 and tg.Generation = 2 and t.Status = 'FAIL' then 'Pending to Debug'
    
    when ROW_NUMBER() over(partition by t.unitid,s.stationtypeid Order by t.creationtime desc)=2 and tg.Generation = 3 and t.Status = 'FAIL' then 'Retest'
    when ROW_NUMBER() over(partition by t.UnitID,s.StationTypeID order by t.CreationTime desc)=1 and tg.Generation = 3 and t.Status = 'FAIL' then 'pending to Debug'
    when ROW_NUMBER() over(partition by t.UnitID,s.StationTypeID order by t.CreationTime desc)>2 and tg.Generation = 3 and t.Status = 'FAIL' then 'Pending to Debug'
    
    when tg.Generation = 2 and t.Status = 'PASS' then 'Passed by Retest'
    when tg.Generation = 3 and t.Status = 'PASS' then 'Passed by Retest'
    
    else 'Next Process' end DefectDesc, 
    
    df.Description DefectDesc2, df.DefectCode, d.Location, d.Comment,d.Time DebugTime, e.UserID + ' [' + e.Firstname + ' ' + e.Lastname + ']' DebugUser
    
    from ffTest t   left join
    ffUnit u on u.ID = t.UnitID left join
    ffStation s on s.ID = t.StationID left join
    ffSerialNumber sn on sn.UnitID = t.UnitID and sn.SerialNumberTypeID = 0 left join
    ffDebug d on d.TestID = t.ID left join 
    ffEmployee e on e.ID = d.EmployeeID left join
    luDefect df on df.ID = d.DefectID left join
    rvwTestGeneration tg on tg.ID=t.ID left join
    ffStationType st on st.ID = s.StationTypeID 
    
    where (t.CreationTime >= GETDATE()-5) and sn.Value ='SUB0602G01HS'
    order by t.CreationTime desc
    
    
    ;with cte as (
    	select	
    			row_number() over (partition by serialNumber order by testTime desc) as row,
    			serialNumber,
    			StationType,
    			Station,
    			TestStatus,
    			TestGeneration,
    			TestTime,
    			DefectDesc,
    			DefectDesc2,
    			LAG(DefectDesc2,1)over(partition by serialNumber order by testtime desc) as DefectDescSig,
    			DefectCode,
    			LAG(DefectCode,1)over(partition by serialNumber order by testtime desc) as DefectCodeSig,
    			Location,
    			LAG(Location,1)over(partition by serialNumber order by testtime desc) as LocationSig,
    			Comment,
    			LAG(Comment,1)over(partition by serialNumber order by testtime desc) as CommentSig,
    			DebugTime,
    			LAG(DebugTime,1)over(partition by serialNumber order by testtime desc) as DebugSig,
    			DebugUser,
    			LAG(DebugUser,1)over(partition by serialNumber order by testtime desc) as DebugUserSig
    				from @Result
    	)
    	select  serialNumber,
    			StationType,
    			Station,
    			TestStatus,
    			TestGeneration,
    			TestTime,
    			DefectDesc,
    			Coalesce(DefectDesc2,DefectDescSig) as DefectDesc2,
    			Coalesce(DefectCode,DefectCodeSig) as DefectCode,
    			Coalesce(Location,LocationSig) as Location,
    			Coalesce(Comment,CommentSig) as Comment,
    			Coalesce(DebugTime,DebugSig) as Debug,
    			Coalesce(DebugUser ,DebugUserSig) as DebugUser
    				from cte
    
    
    
    
    
    
    

    Gracias por la ayuda Javi.

    Saludos.


    Giovanni Rico Silonzochilt

    viernes, 18 de octubre de 2019 21:14
  • De nada
    viernes, 18 de octubre de 2019 21:50