Filter rows of a table based on a condition that implies: 1) value of a field within a range 2) id of the business and 3) date?

I want to filter a TableA, taking into account only those rows whose “TotalInvoice” field is within the minimum and maximum values expressed in a ViewB, based on month and year values and RepairShopId (the sample data only has one RepairShopId, but all the data has multiple IDs).

In the view I have minimum and maximum values for each business and each month and year.

TableA

RepairOrderDataId	RepairShopId	LastUpdated	TotalInvoice
1	10	2017-06-01 07:00:00.000	765
1	10	2017-06-05 12:15:00.000	765
2	10	2017-02-25 13:00:00.000	400
3	10	2017-10-19 12:15:00.000	295679
4	10	2016-11-29 11:00:00.000	133409.41
5	10	2016-10-28 12:30:00.000	127769
6	10	2016-11-25 16:15:00.000	122400
7	10	2016-10-18 11:15:00.000	1950
8	10	2016-11-07 16:45:00.000	79342.7
9	10	2016-11-25 19:15:00.000	1950
10	10	2016-12-09 14:00:00.000	111559
11	10	2016-11-28 10:30:00.000	106333
12	10	2016-12-13 18:00:00.000	23847.4
13	10	2016-11-01 17:00:00.000	22782.9
14	10	2016-10-07 15:30:00.000	NULL
15	10	2017-01-06 15:30:00.000	138958
16	10	2017-01-31 13:00:00.000	244484
17	10	2016-12-05 09:30:00.000	180236
18	10	2017-02-14 18:30:00.000	92752.6
19	10	2016-10-05 08:30:00.000	161952
20	10	2016-10-05 08:30:00.000	8713.08

ViewB

RepairShopId	Orders	Average	MinimumValue	MaximumValue	year	month	yearMonth
10	1	370343	370343	370343	2015	7	2015-7
10	1	109645	109645	109645	2015	10	2015-10
10	1	148487	148487	148487	2015	12	2015-12
10	1	133409.41	133409.41	133409.41	2016	3	2016-3
10	1	19261	19261	19261	2016	8	2016-8
10	4	10477.3575	2656.65644879821	18298.0585512018	2016	9	2016-9
10	69	15047.709565	10	90942.6052417394	2016	10	2016-10
10	98	22312.077244	10	147265.581935242	2016	11	2016-11
10	96	20068.147395	10	99974.1750708773	2016	12	2016-12
10	86	25334.053372	10	184186.985160105	2017	1	2017-1
10	69	21410.63855	10	153417.00126689	2017	2	2017-2
10	100	13009.797	10	59002.3589332934	2017	3	2017-3
10	101	11746.191287	10	71405.3391452842	2017	4	2017-4
10	123	11143.49756	10	55306.8202091131	2017	5	2017-5
10	197	15980.55406	10	204538.144334771	2017	6	2017-6
10	99	10852.496969	10	63283.9899761938	2017	7	2017-7
10	131	52601.981526	10	1314998.61355187	2017	8	2017-8
10	124	10983.221854	10	59444.0535811233	2017	9	2017-9
10	115	12467.148434	10	72996.6054527277	2017	10	2017-10
10	123	14843.379593	10	129673.931373139	2017	11	2017-11
10	111	8535.455945	10	50328.1495501884	2017	12	2017-12

I’ve tried:

SELECT *
FROM TableA 
INNER JOIN ViewB ON TableA.RepairShopId = ViewB.RepairShopId
WHERE TotalInvoice > MinimumValue AND TotalInvoice < MaximumValue
AND TableA.RepairShopId = ViewB.RepairShopId

But I’m not sure how to compare it the yearMonth field with the datetime field “LastUpdated”.

Any help is very appreciated!

Answer

here is how you can do it: I assumed LastUpdated column is the column from tableA which indicate date of

SELECT *
FROM TableA A
INNER JOIN ViewB B
ON A.RepairShopId = B.RepairShopId
AND A.TotalInvoice > B.MinimumValue 
AND A.TotalInvoice < B.MaximumValue
AND YEAR(LastUpdated) = B.year
AND MONTH(LastUpdated) = B.month