Left join NULLs
-
I join two tables with a left join, where I get NULL in the resultset I would like to take the value from the last row that wasn't NULL and do some calc. and display it instead of the NULL value, and for the following NULLs I need to take the value calculated in the first NULL and do some more calc. on it and display those.
I have a bit of a struggle to get it to work, any good idéas how to solv it best?
Cheers,
-
It sounds like you're going to need an offset self-join (or some potentially nasty subqueries) , to go with your left outer join, but I'm with mrpolecat in that we need a lot more info to know for sure.
-
ok, sorry guys for the lack of info, here you go..
Expected result:
Date LoggedValue Calc1 Calc2
2007-06-11 '200' 100 200
2007-06-12 '300' 100 0
2007-06-13 '200' 100 300
So here's how is should work:
The loggedValue column come from a table that is left join so that can contain NULLs.
2007-06-11 has the measured value, so nothing need to be done to that row.
2007-06-12 had NULL in the LoggedValue column so is dhould take the last value that wasn't NULL e.g. 200 from 2007-06-11 and supstract Calc1 and add Calc2 e.g. 200-100+200.
2007-06-13 had NULL in the LoggedValue column, so it should take the calculated value from 2006-06-12 and add calc1 and substract Calc2 e.g. 300-100+0
Any help is really appriciated.
Cheers
-
Hi,
In SQL it is always that when you convert NULL to any of your preferred datatypes, it will be NULL again. It depends on we using intelligence to overcome that. Please go through the following example. May be it will help you resolve it. If you are sure that your Left Table is going to have higher value, then this will certainly help.
CREATE TABLE Tab1
(
TabID INT,
LogVal VARCHAR(12)
)
CREATE TABLE Tab2
(
TabID INT,
LogVal VARCHAR(12)
)
INSERT INTO Tab1 VALUES(1, '300')
INSERT INTO Tab1 VALUES(2, '500')
INSERT INTO Tab1 VALUES(3, '700')
INSERT INTO Tab2 VALUES(1, '100')
INSERT INTO Tab2 VALUES(3, '400')
SELECT * FROM
Tab1
LEFT OUTER JOIN
Tab2
ON
Tab1.TabID = Tab2.TabID
SELECT Tab1.TabID, CalLog =
CASE
WHEN Convert(INT, Tab2.LogVal) IS NULL THEN CONVERT(INT, Tab1.LogVal)
ELSE CONVERT(INT, (Convert(INT, Tab1.LogVal) - Convert(INT, tab2.LogVal)))
END
FROM
Tab1
LEFT OUTER JOIN
Tab2
ON
Tab1.tabID = Tab2.TabiD
If still you have problems, it is better that you convert the Log Val into INT and keep a default value as '0'. This will certainly solve your problem
-
Thx, but I think I need a bit more pointers to head me in the right direction...
The actual query without an attempt to fix the null issue.
SELECT f.TankObjectID,
f.ForecastDate,
f.ForecastValue,
ml.MeasurementLogValue,
d.DeliveryValue,
f.CustomerReference,
d.SupplierReference
FROM dbo.Forecast f
LEFT JOIN dbo.Delivery d ON f.ForecastDate = d.DeliveryDate
LEFT JOIN dbo.MeasurementLog ml ON ml.MeasurementLogTimeStamp = f.ForecastDate
AND ml.TankObjectID = f.TankObjectID
WHERE f.TankObjectID = @TankObjectID
AND f.ForecastDate BETWEEN @ToDate AND @FromDate
So, what happens is if there's notinh in the forecast table for a certain day then I'll have a NULL in the ml.MeasurementLogValue column, and that's when I need to apply the logic to do the calculations to populate values instead of the NULLs
Cheers,
-
If I have slightly understood your problem then I feel you can try the following..
SELECT f.TankObjectID,
f.ForecastDate,
f.ForecastValue,
Case
when ml.MeasurementLogValue IS NULL then <Update with the value you need>
Else <perform the required calculation>
End as MeasurementLogValue,
d.DeliveryValue,
f.CustomerReference,
d.SupplierReference
FROM dbo.Forecast f
LEFT JOIN dbo.Delivery d ON f.ForecastDate = d.DeliveryDate
LEFT JOIN dbo.MeasurementLog ml ON ml.MeasurementLogTimeStamp = f.ForecastDate
AND ml.TankObjectID = f.TankObjectID
WHERE f.TankObjectID = @TankObjectID
-
Hi agin,
That far I did get as well, that biggest problem for me that I don't find a good approach to the last isuue e.g. per my first example on the third row, when I need to do that calculations based on the calculated value on row 2.
So basicly how to preform the else statment...
Any ideas how to tackle that one?
Sorry for not beeing clear enugh.
Cheers,
-
Please post sample data.
K. Matsumura
-
Here it comes..
SELECT f.TankObjectID,
f.ForecastDate,
f.ForecastValue,
Case
when ml.MeasurementLogValue IS NOT NULL then
ml.MeasurementLogValue
Else
"Here's where I need the logic to preform that calculation"
End MeasurementLogValue,
d.DeliveryValue,
f.CustomerReference,
d.SupplierReference
FROM dbo.Forecast f
LEFT JOIN dbo.Delivery d ON f.ForecastDate = d.DeliveryDate
LEFT JOIN dbo.MeasurementLog ml ON ml.MeasurementLogTimeStamp = f.ForecastDate
AND ml.TankObjectID = f.TankObjectID
WHERE f.TankObjectID = @TankObjectID
AND f.ForecastDate BETWEEN @ToDate AND @FromDate
Thx!
-
By sample data I mean DDL and INSERTS for your posted expected result.
K. Matsumura
-
oh sorry...
So when ml.MeasurementLogValue is NULL then I want to take the last value that is not NULL from ml.MeasurementLogValue and substract f.forecastValue and add d.deliveryValue, if more rows have NULL in ml.MeasurementLogValue then the same calculation should be made but instead of startinf with the last value that is not NULL in ml.MeasurementLogValue it should start with the calculated value in the row before.
Please let me know if it's not clear
Thx a lot!
-
I have tried this out. Hope it helps you out... But this shud atleast giv u an idea how u can proceed...
SELECT f.TankObjectID,
f.ForecastDate,
f.ForecastValue,
Case
when ml.MeasurementLogValue IS NOT NULL then
ml.MeasurementLogValue
Else
(Select (Select Top 1 MeasurementLogValue from dbo.MeasurementLog
Where MeasurementLogValue is not null
order by MeasurementLogValue DESC) - (f.ForecastDate) + (d.DeliveryValue))
End MeasurementLogValue,
d.DeliveryValue,
f.CustomerReference,
d.SupplierReference
FROM dbo.Forecast f
LEFT JOIN dbo.Delivery d ON f.ForecastDate = d.DeliveryDate
LEFT JOIN dbo.MeasurementLog ml ON ml.MeasurementLogTimeStamp = f.ForecastDate
AND ml.TankObjectID = f.TankObjectID
WHERE f.TankObjectID = @TankObjectID
AND f.ForecastDate BETWEEN @ToDate AND @FromDate
I'm sorry that i cud do only this much... B'coz without actual or replicated data it is really hard to query in SQL...I was not able to replicate ur tables...
-
Thx for trying to help!
I did actually exactly what you did (did not submit it to see you you guys did't in a diffrent way) and that works great for the first NULL value, but the problem with those after the first is still there, those how should be based on the calculated row before. Thats where I don't knkow how to solve it.
Thx again!
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply