Adding Datediff to staistics page

I had this question after viewing Displaying number of records depdning on table contents - classic asp.

I'm trying to give my users the ability to see how many tasks had a certain status within a user defined timeframe. My code deosn't work and I've given up. Been googling by I'm a complete nuthead when it comes to programming. Any help appreciated.

<!--#include file="db/database_read.inc"-->
<head>
<!--#include file="Include/meta.asp"-->
</head>
<%
    Task = Request.querystring("Task")

  %><form method="get" action="statistics.asp" data-ajax="false">
  <fieldset class="ui-field-contain">
    <label for="Task">Number of tasks recieved</label>
    <select name="Task" id="Task">
      <option value="">Choose</option>
      <option value="7">Last week</option>
      <option value="31">Last month</option>
      <option value="91">3 months</option>
      <option value="182">6 months</option>
      <option value="273">9 months</option>
      <option value="365">12 months</option></select>
  </fieldset>
<input type="submit" data-inline="true" value="Submit" data-ajax="false">
</form><%
Dim objconn,objRSM,strSQL

Set objconn = Server.CreateObject("ADODB.Connection")
objconn.Open strConnect


strSQL = "SELECT concat(Status, ' = ' , Count(Status )) as Numrecords FROM Tasks WHERE DateDiff("d",DateR,Date())> '" & Task & "' group by Status order by Status ASC"

	     set objRSM = objconn.Execute(strSQL)
             
do while objRSM.eof = false
    Records = (objRSM("Numrecords"))
   response.write "<br>" & Records & "<br>"
   objRSM.movenext
loop
%>

Open in new window

... if Task = "" Then strSQL = "SELECT concat(Status, ' = ' , Count(Status )) as Numrecords FROM Tasks group by Status order by Status ASC" else strSQL = "SELECT concat(Status, ' = ' , Count(Status )) as Numrecords FROM Tasks WHERE DateDiff(current_date(), DateR) > " & Task & " group by Status order by Status ASC" end if set objRSM = objconn.Execute(strSQL) ...

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 56

Ryan ChongCommented: 2016-09-07

you may try query such as below:

SELECT concat(Status, ' = ' , Count(Status )) as Numrecords FROM Tasks
where DateDiff(current_date(), DateR) > 31
group by Status order by Status ASC

hence amend your codes to:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

micambAuthor Commented: 2016-09-07

Worked like a charm - as it always does when you are involved :-)

I have a real challenge for you here:

https://www.experts-exchange.com/questions/28966125/Getting-ID-after-log-in-timed-out-Classic-asp.html?anchor=a41777693#a41777693

Adding Datediff to staistics page

Who is Participating?

Already a member? Login.