February 13, 2006 at 9:14 am
I have to ammend our current payment system to allow for multiple currencies. I would like the price page to look something like this.
LowLimit HighLimit US Dollars UK Pounds Euros
1 1 123 99 140
2 9 115 89 130
10 19 100 79 120
with the additional currencies acting like columns along the top. Each site might have a different amount of currencies with a minimum of one.
I could do this in ASP in a loop but I would like to try and see if there was a way of doing it in a nice set based sql if possible. I had thought of using a temp table and building the create statement up as a string and then executing it but then I would just be replicating the same amount of code I would do on the client.
The easy way would just to display each currencies price list one above the other and have n tables but the client would like each currency to display as a column.
SELECT p.PricePK, p.SiteFK, p.LowLimit, p.HighLimit, p.Cost, c.Currency, c.Display
FROM tbl_SITE_CREDIT_PRICING as p
JOINtbl_CURRENCIES as c
ONp.CurrencyFk = c.CurrencyPK
WHERE SiteFK = 9
ORDER BY c.Currency, LowLimit
Any help would be much appreciated.
February 13, 2006 at 9:02 pm
Post your table structure and some sample data with the expected result
February 13, 2006 at 9:31 pm
It must be table like this:
dbo.PriceList (
LimitId int,
CurrencyId int,
Price money)
And 2 tables for descriptions:
dbo.PriceLimit (
Id int
LowLimit int,
HighLimit)
dbo.Currency (
Id int,
CurrencyName nvarchar(50),
CurencyCode char(3),
... whatever you need to describe currencies, e.g. currency symbol
)
And you do
SELECT P.Price, C.CurrencyCode, L.LowLimit, L.HighLimit, ..
FROM dbo.PriceList P
INNER JOIN dbo.PriceLimit L on L.Id = P.LimitId
INNER JOIN dbo.Currency C on C.Id = P.CurrencyId
Inner join ...
Where CurrencyName = ...
_____________
Code for TallyGenerator
February 14, 2006 at 4:04 am
Yes the table structure is bascially the same and that select will give me one currency or a list of price ranges grouped by currency like:
LowLimitHighLimitCostCurrencyCurrencySymbol
11130.0000Dollars$
219120.0000Dollars$
2029110.0000Dollars$
3039100.0000Dollars$
1199.0000Pounds£
21989.0000Pounds£
202979.0000Pounds£
303969.0000Pounds£
but I dont want that I need the currency as a column. So a report like
LowLimitHighLimitCost - DollarsCost - Pounds
11$130.0000£99.0000
219$120.0000£89.0000
2029$110.0000£79.0000
3039$100.0000£69.0000
so I only list the ranges once (as they will be the same) and each currency price will be alongside each other. Some sites might have one currency others might have six or ten. I would like to create something that would take this into account.
As I said in my first msg I was thinking of having a string that built up a create ##tmp table statement adding each currency column in as required etc but then I could easily do something similar on the client. I was wondering if there was a way of doing this in a nice set based solution like a crosstab report.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply