Input:

When Process ID = 610

I have two separate queries that fetch two values from the same table so I am trying to merge them together instaed of calling the table twice.

select [serialno],[storedatetime], [value1], [value2], 
    ROW_NUMBER() OVER (PARTITION BY [serialno] ORDER BY [storedatetime] desc) AS RowNumber 
    from [database] 
    where [processid] = 610 and [stepid] = 50

When this is run the where clause applies to [value1] and nothing is set for [value2] I am trying to make a different where clause to apply to [value2] where the [stepid] is a different number like:

  where [processid] = 610 and [stepid] = 80

This is quite new to me and haven't managed to find a way yet. Any help is appreciated.

Output:

CodePudding user response：

Do you need in this:

SELECT [serialno],
       [storedatetime], 
       MAX(CASE WHEN [stepid]= 50 THEN {some_column_name} END) [value1],
       MAX(CASE WHEN [stepid]= 80 THEN {some_column_name} END) [value2],
       ROW_NUMBER() OVER (PARTITION BY [serialno] ORDER BY [storedatetime] desc) AS RowNumber 
FROM [database] 
WHERE [processid] = 610 and [stepid] IN (50, 80)
GROUP BY 1, 2

CodePudding user response：

While I prefer the aggregation shown in Akina's answer, you may find a join more straight-forward. Select the value1 rows, select the value2 rows, join the two.

select
  v1.serialno, v1.storedatetime, v1.value1, v2.value2,
  row_number() over (partition by v1.serialno order by v1.storedatetime desc) as rownumber 
from
(
  select serialno, storedatetime, value1
  from database
  where processid = 610 and stepid = 50
) v1
join
(
  select serialno, storedatetime, value2
  from database
  where processid = 610 and stepid = 80
) v2 on v2.serialno = v1.serialno and v2.storedatetime = v1.storedatetime;

CodePudding user response：

Slight tweak using both answers and this gets the right results

select [serialno],[storedatetime],  
    MAX(CASE WHEN [stepid]= 50 THEN [value1] END) [value1], 
    MAX(CASE WHEN [stepid]= 80 THEN [value1] END) [value2], 
    ROW_NUMBER() OVER (PARTITION BY [serialno] ORDER BY [storedatetime] desc) AS RowNumber 
from [spi_mfg].[dbo].[processdata] 
where [processid] = 610 and [stepid] IN (50, 80) 
GROUP BY [serialno], [storedatetime]