Bob-
I have several queries calculating values over a number of currencies. I originally tried cross-rate calculations by getting the rate of the transaction currency and the rate of the reporting currency and going from there. This was giving me wrong answers and I was up against a deadline, so I abandoned that and I fudged it by storing cross-rates for each reporting currency (EUR, USD, and GBP) in the rates table and using that in my query. Inevitably, that fudge has come home to roost, the users want more cross-rates (apparently China is getting important, who knew!), so I tried to my cross-rate calculation again. I think I have got it working okay now, but I now have another issue. I am pivoting across the month of the transaction, and instead of getting one line for each property, with the Jan, Feb, Mar, … values in that single row, I now get a separate row for each month, with the Jan figures in the Jan column on one row, the Feb figures in the Feb column on the next row, and so on.
Here is my revised query that lays it out incorrectly
TRANSFORM IIF(ISNULL(Round(Sum(UpsellRevenue
*(SELECT xr1.USDRate
FROM ExchangeRates AS xr1
WHERE xr1.Currency = trk.CurrencyCode AND
xr1.DateOfRate = DATESERIAL(YEAR(DepartureDate) ,MONTH(DepartureDate),1))
/(SELECT xr1.USDRate
FROM ExchangeRates AS xr1
WHERE xr1.Currency = "GBP" AND
xr1.DateOfRate = DATESERIAL(YEAR(DepartureDate),MONTH(DepartureDate),1))),2)),
"",
Round(Sum(UpsellRevenue
*(SELECT xr1.USDRate
FROM ExchangeRates AS xr1
WHERE xr1.Currency = trk.CurrencyCode AND
xr1.DateOfRate = DATESERIAL(YEAR(DepartureDate) ,MONTH(DepartureDate) ,1))
/(SELECT xr1.USDRate
FROM ExchangeRates AS xr1
WHERE xr1.Currency = "GBP" AND
xr1.DateOfRate = DATESERIAL(YEAR(DepartureDate) ,MONTH(DepartureDate),1))),2)) AS CrossValue
SELECT prop.PropertyID, prop.PropertyName
FROM Property AS prop
INNER JOIN (ExchangeRates AS xr
INNER JOIN Tracking AS trk ON xr.Currency = trk.CurrencyCode)
ON prop.PropertyID = trk.PropertyID
WHERE (((trk.TrackingYear)=2014) AND ((prop.PropertyID)=43) AND
((trk.DepartureDate)>=#1/1/2014# And (trk.DepartureDate)<=#12/31/2014#) AND
((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)))
GROUP BY prop.PropertyID, prop.PropertyName, trk.CurrencyCode, DateSerial(Year(DepartureDate),Month(DepartureDate),1)
PIVOT Format([trk.DepartureDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
And this is my current query that lays it out correctly but is restricting my reporting currencies
TRANSFORM IIf(IsNull(Round(Sum(UpsellRevenue*xr.GBPRate),2)),"",Round(Sum(UpsellRevenue*xr.GBPRate),2)) AS CrossValue
SELECT prop.PropertyID, prop.PropertyName
FROM Property AS prop
INNER JOIN (ExchangeRates AS xr
INNER JOIN Tracking AS trk ON xr.Currency = trk.CurrencyCode)
ON prop.PropertyID = trk.PropertyID
WHERE (((trk.TrackingYear)=2014) AND ((prop.PropertyID)=43) AND
((trk.DepartureDate)>=#1/1/2014# And (trk.DepartureDate)<=#12/31/2014#) AND
((xr.DateOfRate)=DateSerial(Year([trk].[DepartureDate]),Month([trk].[DepartureDate]),1)))
GROUP BY prop.PropertyID, prop.PropertyName
PIVOT Format([trk.DepartureDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
Both queries have been reduced to the minimum to just show the problem, I have replaced all the parameters with hard-coded values, there is a SWITCH statement and more currencies tested in the second, and a lot more fields selected and joins in both. I guess the introduction of the SELECT might be causing the problem, but I am not sure how to fix it. Anyone have any ideas?
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar