Self join the table itself

Question

Post reply

Self join the table itself

JeremyU

SSChasing Mays

Points: 640
More actions
August 25, 2022 at 7:53 pm

#4080644
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, 2 months ago by JeremyU.

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

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

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)

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin