SQL Server 2012 :: Calculation Based On Count Function To Format As Decimal Value Or Percentage
Dec 4, 2014
I'm trying to get a calculation based on count(*) to format as a decimal value or percentage.
I keep getting 0s for the solution_rejected_percent column. How can I format this like 0.50 (for 50%)?
select mi.id, count(*) as cnt,
count(*) + 1 as cntplusone,
cast(count(*) / (count(*) + 1) as numeric(10,2)) as solution_rejected_percent
from metric_instance mi
INNER JOIN incident i
on i.number = mi.id
WHERE mi.definition = 'Solution Rejected'
AND i.state = 'Closed'
group by mi.id
ProdName Amount TranType P1 100 A P1 100 S P2 200 A P2 205 S
In case the ProdName is same, and Amount = or (within +/- 5%) of Amount, I have to update the TranType column as IN/OUT respectively as shown below in the tables.
I am okay with using 2 different tables if needed as in the records comes in one table and then i can reference that table to upload the values in another.
ProdName Amount TranType P1 100 IN P1 100 OUT P2 200 IN P2 205 OUT
The order of the records coming in can be different order, they need not be subsequent.
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
I am working on a report using SQL server Business Intelligence development studio in which I need to count the total number of saleslines that either has a status : Delivered or Invoiced.
I am using the inbuilt count function under the calcuations, as: Count(Fields!SalesStatus.Value, scope). I am not sure what scope means here however, I read that scope refers to some dataset, dataregion or grouping. I dont know what that means. I shall be really thankful if someone can help me with this.
INSERT INTO MAIN VALUES ('1000', '1/1/2014',3000,1000,700,1500) INSERT INTO MAIN VALUES ('1000', '3/5/2014',1000,2000,650,200) INSERT INTO MAIN VALUES ('1000', '5/10/2014',500,5000,375,125) INSERT INTO MAIN VALUES ('1000', '11/20/2014',100,2000,400,300) INSERT INTO MAIN VALUES ('1000', '8/20/2014',100,3500,675,1300)
We have customer accounts that we measure usage. We want to run a report for all customers whose current usage is 0 and a count of how many months it has been zero consecutively. Here is an example.
I have the table below and want to show the prop_code if the rent_review_date count is less than 1 in 12 months. This means to show only propcode if there has not been any rent update since the first rent_review_date
DECLARE @table TABLE ( Prop_Code INT ,Current_Rent INT ,Revised_Rent INT ,Rent_Review_Date varchar(10) ,Rent_Review_Time DATEtime)
I want to change decimal precision dynamically without rounding value
For example
10.56788 value for 2 decimal precision is 10.56. 10.56788 value for 3 decimal precision is 10.567. ---CASE 1 without dynamic parameter--------- DECLARE @DECIMALVALUE AS tinyint SELECT CONVERT(DECIMAL(10,2),500000.565356) As Amount
[Code] ....
I am getting error as follows......
Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '@DECIMALVALUE'
This decimal precision format value will vary company to company ...
I have a requirement regarding a color combination data. I have a lookup table that holds a colorid, p1, p2, p3, p4 to p8 which will be having colors Red, Green and Amber. P1 to P8 columns holds these three colors based on their combinations.
I have attached the look up table data for reference.I need to pass the color values to p1 to p8 and need to retrieve the color id based on the passed color. If we pass values for all p1 to p8 then it is easy to get the color code, however it will not happen. The passed values may be dynamic. ie we will not have all 8 values all the times. sometimes we will have 2 colors passed, sometimes 5 colors will be passed.
If i pass only two colors say red and red, i need the color id of only the row that has red and red for p1 and p2 alone. i dont want want all the colorid's that has red and red in p1 and p2 and some other colors in p3 to p4.
The exact colorid of the combination must be returned on passing the values to p1 and p2.I am passing Red and Red as values to P1 and P2. In the look up table we can have 10 rows that has red and red i p1 and p2 like
So the result must have only the colorid 1 and not all the colorid's listed above. when I pass 3 red as values for p1, p2, p3 then the result must be 10. Colorid 1, 20, 30, 40, 50, 60 and 70 must not come in the result.I need a function or procedure that will accept the arguments and provide me the result based on the values.
I have a dataset named Diagnosis_Code_Analysis which has as part of the SQL query: COUNT(DISTINCT IntOOS) AS Cases which is a simple case count. In my tabular report I have a column with the following expression: =Sum(Fields!Cases.Value) and this works fine.
Now, my next column I want to see the percentage of the whole for each case and I am using the following expression: =Sum(Fields!Cases.Value)/Sum(Fields!Cases.Value, "Diagnosis_Code_Analysis")
My results are always 100% for each case (line item). I have tried changing the format to P and even 0.00% but it still reports 100%.
Can someone give me a better method to produce this percentage?
I have the following problem with ROUND. When doing the calculation for each value by a percentage using round, the sum of the result does not equal the sum of the values for the percentage (also using round).
IF OBJECT_ID('Tempdb..#Redondeo') IS NOT NULL DROP TABLE #Redondeo Create Table #Redondeo (Orden int Identity(1,1),Valores money) Insert into #Redondeo Select 71374.24 Union Select 16455.92 Union Select 56454.20 Union Select 9495.18
I am selecting the count of the students in a class by suing select COUNT(studentid) as StCount FROM dbo.student But I need to use a case statement on this like if count is less than 10 I need to return 'Small class' if the count is between 10 to 50 then I need to return 'Medium class' and if the count is more than 50 then 'Big class'.
Right now I am achieving this by the following case statement
SELECT 'ClassSize' = CASE WHEN Stcount<10 THEN 'Small Class' WHEN Stcount>=10 and StCount<=50THEN 'Medium Class' WHEN Stcount>50 THEN 'Big Class' END FROM( select COUNT(studentid) as Stcount FROM dbo.student) Stdtbl
I have a table which gets updated with the usage figure every week. Any similar t-sql which returns the increase in usage percentage of all the columns.
I have a table of People and their ID, the starting month (a fixed number of months, say 10 for this), the ending month, and the percent of work time (0-1 being 0-100%). If they have a % work of 0, I do not want to see anything. But if the % changes, from say .5 to .75, I would need the first and last month they were at .5, and the first and last month they were at .75
The Table:
/****** Object: Table [dbo].[TestProject] Script Date: 02.07.2014 10:15:08 ******/ IF OBJECT_ID('TempDB..#TestProject2','U') IS NOT NULL DROP TABLE [dbo].[#TestProject2] GO CREATE TABLE [dbo].[#TestProject2]( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[Code] ....
The data:
--===== All Inserts into the IDENTITY column SET IDENTITY_INSERT #TestProject2 ON INSERT INTO #TestProject2 ("ID","PersonID", "PercentLoad","MonthID") SELECT 1,123456,0,1 UNION ALL
We have a table to 100M rows and up until now we were fine with an non clustered index a varchar(4000) because we never went above 900 bytes (yes it is a bad design).We have the need to support international character sets now so the column was updated to nvarchar(4000) and now we have data past the 900 byte limit.
The data is long, seems useless but is needed by the business and they need to be able to search "where bigcolumn like 'test%'". With an index, even with a huge amount of data, it was 'fast'. Now of course without an index it is unusable. The wildcard is always at the end of the search. I made a full text index on the column and basic queries such as: select * from ourtable where contains(bigcolumn, 'AReallyLongStringofTextHere') works fine unless there is a space in the data. We loose thousands of returned rows because of spaces in the data.
I have tried select * from ourtable where contains(bigcolumn, '"AReallyLongStringofTextHere that includes spaces"') but not all of the data is returned. I get 112 rows with the contains statement. The table scanning statement of "select * from ourtable where bigcolumn like 'AReallyLongStringofTextHere that includes spaces%' returns 1939 rows.I understand that a full text index is breaking the long string up since it contains spaces. Is there a way to retain the entire string as 1 index entry or is there a way to fix my query to return all of the rows?
/* I am having trouble with the CASE statement when ">50". It comes out as zero (0) when according to the data in the example should be ".9". */ CREATE TABLE #CLASS_TIMES ( START_TIME DATETIME, END_TIME DATETIME ) INSERT INTO #CLASS_TIMES (START_TIME, END_TIME) SELECT '2015-05-08 10:00:00.000','2015-05-08 10:45:00.000' UNION ALL --The record in 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?
Recently I have come across a requirement where i need to design a table.
There are some columns in table like below with DECIMAL Datatype:
BldgLength
BldgHeight
BldgWeight
Based on my knowledge, i know that values before Floating-Point will not be more than 4 digits.
Now as per MSDN,
Precision => 1 - 9 Storage bytes => 5
so i can create column as:
BldgLengthDECIMAL(6,2) DEFAULT 0
OR
BldgLengthDECIMAL(9,2) DEFAULT 0
Now while reading some articles, i came to know that when we do some kind of operation like SUM Or Avg, on above column then result might be larger than current data type.
So some folks suggested me that i should keep some extra space/digits considering above MATH functions, to avoid an Arithmetic Over Flow error.
So my question is what should be value of DataType for above column ?
I have some location assignment data that I need to convert. I need to know how long each account spent in a certain location for each month of it's overall startdate/enddate period.
E.g. Account 1 stayed in USA for 31 days in January, and 15 days in February. Account 1 stayed in UK for 13 days in February and 26 days in March. Etc.
create table #temp(account int, loc varchar(10), startdate datetime, enddate datetime) insert into #temp select 1,'USA','2014-01-01','2014-02-15' insert into #temp select 1,'UK','2014-02-16','2014-03-26' insert into #temp select 1,'AU','2014-03-27','2014-06-07' insert into #temp select 2,'UK','2014-08-15','2014-09-01' insert into #temp select 2,'AU','2014-09-02','2014-10-17' select * from #temp drop table #temp
I have a program which brings in Skill ratings for an assesmnet people with the title of "Worker have to take aolng with a file number they are assigned. The program also being in the reporting line that each worker is a part of.
The rating levels are 1 through 5. What I need to do is to do is create a table that shows the count and percentage of each rating giving to the workers for each skill grouped by Vp, AVP, Supervisor and Director. So all the works that are Ultimately under a AVP and all the wokers that are Ultimately under a director and so on.
NameRoleSkillCount of % of Count of % of Rating 1 Rating 1 Rating 2 Rating 2 Gerald VPA1100 29% 13033% Gerald VPB195 28% 9524% Gerald VPB2120 35% 7018% Gerald VPB330 9% 10025% KimVPA1 KimVPB1 KimVPB2AND SO ON
Hopefully a nice easy one to begin with. I have an access database which I use to build my web pages - I use sql to extract products which meet certain criteria then the pages are automatically generated. I can extract the data without any problem. An example I use is shown below:
=================================================== SELECT * FROM products WHERE products.group = 'LCD-HDTV' AND status = 'LIVE' AND tradeprice > 0 AND description3 > '' ORDER BY diagonal, tradeprice, description3 ===================================================
I now want to get it to work out a discount of 1% on the products shown using the tradeprice field. I've tried many times using the SELECT and SUM functions but am not getting the syntax correct. Any help would be very much appreciated.
I have entered the code as P0 when formatting my Y axis to show 0% to 100%. This showed me percent values as 0% to 10,000%. Then after reading the forums I put the major gridline interval as 0.2 to get jumps of 20% but this just puts a lot of lines on the chart as 0,0.2,0.4 till 10,000%.
I am stuck at a problem, not sure on how to go about writing a query that will return as a percentage the number of fields in a row that are null.
For instance, a row from my table: Row1 : field1 field2 field3
If field3 is empty or null, my query should return 67%.
So far I have gotten the number of fields: select count(1) from information_schema.columns where table_name='myTable'
I could loop through the fields but I am sure there is a simpler way of doing it, I have seen something simpler in the past with some builtin SQL functions. I am using MS SQL 2005.
I have small requirement in my project. I need to display the results of the WHERE clause based on percentage/ranking of exact match.
I mean the result set should be displayed based on percentage match.
For example i have the below table.
create table test ( id int identity(1,1) primary key, ename varchar(10) )
insert into test(ename) select 'REG' insert into test(ename) select 'xyz' insert into test(ename) select 'abc' insert into test(ename) select 'Reg' insert into test(ename) select 'Regsxysn' insert into test(ename) select 'psReg'
I need the output something similar as below
REG Reg Regsxysn psReg
I have tried out with full text indexing but i could'nt get the required output.
I have small requirement in my project. I need to display the results of the WHERE clause based on percentage/ranking of exact match.
I mean the result set should be displayed based on percentage match.
For example i have the below table.
create table test ( id int identity(1,1) primary key, ename varchar(10) )
insert into test(ename) select 'REG' insert into test(ename) select 'xyz' insert into test(ename) select 'abc' insert into test(ename) select 'Reg' insert into test(ename) select 'Regsxysn' insert into test(ename) select 'psReg'
I need the output something similar as below
REG Reg Regsxysn psReg
I have tried out with full text indexing but i could'nt get the required output.
DECLARE @MyPay decimal(5,9); SET @MyPay = 258.235543210; SELECT CAST(@MyPay AS decimal(5,2))
This is what the resultset is currently with the code above:
258.24
I would like to Not have the value round up. I would like to always show only the first two digits to the right of the decimal and not perform any rounding.
This is how I calculate the ratio of failures in an order:
31 Days Table 1 query sum(CASE WHEN (datediff(dd,serDATE,'2015-01-21')) >= 31 THEN 31 WHEN (datediff(dd,serDATE,'2015-01-21')) < 0 THEN 0 ELSE (datediff(dd,serDATE,'2015-01-21'))END) as 31days1 .
How do i loop and pass dates dynamically in the Datediff?
31 Failures Table 2 query SUM(Case when sometable.FAILUREDATE BETWEEN dateadd(DAY,-31,CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102)) AND CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102)Then 1 Else 0 END) As Failures31,31 Day Cal(Formula) combining both Table 1 and Table 2 ((365*(Convert(decimal (8,1),T2.Failures31)/T1.31day))) [31dayCal]This works fine when done for a specific order.
I want a similar kind of calculation done for day wise and month wise.
2. what approach should I be using to achieve day wise and month wise calculation?
I do also have a table called Calender with the list of dates that i can use.
