comparing rows in a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

comparing rows in a query

Author

Topic

iori
Starting Member

20 Posts

Posted - 2006-08-30 : 17:07:18

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-08-30 : 20:35:48

[code]select Current.sku#,
Current.Region,
Current.EffMonth as CurrMonth,
Previous.EffMonth as PrevMonth,
Current.Cost as CurrCost,
Previous.Cost as PrevCost,
Previous.Cost - Current.Cost as Cost-Diff
from YourTable Current
left outer join YourTable Previous
on Current.Region = B.Region
and Current.EffMonth = dateadd(m, 1, B.EffMonth)[/code]

pootle_flump

1064 Posts

Posted - 2006-08-31 : 12:05:46

Minor thing but you need to enclose the interval in quotes in Access:

.... dateadd("m", 1, B.EffMonth)

iori
Starting Member

20 Posts

Posted - 2006-09-03 : 18:09:50

thanks blindman and pootle, that works. Now i m tryiing to put some constraints on the data returned. here is my actual query

SELECT DISTINCT CURRENT.SKU, CURRENT.REGION, CURRENT.COST_TOT, PREVIOUS.COST_TOT, CURRENT.COST_TOT-PREVIOUS.COST_TOT AS COSTDIFF, CURRENT.EFF_DATE, PREVIOUS.EFF_DATE
FROM COST_SKU AS [CURRENT] LEFT JOIN COST_SKU AS PREVIOUS ON (month(current.eff_date)=month(previous.eff_date)+1) AND (CURRENT.sku=PREVIOUS.sku) AND (CURRENT.REGION=PREVIOUS.REGION)
WHERE MONTH(CURRENT.EFF_DATE)<=7 AND DATEDIFF("M",CURRENT.EFF_DATE,"7/12/06")<=12
ORDER BY CURRENT.EFF_DATE DESC;

where the text in green will be my parameters. so MONTH(CURRENT.EFF_DATE)<=7 says to get the records starting from the 7th month and DATEDIFF("M",CURRENT.EFF_DATE,"7/12/06")<=12 says to get the last 12 months only.

the only problem i am having now is marked in red i.e.if i have eff_date that is of previous year say 2005 it wouldnt show up because i am only comparing months in my join statement as u notice above:

month(current.eff_date)=month(previous.eff_date)+1

how can i make the join to compare rows if the year changes, here is an example

eff_date | price
12/1/06 5.00
1/2/05 4.00

this should join as there is a difference of 1 in the month. my query fails bcuz i am using only month to check the date difference. any suggestions?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 08:47:51

.. ON (month(current.eff_date)=month(previous.eff_date)+1) AND ABS(previous.eff_date-current.eff_date) <= 32 AND ...

Peter Larsson
Helsingborg, Sweden

iori
Starting Member

20 Posts

Posted - 2006-09-04 : 12:35:18

peso,
what does ABS(previous.eff_date-current.eff_date) <= 32 do, it doesnt affect the result. blindman had the right code i.e
Current.EffMonth = dateadd(m, 1, B.EffMonth)

for the provided sample dates (without day, just month and year) i had in the beginning. unfortunately it doesnt work when the day is there for example

date | price
4/12/06 4.00
3/5/06 3.00

this wouldnt join because adding 1 to 3/5/06 will give 4/5/06 and the query wouldnt join the records

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 13:02:29

As you wrote yourself, you do not want this month 2006 to also include this month 2005. Therefore I added a new AND clause in addition to the existing telling that dates can be no more than 32 days apart, thus preventing this month to be calculated with this month last year (more than 32 days away).

Peter Larsson
Helsingborg, Sweden

iori
Starting Member

20 Posts

Posted - 2006-09-04 : 13:41:53

thanks for replying peso. I said i want to join records if the current month's year is one greater than the last years december. oops..i just noticed i have my date wrong in the example. here is the corrected example

eff_date | price
1/2/05 4.00
12/1/04 5.00

so this wouldnt join if i use
month(current.eff_date)=month(previous.eff_date)+1)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 17:25:58

Try to use

ON 12 * YEAR(current.eff_date) + MONTH(current.eff_date) = 12 * YEAR(PREVIOUS.eff_date) + MONTH(PREVIOUS.eff_date) + 1

instead.

Peter Larsson
Helsingborg, Sweden

iori
Starting Member

20 Posts

Posted - 2006-09-05 : 13:04:36

that works on my test data!!! :) i still have to check it on the actual data. but hey thanks alot! i m still trying to decode what u did here

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 13:06:42

I am creating a kind of "serial number" for the months.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources