sql server - How do I modify my SQL Query to take into account this specific filtering condition? -

i using sql query 2014 , have following t-sql query running against database:

use database  select *  ( select a.reservationstayid,        c.pmsconfirmationnumber,        a.staydate,        datepart(month,a.staydate) 'month',        datepart(year,a.staydate) 'year',        c.[mth],         a.packageplancode,        c.[market segment code],        c.[currencycode],        a.rateamount,        sum(a.rateamount) over(partition a.reservationstayid) 'cum_rate',        d.[exchange rate],        ((a.rateamount * d.[exchange rate])/1.15) 'pr',        c.[propertycode],        c.[room nights],        c.[tour operator],        c.[group booking id],        c.[source of business],        c.[booking origin (1)],         c.[market final],        c.[createdon],        c.[createdon_rsd]  reservationstaydate  inner join   (select *  [reservationlist]) c on c.[reservationstayid] = a.reservationstayid , c.[mth] = datename(m,staydate) + ' ' + cast(datepart(yyyy,staydate) varchar)  inner join   (select *  [pkgrevenue]) d on d.[reservationstayid] = a.reservationstayid , d.[staydate] = a.staydate  c.[status] <> 'canceled' ) q  q.cum_rate <> 0   order q.reservationstayid, q.staydate 

the above query runs fine want include filter following reservationstayid included in output: 152, 167, 658, 900

these reservationstayid have cum_rate = 0, meaning automatically excluded in output of existing query.

i've tried following did not work:

where (q.cum_rate <> 0 , q.reservationstayid in (152, 167, 658, 900)) 

how add specific condition?

here reasons why reservationstayids 152, 167, 658, 900do not appear on result:

1. no rows on reservationlist, where status <> 'canceled'
2. no matching rows on pkgrevenue

to solve problem, may want turn inner joins left join. additionally, may want use case expression in sum() over clause. , instead of using and in where clause, should use or

select * (     select          --...,         sum(case                  when c.[status] <> 'cancelled' a.rateamount                 else 0             end             ) over(partition a.reservationstayid) 'cum_rate',         --...     reservationstaydate     left  join reservationlist c             on c.[reservationstayid] = a.reservationstayid          , c.[mth] = datename(m, staydate) + ' ' + cast(datepart(yyyy,staydate) varchar)         , c.[status] <> 'canceled'     left join pkgrevenued          on d.[reservationstayid] = a.reservationstayid         , d.[staydate] = a.staydate    ) q      q.cum_rate <> 0     or q.reservationstayid in (152, 167, 658, 900) order q.reservationstayid, q.staydate 

note: changed of joins directly use table instead of using subquery , select * <table>.