Sql-server – Need to Add Update to Query

distinctsql servert-sqlupdate

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                 |
+------------+------------------------------------------------------+------------------------------------------------------+

Best Answer

Here is something you can play with - I did minimal testing in this example and I could have missed something or misunderstood your question or intentions. 

--Demo setup
set nocount on
IF OBJECT_ID('Parts') IS NOT NULL
    DROP TABLE Parts;
IF OBJECT_ID('Vehicles') IS NOT NULL
    DROP TABLE Vehicles;
IF OBJECT_ID('Workorders') IS NOT NULL
    DROP TABLE Workorders;
IF OBJECT_ID('WoPart') IS NOT NULL
    DROP TABLE WoPart;
CREATE TABLE Parts (PartNumber VARCHAR(25), Notes VARCHAR(MAX))
CREATE TABLE Vehicles (RecordId INT,VIN VARCHAR(25))
CREATE TABLE Workorders (RecordId INT,VehKey VARCHAR(25))
CREATE TABLE WoPart (RecordId INT,PartNumber VARCHAR(25))

INSERT INTO Parts (PartNumber,Notes) VALUES('Part1','')
INSERT INTO Parts (PartNumber,Notes) VALUES('Part2','Begin note - COMPATIBLE WITH xyz')
INSERT INTO Vehicles (RecordId,VIN) VALUES(1,'123456789')
INSERT INTO Vehicles (RecordId,VIN) VALUES(2,'987654321')
INSERT INTO Workorders (RecordId,VehKey) VALUES(1,'123456789')
INSERT INTO Workorders (RecordId,VehKey) VALUES(2,'987654321')
INSERT INTO WoPart (RecordId,PartNumber) VALUES(1,'Part1')
INSERT INTO WoPart (RecordId,PartNumber) VALUES(2,'Part1')
INSERT INTO WoPart (RecordId,PartNumber) VALUES(2,'Part2')
go

--To keep the concatenation simple, I created a TVF to return the 
--concatenated strings of VIN's for the part
--The function concatenates with commas for easier visualization, but could
--be changed to use line feeds
IF EXISTS 
(
SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[ConcatenateVinsForPart]')
AND type IN (N'FN',N'IF',N'TF',N'FS',N'FT')
)
    DROP FUNCTION [dbo].[ConcatenateVinsForPart]
GO

CREATE FUNCTION ConcatenateVinsForPart (@PartNumber VARCHAR(25))
RETURNS @temptable TABLE (ConcatString VARCHAR(Max))

BEGIN
    DECLARE @ConcatString VARCHAR(max)
    ;
    WITH DistinctVins AS (
            SELECT DISTINCT VIN
            FROM Vehicles veh
            JOIN Workorders wo ON wo.VehKey = veh.VIN
            JOIN WoPart wop ON wop.RecordId = wo.RecordId
            WHERE wop.PartNumber = @PartNumber
            )

    SELECT @ConcatString = isnull(@ConcatString + ',', '') + VIN
    FROM DistinctVins
    INSERT INTO @temptable(ConcatString) values(@ConcatString)

    RETURN
END
GO

--If you want to see the current notes and the NewNotes BEFORE
--actually running this update, you can use this SELECT statement
SELECT Notes,NewNotes = 
CASE 
    --Checking for note that's never had COMPATIBLE WITH string
    WHEN 0 = PATINDEX('%COMPATIBLE WITH%', NOTES) 
        THEN notes + 'COMPATIBLE WITH ' + VinsForPart.ConcatString 
        + '-Last Compatibility Report: ' + convert(varchar(20),GETDATE())
    --add 15 to get past COMPATIBLE WITH string
    ELSE substring(notes, 1, PATINDEX('%COMPATIBLE WITH%', NOTES) + 15) 
    + ' '
    + VinsForPart.ConcatString
    + '-Last Compatibility Report: ' + convert(varchar(20),GETDATE())
    END
FROM Parts p
CROSS APPLY (
    SELECT ConcatString
    FROM ConcatenateVinsForPart(p.PartNumber)
    ) VinsForPart       


--Update the part table
UPDATE P
SET Notes = CASE 
        --Checking for note that's never had COMPATIBLE WITH string
        WHEN 0 = PATINDEX('%COMPATIBLE WITH%', NOTES) 
            THEN notes + 'COMPATIBLE WITH ' + VinsForPart.ConcatString 
            + '-Last Compatibility Report: ' + convert(varchar(20),GETDATE())
        --add 15 to get past COMPATIBLE WITH string
        ELSE substring(notes, 1, PATINDEX('%COMPATIBLE WITH%', NOTES) + 15) 
        + ' '
        + VinsForPart.ConcatString
        + '-Last Compatibility Report: ' + convert(varchar(20),GETDATE())
        END
FROM Parts p
CROSS APPLY (
    SELECT ConcatString
    FROM ConcatenateVinsForPart(p.PartNumber)
    ) VinsForPart       

--Check to see if parts was updated correctly
select * from Parts
Related Question