January 27, 2004 at 4:41 am
I've a DB where every month I include the CPI-Consumer Price Index of the last month.So, in my page, I'll display the last 24 CPI's indexes, in month-DESC order. I would like to improve it, displaying another column, where the system can automatically calculate and show the increase of the CPI measured in the last 12 months. So, I must program to pick the last index-and-divide it by the 12th. last monthly index, which is of course in the 13th. DESC-ordered line of the DB. For instance: take the DEC2003 CPI index -and- divide it by the DEC2002 index. HOW can I do it? Thanks in advance! Dalton, Rio
January 27, 2004 at 6:39 am
Without structure can only guess. Assume that row contains a date and all months present then
SELECT TOP 24 a.[date],a.CPI,
(a.CPI / b.CPI,1) AS 'Increase'
FROM
a
INNER JOIN
b
ON b.[date] = DATEADD(month,-12,a.[date])
ORDER BY [date] DESC
Far away is close at hand in the images of elsewhere.
Anon.
January 27, 2004 at 11:22 am
Dear David - Excuse me, but I imagined things were simpler. So, he You have: actually the DB is more complex than I first said: there are 04 indexes, all monthly inserted in one same DB, organized by type ("tipoind") (this one is called "ipca"); and, for each one, there'a a page that shows: 1) the month/year ("mesind/anoind"); 2) the concerned index ("numeroind"); 3) its increase (manually calculated, actually) from -1 month ("varmesind") -that's to say, in relation to the last month's index; 4) its increase within its actual year ("varanoind") - in relation to last december, also manually calculated; and 5) its annual increase ("vardozemesind") - from the same month, 01 year ago, manually calculated, too. The structure is -->: "SELECT TOP 24 indecontb.codigoind, indecontb.mesind, indecontb.anoind, indecontb.numeroind, indecontb.varmesind, indecontb.varanoind, indecontb.vardozemesind, indecontb.tipoind FROM indecontb WHERE tipoind LIKE 'ipca' ORDER BY anoind DESC, mesind DESC"<--. This actual pages can be seen in "http://www.contratosonline.com.br/biblioteca/indseconomicos/ipca.asp". Please let me know if I didn't make myself clear. Thanks a lot again. Dalton
January 28, 2004 at 5:40 am
How about this
SELECT TOP 24
i.codigoind,
i.mesind,
i.anoind,
i.numeroind,
(i.numeroind - m.numeroind) / m.numeroind AS 'varmesind',
(i.numeroind - y.numeroind) / y.numeroind AS 'varanoind',
(i.numeroind - a.numeroind) / a.numeroind AS 'vardozemesind',
i.tipoind
FROM indecontb i
INNER JOIN indecontb m
ON y.mesind = (CASE WHEN i.mesind = 1 THEN 12 ELSE END)
AND y.anoind = (CASE WHEN i.mesind = 1 THEN (i.anoind - 1) ELSE i.anoind END)
INNER JOIN indecontb y
ON y.mesind = 12 AND y.anoind = (i.anoind -1)
INNER JOIN indecontb a
ON y.mesind = i.mesind AND y.anoind = (i.anoind -1)
WHERE i.tipoind LIKE 'ipca'
ORDER BY i.anoind DESC, i.mesind DESC
Far away is close at hand in the images of elsewhere.
Anon.
January 28, 2004 at 8:06 am
David - Wow! I'll test it, and return to You, soon! Thanks a lot, once again! Dalton
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply