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.
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
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
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
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
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
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
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
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? :
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