Jump to content
  • 0
Sign in to follow this  
codell

Calculate A % Of Records Where Columnvalue=X Using Sql

Question

Hello,

 

I am looking for help on a solution I am hoping to implement. I will do my best to be clear and include all necessary information but please let me know if you have any questions. I believe this can be accomplished with SQL but am not familiar enough with SQL to begin this on my own.

 

I have a search and report datapage (tabular) that pulls information from Table A. Table A has columns including Client, Project, Asset, Task, and Status where a client can have multiple projects, a project can have multiple assets, assets can have multiple tasks and each task has a status (See Table A.png). Each Project in Table A can have several thousand records beneath it.

 

I would like to use SQL to display a Percentage of all records within a particular Project (lets say project 1) that have a Status of "Complete". "1" will be passed as a parameter into the filter (Column=Project) using @Project_ID.

 

I would like to display just the percentage of complete records and possibly a count of total records, both filtered by @Project_ID. I do not wish to actually display a full table. My ideal product would look something like the second attachment (Table A Completion Percentage).

 

Further notes: I am absolutely open to changing the Datapage in order to accomplish this goal. I am not however looking to display this information in a chart as I really am only looking for the Percentage and do not need to display the percentage of other status' as well, as a pie chart would. Also I want to make sure that this only displays information from a single Project_ID at a time as this percentage will be a Client facing data page and I don't want them to see other clients or projects.

 

I was able to accomplish this solution previously by adding a field to the table that entered a 1 if a record was updated with a Complete status and a 0 if updated with something else but this made the data quite messy and it complicated things if the data was updated via an import as the calculation field was never submitted or calculated. The calculation as I'm sure you guys know, only exists on the Datapage level and not within the database itself. Anyways, I am redoing this to avoid going down that road again.

 

Thank you all so much in advance for any help you can provide.

 

Cooper

post-14492-0-00616000-1440610109_thumb.p

post-14492-0-69714500-1440611105_thumb.p

Share this post


Link to post
Share on other sites

2 answers to this question

Recommended Posts

  • 0

Hello codell,

 

You can add the following code in the 'Formula' section in the calculated field:

(CONVERT(float, (SELECT COUNT(Project) FROM TableName WHERE Project=target.[@field:Project] AND Status=target.[@field:Status])))/(CONVERT(float, (SELECT COUNT(Project) FROM TableName WHERE Project=target.[@field:Project])))*100

Enter name of your table instead of TableName.

 

Hope it helps

Share this post


Link to post
Share on other sites
  • 0

Iren,

 

Thank you so much for your help. With a little bit of tweaking I was able to get the code to work perfectly. I will include it below if anyone would need this as a reference.

(CONVERT(float, (SELECT COUNT(Project) FROM Table_A WHERE Project=[@Project_ID] AND Status='Complete')))/(CONVERT(float, (SELECT COUNT(Project) FROM Table_A WHERE Project=[@Project_ID])))

I changed the conditions for Project to receive the parameter [@Project_ID] instead. I also changed the Condition for Status to receive the static value of 'Complete'. And finally the other change I made is I removed the "*100" at the end because simply formatting as a percentage takes care of that for us.

 

Once again I truly appreciate your help.

 

Cooper

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...