Self join the table itself

  •  

    Hello

    I have such a situation

    I have a table, I called it 'table' that I most likely need to join to itself.

    I have lots of calculations that for 13 weeks and i have a set parameter to use more weeks, however it turns out that I need 3 more fields namely:

    1) Last Week Actuals

    2)  Last Week Forecast

    3) Last Week Unit Error which is a calculation of Last Week Actuals - Last Week Forecast.

    Does someone know how I can add three more fields taking only the last week data for those 3 columns and everything else should be still taking 13 or whoever weeks I have set for the whole dataset. I thought that I need to self join it.

    Does it look right?

     

    select * , t2.Actuals AS 'Last week Actuals', t2.Forecast AS 'Last week Forecast', ABS(t2.Actuals - t2.Forecast) AS 'Last Week Unit Error'
    FROM TABLE f1
    left outer join (select DMDUNIT, LOC, STARTDATE from TABLE f2
    on f1.DMDUNIT=f2.DMDUNIT2 and f1.LOC=f2.LOC and f1.STARTDATE=f2.STARTDATE)
    where STARTDATE = DATEADD(DAY,-7,DATEDIFF(DAY,0,GETDATE()))

    • This topic was modified 2 years, 4 months ago by  JeremyU.
  • Just to confirm, the code looks like you only have 1 row per week... is that correct?

    If so, LAG would would a treat here.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Without knowing the structure of your tables, this is impossible to answer with any certainly.

    In line with Jeff's question, I would have expected 'Last week actuals' to be, in pseudo-code:

    SUM(t2.Actuals)
    FROM ...
    WHERE
    t2.TransactionDate >= [start of last week]
    AND t2.TransactionDate < [start of this week]
    GROUP BY [t2.SomeId]

    If you can build a separate query which includes the Id (which I have called SomeId above) and the three additional columns which you need, you should be able to JOIN from that to your main query on SomeId & then you will be able to add the three new columns to the main query's results.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply