Hello guys. I have two tables :
create table CurrentDay (
ID int identity primary key,
ssn varchar(10),
val money,
CheckDate datetime,
CurrentStatus tinyint)
create table PreviousDay (
ID int identity primary key,
ssn varchar(10),
val money,
CheckDate datetime,
CurrentStatus tinyint)
I need update field CurrentDay.CurrentStatus with this values:
- 111,if in the PreviousDay.ssn is the same ssn as in the CurrentDay and if the PreviousDay's val is the same as in the CurrentDay;
- 112,if in the PreviousDay.ssn is the same as in the CurrentDay and if the PreviousDay's val is bigger as in the CurrentDay;
- 113,if in the PreviousDay.ssn is the same as in the CurrentDay and if the PreviousDay's val is smaller as in the CurrentDay;
- 114,if in there is not PreviousDay.ssn the same as in the CurrentDay i.e. the ssn in CurrentDay only (it is today's info) .
I'd wrote some query,but i it is desirable to find other way for doing this task using only one join between PreviousDay and CurrentDay tables. Obviously,that this isn't a very lucky kind...Here is my variant:
Update CurrentDay
Set CurrentStatus=case
when exists (select PreviousDay.ID from PreviousDay
where PreviousDay.ssn=CurrentDay.ssn AND
PreviousDay.val=CurrentDay.val AND
PreviousDay.CheckDate=DATEADD(day,-1,CurrentDay.CheckDate))
then 111
when exists (select PreviousDay.ID from PreviousDay
where PreviousDay.ssn=CurrentDay.ssn AND
PreviousDay.val>CurrentDay.val AND
PreviousDay.CheckDate=DATEADD(day,-1,CurrentDay.CheckDate))
then 112
when exists (select PreviousDay.ID from PreviousDay
where PreviousDay.ssn=CurrentDay.ssn AND
PreviousDay.val<CurrentDay.val AND
PreviousDay.CheckDate=DATEADD(day,-1,CurrentDay.CheckDate))
then 113
when exists (select PreviousDay.ID from PreviousDay
where PreviousDay.ssn!=CurrentDay.ssn AND
PreviousDay.CheckDate=DATEADD(day,-1,CurrentDay.CheckDate))
then 114
end;
Here is other query,but in that case i cannot update CurrentDay.CurrentStatus' fieid with value 114 because there is only matching rows on ssn in both tables:
Set Currentday.CurrentStatus=(select 111 where PreviousDay.val=CurrentDay.val union all select 112 where and PreviousDay.val>CurrentDay.val
union all select 113 where and PreviousDay.val<CurrentDay.val /*union all select 114 */ )
from PreviousDay join CurrentDay on PreviousDay.ssn=CurrentDay.ssn and PreviousDay.CheckDate=DATEADD(day,-1,CurrentDay.CheckDate)
Do you have any other ideas?