join problem

Hi,
I have problem with joining.
DDL:
CREATE TABLE Items (
item_code INTEGER NOT NULL,
item_description VARCHAR(50) NOT NULL,
PRIMARY KEY (item_code)
);
CREATE TABLE VAT_Groups (
vat_group CHAR(1) NOT NULL,
vat_description CHAR(20) NOT NULL,
PRIMARY KEY (vat_group)
);
CREATE TABLE VAT_Percents (
vat_group CHAR(1) NOT NULL,
start_date DATE NOT NULL,
end_date DATE,
vat_percent NUMERIC(5,2) NOT NULL,
PRIMARY KEY (vat_group, start_date),
FOREIGN KEY (vat_group) REFERENCES VAT_Groups (vat_group)
);
CREATE TABLE Items_VAT_History (
item_code INTEGER NOT NULL,
vat_group CHAR(1) NOT NULL,
start_date DATE NOT NULL,
end_date DATE,
PRIMARY KEY (item_code, vat_group, start_date),
FOREIGN KEY (item_code) REFERENCES Items (item_code),
FOREIGN KEY (vat_group) REFERENCES VAT_Groups (vat_group)
);
Sample data:
INSERT INTO Items VALUES (1, 'Vegetables');
INSERT INTO Items VALUES (2, 'Vine');
INSERT INTO Items VALUES (3, 'Milk');
INSERT INTO VAT_Groups VALUES ('E', 'Common VAT');
INSERT INTO VAT_Groups VALUES ('C', 'Lower VAT');
INSERT INTO VAT_Percents VALUES ('E', '2004-01-01', '2004-12-31', 20.00);
INSERT INTO VAT_Percents VALUES ('E', '2005-01-01', NULL, 18.00);
INSERT INTO VAT_Percents VALUES ('C', '2004-01-01', NULL, 8.00);
INSERT INTO Items_VAT_History VALUES (1, 'E', '2004-01-01', '2005-06-30');
INSERT INTO Items_VAT_History VALUES (1, 'C', '2005-07-01', NULL);
INSERT INTO Items_VAT_History VALUES (2, 'E', '2004-01-01', NULL);
INSERT INTO Items_VAT_History VALUES (3, 'C', '2004-01-01', NULL);
Desired Result:
item_code start_date end_date vat_percnt
---
1 2004-01-01 2004-12-31 20.00
1 2005-01-01 2005-06-30 18.00
1 2005-07-01 NULL 8.00
2 2004-01-01 2004-12-31 20.00
2 2005-01-01 NULL 18.00
3 2004-01-01 NULL 8.00
or equally good result:
item_code start_date end_date vat_percnt
---
1 2004-01-01 2004-12-31 20.00
1 2005-01-01 2005-06-30 18.00
1 2005-07-01 NULL 8.00
2 2004-01-01 2004-12-31 20.00
2 2005-01-01 NULL 18.00
3 2004-01-01 2004-12-31 8.00
3 2005-01-01 NULL 8.00
Thanks
Srdjan MijatovHi
Try:
SELECT i.item_code,
CONVERT(CHAR(10),CASE WHEN h.start_date >= p.start_date THEN h.start_date
ELSE P.start_date END, 121) AS start_date,
CONVERT(CHAR(10),CASE WHEN ISNULL(h.end_date,'29991231') <=
ISNULL(p.end_date,'29991231') THEN h.end_date ELSE p.end_date END,121) AS
end_date,
p.vat_percent
FROM Items i
JOIN Items_VAT_History h on i.item_code = h.item_code
JOIN VAT_Percents P on h.vat_group = p.vat_group
AND ( ( h.start_date >= p.start_date AND h.start_date <=
ISNULL(p.end_date,'29991231') )
OR ( h.end_date <= ISNULL(p.end_date,'29991231') and
ISNULL(h.end_date,'29991231') >= p.start_date )
OR ( h.start_date <= p.start_date AND ISNULL(h.end_date,'29991231') >=
ISNULL(p.end_date,'29991231') )
)
ORDER BY i.item_code, start_date
John
"Srdjan Mijatov" wrote:

