I need to write a query that will find a certain value, go up 2 rows above it, then insert a description

Question

Hello all once again,

I need help in writing a query. Please look at the ProductIdentifier Row below. I have inserted a column called LineItemInIIFFile. I need to write a code that will:
1. Find each ProductIdentifier = 'AWRPNS0003%%'
2.Go 2 rows upward from this product identifier and replace that ProductIdentifier with 'AWRPNS0003%%'.
3. After the replacements, I will insert a 'RS_PHN' in the LineItemInIIFFile column where the new ProductIdentifiers are 'AWRPNS0003%%'.
After all is found and all the 'RS_PHN' are inserted, I need to put all of this into a temp table somehow.

I recieved help yesterday for finding id's and then replacing that id with the id that is directly below it. I got help from a knowledgable member here and I tried to apply the same logic again but it does not work. Here is the code:

Select  SipID, 
        SaleInvoiceID, 
        AssociationNumber, 
        Priority, 
        TotalPrice, 
        TotalCost, 
        SerialNumber, 
        ContractNumber, 
        ActivatedThroughPAW, 
        DateCreatedatMidnight, 
        ReceivedDate, 
        InvoiceIDByStore, 
        Location, 
        ProductIdentifier, 
        Description, 
        ShortDescription, 
        GLAccountID1, 
        GLAccountID2, 
        GLAccountID3, 
        GLAccountID4, 
        CategoryName, 
        RevenueStreamID, 
        RevenueType
into PREPSTREAM
from TABLE_A3
order by InvoiceIDByStore,  
        Priority, AssociationNumber

--2. anything below a revenueidstream of 7, assign that number to replace that 7. ex. if you have a revenuestreamid of 7, go to the revenuestreamid below it, grab that id, replace the 7 with that id

;WITH cte (rowID, SipID, 
        SaleInvoiceID, AssociationNumber, Priority, TotalPrice, TotalCost,
         SerialNumber, ContractNumber, ActivatedThroughPAW, 
        DateCreatedatMidnight, ReceivedDate, InvoiceIDByStore, 
        Location, ProductIdentifier, Description, ShortDescription,
         GLAccountID1, GLAccountID2, GLAccountID3, GLAccountID4,
         CategoryName,   RevenueStreamID, RevenueType) AS 

 (SELECT row_number() OVER (ORDER BY InvoiceIDByStore) AS rowID, SipID, 
        SaleInvoiceID, AssociationNumber, Priority, TotalPrice, TotalCost, 
        SerialNumber, ContractNumber, ActivatedThroughPAW, DateCreatedatMidnight, 
        ReceivedDate, InvoiceIDByStore, Location, ProductIdentifier, Description, 
        ShortDescription, GLAccountID1, GLAccountID2, GLAccountID3, GLAccountID4, 
        CategoryName, RevenueStreamID, RevenueType
FROM PREPSTREAM AS tn
)
SELECT rowID, SipID, SaleInvoiceID, AssociationNumber, 
        Priority, TotalPrice, TotalCost, SerialNumber, 
        ContractNumber, ActivatedThroughPAW, DateCreatedatMidnight, 
        ReceivedDate, InvoiceIDByStore, Location, ProductIdentifier, 
        Description, ShortDescription, GLAccountID1, GLAccountID2, 
        GLAccountID3, GLAccountID4, CategoryName, RevenueStreamID, 
        RevenueType, CASE WHEN cte.revenueStreamID = 7
        THEN 
         ( SELECT cte2.RevenueStreamID 
        FROM cte AS cte2 
        WHERE cte2.rowID = cte.rowID + 1)
         ELSE cte.revenueStreamID 
        END as RevenueStreamID_Mod
        FROM cte 
        order by InvoiceIDByStore, Priority, AssociationNumber

I am only a beginning DBA of about 8 months and I have been put in charge of changing our Sales Import Process of the company that I am working for so please bear with me. Any help would be greatly appreciated.

alt text

Answer 1

So would something like this work? I'm going off of your other question that was closed. It might help you to get you pointed in the right direction anyway.

NOTE: This solution is assuming that the row you wish to update IS ALWAYS 2 rows lower (in terms of ID). I would strong suggest that you test this or any solution very thoroughly.

 CREATE TABLE #temp (id INT IDENTITY(1,1), Location CHAR(5), ProductIdentifier VARCHAR(25))
 GO

 INSERT #temp (Location, ProductIdentifier)
    SELECT 'DFW03', 'PCVPNS000003' UNION
    SELECT 'DFW03', 'LPEFNS000003' UNION
    SELECT 'DFW03', 'AWPHRI000524' UNION
    SELECT 'DFW03', 'AWUPRB000041' UNION
    SELECT 'DFW03', 'AWUPRB000305' UNION
    SELECT 'DFW03', 'AWPHRI00524' UNION
    SELECT 'DFW03', 'AWRPNS000305' UNION
    SELECT 'DFW03', 'AWUPRB000041' UNION
    SELECT 'DFW03', 'AWUPRB000042' UNION
    SELECT 'DFW03', 'AWPHSS000536' UNION
    SELECT 'DFW03', 'AWACRB000074' UNION
    SELECT 'DFW03', 'AWRPNS000343' UNION
    SELECT 'DFW03', 'AWACRB000075' UNION
    SELECT 'DFW03', 'AWACRB000074' UNION
    SELECT 'DFW03', 'AWPHSS000536' UNION
    SELECT 'DFW03', 'AWRPNS000343' UNION
    SELECT 'DFW03', 'PPOSMAT00001'
GO

;WITH MyCte AS
    (SELECT ID, Location, ProductIdentifier
       FROM #temp 
         WHERE ProductIdentifier = 'AWRPNS000343'
)
UPDATE a
SET ProductIdentifier = b.ProductIdentifier
FROM #temp a 
    INNER JOIN MyCte b ON a.id = b.id - 2
GO  
SELECT * FROM #temp 
GO
DROP TABLE #temp
GO

I need to write a query that will find a certain value, go up 2 rows above it, then insert a description

Follow this question

Related Questions