Here is the sample data .
Tradedate Type TotalOrdQty FillQty
2008-01-24 ATS 1392906.0 817965.0
2008-01-23 ATS 645306.0 469269.0
.... Goes on
Depending on the Type Column, I have to calculate the Percentage change in the TotalOrderQty and FillQty ( Daily , monthly , Yearly )
Is there a way to calculate the Percentage change with out using a
cursor.
Thanks
Venu
My site has members. Each member has a certain goal, an amount that they are hoping to raise. Here's my tables:
---------------------
Members
---------------------
ID | Goal
---------------------
---------------------
Donors
---------------------
Amount | MemberID
---------------------
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
All it does is return 0.
I'm trying to calculate the % of people but it doesn't seem to be working. I get the following error:
ORA-00904: "ACT"."PAT_ID": invalid identifier
00904. 00000 - "%s: invalid identifier"
What am I doing wrong?
WITH ENCOUNTERS AS
(
SELECT COUNT(*) AS TOT_COUNT
FROM
(
SELECT DISTINCT PATIENT.PAT_ID
[code]....
Hi,
I have a column called QTY and I need to calculate the percentage for each row based on the total sum of the column. They are all decimals. I have an OLE DB source that is reading the table used an aggregate task using the QTY column with operation SUM and called the output SUM OF QTY. Then I have a Derived Column task that takes the QTY column / SUM OF QTY * 100 to create the percentage. My numbers are not coming out correctly and when I put a data viewer from the aggregate task, it shows the same number for both columns:
QTY SUM OF QTY
0.01833 0.01833 (this should be the sum of all the decimals in the QTY col)
What am I doing wrong?
Thanks in advance!
Isabelle
Hi All,
I have to create a table like this using SSRS.
LoanStatus NoOfLoans Amouunt Percentage
Started
1,000
1,000,000.00
100%
Sent to Partner
350
350,000.00
35%
Denied by Partner
50
50,000.00
5%
Accepted by Partner
300
30,000.00
30%
Loan Apps Pnd Disb
10
100,000.00
1%
Loan Apps Disbursed
290
290,000.00
29%
Table is grouped by loan status.
Percentage is calculate as NoOfLoans/TotalNoOfLoans.
Please can anyone tell me how to calculate the grand total and Percentages?
Calculating % of Column Total in Charts...
Doing this exercise in Excel is always very simple:
Hello forum.
I have three columns in my table with the following datatypes:
Date - DateTime
Height - Decimal
Change_From_last_Height - Decimal
I am using "SQL Server 2005 Express Edition". I'm fairly new to SQL and would greatly appreciate any help or advice I can get.
The "date" column increases by an extra day in every new row and I then enter the new height of the plant. What I want to know is how I can get SQL server express to automatically enter the difference in height between the current row's height and that of the previous row.
Is it possible to automate the entry in the Change_From_Last_Height column in SQL?
Put another way, I know how to find the difference between two values in the same row but different columns, but how do I calculate the difference between values in adjacent Rows (ie. Rows next to each other)?
Thank you.
I create a report base on categories and sales of goods. Now I have Daily Info about all Products.
But I Need to present this report base on weekly periods. in pivotal format.
I family with pivotal format but change between daily report and weekly report is ambiguous for me.
Coming from Oracle background:How do you carry thiese admin tasks in SQL 2000 Environment :Check instance availabilityCheck listener availabilityCheck alert log files for error messagesClean up old log files before log destination gets filledAnalyze tables and indexes for better performanceCheck tablespace usageFind out invalid objectsMonitor users and transactionsThanks for help.
View 1 Replies View RelatedHello
I need a little help.
I'm working on this query but is not returning the value I want
SELECT CONVERT(float, hits)/ abat as 'AVE'
The hit column value is 3 and abat 4 I want to return 750 but so far I'm getting
.75 or if I try decimal returns .7500000000
Thanks
Robert
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
View 5 Replies View RelatedHi i create a temp table that count how many A,B,C and D there where in a table.
I need calculate the percentage.It could be easy put the problem is that i cannot devide by the same number.
Here my temp table DECLARE @Temp table
(
strLastName varchar(50),
strFirstName varchar(50),
strReponse char(10),
strNomObjet nvarchar(200),
strDescripteurOrdre char(10),
strNomDescripteur nvarchar(200),
strObjetOrdre char(10)
)
I want to check how many A there is.
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.
It will give me this
36
36
35
36
35
36
36
35
35
So i need to take the first 25
25 / 36 * 100 = 69.4%
27 / 36 * 100 = 75%
26 / 35 * 100 = 74.2%
I hope you understand what i mean!
Any idea?
Tks in advance
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
View 3 Replies View RelatedHi 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 ??
View 5 Replies View RelatedOk this may have been answered somewhere but to be honestly I don't even know what I need so its hard for me to search for it. Here is what I have going on lets see if anyone out there can answer this one. I have a 20 year old system that is tied to some lab equipment that we use for coal testing. The equipment runs back to an old win98 PC which is running a DOS program that has long since been abandoned by the manufactures. I can't upgrade the program because it will mean upgrading the hardware which is somewhere in the neighborhood of $100k at least. The program saves everything in what I believe is a dBase IV file (.dbf) and using Excel I can easily see the data inside. What I am trying to do, if I can figure out a way other than writing my own custom app, is to do a daily export/import of the data from the dbf file into a more usable database, then create some spiffy forms to mess with the data. Each day the dbf file is wiped out by the ancient program and a new one is created. My database experience is somewhat limited I have at best a rudimentary knowledge of SQL but I should be able to follow along even if I have to do a little research. Mainly what I am looking for is a way to import the dbf file into an already existing database (new or existing table) automatically via a script or something. I want to retain all the data from past days with out editing the the original DBF file. Can this be done or am I just asking the impossible?
Thanks for your time,
Donavan
Just wonder what do you dba do in your daily tasks. I usually have meetings where I have to say what Ive been doing during the week.
=============================
http://www.sqlserverstudy.com
Hi: I am having a problem with the Syntax. I am trying to get the daily value for a contract_nbr. The selection below shows that I am selecting by Case statement when day = 2 (2nd Day) and the month is a parameter provider by the user. For this particular example, I am using 2 (February).
Thanks for the help !!!!
[code]
Set @Req_Month = '2'
SELECT Distinct a.contract_nbr,
Case when Day(c.Beg_eff_date) = 2 and month(c.Beg_eff_date)= @Req_Month
Then c.rcpt_nom_vol-c.rcpt_fuel-rcpt_act_vol
Else 0
End As Day_2
from TIES_Gathering.dbo.contract a
Inner Join TIES_Gathering.dbo.NOm b on a.contract_nbr = b.contract_nbr
Inner Join TIES_Gathering.dbo.Nom_vol_detail c on c.Nom_id = b.Nom_id
where (a.contract_sub_type = 'INT') and (a.Contract_type_code ='GTH')
and (DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) < a.current_expirtn_date)
and (c.rcpt_dlvry_ind ='R')
Group by a.contract_nbr
[code]
Hello All,
What should i have to check as a DBA at every new day. Is any one having scripts for the checklist then please send me that scripts.
Thanks
Prashant Hirani
In practice, I find encryption kind of messy to use -- opening, closing keys, use of certificates, all the while trying not to give away the password in cleartext. If our .NET programmers are to use it efficently in high-level code we need a function call, say "EncryptValue(<input value>)", that simply returns the encrypted value. Is it possible to write such a function? (And of course, we need the mate "DecryptValue(<encrypted value>)".
TIA,
Barkingdog
How can I return daily running totals for each day:
TABLE:
date: # of downloads
1/1/2007 100
1/1/2007 12
1/1/2007 8
1/2/2007 100
1/2/2007 20
1/2/2007 20
1/3/2007 40
example of what I want:
RESULTS:
date number of downloads total
1/1/2007 120 120
1/2/2007 140 260
1/3/2007 40 300
I want to return a running total value for each seperate day.
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.
View 2 Replies View RelatedOk 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?
Hello every one, I am trying to get the total percentage of a column
Regions
Workbooks Required
Workbooks Sent
Workbooks Returned
Workbooks Complete
A
20
21
20
18
B
33
33
33
30
C
19
29
18
16
D
9
18
8
8
Totals
Thanks in advance for any tips/solutions.
Hi Guys,
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.
Hi,
easy question for advanced users:
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.
Thx in advance for any help.
Andreas
b/w two colums?
i have two numberic columns
i want percentage of column2/column1
They are on separate table.. l am intended to creat view with my results.
thanks
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'
[code]....
Hi, Please help.
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
Hi, Please help.
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.
Please help if you can!!
Thanks, Kris
I have following tables structure
Employee
---------
EMP_ID varchar PK
NAME varchar
DEPARTMENT_CODEvarchar
POSITION_CODEint
Position
--------
POSITION_CODEint PK
POSITION_NAMEvarchar
Department
----------
DEPARTMENT_CODEvarchar PK
DEPARTMENT_NAMEvarchar
Training_Module
---------------
TRA_ID varchar PK
TRA_NAMEvarchar
TRA_GROUPvarchar (three group, A,B and C)
View_Training_Module
--------------------
MV_ID int PK
EMP_ID varchar
TRA_ID varchar
VIEW_DATE datetime
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 ?
regards
Martin
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
View 3 Replies View Related