July 22, 2010 at 5:00 am
LadyG (7/22/2010)
The problem is with the last part of the script, insert & select. Obvioulsy, as new_cost doesn't exist in the sales table, the calculated data needs to go somewhere in order to extract into the log table. Or am I completely on the wrong train of thought?
Without a sample data set to test against, I could only offer a guess.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 22, 2010 at 6:55 am
Sent via PM. Would appreciate your ideas. Thanks
July 22, 2010 at 7:43 am
Would appreciate your input to this.
Script doesn't quite work. I'm not getting the values expects, getting 0s and nulls. What is wrong?? Have attached attributes for both tables.
SET NOCOUNT ON
DECLARE @income money,
@cost money,
@costAdd money,
@order_id bigint,
@order_value bigint,
@order_line_no bigint,
@promoID int,
@PromoCheck int,
@entCode bigint,
@new_cost money
UPDATE tmp_hhrsales2 SET
@costAdd = CASE
WHEN net_income = 0 and cost <> 0 THEN @costAdd + @cost
WHEN net_income <> 0 and @costAdd <> 0 THEN 0
ELSE @costAdd END,
cost = CASE
WHEN net_income = 0 and cost <> 0 THEN 0
WHEN net_income <> 0 and @costAdd <> 0 THEN cost + @costAdd
ELSE cost END
-- extra columns for log_hhrsales_cos_change: oldcost and newcost
FROM tmp_hhrsales2 WITH(TABLOCKX)
WHERE PL_Order_Value <> 0 and PromoCheckID <> 60 AND Entity_Code <> 10000001054
OPTION (MAXDOP 1)
INSERT INTO log_hhrsales_cos_change
SELECT PL_Order, PL_Txno, oldcost, new_cost
FROM tmp_hhrsales2
WHERE PL_Order_Value <> 0 and PromoCheckID <> 60 AND Entity_Code <> 10000001054
July 22, 2010 at 7:48 am
LadyG (7/22/2010)
Sent via PM. Would appreciate your ideas. Thanks
No PM received. Without table creation scripts and some sample data, it's difficult to proceed.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 22, 2010 at 8:02 am
Sent PM again. HAve you received it??
July 22, 2010 at 9:13 am
All attached.
July 22, 2010 at 10:14 am
LadyG (7/22/2010)
Sent PM again. HAve you received it??
Yes thank you, but there is still no script to populate a table with data to use for testing.
Typically it would be something like:
CREATE TABLE #Test (TestID INT, TestStuff VARCHAR(10))
INSERT INTO #Test (TestID, TestStuff)
SELECT 1, 'First test' UNION ALL
SELECT 2, 'Second test' UNION ALL
SELECT 3, 'Third test'
where the column names and values are representative of the problem you are attempting to solve. If you click on the highlighted word this in my signature, it will show you how to do this quickly.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 22, 2010 at 11:06 am
Chris Morris-439714 (7/21/2010)
Even if there is a clustered index on the table in the order that the update is supposed to be executed, there's no guarantee that the rows will be FETCHed in the correct order...
You know, of course, that I have to disagree with that if it's a simple update with no cursor. 😉
{EDIT} Ah... sorry... took it out of context.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2010 at 2:36 am
Jeff Moden (7/22/2010)
Chris Morris-439714 (7/21/2010)
Even if there is a clustered index on the table in the order that the update is supposed to be executed, there's no guarantee that the rows will be FETCHed in the correct order...
You know, of course, that I have to disagree with that if it's a simple update with no cursor.😉{EDIT} Ah... sorry... took it out of context.
No worries Jeff. I get a warm fuzzy feeling knowing you're peering over my shoulder on this one.
All we need now is a set of sample data to munch.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 23, 2010 at 2:45 am
Here you go Chris.
Thanks
July 23, 2010 at 2:47 am
oops, forgot this one!
July 23, 2010 at 2:49 am
LadyG (7/23/2010)
Here you go Chris.Thanks
Lovely job LadyG, thanks for this!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 23, 2010 at 3:09 am
LadyG (7/23/2010)
Here you go Chris.Thanks
There are a few errors in here - the only one which appears to matter is the CREATE INDEX..statement which references a column which doesn't exist in the table.
The results of the calculation are dependant upon this index.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 23, 2010 at 3:49 am
My apologies Chris, the table has a huge host of columns so had to pick out the relevant ones. I have redone the script and data capture as well.
July 23, 2010 at 6:06 am
This uses the original data set:
-- change the name of the working table to #_hhrsales2 for testing
-- add a new column ([original_cost] [money] NULL) to the working table
-- There's no work to be done in the original dataset,
-- so create a row to work on
UPDATE #_hhrsales2 SET cost = 0.22
WHERE [PL_Order] = 105553117866341 AND [PL_TxNo] = 13 -- row 6, filtered set, ordered
-- process through the set in the designated order and:-
-- if a row is intercepted where [net_income] = 0 but [cost] <> 0
-- add the [cost] to the variable, and set [cost] to 0
-- if a row is intercepted where [net_income] <> 0 and there's a nonzero value in the variable
-- add the value in the variable to the [cost], and reset the variable to 0.
DECLARE @costSum MONEY, @newcost MONEY
SELECT @costSum = 0, @newcost = 0
UPDATE t SET
original_cost = cost,
@newcost = cost = CASE
WHEN net_income = 0 AND cost <> 0 THEN 0
WHEN net_income <> 0 AND @costSum <> 0 THEN cost + @costSum
ELSE cost END,
@costSum = CASE
WHEN net_income = 0 AND cost <> 0 THEN @costSum + cost
WHEN net_income <> 0 AND @costSum <> 0 THEN 0
ELSE @costSum END
FROM #_hhrsales2 t WITH(TABLOCKX)
WHERE PL_Order_Value <> 0 AND PromoCheckID <> 60 AND Entity_Code <> 10000001054
OPTION (MAXDOP 1)
-- check the results, rows 3 and 4
SELECT *
FROM #_hhrsales2
WHERE [PL_Order] = 105553117866341
ORDER BY [PL_Order], [PL_TxNo] DESC
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply