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!
Advertisement
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