x

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

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][1]

[1]: http://ask.sqlservercentral.com/storage/temp/186-goup2rows.jpg
goup2rows.jpg (83.9 kB)
more ▼

asked May 03, 2012 at 02:50 PM in Default

cdurham gravatar image

cdurham
230 22 22 24

How do you know the order of items? (ie what's your SELECT statement that brings back the data set that contains your AWRPNS000343 record and the record to be adjusted)?
May 03, 2012 at 03:02 PM ThomasRushton ♦
Here is what I have so far:

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 REVISEDTABLE
from TABLE_A3
order by InvoiceIDByStore, Priority, AssociationNumber

--add a column to put the line item description. ex RS_PHN
ALTER TABLE REVISEDTABLE
ADD LineItemInIIFFile varchar (50)

I have noticed when the ProductIdentifier = 'AWRPNS000343', The Association is 1, and the Priority is 3.
I am just needing a query that will find the ProductIdentifiers that equal 'AWRPNS000343'. When this is found (hence, the Association will be 1, and the priority will be 1), I need to go up 2 rows. When I go up 2 rows, I need to put 'RSPHN' in the LineItemInIIFFile column. I need for this process to continue until I reach the end of the records.
May 03, 2012 at 03:09 PM cdurham
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered May 07, 2012 at 02:53 AM

JohnM gravatar image

JohnM
6.8k 1 3 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x41
x24
x17
x3

asked: May 03, 2012 at 02:50 PM

Seen: 975 times

Last Updated: May 07, 2012 at 02:53 AM