November 6, 2015 at 8:18 am
HI, I’m a newbie with SSIS and I live in the Netherlands, so my English may not be so good.
I have a stage database with three tables: product, order_header and order_item.
The table product contains the columns product_code, product, date_price (date from which the price is valid), cost and price.
The table order_hrader contains the columns order_id, order_date and customer_number.
The table order_detail contains the colums order_id, line_no, product_code, cost, price and margin.
I also have a NDS database with the tables product, order_header and order_item. These tables have the same columns as the stage tables AND (of course) a key column, product_key for the table product and order_key for the table order_header and the table order_item.
I have made a SSIS package to populate the NDS tables from the stage tables.
I use a Execute SQL task to populate the NDS tables order_header and order_item. The query that I use for the table order_item is
insert into NDS.dbo.order_item
(order_key, line_no, product_key, price, cost, margin, created, last_updated)
select distinct
oh.order_key as order_key,
od.line_no as line_no,
p.product_key as product_key,
od.price as price,
od.cost as cost,
od.margin as margin,
GETDATE() as created,
GETDATE() as last_updated
from stage.dbo.order_detail od
left join NDS.dbo.product p
on od.product_code = p.product_code
left join NDS.dbo.order_header oh
on od.order_id = oh.order_id
where not exists
( select * from NDS.DBO.order_item oi
where oi.order_key = oh.order_key AND oi.line_no = od.line_no)
This works fine until the date_price changes. Then , instead of one new row in the NDS table order_item, there are two new rows. The problem is that the column product_code returns two values, because there are two different product_keys (one for the first date_price and one for the next date_price).
Can anyone help me and tell me what I did wrong and what to do to make it work?
Thanks a lot
November 9, 2015 at 3:38 am
Hi
If I clearly understand, you want to insert only the latest price from dbo.product, don't you?
Best regards,
Mike
November 9, 2015 at 6:04 am
Hi Mike,
Thanks for your reply. Yes, I want a new row for every sale since the price has changed.
Please allow me to give you an example.
The price for product A on January 1 2015 is € 10. Product A has been sold on January 5 2015. Besides the other SSIS packages to populate the Stage and NDS tables, I execute a SSIS package that contains the query above to populate the NDS table order_item.
Then, on June 1 2015 the price for product A changes to € 11, product A is sold on June 5 2015, so I execute all the SSIS packages again, but this time the query that populates order_item affects 2 rows. A new row for the sale on January 5th with the new product_key and one new row for the sale on June 5th, but I only want one new row for the sale on June 5th. The row with the sale on January 5th may not be changed or deleted because I want to be able to see all the sales in 2015 at the end of this year.
I hope you can tell me how to achieve this.
Best regards,
Sjors
November 9, 2015 at 6:18 am
Hi Sjorsd
Please try with cte:
WITH latest AS (SELECT ROW_NUMBER() over(PARTITION BY product_code, ORDER BY date_price DESC) AS RowNumber,product_key ,price
FROM dbo.product )
insert into NDS.dbo.order_item
(order_key, line_no, product_key, price, cost, margin, created, last_updated)
select distinct
oh.order_key as order_key,
od.line_no as line_no,
p.product_key as product_key,
od.price as price,
od.cost as cost,
od.margin as margin,
GETDATE() as created,
GETDATE() as last_updated
from stage.dbo.order_detail od
left join latest p
on od.product_code = p.product_code
AND p.RowNumber = 1
left join NDS.dbo.order_header oh
on od.order_id = oh.order_id
where not exists
( select * from NDS.DBO.order_item oi
where oi.order_key = oh.order_key AND oi.line_no = od.line_no)
And let me know if it's OK for you.
Br,
Mike
November 9, 2015 at 8:01 am
Hi Mike,
Thanks so far, but the query returns the message
Msg 207, Level 16, State 1, Line 19
Invalid column name 'product_code'.
November 9, 2015 at 8:17 am
Forgive me, please replace line
WITH latest AS (SELECT ROW_NUMBER() over(PARTITION BY product_code, ORDER BY date_price DESC) AS RowNumber,product_key ,price
with code
WITH latest AS (SELECT ROW_NUMBER() over(PARTITION BY product_code, ORDER BY date_price DESC) AS RowNumber,product_key,product_code ,price
November 9, 2015 at 8:18 am
Hi Mike,
I have changed
WITH latest AS (SELECT ROW_NUMBER() over(PARTITION BY product_code, ORDER BY date_price DESC) AS RowNumber,product_key ,price
FROM dbo.product )
In
WITH latest AS (SELECT ROW_NUMBER() over(PARTITION BY product_code, ORDER BY date_price DESC) AS RowNumber,product_key ,product_code, price
FROM dbo.product )
So the correct query is
WITH latest AS (SELECT ROW_NUMBER() over (PARTITION BY product_code ORDER BY datum_prijs DESC)
AS RowNumber, product_key, product_code, totale_prijs
FROM NDS.dbo.product)
insert into NDS.dbo.order_item
(order_key, line_no, kenteken, product_key, prijs, unit_cost, marge, created, last_updated)
select distinct
oh.order_key as order_key,
od.line_no as line_no,
od.kenteken as kenteken,
p.product_key as product_key,
od.catalogusprijs as prijs,
od.totale_prijs as unit_cost,
od.marge as marge,
GETDATE() as created,
GETDATE() as last_updated
from stage.dbo.order_detail od
left join latest p
on od.product_code = p.product_code AND p.RowNumber = 1
inner join NDS.dbo.order_header oh
on od.order_id = oh.order_id
where not exists
( select * from NDS.DBO.order_item oi
where oi.order_key = oh.order_key AND oi.line_no = od.line_no)
Now the query returns one row, with the new product_key and the latest price.
This is what I wanted
Thank you very much for your help!
BR, Sjors
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy