February 18, 2009 at 8:24 am
Ramesh (2/18/2009)
SELECTi.Name, s.stat1, s.stat2, s.stat3, k.kpi1, k.kpi2, k.kpi3
FROMItems i
INNER JOIN
(
SELECTISNULL( s.IDItem, k.IDItem ) AS IDItem,
s.stat1, s.stat2, s.stat3, k.kpi1, k.kpi2, k.kpi3
FROM[STATISTICS] s
FULL OUTER JOIN KPIs k ON s.IDItem = k.IDItem AND s.Date = k.Date
WHEREs.IDItem = 100 OR k.IDItem = 100
) ks ON i.ID = ks.IDItem
WHERE i.ID = 100
thnx
but shouldn't the first line be
SELECTks.Name, ks.stat1, ks.stat2, ks.stat3, ks.kpi1, ks.kpi2, ks.kpi3
This doensn't make me happy by the way. I have been working on my querybuilder (which dynamicly builds a query) for more than a week, and a query like above isn't possible in it yet 🙁
February 18, 2009 at 8:45 am
Will the below query is supported by your query builder? If not, can you tell us what queries are supported, 'cause I am out of ideas?:D
SELECTi.Name, s.stat1, s.stat2, s.stat3, k.kpi1, k.kpi2, k.kpi3
FROMItems i
LEFT JOIN [Statistics] s ON s.IDItem = i.ID
LEFT JOIN KPIs k ON k.IDItem = i.ID
AND
(
s.IDItem IS NULL
OR
(
s.IDItem IS NOT NULL
AND s.Date = k.Date
)
)
WHERE i.ID = 100
--Ramesh
February 18, 2009 at 10:30 am
Ramesh (2/18/2009)
Will the below query is supported by your query builder? If not, can you tell us what queries are supported, 'cause I am out of ideas?:D
SELECTi.Name, s.stat1, s.stat2, s.stat3, k.kpi1, k.kpi2, k.kpi3
FROMItems i
LEFT JOIN [Statistics] s ON s.IDItem = i.ID
LEFT JOIN KPIs k ON k.IDItem = i.ID
AND
(
s.IDItem IS NULL
OR
(
s.IDItem IS NOT NULL
AND s.Date = k.Date
)
)
WHERE i.ID = 100
Are you sure this one works??
Because i have tried several things like this, and neither one of them worked because s.IDItem is not null, it simply isn't there at all!! I did try something with an extra virtual table with the dates in it and than joined the kpi and statistic with this one and than the above will work because the left join returns a null column
February 18, 2009 at 10:42 am
If have tested it.... and indeed: it doesn't work
The testcase :
create table #Stats (
ID int identity primary key,
ItemID int,
Date smalldatetime,
Stat1 float,
Stat2 float,
Stat3 float);
create table #KPIs (
ID int identity primary key,
ItemID int,
Date smalldatetime,
KPI1 float,
KPI2 float,
KPI3 float);
create table #Items (
ID int primary key,
Name char(10));
insert into #Items (ID, Name)
values (1, 'test')
insert into #Stats (ItemId, Date, Stat1, Stat2, Stat3)
SELECT 1, '2009-01-01', 1, 2, 3
UNION ALL
SELECT 1, '2009-01-02', 2, 3, 4
UNION ALL
SELECT 1, '2009-01-04', 4, 5, 6
UNION ALL
SELECT 1, '2009-01-06', 7, 8, 9
insert into #KPIs (ItemId, Date, KPI1, KPI2, KPI3)
SELECT 1, '2009-01-01', 10, 20, 30
UNION ALL
SELECT 1, '2009-01-02', 20, 30, 40
UNION ALL
SELECT 1, '2009-01-03', 40, 50, 60
UNION ALL
SELECT 1, '2009-01-04', 70, 80, 90
And your query, rewriten to work on the temp tables:
SELECT i.Name, ISNULL(s.Date, k.Date), s.stat1, s.stat2, s.stat3, k.kpi1, k.kpi2, k.kpi3
FROM #items i
LEFT JOIN #Stats s ON s.ItemID = i.ID
LEFT JOIN #KPIs k ON k.ItemID = i.ID
AND
(
s.ItemID IS NULL
OR
(
s.ItemID IS NOT NULL
AND s.Date = k.Date
)
)
WHERE i.ID = 1
I only see statistics for the next dates:
2009-01-01 00:00:00
2009-01-02 00:00:00
2009-01-04 00:00:00
2009-01-06 00:00:00
And as you can see, 03 and 06 are missing... (and 05 is missing, but he's supposed to be missing)
February 18, 2009 at 10:48 am
Ramesh (2/18/2009)
SELECTi.Name, s.stat1, s.stat2, s.stat3, k.kpi1, k.kpi2, k.kpi3
FROMItems i
INNER JOIN
(
SELECTISNULL( s.IDItem, k.IDItem ) AS IDItem,
s.stat1, s.stat2, s.stat3, k.kpi1, k.kpi2, k.kpi3
FROM[STATISTICS] s
FULL OUTER JOIN KPIs k ON s.IDItem = k.IDItem AND s.Date = k.Date
WHEREs.IDItem = 100 OR k.IDItem = 100
) ks ON i.ID = ks.IDItem
WHERE i.ID = 100
This one DOES work!!
The rewriten version to test with the temp tables:
SELECT i.Name, ks.date, ks.stat1, ks.stat2, ks.stat3, ks.kpi1, ks.kpi2, ks.kpi3
FROM #Items i
INNER JOIN
(
SELECT ISNULL( s.ItemID, k.ItemID) AS ItemID,
ISNULL( s.Date, k.Date) AS Date,
s.stat1, s.stat2, s.stat3, k.kpi1, k.kpi2, k.kpi3
FROM #stats s
FULL OUTER JOIN #KPIs k ON s.ItemID = k.ItemID AND s.Date = k.Date
WHERE s.ItemID = 1 OR k.ItemID = 1
) ks ON i.ID = ks.ItemID
WHERE i.ID = 1
The "problem" with this one is my querybuilder, ofcourse i can rewrite it, but i rather change it as little as possible to keep it compatible with the total queries. So for the querybuilder your second suggestion was MUCH better, since the kpi and statistic data arent joined in a innerselect. But unfortunately...... that one didn't work 😛
February 19, 2009 at 2:20 am
Ok..., the below query is tested and it works...
SELECTi.Name, ISNULL(s.Date, k.Date), s.stat1, s.stat2, s.stat3, k.kpi1, k.kpi2, k.kpi3
FROM#items i
LEFT JOIN #Stats s
FULL JOIN #KPIs k ON k.ItemID = s.ItemID AND s.Date = k.Date
ON s.ItemID = i.ID OR k.ItemID = i.ID
WHEREi.ID = 1
--Ramesh
February 19, 2009 at 3:00 am
Ramesh (2/19/2009)
Ok..., the below query is tested and it works...
SELECTi.Name, ISNULL(s.Date, k.Date), s.stat1, s.stat2, s.stat3, k.kpi1, k.kpi2, k.kpi3
FROM#items i
LEFT JOIN #Stats s
FULL JOIN #KPIs k ON k.ItemID = s.ItemID AND s.Date = k.Date
ON s.ItemID = i.ID OR k.ItemID = i.ID
WHEREi.ID = 1
WOW great!! this is exactly what i need.
I didn't know it was allowed to do an innerjoin like this. I keep learning things here every day 😎
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply