Solved

Displaying number of records depdning on table contents - classic asp

Posted on 2016-09-04
13
46 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Designing forms 3 41
IIS components 2 30
Replace value 2 27
Ajax on ASP 2 31
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

680 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