Finding only the modified rows
-
Hi friends,
Here is an interresting problem (probably an exercise for Jonathan
)Given the following tables with the following data:
create table supplier (su_id int, su_name varchar(50))
create table transporter (tr_id int, tr_name varchar(50))
create table product (pr_id int, pr_name varchar(50), su_id int, tr_id int)
create table productprices (pr_id int, pp_price money, pp_startdate datetime, pp_enddate datetime)
create table modifications (md_type char(2), md_id int, md_date datetime)
insert into supplier values (1, 'supplier 1')
insert into supplier values (2, 'supplier 2')
insert into transporter values (1, 'transporter 1')
insert into transporter values (2, 'transporter 2')
insert into product values (1, 'product 1', 1, 1)
insert into product values (2, 'product 2', 1, 2)
insert into product values (3, 'product 3', 2, 1)
insert into product values (4, 'product 4', 2, 2)
insert into productprices values (1, 100, '20030101', '20031031')
insert into productprices values (1, 110, '20031101', '20031231')
insert into productprices values (2, 200, '20030101', '20041231')
insert into productprices values (3, 300, '20030101', '20030531')
insert into productprices values (3, 310, '20030601', '20051231')
insert into productprices values (4, 400, '20031110', '20051231')
insert into modifications values ('su', 1, '20000101')
insert into modifications values ('su', 2, '20000101')
insert into modifications values ('tr', 1, '20000101')
insert into modifications values ('tr', 2, '20000101')
insert into modifications values ('pr', 1, '20000101')
insert into modifications values ('pr', 2, '20000101')
insert into modifications values ('pr', 3, '20000101')
insert into modifications values ('pr', 4, '20031110')
insert into modifications values ('pr', 3, '20031105')
I should get the following list BUT only for the rows having been modified after a given date (let's say after 01/11/2003) or the price date has become valid after the given date
selectpr.pr_id,
pr_name,
su_name,
tr_name,
pp_price
fromproduct prjoin supplier su on pr.su_id = su.su_id
join transporter tr on pr.tr_id = tr.tr_id
join productprices pp on pp.pr_id = pr.pr_id
wheregetdate() between pp_startdate and pp_enddate
Any idea?
Bye
Gabor
Bye
Gabor -
I've forgotten to say that the modifications table is feed throu insert/update triggers.
So the goal is to show all rows where a record exists for one of the component table for the given record in the modifications table or the price period change has occured since the parameter date
Bye
Gabor
Bye
Gabor -
Thanks, I needed the exercise.
DECLARE @cutoff datetime
SET @cutoff = '20031101'
SELECT pr.Pr_Id, Pr_Name, Su_Name, Tr_Name, PP_Price
FROM Product pr JOIN Supplier su ON pr.Su_Id = su.Su_Id
JOIN Transporter tr ON pr.Tr_Id = tr.Tr_Id
JOIN ProductPrices pp ON pp.Pr_Id = pr.Pr_Id
WHERE GETDATE() BETWEEN pp.PP_StartDate AND pp.PP_EndDate
AND
(EXISTS (SELECT *
FROM Modifications
WHERE Md_Type = 'pr' AND Md_Id = pr.Pr_Id AND Md_Date > @cutoff) AND
EXISTS (SELECT *
FROM Modifications
WHERE Md_Type = 'tr' AND Md_Id = tr.Tr_Id AND Md_Date > @cutoff) AND
EXISTS (SELECT *
FROM Modifications
WHERE Md_Type = 'su' AND Md_Id = su.Su_Id AND Md_Date > @cutoff)
OR pp.PP_StartDate > @cutoff)--Jonathan
--Jonathan -
Jonathan,
I have to tell you that unfortunatly there is an error in your code
The product id 3 and 4 should have been retrived as both of the products have been modified after the cutoff date
quote:
insert into modifications values ('pr', 4, '20031110')insert into modifications values ('pr', 3, '20031105')
How do you like this:
DECLARE @cutoff datetime
SET @cutoff = '20031101'
SELECT pr.Pr_Id, Pr_Name, Su_Name, Tr_Name, PP_Price
FROM Product pr JOIN Supplier su ON pr.Su_Id = su.Su_Id
JOIN Transporter tr ON pr.Tr_Id = tr.Tr_Id
JOIN ProductPrices pp ON pp.Pr_Id = pr.Pr_Id
WHERE GETDATE() BETWEEN pp.PP_StartDate AND pp.PP_EndDate
AND ((EXISTS (SELECT *
FROM Modifications
WHERE Md_Type = 'pr' AND Md_Id = pr.Pr_Id AND Md_Date > @cutoff)
OR EXISTS (SELECT *
FROM Modifications
WHERE Md_Type = 'tr' AND Md_Id = tr.Tr_Id AND Md_Date > @cutoff)
OR EXISTS (SELECT *
FROM Modifications
WHERE Md_Type = 'su' AND Md_Id = su.Su_Id AND Md_Date > @cutoff)
OR pp.PP_StartDate > @cutoff))
Any how thank you for the idea!
Bye
Gabor
Bye
Gabor
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply