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