hola a todos tengo un query y quiero agregar un grupo de variables que van a afectar al query
este es mi query
Código SQL:
Ver originalSELECT B.Part_No,
B.Name,
SUM(A.Quantity) AS 'Cantidad OK',
ISNULL((SELECT SUM(D.Quantity) FROM Part_v_Scrap D
WHERE A.Job_Key = D.Job_Key AND A.Part_Key = D.Part_Key AND CONVERT(DATE,A.Change_Date) BETWEEN @FechaInicio AND @FechaFin AND CONVERT(DATE,D.Add_Date) BETWEEN @FechaInicio AND @FechaFin),0) AS 'SCRAP',
ISNULL((SELECT SUM (COALESCE (A.Quantity, 0)) FROM Part_v_Container_Change2 AS A
WHERE CONVERT(DATE,A.Change_Date) BETWEEN @FechaInicio AND @FechaFin
AND A.Container_Status = 'Rework' AND A.Last_Action = @Linea
AND A.Active = 1 ),0) AS 'Cantidad Retrabajo',
ISNULL((SELECT SUM(D.Quantity) FROM Part_v_Scrap D
WHERE A.Job_Key = D.Job_Key AND A.Part_Key = D.Part_Key AND CONVERT(DATE,A.Change_Date) BETWEEN @FechaInicio AND @FechaFin AND CONVERT(DATE,D.Add_Date) BETWEEN @FechaInicio AND @FechaFin),0) +
ISNULL((SELECT SUM (COALESCE (A.Quantity, 0)) FROM Part_v_Container_Change2 AS A
WHERE CONVERT(DATE,A.Change_Date) BETWEEN @FechaInicio AND @FechaFin
AND A.Container_Status = 'Rework' AND A.Last_Action = @Linea
AND A.Active = 1 ),0) AS 'Total defectos'
FROM Part_v_Container_Change2 A
INNER JOIN Part_v_part B ON A.Part_Key = B.Part_Key
WHERE CONVERT(DATE,A.Change_Date) BETWEEN @FechaInicio AND @FechaFin
AND A.Location = @Linea
AND (A.Last_Action = 'Added at Container Form'
OR A.Last_Action = 'Container Full')
GROUP BY b.name, b.Part_Key, b.Part_No,A.Job_Key,A.Part_Key, CONVERT(DATE,A.Change_Date),A.Container_Status
quiero agregar un grupo de variables que va a afectar a A.Location en el query dependiendo el que el usuario mande dentro del grupo de variables
por ejemplo
Grupo QUERY linea A = @lineaA Liena B = @LineaB
no se si hacerlo como un case algo asi
CASE WHEN @Linea = 'Linea_A' THEN @Linea = 'Linea-1' WHEN @Linea = 'Linea_B' THEN @Linea = 'Linea-2' ELSE result END;