> Hi,
> I have problem with joining.
>
> DDL:
> CREATE TABLE Items (
> item_code INTEGER NOT NULL,
> item_description VARCHAR(50) NOT NULL,
> PRIMARY KEY (item_code)
> );
> CREATE TABLE VAT_Groups (
> vat_group CHAR(1) NOT NULL,
> vat_description CHAR(20) NOT NULL,
> PRIMARY KEY (vat_group)
> );
> CREATE TABLE VAT_Percents (
> vat_group CHAR(1) NOT NULL,
> start_date DATE NOT NULL,
> end_date DATE,
> vat_percent NUMERIC(5,2) NOT NULL,
> PRIMARY KEY (vat_group, start_date),
> FOREIGN KEY (vat_group) REFERENCES VAT_Groups (vat_group)
> );
> CREATE TABLE Items_VAT_History (
> item_code INTEGER NOT NULL,
> vat_group CHAR(1) NOT NULL,
> start_date DATE NOT NULL,
> end_date DATE,
> PRIMARY KEY (item_code, vat_group, start_date),
> FOREIGN KEY (item_code) REFERENCES Items (item_code),
> FOREIGN KEY (vat_group) REFERENCES VAT_Groups (vat_group)
> );
>
> Sample data:
> INSERT INTO Items VALUES (1, 'Vegetables');
> INSERT INTO Items VALUES (2, 'Vine');
> INSERT INTO Items VALUES (3, 'Milk');
> INSERT INTO VAT_Groups VALUES ('E', 'Common VAT');
> INSERT INTO VAT_Groups VALUES ('C', 'Lower VAT');
> INSERT INTO VAT_Percents VALUES ('E', '2004-01-01', '2004-12-31', 20.00);
> INSERT INTO VAT_Percents VALUES ('E', '2005-01-01', NULL, 18.00);
> INSERT INTO VAT_Percents VALUES ('C', '2004-01-01', NULL, 8.00);
> INSERT INTO Items_VAT_History VALUES (1, 'E', '2004-01-01', '2005-06-30');
> INSERT INTO Items_VAT_History VALUES (1, 'C', '2005-07-01', NULL);
> INSERT INTO Items_VAT_History VALUES (2, 'E', '2004-01-01', NULL);
> INSERT INTO Items_VAT_History VALUES (3, 'C', '2004-01-01', NULL);
>
> Desired Result:
> item_code start_date end_date vat_percnt
> ---
> 1 2004-01-01 2004-12-31 20.00
> 1 2005-01-01 2005-06-30 18.00
> 1 2005-07-01 NULL 8.00
> 2 2004-01-01 2004-12-31 20.00
> 2 2005-01-01 NULL 18.00
> 3 2004-01-01 NULL 8.00
> or equally good result:
> item_code start_date end_date vat_percnt
> ---
> 1 2004-01-01 2004-12-31 20.00
> 1 2005-01-01 2005-06-30 18.00
> 1 2005-07-01 NULL 8.00
> 2 2004-01-01 2004-12-31 20.00
> 2 2005-01-01 NULL 18.00
> 3 2004-01-01 2004-12-31 8.00
> 3 2005-01-01 NULL 8.00
>
>
> Thanks
> Srdjan Mijatov
>|||Thank you, its working.
I tried to figure out that complex join condition

> AND ( ( h.start_date >= p.start_date AND h.start_date <=
> ISNULL(p.end_date,'29991231') )
> OR ( h.end_date <= ISNULL(p.end_date,'29991231') and
> ISNULL(h.end_date,'29991231') >= p.start_date )
> OR ( h.start_date <= p.start_date AND ISNULL(h.end_date,'29991231') >=
> ISNULL(p.end_date,'29991231') )
> )
Then I run query without it and it is working agian.
Srdjan