Need help in optimizing update statement

Question

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?

Answer 1

You can try something like this

UPDATE CurrentDay 
        SET CurrentStatus =
        CASE 
            WHEN cd.val = pd.val THEN 111
            WHEN cd.val < pd.val THEN 112
            WHEN cd.val > pd.val THEN 113
            WHEN pd.val IS NULL THEN 114
        END
FROM    CurrentDay cd LEFT JOIN
        PreviousDay pd ON   cd.ssn = pd.ssn
                        AND cd.CheckDate = DATEADD(d, 1, pd.CheckDate)

This was my test code. Play around with the val entries for PreviousDay to see if it works, or completely remove the entry for PreviousDay to see the new Entry option.

DECLARE  @CurrentDay table (
ID int identity primary key,
ssn varchar(10), 
val money, 
CheckDate datetime, 
CurrentStatus tinyint)



DECLARE @PreviousDay table (
ID int identity primary key,
ssn varchar(10), 
val money, 
CheckDate datetime, 
CurrentStatus tinyint)

INSERT INTO @CurrentDay (ssn, val, CheckDate)  SELECT 1, 1, '02 Jan 2009'
INSERT INTO @PreviousDay (ssn, val, CheckDate)  SELECT 1, 0, '01 Jan 2009'

UPDATE @CurrentDay 
        SET CurrentStatus =
        CASE 
            WHEN cd.val = pd.val THEN 111
            WHEN cd.val < pd.val THEN 112
            WHEN cd.val > pd.val THEN 113
            WHEN pd.val IS NULL THEN 114
        END
FROM    @CurrentDay cd LEFT JOIN
        @PreviousDay pd ON  cd.ssn = pd.ssn
                        AND cd.CheckDate = DATEADD(d, 1, pd.CheckDate)

SELECT * FROM @CurrentDay

Answer 2

Something like:

UPDATE
    C
Set
    CurrentStatus = CASE
            WHEN P.val = C.val THEN 111
            WHEN P.val > C.val THEN 112
            WHEN P.val < C.val THEN 113
            ELSE 114 ---this works because if P.VAL is null, that is no matching row
        END
FROM
    CurrentDay C
    LEFT JOIN
    PreviousDay P On C.ssn = P.ssn AND P.CheckDate = DATEADD(day, -1, C.CheckDate))