Rank Daily Results

[MikeAuz1979]

Hi, Using access 2000 I have a query that produces results like:

Site# Date Usage
1 01-jan-07 300
1 02-jan-07 250
1 03-jan-07 400

Would anyone have any idea How I could add another field that ranks the usage like below?

Site# Date Usage Rank
1 01-jan-07 300 2
1 02-jan-07 250 3
1 03-jan-07 400 1

Thanks for any help
Mike

 

[PHV]

Something like this ?
SELECT A.[Site#], A.Date, A.Usage, Count(*) AS Rank
FROM tbl2Rank AS A INNER JOIN tbl2Rank AS B ON A.[Site#] = B.[Site#] AND A.Usage <= B.Usage
GROUP BY A.[Site#], A.Date, A.Usage
ORDER BY 1, 2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MikeAuz1979]

PHV That's great, but would you have any idea how I could incorporate this into my current query:
(So I get these fields and your Rank field)

SELECT CCDR_SITE.SITE_NUMBER, CCDR_SITE.SITE_NAME, CCDR_AGL_VOLUMES.READING_DATE, CCDR_AGL_VOLUMES.CONSUMPTION_GJ, QryNewMDQSumHistory.BOOKED_GJ_BASE, [HV_VALUE]/1000 AS HeatingValue FROM QryNewMDQSumHistory RIGHT JOIN (CCDR_SUBCONTRACT RIGHT JOIN ((CCDR_SITE INNER JOIN CCDR_AGL_VOLUMES ON CCDR_SITE.DELIVERY_POINT_ID = CCDR_AGL_VOLUMES.STATION_ID) INNER JOIN CCDR_ACTIVE_SITE ON CCDR_SITE.SITE_NUMBER = CCDR_ACTIVE_SITE.SITE_NUMBER) ON CCDR_SUBCONTRACT.ACCOUNT_NUMBER = CCDR_ACTIVE_SITE.ACCOUNT_NUMBER) ON QryNewMDQSumHistory.SUBCONTRACT_NUMBER = CCDR_SUBCONTRACT.SUBCONTRACT_NUMBER WHERE QryNewMDQSumHistory.BOOKING_START_DATE<=[READING_DATE] AND QryNewMDQSumHistory.[Booking End Date]>=[READING_DATE] AND CCDR_SUBCONTRACT.ACTUAL_CONTRACT_START_DATE<=[READING_DATE] AND CCDR_SUBCONTRACT.SUBCONTRACT_EXPIRY_DATE>=[READING_DATE] AND CCDR_AGL_VOLUMES.READING_DATE >= Date()-1 AND CCDR_AGL_VOLUMES.READING_DATE <= Date()-1 AND CCDR_SITE.SITE_NUMBER = '099989700' order by 1 asc, 2 asc, 3 asc

Thanks Heaps
Mike

 

[MikeAuz1979]

Hey all, further to this is there a way in access to do the same type of subqueries that you can do in SQL server/Oracle.

For Example:

Select u.Site#, u.Site_Name,u.Date u.Usage, MySubQuery.Rank
from tblUsage u,

(SELECT A.[Site#], A.Date, Count(*) AS Rank
FROM tbl2Rank AS A INNER JOIN tbl2Rank AS B ON A.[Site#] = B.[Site#] AND A.Usage <= B.Usage
GROUP BY A.[Site#], A.Date, A.Usage
ORDER BY 1, 2) MySubQuery

Where MySubQuery.Date = u.Date
AND MySubQuery.Site# = u.Site#

Thanks for any help

 

[PHV]

SELECT u.[Site#], u.Site_Name, u.Date, u.Usage, r.Rank
FROM tblUsage u INNER JOIN (
SELECT A.[Site#], A.Date, Count(*) AS Rank
FROM tbl2Rank AS A INNER JOIN tbl2Rank AS B ON A.[Site#] = B.[Site#] AND A.Usage <= B.Usage
GROUP BY A.[Site#], A.Date
) r ON u.Date = t.Date AND u.[Site#] = r.[Site#]


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886