Calculate Ratio : Urgent
Nov 14, 2007I have to get the ratio like A:B with two calculated columns. A and B both are the counts of the rows.
Please help me with this, it's really urgent!
ADVERTISEMENT
Transact SQL :: How To Calculate A Ratio
Oct 28, 2015How do I author a SQL Query which meets the following requirement: Display a ratio of (instances where status is a success)/(instances where status is a success or instance is a failure). The below syntax is incorrect. However, I hope to beter explain what I am trying to accomplish using the incorrect SQL query below.
SELECT SUM(Case When Status = "SUCCESS")/SUM( Case When Status = "FALLBACK")
FROM ruleResults;
Power Pivot :: Sum For Events And Time - Calculate Total Ratio
May 22, 2015I created a PowerPivot measure which is a ratio Ratio = Number of Events/Time calculated on 12 months. I would like the grand total to be this Rate Sum(Number Of Events)/Sum(Time calculated).
In my Pivot I have one measure which I called Value and this value can have different types depending on one attribute.For instance one attribute is number of events, an other one is time and the third one is ratio.I want to display a custom grand total simple sum for events and time and a calculation of my ratio for ratio.
For instance
201501 201502 201503 TOTAL
Number of events 8 10 10 28
Time 5 5
4 14
Ratio 8/5 10/5 10/4 28/14
Stored Procedure To Calculate Month Salary(urgent)
Aug 30, 2005i want to calculate the month salary of an employee.which will be calculated on the basis of previous available leaves and present available leave(i.e) 2 per month.
View 2 Replies View RelatedCache Hit Ratio Vs Buffered Cache Hit Ratio
Mar 13, 2003On Microsoft performance monitor, what is the difference between SQL Server Cache Manager: Cache Hit Ratio and SQL
Server Buffer Manager: Buffer Cache Hit Ratio? We have a production server where the buffer cache hit ratio is
consistently at 99%, which is normal. However, the cache hit ratio is 73%. What is the difference between the two
hit ratios, and why would we have such a significant difference between the two?
Any help or advice would be much appreciated.
Cache Hit Ratio Over 100%
Sep 18, 2000I have been seeing this strange statistics in one of our servers. The cache hit ratio has gone beyond 100%, it is currently showing 124%. Has anyone seen this before.
Thanks
Cache Hit Ratio
Mar 18, 2005I have a large dell server with 4 processors, and 8 gig of memory on win 2000 advanced with sql 2000 enterprise edition running a 3rd party app. My cache hit ratio averages about 76%. I thought the gneral rule was if you get below 80% to add more memory. However my memory manager shows I am only using 71% of my memory and have a full gig available. I have the sql server set to use about 7.1 gig of the 8 gig on the server. My ? is if I am only using 71% of my memory, will will adding more memory actually help my cache hit ratio
View 5 Replies View RelatedMDX Ratio And All Member
May 21, 2008Hello,
I have a calculated member that is a ratio calculation. It works fine but I would like that the value for the "All" member to be the sum of the children.
So I have the following MDX where the calculated member [Dim Group Rubric].[Group Rubric Description].[BO + ADM Cost] does not give me good results for the "All" member:
WITH
MEMBER [TOTAL YEAR CONTRACT] AS sum({[Total Year]},[Dim Contract].[Contract Number].[563131])
MEMBER [TOTAL YEAR BU] AS sum ([Total Year])
MEMBER [Dim Group Rubric].[Group Rubric Description].[TOTAL BU PVN] as
([Dim Group Rubric].[Group Rubric Description].[PVN], [TOTAL YEAR BU])
MEMBER [Dim Group Rubric].[Group Rubric Description].[TOTAL BU BO + ADM] as
([Dim Group Rubric].[Group Rubric Description].[BO + ADM], [TOTAL YEAR BU])
MEMBER [Dim Group Rubric].[Group Rubric Description].[BO + ADM Cost] as
([Dim Group Rubric].[Group Rubric Description].[PVN], [TOTAL YEAR CONTRACT])
/ ([Dim Group Rubric].[Group Rubric Description].[TOTAL BU PVN], [TOTAL YEAR BU])
* ([Dim Group Rubric].[Group Rubric Description].[TOTAL BU BO + ADM], [TOTAL YEAR BU])
SELECT
NON EMPTY {[TOTAL YEAR CONTRACT],[TOTAL YEAR BU]}
ON 0,
{[Dim Group Rubric].[Group Rubric Description].[BO + ADM Cost]}
*
UNION(
[Dim BV Organisation Finance].[Business Unit].[All]
,FILTER(
[Dim BV Organisation Finance].[Business Unit].children
,[Dim BV Organisation Finance].[Business Unit Code].currentmember.name
= '071MIND1'
)
,FILTER(
[Dim BV Organisation Finance].[Business Unit].children
,[Dim BV Organisation Finance].[Business Unit Code].currentmember.name
<> '071MIND1'
)
)
ON 1
FROM (SELECT ([Dim Year].[Year].&[2008]) ON 0
FROM (SELECT ({[Dim BV Organisation Finance].[Business Unit Code].[071MIND1]
,[Dim BV Organisation Finance].[Business Unit Code].[071DIDD1]}) ON 0
FROM [BV Contract Margin DW]))
When I launch this query I obtain:
TOTAL YEAR BU
BO + ADM Cost All - 45013 (I would like to obtain here - 90651)
BO + ADM Cost 071MIND1 - 35680
BO + ADM Cost 071DIDD1 - 54971
Please do you know what should be changed in this query to obtain the good results?
Thanks.
Guillaume
Cache HIT Ratio Problem
Jul 23, 2005HelloI am tring to figure out why our SQL server is a bit sluggish fromtime to time.It is running a dual XEON, with 2.5 GB RAM, and a fast SCSI I/O subsystem setup as follows.OS, mirrored 2 drivesSQL DATA 16 HDD RAID 10SQL LOG 4 HDD RAID 10SQL tempdb 4 HDD RAID 10OS = win 2003SQL = sql2000 standard editiondbcc showcontig shows me nothing special, it looks okI launch performacne monitor and add SQL server cache manager: hitratioand it is constantly at 7% and never changes up or down, it is justconstant.Can this be correct? and if so it sounds rather bad, we have ahandfull of large tables that are heavily used and enough RAM to holdthem all in RAM so I really do not understand why the cache hit ratiois not higher.Any hints would be greatrgdsMatt
View 9 Replies View RelatedTo Find Ratio Between Two Columns
Oct 16, 2007 Can anyone pls help me to solve my problem in SSRS. Also I wanted to plot the 'Ratio' in to a chart against the 'Year'.
The scenario is:
Year Amount_A Amount_B Ratio(Amount_A/Amount_B)
05 x y x/y
06 a b a/b
07 m n m/n
Its very needy.
Buffer Cache Hit Ratio...how To Measure?
Jan 2, 2004Does anyone know how to measure the buffer cache hit ratio? I have been reading a lot about it but can't find this measurement in Performance Monitor.
Thanks
Getting Reliable Information About Cache Hit Ratio
May 5, 2004Maybe I am just a lot better at this than I thought, but I figure that somewhere there is a mathematical rule that is being overlooked. When I run dbcc sqlperf (lrustats) on some of my production machines, I sometimes end up with a cache hit ratio (which is defined as a percentage, mind you) that is slightly over the limit:
Statistic Value
-------------------------------- ------------------------
Cache Hit Ratio 100.00898
Cache Flushes 0.0
Free Page Scan (Avg) 0.0
Free Page Scan (Max) 0.0
Min Free Buffers 331.0
Cache Size 4362.0
Free Buffers 9434.0
I suspect some counter somewhere is getting wrapped around its 4 byte limit. Is there any reliable source for getting statisics about SQL Server performance? Users tend be unreliable and say everything is slow.
T-SQL (SS2K8) :: Procedure Cache Hit Ratio
Jun 20, 2014I'm putting together some monitor scripts, have buffer cache ratio etc etc but struggling to get an accurate script for the current procedure cache hit ratio...
View 1 Replies View RelatedSQL 2012 :: Value Of Buffer Cache Hit Ratio
Jul 16, 2015What is better values for Buffer Cache Hot Ratio in SQL Server 2012. Is the values changed from 2008r2 to 2012?
View 2 Replies View Related100% Buffer Cache Hit Ratio Not Always Optimal?
Jun 19, 2008I am wondering if 100% buffer cache hit ratio is considered not good in general?
Are there instances that it is actually bad and can contribute to server performance degradation?
Any thoughts on the topic most welcome :)
--------------------
keeping it simple...
Creating Ratio From Summary Data
Dec 1, 2014I am doing some work in microstrategy reports and using SQL data cube. I am creating a summary report (Counting on Unique ID) in MS where when I put in a particular attribute/column from cube it splits the data count down into 2 separate values that I have defined in the select case statement of the SQL query.
I want to work out a ratio of the count unique ID between these two values but can't do this in MS report as it is not a physical column but summary split of data.Is there a way I can do this in SQL? My summary looks something like this:
N E
====================== =========================
**Y** N **Y** N
========= ========== ========= =========
570 140 89 56
Where the Y/N field is the one I have split down against the N/E column. I want to work out ratio between the 2 "Y" fields but in SQL. Here is a SQL example snapshot of my data:
Unique_ID New/Existing Application Attend_Interview
========= ======================== ================
12554445 E Y
65766879 N N
53375654 N Y
44323224 E N
93656786 E Y
wondering if I might be able to do a procedure or similar or something more dynamic in SQL query?
Troubleshooting SQL Buffer Cache Hit Ratio
Jul 23, 2005This issue just happen recently. The buffer cache ratio went from > 90%to 50% and has slowly been climbing back up over 8 hours or so. Itscurrently @ 76%. Is this something I should take action on immediately?It seems to be coming back to normal...
View 1 Replies View RelatedEstimation The Buffer Cache Hit Ratio
Sep 1, 2006HiI have trouble with MSSQL2000 SP4 (without any hotfixes). During last twoweeks it start works anormally. After last optimalization (about few monthsago) it works good (fast, without blocks). Its buffer cache hit ratio wasabout 99.7-99.8. Last day it starts work slow, there was many blocks anddedlocks. There are no any queries, jobs and applications was added. Nowbuffer cache hit ratio oscilate about 95-98. I try update statistics andreindex some hard used tables, but there is no effect or effect is weryshort (after few hours problem return).Mayby somene know what it could be?Is it possible to estimate how each table (using DBCC SHOW_STATISTICS orDBCC SHOWCONTIG or others) how the table affect on total buffer cache hitratio?Marek---www.programowanieobiektowe.pl
View 1 Replies View RelatedBuffer Catche Hit Ratio Counter Always Is Maximum (100). WHY ?
Sep 26, 2000Hi,
In m*y SQL server 7.0,
when using Performance, I see in graph: Buffer Catche Hit Ratio Counter
(SQLServer Buffer Manager objects)always is maximum (100). What does this mean ? What's the Buffer Catche Hit Ratio?
How do I configure SQL server to performance?
Thanks in advance.
DB Engine :: DMV To Check Buffer Hit Ratio On Several Servers
Oct 30, 2015I'm collecting performance data via DMV to check the buffer hit ratio on several servers. Sometimes I get values high above 100% hit Ratio.
Example (50050%):
SQLServer:Buffer Manager:Buffer cache hit ratio 2002
SQLServer:Buffer Manager:Buffer cache hit ratio base 4
SQL Server Admin 2014 :: How To Test Random / Sequential Ratio
Aug 31, 2015I am checking some ratio numbers for our system engineers, those are
Read/write ratio?
Random/sequential ratio?
Read/write block size?
For Read/write ratio, I am using below query,
SELECT
m.type_desc
, CEILING(sum(num_of_bytes_read*1.0) / (sum(num_of_bytes_read*1.0) + sum(num_of_bytes_written*1.0)) * 100) AS 'Read %'
, CAST((sum(v.size_on_disk_bytes) / 1024.0 / 1024 / 1024) AS MONEY) AS 'FileSizeGB'
[Code] ....
Random/sequential ratio, I googled but cannot find a similar query to get the result?
Reporting Services :: SSRS - Show Percentage Or Ratio In Stacked Bar
Nov 20, 2015Sample Query :
SELECT 'A' [Type], 1 AS [NUMERATOR] , 2 AS [DENOMINATOR]
UNION ALL
SELECT 'B' [Type], 2 AS [NUMERATOR] , 4 AS [DENOMINATOR]
UNION ALL
SELECT 'C' [Type], 3 AS [NUMERATOR] , 6 AS [DENOMINATOR]
[Code] ...
I need to show the ratios beside each bar (shown in red) as shown in the above diagram.
Analysis :: Stored Procedure Ratio To Parent Truncating Data
Jun 11, 2015I'm trying to use the Ratio to Parent sproc - [URL] .... The problem is that the stored proc appears to be pegged at 4 decimal places (which translates to ##.##%). This is losing precision and isn't adding up to 100% when the ratios are summed individually.
Can this be fixed within SSAS or will i need to modify the assp code? .NET decimals are meant to automagically scale, so not sure why it's coming out as (5,4) but my .net coding knowledge is fairly poor.I get the correct behaviour if hardcode a ratio to parent MDX calc (the resulting ratio has as many decimal points as are required)
SQL 2012 :: DB Engine Page Life Expectancy And Buffer Cache Hit Ratio Is Too Low
Sep 12, 2014Our server administrator forwarded some messages from SCOM that indicate:
SQL DB Engine 2012 Page Life Expectancy and Buffer Cache Hit Ratio is too low
When I logged into the offending server, I could not find anything in the SQL Log File that indicates this.
I was wondering how did SCOM identify this issue - where in SQL Server would this have been reported to SCOM?
Timeout Expired -- URGENT, URGENT, URGENT!!!
Sep 27, 2000This morning I can not connect to our SQL Server 7.0 whatever from client or server. The error message which I list below:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
A connection could not be estabished to server--Timeout expired
Please verfy SQL Server is running and check your SQL Server registration properties and try again.
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
We use windows NT authentication. We did not do any change on NT. The SQL Server daily schedule job usally stoped at 10:00AM, but today from the Window NT Task Manager, we can see that the SQL Server is still running untill now.
Please help!!!
Best Practice For Data Size/Log Size Ratio
Jul 23, 2005Just wanted to know what is a general rule of thumb when determining log file space against a database's data file.We allow our data file for our database to grow 10%, unlimited. We do not allow our log file to autogrow due to a specific and poorly written process (which we are in a three month process of remove) that can balloon the log file size.Should it be 10% of the Data file, i.e. if the Date file size is 800MB the log file should be 8MB?I realize there are a myraid of factors that go against file size but a general starting point would be nice.ThanksJeff--Message posted via http://www.sqlmonster.com
View 4 Replies View RelatedURGENT.Sql Server Does Not Recognise MAPI , Or Profile Name URGENT
Oct 26, 2000hi, I have settup up sql mail and did the following:
1. created an E-mail account and configured Out look by creating a pop3 mail profile. tested it by sending and receiving mail, that is ook
2. I Created one domain account for MSsqlserver and Sql Agent service. both services use same account and start automatically in the control panel-services
3. I used the profile that I created in outlook to test the sql mail but got an error:
Error 22030 : A MAPI error ( error number:273) occurred: MapiLogon Ex Failed due to MAPI
Error 273: MAPI Logon Failed
I really do not know what went wrong. I followed the steps from bol and still having a problem. Am I missing something.
I do have a valid email account
I do have a valid domain account
I tested outlook using the email account and it worked. so why sql server does not recognise MAPI.
My next question, How to configure MAPI in Sql server if what I did was wrong.
Urgent, Urgent !! My Sql Server Refused To Start Due To Encrypton
Mar 23, 2001Hi, I have 2 windows 2000 server in cluster with sql server 2000 enterprise edition installed.
I have activated the Server-Requested Encryption by using the sql server network utility (Force Protocol Encryption). After this, I have stoped sql server service. But I can't start it at this moment.
The error is:
19015: The encrypton is required but no available certificat has been found.
Please help me to start sql server.
Thanks.
Michel
Urgent Urgent Please.(Access SQL Pass Through Queries)
Jul 6, 2000Hello,
I am facing a huge problem in my sql server database using access as a front end.The main problem is trying to execute queries "views" ,since they reside on sql server now,and using variables or parameters in reports and forms to filter on this query.
Ex.
how can the following be implemented using the same query but in sql server?
Access
------
SELECT MAT_Charts.YYYYMM
FROM MAT_Charts
WHERE ((([Area_Code] & "-" & [GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
GROUP BY MAT_Charts.YYYYMM;
It is specifically this statement in which I am interested:
[GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
Thank you very much for your concern.
Calculate % Qry
Feb 13, 2007Hi,
I am trying to execute a query which calculate % on group by. here is the query
SELECT C.USR_HIGHEST_DEGREE,COUNT(DISTINCT C.MASTER_CUSTOMER_ID) AS [# MEM],(convert(numeric(5,2),COUNT(DISTINCT C.MASTER_CUSTOMER_ID))
/ (
SELECT convert(numeric(5,2),COUNT(CC.MASTER_CUSTOMER_ID)) FROM MBR_PRODUCT AS MPP WITH (NOLOCK) INNER JOIN PRODUCT AS PP WITH (NOLOCK) ON MPP.PRODUCT_ID = PP.PRODUCT_ID INNER JOIN ORDER_MASTER AS AA WITH (NOLOCK) INNER JOIN CUSTOMER AS CC WITH (NOLOCK) ON AA.SHIP_MASTER_CUSTOMER_ID = CC.MASTER_CUSTOMER_ID INNER JOIN ORDER_DETAIL AS BB WITH (NOLOCK) ON AA.ORDER_NO = BB.ORDER_NO ON MPP.PRODUCT_ID = BB.PRODUCT_ID WHERE (CC.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (BB.CYCLE_END_DATE >= GETDATE()) AND (AA.ORDER_STATUS_CODE = 'A') AND (BB.LINE_STATUS_CODE = 'A') AND (BB.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MPP.LEVEL1 IN ('NATIONAL'))
)*100 AS [%]
FROM MBR_PRODUCT AS MP WITH (NOLOCK) INNER JOIN PRODUCT AS P WITH (NOLOCK) ON MP.PRODUCT_ID = P.PRODUCT_ID INNER JOIN ORDER_MASTER AS A WITH (NOLOCK) INNER JOIN CUSTOMER AS C WITH (NOLOCK) ON A.SHIP_MASTER_CUSTOMER_ID = C.MASTER_CUSTOMER_ID INNER JOIN ORDER_DETAIL AS B WITH (NOLOCK) ON A.ORDER_NO = B.ORDER_NO ON MP.PRODUCT_ID = B.PRODUCT_IDWHERE (C.CUSTOMER_STATUS_CODE = 'ACTIVE') AND (B.CYCLE_END_DATE >= GETDATE()) AND (A.ORDER_STATUS_CODE = 'A') AND (B.LINE_STATUS_CODE = 'A') AND (B.FULFILL_STATUS_CODE IN ('A', 'G')) AND (MP.LEVEL1 IN ('NATIONAL')) GROUP BY C.USR_HIGHEST_DEGREE
My problem here it , query gives error. I would appreciate if someone can give me any idea how to go about this. I tried calculating % outside sql but still doesn't work.
Any help would be appreciate.
Calculate Sum Of SQL Top 10
Mar 30, 2007I have an SQL statement which returns the Top 10 states with the number of visitorsSELECT TOP 10 Customer.State States, COUNT(Customer.state) Visitors FROM [Customer] WHERE Customer.year = '2006' GROUP BY Customer.state ORDER BY COUNT(Customer.state) DESCSo far this is what I havestate| visitorsMD341527.2PA215417.2NJ127510.2NY10258.2VA8136.5MA2922.3FL2562DE2431.9OH2411.9CA2381.9But what i need is to calculate the total for the Visitors column in my SQL so that is like soMD341527.2PA215417.2NJ127510.2NY10258.2VA8136.5MA2922.3FL2562DE2431.9OH2411.9CA2381.9Total Top 10995279.3Total for All Years12555100I tried using the sum but I was only getting one value and not the rest...So how can i accomplish this?Thank you
View 8 Replies View RelatedHow To Calculate?
Feb 13, 2008
Hi all,
I'm having the table in the following structure,
UId - int
Pct - int
Amt - money
Example
UId Pct Amt
12 25 1500
12 30 2500
12 45 2000
i want to calculate to calculate the sum of amount for the UId 12 in the following manner,
sum of ( Amt * (Pct/100)) for UId = 12
how to write query in sql server 2000?
when i calculate if the value (Pct/100) < 0 that is 0.25 then it will take it as 0 so i'm getting all the values as 0.
Thanks!
Calculate Mtd And Ytd.
Mar 7, 2001I have a table that has a providername and totals field.
I want to be able to calculate the MTD and YTD totals for each provider.
EX. Provider MTD YTD
Bob 100.00 300.00
But I am not sure how. I can get both totals seperately, but not n the same line....
Thanks
Ron