A query I'd like to be able to run is one where it looks through all of the maintenance logs of our vehicles, finds those where the part is question was used, and then follow it all back to output the VIN of the distinct list of vehicles. (I already have this part written, but for all I know it may need to be optimized)
After that, the part's Notes column (Parts.Notes) needs to be overwritten starting after a particular key phrase in the part's existing Notes column ('COMPATIBLE WITH...[line break]'), and after the new list has been input to add a line break followed by 'Last Compatibility Report: ' + GETDATE()'
I would like for this to run through all rows in the Parts table, so that it need only be run, say- once a quarter. (This report is later likely going to be tied in to another query which will need to be run once a quarter, and that one will update Parts.Minstock and Parts.Maxstock using a section of a prior query I just got help with today.)
The reason I'm trying to write this report is that some times our Foremen need to know what parts we have in stock that are compatible with a vehicle, and while usually the vehicle number doesn't change, the VIN absolutely does not change, so by running a special search built into the UI of our inventory software I can search for those parts where their Notes column contains the VIN number.
Here is what I have so far- I'm not sure how to go about getting an UPDATE to start at a particular point in Notes, so there are two different sections:
Tables:
Work Orders
RecordID LONG (PK)
VehKey LONG (FK)
Vehicles
RecordID LONG (PK)
VIN TEXT
WoPart
RecordID LONG (PK)
PartNumber TEXT
Parts
PartNumber TEXT
Notes MEMO
Query :
CREATE TABLE
WoPart
(RecordID INT
,PartNumber VARCHAR(25))
,WorkOrders
(RecordID INT
,VehKey VARCHAR(25))
,Vehicles
(RecordID INT
,VIN VARCHAR(25))
,Parts
(PartNumber VARCHAR(25)
,Notes VARCHAR(MAX))
INSERT INTO
Parts
(PartNumber,Notes) VALUES('Part1','')
,(PartNumber,Notes) VALUES('Part2','Begin note - COMPATIBLE WITH xyz')
INSERT INTO
Vehicles
(RecordId,VIN) VALUES(1,'123456789')
,(RecordId,VIN) VALUES(2,'987654321')
INSERT INTO
Workorders
(RecordId,VehKey) VALUES(1,'123456789')
,(RecordId,VehKey) VALUES(2,'987654321')
INSERT INTO
WoPart
(RecordId,PartNumber) VALUES(1,'Part1')
,(RecordId,PartNumber) VALUES(2,'Part1')
,(RecordId,PartNumber) VALUES(2,'Part2')
--Make all of part's notes before "COMPATIBLE WITH..." a variable
DECLARE
@Notes nvarchar(8000) = Parts.Notes
UPDATE Parts
--VIN list replaces anything written after "COMPATIBLE WITH..." in Notes field
SET Notes = CONCAT(@Notes,[VIN list query])
--Need to make the query go through all parts one part number at a time
SELECT DISTINCT VIN
FROM Vehicles
INNER JOIN WorkOrders
ON Vehicles.RecordID = WorkOrders.VehKey
--Follow back WoPart results to the distict vehicles the specified part was used on
WHERE VehKey IN (SELECT DISTINCT VehKey
FROM WorkOrders
INNER JOIN WoPart
ON WorkOrders.RecordID = WoPart.RecordID
--Search all WoPart entries for when the specified part number was used
WHERE WorkOrders.RecordID IN (SELECT RecordNum
FROM WoPart
WHERE PartNumber = 'RS4636'))
ORDER BY VIN;
I remember reading a tip online that you should use SELECT to test your UPDATE expression and make sure that it would be updating the correct field properly before actually running the query, and I'm guessing that would involve writing something as simple as...
SELECT
PartNumber AS [Part Number]
,Notes
,[Query] AS [New Notes]
FROM
Parts
Here is an example of what the query should look like, from the subqueries, to the inner query, to the outer query and final result (copy-paste into 'Notepad' for proper display):
SUBQUERY AND INNER QUERY:
WoPart
+----------+------------+
| RecordID | PartNumber |
+----------+------------+
| 12345678 | BD7250 |
| 23456789 | BD7250 |
| 34567890 | BD7250 |
| 45678901 | BD7250 |
| 56789012 | BD7250 |
| 67890123 | BD7250 |
| ... | BD7250 |
WorkOrders
+----------+--------+
| RecordID | VehKey |
+----------+--------+
| 12345678 | 23 |
| 23456789 | 25 |
| 34567890 | 48 |
| 45678901 | 51 |
| 56789012 | 53 |
| 67890123 | 59 |
| ... | ... |
Vehicles
+----------+-------------------+
| [VehKey] | VIN |
+----------+-------------------+
| 123 | 4DRBUAAN8BB317993 |
| 234 | 4DRAPAFH27A289580 |
| 345 | 4DRBUAAN78B577679 |
| 456 | 4DRBUAAN99B045870 |
| 567 | 1HVBBAAN41H511534 |
| 678 | 1HVBBAAN61H511535 |
| ... | ... |
OUTER QUERY:
Parts
+------------+------------------------------------------------------+------------------------------------------------------+
| PartNumber | Notes | New Notes |
+------------+------------------------------------------------------+------------------------------------------------------+
| BD7250 | Descriptions: High Velocity Dual-Flow Lube Spin-on | Descriptions: High Velocity Dual-Flow Lube Spin-on |
| | Contains: Venturi-Type Cone | Contains: Venturi-Type Cone |
| | Fits: International Engines, Trucks | Fits: International Engines, Trucks |
| | Replaces: International 1841606-C1, 1842639-C91; | Replaces: International 1841606-C1, 1842639-C91; |
| | Donaldson P550656; Fleetguard LF9025 | Donaldson P550656; Fleetguard LF9025 |
| | Thread: M95 x 2.0 | Thread: M95 x 2.0 |
| | O.D.: 4-21/32 (118.3) | O.D.: 4-21/32 (118.3) |
| | Length: 14-7/32 (361.2) | Length: 14-7/32 (361.2) |
| | I. Gskt.: [1] Included | I. Gskt.: [1] Included |
| | O. Ring: [2] Viton Included | O. Ring: [2] Viton Included |
| | | |
| | COMPATIBLE WITH... | COMPATIBLE WITH... |
| | 4DRBUAAN8BB317993 | 4DRBUAAN8BB317993 |
| | 4DRAPAFH27A289580 | 4DRAPAFH27A289580 |
| | 4DRBUAAN78B577679 | 4DRBUAAN78B577679 |
| | | 4DRBUAAN99B045870 |
| | Last Compatibility Report: 2/7/2017 | 1HVBBAAN41H511534 |
| | | 1HVBBAAN61H511535 |
| | | ... |
| | | |
| | | ... |
| | | 1HTMMAAM67H476118 |
| | | |
| | | Last Compatibility Report: 3/23/2017 |
+------------+------------------------------------------------------+------------------------------------------------------+