Classic ASP Access database query, sum and group

2
votes

I have a classic ASP front end to an Access database (2003) and would like to run some calculations on my data. I have items that are rented on a daily basis and there is a daily running cost associated with that. Here are the table details;

  • Table name: rent_table
  • Fields:
    • rentid
    • startdate
    • enddate
    • rentfee
    • rentcost
    • bookingfee

Currently in Excel I do this;

(startdate-enddate) * rentfee+bookingfee -minus (startdate-enddate) * rentcost

That gives me the profit per rentid. I order it by enddate and then group it on a monthly basis (end of month) so that when the enddate has expired I know that the profit can be released. In Excel I manually do the month grouping / summing.

The goal is to automate the process in ASP so that I get the results summarised;

  • Report Heading: profit, monthend
  • Result line1: £2.00, Jan 14
  • Result line2: £4.00, Feb 14
  • Result lineN: £6.00, Jan 16

I know how to construct the ASP page, connect to the database and display query results. It is really the query that I need please. Thank you in advance (and please be gentle, my first post).

1
sqlms-accessasp-classic

1 Answers

1
votes

Something like the following should get you close. The query is untested but should give you a good starting point. The month will be returned as a number from 1 to 12; you will probably want to format that as you see fit.

SELECT Month(enddate) AS Mo, Year(enddate) as Yr, 
       Sum( ((enddate - startdate) * rentfee) + bookingfee - ((enddate - startdate) * rentcost) ) as Profit
FROM rent_table
WHERE enddate Is Not Null
GROUP BY Year(enddate), Month(enddate)
ORDER BY Year(enddate), Month(enddate)