Adding Datediff to staistics page

Course Of The Month

8 days, 1 hour left to enroll

View July's Free Course

Become a Premium Member and unlock a new, free course in leading technologies each month.

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

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

Open in new window

hence amend your codes to:

...
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)
...

Open in new window

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

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Course Of The Month

Adding Datediff to staistics page

Welcome to Experts Exchange

Featured Post

Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Enjoyed your answer?

Keep in touch with Experts Exchange

Live Consultants