Where clause issue

Transact-SQL
1

In my Query I can not pass with desired results. It only gives me the records where main.row=filtered.row
where main.row between filtered.row-10 and filtered.row+10

but I can pass and get the full range.
where main.row between 951-10 and 951+10

I tried cast and convert as int of filtered.row as well as the cast and convert of the main.row

I also did the math for the between function in the filtered subquery and still the results are main.row=filtered.row

Here is my query

select main.model, main.make, main.row as main_row, filtered.row as filtered_row
from
(/* This left joins the filtered list to the main counted list /
select row_number() over(order by make, model) as row, COUNT(make) CNT, Make, Model
from vhvin
GROUP BY Make, model
)main
left join
( / this adds to the counted Query the date, and a filter for specific date*/
select vhvin.model,vhvin.Make, DateCreate, row, row-10 less10, row+10 as more10
from VHVIN
join
( /* This counts the number of make and models in the vehicle table */
select row_number() over(order by make, model) as row, COUNT(make) CNT, Make, Model
from vhvin
GROUP BY Make, model
) counted on counted.make=vhvin.make and counted.model=vhvin.model and counted.cnt=1
where Datecreate between dateadd(day,-7, getdate()) and getdate()
) filtered on filtered.model=main.model and filtered.make=main.make
where main.row between filtered.row-10 and filtered.row+10
order by main.row

2

Replace the WHERE with AND as shown below

....
where Datecreate between dateadd(day,-7, getdate()) and getdate()
) filtered on filtered.model=main.model and filtered.make=main.make	
and main.row between filtered.row-10 and filtered.row+10
order by main.row

When you use a WHERE clause in a LEFT JOIN that has a column from the right table, it essentially turns into an inner join. The way to get around that is to put the WHERE clause in the join condition (or explicitly include a condition to handle the null values from the right table).

3

Thank you for your response.

That gave me a full set of data.

I then tried to alter it like

) filtered on (filtered.model=main.model) and (filtered.make=main.make)
and (main.row between filtered.row-10 and filtered.row+10)

and that also gave me full range of data.

also tried isnull(filtered.row, 0)

4

I am afraid I am not following you. What is the difference between what I had posted and your changed WHERE clause? I see additional brackets, but those do not change the logic.

5

It didn't give me a filtered set. It was the full set. all records. As if the "and main.row between filtered.row-10 and filtered.row+10" didn't exist. What I had originally gave me a filtered set but filtered with out any range. It was just the one record of filtered.row not the range of -10 to +10 (20 records). Yours gave me ALL the records.

6

So I got a workable result by changing join predicates of the filtered subquery to be
) filtered on filtered.row between main.row-10 and main.row+10

So in the results, for the filtered.row I got the main.row field in that range.

I'm not totally satisfied but it works.

Thanks.