Transact SQL :: Adding A Percentage To Value
Nov 30, 2015
I insert/update the figure information in this table with daily figures - ie if no record exists insert new name and figure, if figure exists, update figure.
I have been asked to add logic in the insert/update SP to add a fee of 0.25% to any daily figure that results in the total value in the base table being over 1000.
For example: Base table value is 900 before update ----- Daily figure is 200 so 900 + 200 = 1100 after update of base data. New logic dictates that 0.25% must be added to 100 of this daily figure, as 100 brings it up to 1000 and the other 100 (which makes the 200) takes it over the 1000 threshold. 100 + 0.25% = 0.25 ------- Total value to add to base table = 100 + 100 + 0.25 = 200.25.i am keen to avoid WHILE loops and cursors..
View 4 Replies
ADVERTISEMENT
May 6, 2015
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
View 13 Replies
View Related
Oct 28, 2015
I totally forgot how can I make a percentage. Look at the code below:
create table #acca (name varchar(10), tipo varchar(10), lett int)
insert into #acca values ('Italy','Europe',15), ('France','Europe',10), ('Colombia','America',15), ('Cile','America',75)
select * from #acca
/*Query Number 1 */
select name, tipo, lett, sum(lett) over (partition by tipo) as TotCon,
sum(lett) as Total
from #acca group by name,tipo,lett;
/*Query Number 2*/
with cte as (
select name, tipo, lett, sum(lett) over (partition by tipo) as TotCon,
sum(lett) as Total
from #acca group by name,tipo,lett)
select name, tipo, lett/totcon from cte
Question query number 1: how can I retrieve the absolute total? Sum(lett) over what?
Question query number 2: why lett / totcon retrieves 0?
Question plus: is there a way to retrieve the percentage without using the cte?
View 4 Replies
View Related
May 31, 2015
How can I add a Total Column as the last column in this query and also add a total column to the bottom row of the query? Then after the total column on the right, add a % column. So my expected returned set would be like so:
Location
Train
Bat
Ball
Nuggets
Total
%
[code]..
View 12 Replies
View Related
Apr 2, 2015
This is on SQL Server 2008. Please find a detailed description and the file of the data, that I am working on.
Requirements:
1.
If
'Channel'
is
not
equal to "Omnibus"
where
the 'Trans Description'is
equal to "Purchase"
and
"Redemption"
for
one purchase and
one redemption that match on 'System'
,
'Account TA Number'
,
'Product Name'
,
'Settled Date'
,
and
where
the 'Trade Amount'
of the purchase and
redemption is
within 5%,
then
display those set of records.
2.
If
deemed wash trades,
allow user to update the purchase and
redemption pair 'Trans Description'
from
"Purchase"
to "Exchange In"
and
'Trans Description'
from
"Redemption"
with
"Exchange out"
System Channel Dealer Name Firm Name Product Cusip Product Name Product Share Class Trade ID Settled Date Account TA Number Trans Description Trade Amount
SCHWABPORTAL US - ASG MILLIMAN MILLIMAN 64128K777 Strategic Income Fund A 29806259 30-Jan-15 000BY00F2RW Redemption $ 25,68,458.15
[Code] .....
View 36 Replies
View Related
Nov 26, 2015
I am using the below script to get space alerts and now i am interested in sending alerts if for any drive space available is Less than 10% or 15%.. how to convert beelow code to find in %
Declare @Drives Varchar(20)
DECLARE @Spaces Varchar(50)
DECLARE @availableSpace FLOAT
DECLARE @alertMessage Varchar(4000)
DECLARE @RecipientsList VARCHAR(4000);
CREATE TABLE #tbldiskSpace
[Code] ....
View 3 Replies
View Related
May 18, 2015
I am trying to create the following in SQL automatically.
Step needs to double itself each time. How would I write that one out ? Yes I would have the first number as 1.
ID
Step
1
1
2
2
3
4
4
8
5
16
6
32
7
64
8
128
9
256
10
512
View 6 Replies
View Related
Aug 20, 2015
Tried a few ways and I can ge this to work at the end in then WHEN part. Just struggling to put this together to be accepted as a CASE WHEN statement, probably missing the obvious.
Case when Postcode like '%[abcdefghijklmnopqrstuvwxyz%]' then 'Lowercase Postcode' else 'Postcode OK' end as [DQPostcode]
collate Latin1_General_CS_AS
Simple terms looking for all instances of Lowercase characters in the Postcode field
View 5 Replies
View Related
May 19, 2015
I have a subquery i wanted to add a as a fourth column to my Original Query(is the one below the subquery). How to combine the two queries to one statement?? I tried but was getting an error "Subquery returned more than 1 value "
select
COUNT(*)FreeReduced
from students s join Buildings b on
s.Building_ID = b.Building_ID
where s.Activeness =1 and (Eligibility = 3 or Eligibility =2)
group by building_number,Building_Name
[Code] ....
View 3 Replies
View Related
May 27, 2015
ALTER TABLE [dbo].[bkrm_lendcoll] ADD CONSTRAINT
ReqdCovgAmt_constraint33 CHECK
( case
when CovgAmt = 0 and ReqdCovgAmt = 0 then 1
when CovgAmt = 0 and ReqdCovgAmt = 1 then 1
when CovgAmt = 1 and ReqdCovgAmt = 0 then 1
when CovgAmt = 1 and ReqdCovgAmt = 1 then 0
end =1
)
This is my first attempt to add a constraint for business logic. The desired behavior is that the two columns together have the same behavior as a radio button. (one can be true, the other true, both can be false, but both cannot be true.) I get this error when I attempt to update it.
The ALTER TABLE statement conflicted with the CHECK constraint "ReqdCovgAmt_constraint33". The conflict occurred in database "Std", table "dbo.lendcoll".
So, basically my question is, when you have two bit columns and want them to have the truth table such as described, how can I set a Check constraint to enforce this?
View 14 Replies
View Related
Aug 18, 2015
I'm trying to write a stored procedure that performs a select statement of the RequestID column and the total of the disk size for that row. ie the values on RequestAdditionalDisk1Size + RequestAdditionalDisk2Size + RequestAdditionalDisk3Size where the Requester equals a certain value. I can perform the select statement fine on the individual values, how to add the values of the Disk sizes together and present that back in the select statement.So far the code looks like but is giving me an error around the line performing a SUM.
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spMyRequests]
-- Add the parameters for the stored procedure here
[code]....
View 2 Replies
View Related
Oct 14, 2015
I am trying to do the following;
declare @table table
(
ID int,
InvoiceNumber varchar(10)
)
insert into @table
values (1, 20),
(2, 20),
(2, 20),
(3, 60),
(3, 60)
I am trying to add sequential alphabet letters to the InvoiceNumber value on each row. Example;
1 20a
2 20b
3 20c
4 60a
5 60b
and so on.
View 4 Replies
View Related
Apr 24, 2015
I have a SQL server instance being used as our data warehousing environment. The instance consists of several databases that I am snapshotting as part of our high availability strategy for data. I've created a stored procedure that takes the source database as an argument and that will create a new snapshot when a new one needs to be created and will also automatically remove the old snapshot. It also updates some synonym tables that point to the new snapshot but that might not be an important detail.
I would like to have the stored procedure stored some place global to all of the databases that I am routinely snapshotting, but that would mean putting it in the master database. Although having it there makes things significantly better in terms of usability, it seems like there's something wrong with putting any stored procedures in the master database. Am I wrong? Is it OK to put stored procedures there in situations like this?
View 4 Replies
View Related
May 14, 2015
I have dw schema in the database, owned by user dw.The login name is dw. The login had db_owner right in the database. The default schema for the login on the database is dw.Now Once I assign 'sysadmin' serverrole to dw login, I started seeing stored proc not found error, if try to execute stored proc without mentioning dw.spname;Also I am seeing table not found error while quering tables under dw schema, after the change.
View 20 Replies
View Related
Apr 28, 2015
I am planning to add some new columns to an existing sql server 2012 table. I know that I need to use the alter statement to accomplish this goal. However my questions is the location of where I want to add the new columns to the table. It would make more sense to add the new columns to the middle of the table since these columns have a similar meaning as other columns in the middle of the table.However is it better to add these new columns at the end of the table? I am asking this question since I am thinking I might need some sql to move the values of existing columns and values around?Thus is it better to add new columns to a table in the middle of the table, at the end of the table, or at the end of the table? If so, can you tell me why one location is better than another location?
View 12 Replies
View Related
Oct 8, 2015
I have the following querry :
SELECT APHIST.ReturnDate AS ATDATE
,API_HIST.[ActionPlanItemID]
,API_HIST.[ActionPlanID]
,PIT.[ProductItemID]
[Code] ....
It produced the following result
Based on my querry when n=2, it should be set to 0, why it cannot be set ?
if I added the value 2 to the list of values as below :
CROSS APPLY (Values(0),(1),(2)) d(n)
Then the value n=2 is updated but it adds extra duplicated rows which is not part of my real
View 11 Replies
View Related
Oct 5, 2015
I would like to add database to single user mode to enable broker. So, i have tried this:
ALTER DATABASE myDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE myDb SET ENABLE_BROKER
ALTER DATABASE myDb SET MULTI_USER
But the query works more than half an hour then i have stopped it. i guess it has infinite loop, I get also many of this messages:
Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
There is no other transaction also there is no other user connections. So, what happens?
View 5 Replies
View Related
Oct 12, 2015
I have a table of raw data where each column can be null. The thought was to create an identity key (1,1) and set as primary for each row. (name/ address / zip/country/joindate/spending) with surrogate key: "pkid".However other queries will not use this primary key. So for instance they may count the # of folks at a zip, select all names, addresses etc. The queries may order by join date, or select all the people that joined on a specific date.No other code would logically use the primary key (surrogate primary id key), therefore would it still have any performance benefits? at this time the table would have no clustured or nonclustured indexes or keys. I'm curious if there are millions of records.
View 7 Replies
View Related
Apr 24, 2013
IF NOT EXISTS (SELECT TOP 1 1 FROM dbo.syscolumns WHERE id = OBJECT_ID(N'dbo.Employee) and name = 'DoNotCall')
BEGIN
ALTER TABLE [dbo].[Employee] ADD [DoNotCall] bit not null Constraint DoNot_Call_Default DEFAULT 0
IF ( @@ERROR <> 0 )
GOTO QuitWithRollback
END
It just takes a LOT of time in SQL Server Management studio. I have to cancel the query and cancelling takes a whole lot time. I am using SQL Server 2008.
View 4 Replies
View Related
Aug 28, 2015
I have a a Group By query which is working fine aggregating records by city. Now I have a requirement to focus on one city and then group the other cities to 'Other'. Here is the query which works:
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars'
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]
Here is the result:
St. Louis 1000
Kansas City 800
Columbia 700
Jefferson City 650
Joplin 300
When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars'
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]
Here is the result:
St. Louis 1000
Other Missouri City 800
Other Missouri City 700
Other Missouri City 650
Other Missouri City 300
What I would like to see is a result like:
St. Louis 1000
Other Missouri City 2450
View 5 Replies
View Related
Nov 19, 2015
If I just use a simple select statement, I find that I have 8286 records within a specified date range.
If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277
Is there something I am doing incorrectly in the following sql?
DECLARE @startdate date = '03-06-2015'
DECLARE @enddate date = '10-31-2015'
DECLARE @afterTime time = '17:00'
SELECT
General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct
[Code] ....
View 20 Replies
View Related
Jul 30, 2015
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
I want to do it using SELECT. Is it possible?
View 13 Replies
View Related
Sep 3, 2015
Is there any way or option to get the all columns of dataset added to table when we add a table in data region. It will take lot of time to add one by one and also there are chances to add one column ore than once.
View 7 Replies
View Related
Mar 1, 2004
Hello
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
View 2 Replies
View Related
May 26, 2006
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?
View 1 Replies
View Related
Mar 18, 2008
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 Related
Jun 3, 2008
Hi 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
View 8 Replies
View Related
Jul 23, 2005
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 Related
Mar 11, 2008
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 ??
View 5 Replies
View Related
Sep 13, 2006
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 Related
Mar 11, 2008
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?
View 10 Replies
View Related
May 6, 2008
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.
View 5 Replies
View Related
Aug 5, 2007
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.
View 13 Replies
View Related
