Inner left join to get value on previous date

Question

I am trying to get value of GR2 on previous date TabDate in following query:

    DECLARE @ShN money=STRLOG.dbo.fn_VarValue('ShN') 
    DECLARE @ShNs money=STRLOG.dbo.fn_VarValue('ShNs') 
    DECLARE @ShNN money=STRLOG.dbo.fn_VarValue('ShN')     +STRLOG.dbo.fn_VarValue('ShNs') 
    DECLARE @ShD money=STRLOG.dbo.fn_VarValue('ShD') 

    SELECT sum(
        CASE 
            WHEN ttab.GR2='33' 
            THEN 
                CASE 
                -- Randam ar sekianti diena sventes         
                WHEN tholy.[ID] IS NOT NULL
                THEN @ShN

                ELSE @ShN 
                END
            WHEN ttab.GR2='3' 
            THEN 
                CASE 
                -- Randam ar sekianti diena sventes         
                WHEN tholy.[ID] IS NOT NULL
                THEN @ShNs
                -- Randam ar tai pirma 3-cios pamainos diena
                WHEN tprev.GR2='3'
                THEN @ShNN

                ELSE @ShNs 
                END
            ELSE 
                CASE 
                -- Randam ar sekianti diena sventes         
                WHEN tholy.[ID] IS NOT NULL
                THEN @ShD-60
                ELSE @ShD 
                END
        END
        )/60, 
        ttab.EmplCodeID
        FROM tbl_TabelWHrs          INNER JOIN tbl_Tabel ttab           
             ON tbl_TabelWHrs.TabWHrsID = ttab.TabWHrsID
        LEFT JOIN tbl_Holidays tholy            
             ON [HolidayDate]=DATEADD("DAY",1,[TabDate])
        LEFT JOIN  tbl_Tabel tprev          
             ON ttab.[EmplCodeID]=tprev.[EmplCodeID]            
             AND [ttab.TabDate]=DATEADD("DAY",-1,[tprev.TabDate])
        WHERE (tbl_TabelWHrs.TabMon='2014.12' AND ttab.EmplCodeID='7040023' AND ttab.GR2 is not null)
        GROUP BY ttab.EmplCodeID;

What I get on execution is 2 errors:

Msg 207, Level 16, State 1, Line 47 Invalid column name 'ttab.TabDate'. Msg 207, Level 16, State 1, Line 47 Invalid column name 'tprev.TabDate'.

Its something wrong with "LEFT JOIN tbl_Tabel tprev". Error rises on fields in line "AND [ttab.TabDate]=DATEADD("DAY",-1,[tprev.TabDate])". What I do wrong here?

whether you have `TabDate` column in `tbl_Tabel` – Pரதீப் Jan 25 '15 at 09:34 — Pரதீப், Jan 25 '15 at 09:34
Yes, I have column TabDate in tbl_Tabel – litpost Jan 25 '15 at 09:45 — litpost, Jan 25 '15 at 09:45

score 1 · Accepted Answer · answered Jan 25 '15 at 10:10

the problem here is use have used square bracket before the alias name and ended it after the column name so [tprev.TabDate] will be considered as a column, Alias name is escaped here.

change the condition like this.

.....
LEFT JOIN  tbl_Tabel tprev          
     ON ttab.[EmplCodeID]=tprev.[EmplCodeID]            
     AND ttab.[TabDate]=DATEADD("DAY",-1,tprev.[TabDate])  -- here
....

Inner left join to get value on previous date

1 Answers1