Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Displaying number of records depdning on table contents - classic asp

Posted on 2016-09-04
13
44 Views
Last Modified: 2016-09-05
Hi all,

I want to start messing around with KPI's in my system. I know how to do a simple record count (code displayed below).

I have a task management system where my users have a lot of control concerning system behaviour. For instance, when a task is created it gets the status recieved and when it's done it gets the status Completed. However - in between these types of status, the user can freely define as many status types as they want. So what I want to accomplish is this:

I want my code to run through my Task table counting the number of records per task status - i.e. Tasks completed = 58, Tasks Received = 32, Tasks under review = 23, Tasks waiting on repair = 12 etc. I wont know beforehand what type of status's are present in the system (besides Received and completed) and they may change. How do I accomplish this? Current code below which doesn't solve the issue as it oly looks for one type of task.

Dim objconn,objRSM,strSQL

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

	  strSQL = "SELECT Count(*) as Numrecords FROM Fejlmelding order by Status ASC"
	     set objRSM = objconn.Execute(strSQL)
             set objRSM = objconn.Execute(strSQL)
         
Modtaget = (objRSM("NumRecords"))

Open in new window

0
Comment
Question by:micamb
  • 7
  • 6
13 Comments
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 41784291
how's your table design looks like? in general, you need to group by with the status text, like:
SELECT Status , Count(Status ) as Numrecords
FROM Fejlmelding
group by Status
order by Status ASC

Open in new window

0
 

Author Comment

by:micamb
ID: 41784295
That returns the number 5 which puzzles me as there in that particular database only exists 4 types currently (Received, Completed, See comments & rejected). How do I display the status text next to the number(s)?
0
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 41784301
>>That returns the number 5 which puzzles me as there in that particular database only exists 4 types currently (Received, Completed, See comments & rejected)

if we are querying same source (same table in same database) with same query, it should return the exact number of records, either as you claimed it 4 or 5 records in total.

>>How do I display the status text next to the number(s)?
do you want to do it in query or in asp ?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

Author Comment

by:micamb
ID: 41784303
Just to clarify:

I'm looking for code, that can display thinsg like this (in this particular database):

Recieved = 12
See comments = 3
Completed = 7
Rejected = 3

(And any other status type found in that table)
0
 

Author Comment

by:micamb
ID: 41784304
Query, please - and thank you so much for the assistance so far!
0
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 41784310
may i also know what database are you using?
0
 

Author Comment

by:micamb
ID: 41784315
mysql
0
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 41784318
see if this is what you need?
SELECT concat(Status, ' = ' , Count(Status )) as Numrecords
FROM Fejlmelding
group by Status
order by Status ASC

Open in new window

0
 

Author Comment

by:micamb
ID: 41784329
We are getting close. This returned:
Completed = 713

However the other types of status are missing. I probably need a loop of some sort? I'll just post the code as it looks now.

<!--#include file="db/database_read.inc"--><%
Dim objconn,objRSM,strSQL

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

	  strSQL = "SELECT concat(Status, ' = ' , Count(Status )) as Numrecords FROM Fejlmelding group by Status order by Status ASC"
	     set objRSM = objconn.Execute(strSQL)
             
         


Records = (objRSM("Numrecords"))



%>
<br><%= Records %><br>

Open in new window

0
 
LVL 51

Accepted Solution

by:
Ryan Chong earned 500 total points
ID: 41784335
>>However the other types of status are missing

you need to do a loop in your asp codes... like:
<!--#include file="db/database_read.inc"--><%
Dim objconn,objRSM,strSQL

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

	  strSQL = "SELECT concat(Status, ' = ' , Count(Status )) as Numrecords FROM Fejlmelding 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

0
 

Author Comment

by:micamb
ID: 41784359
Absolutely amazing! Thanks a million!
0
 

Author Closing Comment

by:micamb
ID: 41784360
Great solution that solved my problem quickly and efficiently!
0
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 41784375
you're welcome glad could make help, cheers
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ASP Sessions Being Cleared/Modified 6 87
RegEx Help - open to other simple suggestions 8 58
Select record with the most recent date 14 67
Filktering Alphabetically 8 30
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

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

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question