SQLTeam.com | Weblogs | Forums

Filtering Dates


#1

Hello,

I have a query that shows me the exchange rate (XRate) from a exchange date(XDate), I need to found the correspondent XRate but with the same date but one year ago.

Example :

XDate     |  XRate      |

2016-03-01 | 18.1706
2016-02-29 | 18.2000
2015-12-01 | 16.2500
2015-03-01 | 14.9625
2014-01-01 | 12.1720

And I need this :

XDate     |  XRate       

2016-03-01 | 18.1706
2015-03-01 | 14.9625

This is my code :

SELECT syscreated AS XDate, rate_exchange AS XRate
FROM dbo.rates
ORDER BY syscreated DESC

Thanks to all.


#2

Something like this perhaps:

SELECT syscreated AS XDate, rate_exchange AS XRate, yearago.XRate as yearago_XRate
FROM dbo.rates r2
CROSS APPLY
(
	SELECT rate_exchange AS XRate
	FROM dbo.rates r2
	WHERE r2.XDATE = dateadd(YEAR, -1, r1.XDATE)
) yearago(XRate)
ORDER BY syscreated DESC

#3

Hi gbritton,

i followed your instructions but I'm having an error : "yearago has more columns than weere specified in the column list"

the code is this :

SELECT syscreated AS XDate, rate_exchange AS XRate, yearago.XRate as yearago_XRate
FROM dbo.rates r1
CROSS APPLY
(
SELECT rate_exchange AS XRate, syscreated AS XDate
FROM dbo.rates r2
WHERE r2.syscreated= dateadd(YEAR, -1, r1. syscreated)
) yearago(XRate)
ORDER BY syscreated DESC


#4

Now is working but shows me the XDate "2015-11-13" and it's pulling the XRate for 2015-11-13 and for 2014-11-13 and I need the current year 2016-03-01 and the past year 2015-03-01 what I have wrong?

SELECT syscreated AS XDate, rate_exchange AS XRate, yearago.XRate as XRate2
FROM dbo.rates r1
CROSS APPLY
(
SELECT rate_exchange AS XRate
FROM dbo.rates r2
WHERE r2.syscreated= dateadd(YEAR, -1, r1. syscreated)
) yearago(XRate)
ORDER BY syscreated DESC


#5

Nothing. The query uses the data in the source table and looks back one year. If you have a row with the XDATE 2016-03-01 it will look back and pull the date for 2015-03-01. Basically the query pulls the rate for XDATE - one year for each row in the imput


#6

Yes, thats I need, but the query sends me 2015 and 2014 and I have 2016 too soo I must see 2016 and 2015 first but something is failing and I dont know why


#7

please post a few (5-10) rows of your input data


#8

Every day i enter a xrate so I always must have the xrate from current date and the same date but 1 year ago
XDATE XRATE
2016-03-02 06:58:49.000 18.102
2016-03-01 07:02:24.000 18.1706
2016-02-29 07:02:22.000 18.168
2016-02-29 07:02:05.000 18.168
2016-02-29 07:01:46.000 18.168
2016-02-26 06:54:27.000 18.2893
2016-02-25 07:01:08.000 18.1948
2016-02-24 07:05:04.000 18.0568
2016-02-23 07:03:00.000 18.2762
2016-02-22 07:01:47.000 18.1439

So this what I need

         XDATE                       XRATE         XDATEyearago     XRATE2yearago

2016-03-02 06:58:49.000 18.102 2015-03-02 14.123


#9

OK -- well that's just what my query will do. e.g. for the first row, it looks for the rate exactly one year ago.

However, I see that you have the date stored as datetime. That's a problem for my query. it takes the date(time), subtracts one year and looks for a matching date(time). Likely it won't find a match, because of the time portion. One way around it is to cast the datetime column to date for the comparison

e.g.

CAST(r1. syscreated as date)


#10

I made the changes You recomended but still having the same results and I dont understand why is jumping to november 2015 and november 2014, in could understand march 02 2015 and march 02 2014 but why november? :

XDATE1 | XRATE1 | XDATE2 | XRATE2
2015-11-13 | 16.7173 | 2014-11-13 | 13.5874

this is the code

SELECT CAST(syscreated AS Date)AS XDate1, rate_exchange AS XRate, yearago.XDate2, yearago.XRate as XRate2
FROM dbo.rates r1
CROSS APPLY
(
SELECT rate_exchange AS XRate, CAST(syscreated AS Date) AS XDate2
FROM dbo.rates r2
WHERE r2.syscreated= dateadd(YEAR,-1, r1. syscreated)
) yearago(XRate,XDate2)
ORDER BY XDate1 DESC


#11

XDATE1 | XRATE1 | XDATE2 | XRATE2
2015-11-13 | 16.7173 | 2014-11-13 | 13.5874

that looks correct to me. November to November, a year ago

BTW, you need to cast as date in the WHERE Clause:

WHERE cast(r2.syscreated as date)= castdateadd(YEAR,-1, r1.syscreated) as date)