I'm working with SQL RS 2000 and am putting together some pie charts. In my query, I am counting the total number of records grouped by a case statement so I get the total of one specific type and then the total of all other. What I am wanting to do is in addition to having the actual record count, I would like to also show the percentage of the whole. In the past, what I have done is written a separate query that does not do any grouping and then in my report, divide my individual total by my grand total and get my percentage that way.
I was just wondering if there is any way to get the percentage without having to write the additional query?
Below is my SQl which just counts the number of appointments and grouped by clinic. This is great but what I'd like to add is the percentage within each clinic.
For example Clinic BRESRAD1 has a total of 61 appointments, of which 75.41% are Normal Appointments and 24.59% are Diagnostic, Ideally I would like the percentage in the next column.
BRESRAD1 Normal Appointment 46 BRESRAD1 Diagnostic Appointment 15 BRESRAD2 Normal Appointment 17 BRESRAD2 Diagnostic Appointment 12 BRESRAD3 Normal Appointment 34 BRESRAD3 Diagnostic Appointment 43
My SQL is as follows:
SELECT ClinicCode, CASE WHEN [ApptTypeDesc] LIKE '%Diag%' THEN 'Diagnostic Appointment' ELSE 'Normal Appointment' END AS [Diagnostic Appt], COUNT(OPAppointmentID) AS CountOfOPAppointmentID FROM dbo.OP_APPOINTMENT WHERE (AttendStatusNatCode IN ('5', '6')) AND (ApptFinYr = '2014/15') GROUP BY ClinicCode, CASE WHEN [ApptTypeDesc] LIKE '%Diag%' THEN 'Diagnostic Appointment' ELSE 'Normal Appointment' END ORDER BY ClinicCode
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName Clm ABC-X
X
Page2:
GroupName Clm ABC-Y
Y
Page3:
GroupName Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
I need to write a page that figures out the percentage of students passing an assessment item where students are from a particular school, grade, gender, and are not medically exempt. I did a Count Statement on all test taker, and another Count Statement for the number passing the item who were not exempt. Both of the statements are shown through seperate datalist controls. I then want to get the percentage by dividing the number passed by the number taken and mulitplying that by 100. Is there a better technique or can I do this in a sql statement somehow?
I am havingSELECT DISTINCT Name, (( count(id_Name) * 100 ) / (SELECT count(id_Name) AS tt FROM dbo.Name)) as PercentageFROM dbo.NameGROUP BY Namebut I get only integers : 1,0,4,12I need : 1.63, 0.4, 4.05, 12.78how can I do it ?is there a better way to calculate a percentage ?thank you
SELECT strNomObjet,strNomDescripteur,count(*) FROM @Temp WHERE strReponse = 'A' GROUP BY strNomObjet,strNomDescripteur
This will give me somthing like this For the first one there 25 A Second 27 A
NOMBRE ET OPÉRATIONSdémontre une compréhension du concept du nombre25 NOMBRE ET OPÉRATIONSpeut faire les opérations sur les nombres pour résoudre des problèmes27 STATISTIQUES ET PROBABILITÉSpeut suivre les étapes d'une démarche statistique26 RÉGULARITÉS ET RELATIONSutilise des régularités (suites logiques) pour résoudre des problèmes24 RÉGULARITÉS ET RELATIONSutilise des relations mathématiques (algèbre) pour résoudre des problèmes23 FORMES ET ESPACE (géométrie)utilise la mesure pour résoudre des problèmes17 FORMES ET ESPACE (géométrie)utilise les figures géométriques pour résoudre des problèmes22 STATISTIQUES ET PROBABILITÉSutilise les probabilités pour résoudre des problèmes27 FORMES ET ESPACE (géométrie)utilise les transformations géométriques pour résoudre des problèmes22
After that i use
SELECT count(*) FROM @Temp GROUP BY strNomObjet,strNomDescripteur
That will give me how many person have been tested.
Hello!I have a need to create a function that will return a sublist ofelements from a table based on an associated column value and itsranking within the table (based on a percentage range).To expand with a simple example (only 4 elements):Element | Value---------------A102 | 5A506 | 10A322 | 15A342 | 20I would like to be able to return the range of elements that lie in the50-75% value range (e.g. third quartile range). Therefore with onlythese four elements I would want to return the element code 'A322'.I have attempted to achieve this by using SELECT TOP n PERCENT. I canbring back the required range by combining two select percentstatements.SELECT Element from ELEMENT_TABLEWHERE Element IN(SELECT TOP 75 PERCENT Element FROM ELEMENT_TABLE ORDER BY Value)AND Element NOT IN(SELECT TOP 50 PERCENT Element FROM ELEMENT_TABLE ORDER BY Value)The problem is that I cannot seem to pass a variable to this percentageso if I want another percentage range set, I ave to define anotherfunction.My question therefore is does anyone know either(1) a way I can pass a variable (n) to these SELECT n PERCENTstatementsOR(2) an alternative way of doing this?Many thanks!Andrew
Hi team, I have a column 'a' of a table in which i would like to calculate a percentge of all entries in that column 'a' and place the results in a column 'c' beside column 'a'. Can this be done in sql server ??
Lets say I have a table called ProductProductId Name Price Category1 Table 20.00 Furniture2 Chair3 Lamp Misc What is the SQL command that would give me as a percentage the completeness of a row, either complete or empty?For e.g. SELECT ... FROM Product WHERE ProductId = 2 ; should give a result of 50% as half the Columns in that row are empty.e.g. SELECT ... FROM Product WHERE ProductId = 3 ; should give a result of 25% as 1/4 of the Columns in that row are empty.I tried used something like SELECT COUNT(COLUMN_NAME) AS Expr1FROM INFORMATION_SCHEMA.COLUMNSWHERE (TABLE_NAME = 'Product')but dont know how to go forward.
Ok guys, here is my query GridSqlDataSource1.SelectCommand = "SELECT *, (([WinSum])/(([WinSum]) + ([LossSum]))) AS WinPercent FROM [ResultsView] WHERE ([CDIV] = @CDIV) ORDER BY [CTEAM]" I need it to give me a 3 decimal percentage for WinPercent, but right now it is giving me 0 because as an example: 6 / 7 = 0 instead of 0.857 Any ideas?
I am in need some uinderstanding of how to return the percentage of calls that were closed, between 1, 2 ,3, 4 and 5 days (possibly extendable to catch all those that were holding on to jobs to make it look like the were busy) as well as including all open jobs.
Because of my newbie status I am stumped at how to even start a query of this magnitude, and no this is not a school assignment :-) just some one who is very new to T-SQL and wanting to learn how to extract data from an MS SQL database server, as I find it fascinating, also my bosses find it fascinating the type of data I am returning for them so far.
So enough waffling.
This query will be run through VBA, any variable(s) will be supplied by VBA.
DB name = Envisage Table name = HD_Call Columns = DateRaised (datetime, null), DateCompleted (datetime, null)
Help in understanding how to construct this query will be grately appreciated.
I have a dimension called "Movement rate". The members are "1" to "6". There is a measure called "No. of Products".
What i easily like to do is showing the percentages "No. of Product" for each "Movement rate" of the amount of "No. of Products" without using the frontend-tool.
I have requirement in which i need to calculate percentage value based on billamount and concession amount..to calculate the percentage part and show the o/p.
SELECT PP.KID_ID_NO_V,(PP.FIRSTNAME_V + SPACE(1) + PP.LASTNAME_V)AS [PATIENT_NAME],BM.TOTAL_AMOUNT_M AS [BILL_AMOUNT],BM.CONCESSION_AMOUNT_M AS [CONCESSION_AMOUNT],BM.BILL_AMOUNT_M AS [TOTAL_AMOUNT], FROM BILL_MASTER BM INNER JOIN PATIENT_PROFILE PP ON BM.PATIENT_ID_N=PP.PATIENT_ID_N WHERE BM.BILL_SETTLED_C='Y'
I need to calculate percentage [COUNT(ALERT_RECEIVED_DATE) FRAUDCT,over SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK] I tried my best but I cant come up with the solution.
Please, help.
Thk
SELECT CONVERT(nvarchar(10),dateadd(d,-day(ALERT_RECEIVED_DATE) + 1,ALERT_RECEIVED_DATE),101) PERIOD, COUNT(ALERT_RECEIVED_DATE) FRAUDCT, SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK, SUM(CASE WHEN FRAUD_DECISION ='D' THEN 1 ELSE 0 END) DECLINED, SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) PENDING_EXCEPTION, SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION='D' THEN 1 ELSE 0 END) DECLINED_EXCEPTION FROM TBLFRAUDFINDER O
I need to calculate percentage [COUNT(ALERT_RECEIVED_DATE) FRAUDCT,over SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK] I tried my best but I cant come up with the solution.
Please, help.
Thk
SELECT CONVERT(nvarchar(10),dateadd(d,-day(ALERT_RECEIVED_DATE) + 1,ALERT_RECEIVED_DATE),101) PERIOD, COUNT(ALERT_RECEIVED_DATE) FRAUDCT, SUM(CASE WHEN FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) FRAUDUNWK, SUM(CASE WHEN FRAUD_DECISION ='D' THEN 1 ELSE 0 END) DECLINED, SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION IS NULL THEN 1 ELSE 0 END) PENDING_EXCEPTION, SUM(CASE WHEN A.FRAUDID IS NOT NULL AND FRAUD_DECISION='D' THEN 1 ELSE 0 END) DECLINED_EXCEPTION FROM TBLFRAUDFINDER O
I am relatively new to SQL i.e. I know the basics!
What I am trying to do is: I have a database of customer spend and I am trying to segment them into certain %ages of the search results. So, for example, find all of the customers that are in the top 10% of spenders (I also want to only select customers with a spend of greater than x!). I am trying to do this using a Case When but feel that I may be out of my depth.
Training_Module table data like this TRA_ID..TRA_NAME..TRA_GROUP -------------------------------------- v01SafetyVideo1G1 v02SafetyVideo2G1 v03SafetyVideo3G1 V04SafetyVideo4G2 V05SafetyVideo5G2 v06SafetyVideo6G2 v07SafetyVideo7G3 v08SafetyVideo8G3 v09SafetyVideo9G3
View_Training_Module table data like this EMP_ID.........TRA_ID....VIEW_DATE ------------------------------------------- p0006367V016/2/2007 p0006367V026/2/2007 p0006367V036/2/2007 p0003892V016/12/2007 p0003892V026/12/2007 p0003892V036/12/2007 p0003890V016/15/2007 p0003890V026/15/2007 p0003890V036/15/2007 p0001232V046/16/2007 p0001232V056/16/2007 p0001232V066/16/2007 p0001230V076/17/2007 p0001230V086/18/2007 p0001230V096/18/2007
We have 44 Safety training videos (15 minutes)
How can calculate the percentage of each employee in query ?
if emploee view 22 video it means that this employee 50% view the videos.
We have to calcuate 1. total number of video view by each employee, sum 2. each employee perentage of viewing.ie. percentage % 3. Group wise percentage, ? we have three group A,B,and C, calcuate the each group percentage in query ?
I'm trying to get the percentage of money raised compared to their goal. Basically 100*(SUM(Amount)/Goal). Here's my stored procedure:
CREATE PROCEDURE sproc_GetGoalPercentage ( @memberid int ) AS SELECT(SUM(D.Amount)/M.Goal)*100 FROMMembers AS M INNER JOIN Donors AS D ON M.ID = D.MemberID WHEREM.ID = @memberid GROUP BY M.Goal RETURN
I am trying to figure out the syntax for a query that will essentiallygive me the Percentage each of my areas contributes to the Whole. Iknow this can be achieved by multiple queries but I would like to keepit intact as one single query if possible.For Example I have the following data set--AREA MOUNE 1234SO 4312WE 12312MW 97123NE 1123SO 31WE 312MW 971The results I would like to see would look likeAREA MOU PERCENTMW 98094 .83536NE 2357 .02007WE 12624 .10751SO 4352 .03706The query I came up with is--SELECT DISTINCT Area, SUM(MOU) AS AREA_TOTAL, sum(MOU) /(SELECT SUM(MOU) AS TOTAL_MOUFROM [2004_NOVEMBER_COST])as[PERCENT]FROM [2004_NOVEMBER_COST]GROUP BY AreaAll seems to calculate with the exception of the Percent where all thepercentages are 0's.I think I need to take the first line AREA_TOTAL and now divide by theSUM(MOU) like this--SELECT DISTINCT Area, SUM(MOU) AS AREA_TOTAL, AREA_TOTAL /(SELECT SUM(MOU) AS TOTAL_MOUbut I get Invalid Column.I essence I think it is a simple query but I am hitting a wall. Anyadvice would help.Thanks,Ben
I want to add a percent column to the RIGHT of the total, and also on the bottom row. I can't find any clear examples of how to do this. If I had a new column, it adds additional headers beneath my top row. Or, my columns appear to the LEFT of the data, not the right. Can some please post some simple instructions that will make my simple matrix look like this:
Name Jan Feb Total %
John 5 6 11 60%
Mary 3 4 7 40%
Total 8 10 18 100%
% 40% 60% 100% 100%
I am so stuck on this I can pull my hair out.
Thanks!
Michael
p.s. I really hope the next version of SSRS has a simple "Sub-total %" option that you can enable just like the sub-total column.
What's incorrect with my sql so that the pct is not being calculated? I'm returning all zeros. ------------------------------------------------------------------------------------------------
SELECT
sa.schoolc sch#,
s.schname AS School,
Y = sum(CASE WHEN u.logindate IS NOT NULL THEN 1 END),
N = sum(CASE WHEN u.logindate IS NULL THEN 1 END),
pct = (sum(CASE WHEN u.logindate IS NOT NULL THEN 1 END)/
(sum(CASE WHEN u.logindate IS NOT NULL THEN 1 END)+sum(CASE WHEN u.logindate IS NULL THEN 1 END))) * 100
FROM
users AS u
INNER JOIN stugrp_active AS sa ON u.username = sa.suniq
Hello, I am working on generating a Pie Chart in Reporting Services. I want to display percentage (%) in the pie chart slices, rather than having the actual values, since it does not fit properly into charts. A percentage would be better as a report.
Hope the question is clear. I would be grateful to anyone who answers this question.
How do I write a query to give me the total records in databaseA.tableA divided by the total records in databaseB.tableA This is what I have so far:DECLARE @today as datetime DECLARE @past as datetime
/* set the dates */ set @today = getdate() set @past = getdate() - 3 /* Go back 3 days */
/* create temp table for holding total cases*/ CREATE TABLE #CaseTotalCount ( TotalCount int )
/**/ CREATE TABLE #FlashCycleCount ( FlashCount int )
/**/
INSERT INTO #CaseTotalCount (TotalCount) SELECT COUNT (DataBaseA.dbo.tblProcedureData.CaseID) as TotalCount FROM DataBaseA.dbo.tblProcedureData WHERE DataBaseA.dbo.tblProcedureData.Date < @today AND DataBaseA.dbo.tblProcedureData.Date > @past
/**/ INSERT INTO #FlashCycleCount (FlashCount) SELECT COUNT (DataBaseB.dbo.tblFlashLog.FlashLogID) AS FlashCount FROM DataBaseB.dbo.tblFlashLog WHERE DataBaseB.dbo.tblFlashLog.LoadDate < @today AND DataBaseB.dbo.tblFlashLog.LoadDate > @past
/*function call*/ SELECT dbo.percentage(#FlashCycleCount.FlashCount, #PeriOpTotalCount.TotalCount) FROM #FlashCycleCount JOIN #PeriopTotalCount ON #PeriOpTotalCount.TotalCount != 0 This works but when I try to bind this to a formview a get nothing. any ides?
