Save values from column to an array and loop through

Question

I have two tables: CustomerOrder and OrderItem.

CustomerOrder has these columns: ID, CustomerId, DateOrdered, IsCreditCardPayment. OrderItem has these columns: OrderId(FK from CustomerOrder), ProductId, Quantity, Price.

What I need to do is calculate the amount of the provision the bank takes from my business when my customers pay with a credit card. The provision is 2%.

My question is how to save the ID's in an array of those orders paid by a credit card and then tell SQL Server to only multiply the quantity and price of the order items that have that same ID from the array?

So far I have this blob:

SELECT ID FROM CustomerOrder 
WHERE DateOrdered BETWEEN '2000-01-01' AND '2000-02-01' 
IF( ? )
    BEGIN
    SELECT ID FROM CustomerOrder
    WHERE IsCreditCardPayment = 1
    END
FROM CustomerOrder

I am using:

Sql Server 14.0.17230.0

Andrey Nikolov · Accepted Answer · 2018-11-30 12:18:51Z

2

You don't need to save these IDs. Just update them directly:

UPDATE CustomerOrder SET
    ProvisionAmount = 0.02 * OrderAmount
WHERE DateOrdered BETWEEN '2000-01-01' AND '2000-02-01' and IsCreditCardPayment = 1

You may also want to tack which orders are already updated, e.g. by adding ProvosionAmount is null or add some flag.

If you really need to "save" these IDs, you can use a sub-query:

UPDATE AnotherTable SET SomeField = ... where OrderId in (SELECT ID FROM 
    CustomerOrder 
    WHERE DateOrdered BETWEEN '2000-01-01' AND '2000-02-01' )

Or save them to a temp table or table variable:

-- Use table variable
DECLARE @OrderIDs table(OrderId int)
insert into @OrderIDs
SELECT ID FROM 
CustomerOrder 
WHERE DateOrdered BETWEEN '2000-01-01' AND '2000-02-01'

-- Create a new temp table
SELECT ID FROM 
insert into #OrderIDs
CustomerOrder 
WHERE DateOrdered BETWEEN '2000-01-01' AND '2000-02-01'

-- Insert into pre-created table
CREATE TABLE #OrderIDs(OrderId int)
insert into #OrderIDs
SELECT ID FROM 
CustomerOrder 
WHERE DateOrdered BETWEEN '2000-01-01' AND '2000-02-01'

and then use one of the above to update your data.

edited Nov 30, 2018 at 12:18

answered Nov 30, 2018 at 12:12

Andrey Nikolov

13.5k3 gold badges26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shepherd Over a year ago

Hey Andrey thank you for taking the time to answer my question. I read somewhere that data that could be calculated isn't supposed to be put in a table because it was considered a waste of disk space and rather be calculated "on the fly". However the solution you offered seems pretty simple and awesome, therefore I'm accepting it.

Collectives™ on Stack Overflow

Save values from column to an array and loop through

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related