SQLTeam.com | Weblogs | Forums

Left Outer Join


#1

Hi All,

Query

Insert Dateset -- Loaded for all months
SELECT
CurrentMonthValue
FROM Processeddata

Update A
Set A.PreviousMonthvalue = B.CurrentMonthvalue
From Dataset A
LEFT Outer Join
Dataset B
ON A.ID = B.ID
AND A.place = B.Place
WHERE B.Date = Month(A.Date)-1

Output
Product Current Month Previous Month previous month1 previousmonth2
xxxx Currentvalue Previousvalue etc etc

The problem in the above query is..it is updating only all applicable data in the previous month. logically last month may have more records which are not available in the current month.

I wanted to update all the records in the previous month (even if condition not matches). For example, the current month may not apply for all place but it is filtering for the previous month too.

Please help on this.


#2

Point 1
Not sure if that your actual SQL code or not as the B.Date = Month(A.Date)-1
is a little off. You comparing a date to an integer.
B.Date = DateAdd(mounth,-1,A.Date)

Point 2
You using a left join which means the there doesn't have to be a match in DataSet B.
However, you are using the results of Dataset B in your WHERE clause which means that B.Date could potentially be NULL.
Possibly move what you have in the WHERE clause in to the ON part of JOIN using an AND statement.
ON A.ID = B.ID
AND A.place = B.Place
AND B.Date = DATEADD(Month,-1,A.Date)

Point 3
You stated: -
"The problem in the above query is..it is updating only all applicable data in the previous month. logically last month may have more records which are not available in the current month."
When you refer to the "previous month" are you referring to the PreviousMonthvalue?
If so, then this will become NULL if there is no match to the previous month if you implement point 2


#3

Thank you Lewie

Point 1
Not sure if that your actual SQL code or not as the B.Date = Month(A.Date)-1
is a little off. You comparing a date to an integer.
B.Date = DateAdd(mounth,-1,A.Date)

I'm using like DATEADD(MONTH,DATEDIFF(MONTH,0,a.Date)-1,0)

Point 2
You using a left join which means the there doesn't have to be a match in DataSet B.
However, you are using the results of Dataset B in your WHERE clause which means that B.Date could potentially be NULL.
Possibly move what you have in the WHERE clause in to the ON part of JOIN using an AND statement.
ON A.ID = B.ID
AND A.place = B.Place
AND B.Date = DATEADD(Month,-1,A.Date)

Point 3
You stated: -
"The problem in the above query is..it is updating only all applicable data in the previous month. logically last month may have more records which are not available in the current month."

When you refer to the "previous month" are you referring to the PreviousMonthvalue?
- Yes

If so, then this will become NULL if there is no match to the previous month if you implement point 2

But our requirement is it has to update all the respective values if it does not match.


#4

If there is no matching ID AND place in the previous month, how can you update the current month with a "respective value" that does not exist unless you place an ISNULL around it.
Update A
Set A.PreviousMonthvalue = ISNULL(B.CurrentMonthvalue,0) -- Zero if no matching entry in previous month
From Dataset A
LEFT Outer Join
Dataset B
ON A.ID = B.ID
AND A.place = B.Place
WHERE B.Date = Month(A.Date)-1