Solved

Displaying number of records depdning on table contents - classic asp

Posted on 2016-09-04
13
30 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 49

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 49

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
 

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 49

Expert Comment

by:Ryan Chong
ID: 41784310
may i also know what database are you using?
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:micamb
ID: 41784315
mysql
0
 
LVL 49

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 49

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 49

Expert Comment

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now