June 15, 2004 at 4:27 am
Hi,
Please can you help me with a Query.
I have a Table that contains Currency Rates. This Table has the following Columns:
Currency Rate Code
Rate Year
Rate Month
I need to build a Query that will return each Currency Code in the Table with a count of the Months that Rates exists in the Table that fall between two given dates.
For example, the output I would require would be.
GBP 40
USD 20
I have a Visual Basic application which does alot of Order processing and I need a function that will validate that I have all the Currency Rates required to process all Orders. I thought I could use the DateDiff command in VB to count the actual months between then min and max dates and then compare this against the results from the query. If I have any missing Currency Rates then I can save myself alot of time by not running the process until these are entered.
I have tried to build this query but when I have two year and month WHERES I knock out too many records.
I hope this makes sense. Any help will be much appreciated.
Many thanks in advance
Neil
June 15, 2004 at 4:45 am
Assuming the table name to be CurrencyRates and the given dates as @StartDate and @EndDate, the Query would be
SELECT
[Currency Rate Code],
COUNT(*)
FROM
CurrencyRates
WHERE
[Rate Year] BETWEEN YEAR(@StartDate) AND YEAR(@EndDate)
AND
[Rate Month] BETWEEN MONTH(@Startdate) AND MONTH(@EndDate)
GROUP BY [Currency Rate Code]
Regards,
Beulah Kingsly
June 15, 2004 at 7:23 am
SELECT c.CurrencyRateCode, SUM(ISNULL(m.Match,0))
FROM (SELECT DISTINCT CurrencyRateCode FROM CurrencyRates) c
LEFT OUTER JOIN (SELECT CurrencyRateCode,1 AS Match
FROM CurrencyRates
WHERE DATEADD(month,RateMonth-1,DATEADD(year,RateYear-1900,''))
BETWEEN @StartDate AND @EndDate ) m
ON m.CurrencyRateCode = c.CurrencyRateCode
GROUP BY c.CurrencyRateCode
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply