How to apply partition by and row_number() on inner join and case statement in sql query

Question

I have a sql query

Now in the given logtime a single beam_current value are coming multiple times at different logtime.I want just one value of beam_current not duplicate.For this I want to apply row_number and partition by beam_current in the above Sql Query.But I'm not getting how to apply it.

Answer 1

Made few changes

Removed unwanted brackets in Join condition. Also there was a mistake in Join without ON condition, corrected it

Then kept only the join conditions in On clause moved the filters to where clause.

Try this.

;WITH cte
     AS (SELECT b.logtime,
                b.beam_current,
                b.beam_energy,
                CASE
                  WHEN a.st2_vs2_bag1_onoff = 0 THEN c.st2_vs2_bag1_rb
                  ELSE 0
                END                     AS st2_vs2_bag1_rb,
                CASE
                  WHEN a.st2_vs2_bag8_onoff = 0 THEN c.st2_vs2_bag8_rb
                  ELSE '0'
                END                     AS st2_vs2_bag8_rb,
                CASE
                  WHEN a.st2_vs2_bag9_onoff = 0 THEN c.st2_vs2_bag9_rb
                  ELSE '0'
                END                     AS st2_vs2_bag9_rb,
                Row_number() OVER(partition BY b.beam_current
                    ORDER BY b.logtime) RN
         FROM   INDUS2_VACUUM.dbo.main_vacuum_analog c
                INNER JOIN INDUS2_VACUUM.dbo.main_vacuum_status a
                        ON c.logtime = a.logtime
                INNER JOIN INDUS2_BDS.dbo.DCCT b
                        ON a.LOGTIME = b.LOGTIME
         WHERE  b.beam_current LIKE '%9.96'
                AND b.logtime BETWEEN '2014-08-09 00:00:00' AND '2014-08-09 23:59:59'
                AND b.beam_current IN ( '10.01', '20.02' )
                AND b.beam_energy BETWEEN '550' AND '551')
SELECT logtime,
       beam_current,
       beam_energy,
       st2_vs2_bag1_rb,
       st2_vs2_bag8_rb,
       st2_vs2_bag9_rb
FROM   cte
WHERE  rn = 1
ORDER  BY logtime