CODEWITHSUNDEEP
sqlsql-servert-sql
fvdev
fvdev

Reputation: 35

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!

Upvotes: 0

Views: 47

Answers (1)

eshirvana
eshirvana

Reputation: 24603

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

Upvotes: 1

Related Questions