I need to divide amount in joint account. So if joint account has 2 account holders & amount is 35622.15 then one person should have 17811.08 and other person should have 17811.07
If I used below query it just give me 17811.08 for both account holders so when we sum it it's one penny extra.
select cast((35622.15/2) as decimal(15,2))
Is there any way i can achieve this.
Background:
Void Start Date: When a property becomes empty or vacant
Let Date: When the property is filled in again
I have the sample data below and like to show the void loss per month basis as below:
1) Allocate the amount from voidloss column between months based on voiddays:
for example for propcode 3698 the amount 13,612.56 needs to be divided between September and October based on the VoidDays. i.e of the 39 voiddays, 25 where in September and 14 in October hence 8726 will be allocated to September and 4886.56 to October
2) After allocating the amount sum the amount by controlgroup and total the voiddays per month. It will be great if we can divide the voiddays between months and sum them by controlgroup as well.
So in the end result we should have
ControlGroup, Month, Year, VoidLoss, VoidsdayinMonth
106 September 2014 8726 25
106 October 2014 4886.56 14
106 December 2014 2940 7
Declare @voidloss Table
( History_IND INT
,PropCode VARCHAR(10)
,VoidCategory VARCHAR(10)
,ControlGroup VARCHAR(10)
,VoidStartDate date
[Code] .....
I have typed a query by combining multiple tables to show sum of TotalReceivedAmount by MRN. It also have payment mode which is paid by cash or credit card. Now the query shows the sum for MRN combining all the payment done by credit card and cash. How am I supposed to show the amount paid by credit card and cash separately?
Here is the query :
SELECT Distinct dbo.CA_Payment.MRN, dbo.CA_Patient.FirstName, dbo.CA_PaymentModeMaster.Description AS PaymentMode,
CASE (dbo.CA_Payment.PaymentModeID) WHEN 2 THEN dbo.CA_Payment.CardNumber END AS CreditCardNumber, CASE (CA_Registration.PatientTypeID)
WHEN 1 THEN dbo.CA_PatientTypeMaster.Description WHEN 2 THEN dbo.CA_DebtorMaster.DebtorName END AS Debtor,
[Code ....
Being one step removed from innumerate, I was wondering whether there was a more elegant way to avoid divide by zero error instead of trudging through a bunch of isnulls.
My intuition tells me that since multiplication looks like repeated addition, that maybe division is repeated subtraction?
If that's true is there a way to finesse divide by zero errors by somehow reframing the statement as multiplication instead of division?
The sql statement that is eating my kishkas is
cast(1.0*(
(ISNULL(a.DNT,0)+ISNULL(a.rex,0)+ISNULL(a.med,0))-(ISNULL(b.dnt,0)+ISNULL(b.rex,0)+ISNULL(b.med,0))/
ISNULL(a.DNT,0)+ISNULL(a.rex,0)+ISNULL(a.med,0)) as decimal(10,4)) TotalLossRatio
Is there a way to nucleate the error by restating the division? My assertion underlying this statement is that the a alias represents a premium paid, so between medical, pharmacy and dental, there MUST BE at least one premium paid, otherwise you wouldn't be here. the b alias is losses, so likewise, between medical, pharmacy and dental, there MUST BE at least one loss (actually, it just occurred to me that maybe there are no losses, but that would be inconceivable, but ill check again)) so that's when it struck me that maybe there's a different way to ask the question that obviates the need to do it by division.
I need to evaluate a formula in sql server 2008
Table 1 contains
Entity Value
A 2424053.500000
B 1151425.412500
C 484810.700000
Table 2 contains
Entity Formula
A (2100*(1-0.0668)*24*mday*10)
B (1000*(1-0.0575)*24*mday*10)
C (1260*(1-0.09)*24*mday*10)
Where mday is number of days taken from user
I need to calculate the output of value/formula for each entity can you provide me the query for the same
The datatype for formula column is varchar
I do not have the liberty to use cursors or loops.mday will be a input fromt the user say 'mday = 31' ..i need to divide the value in the first table with the computed value of the formula after replacement
I am trying to figure out how to pivot a temporary table. I have a table which starts with a date but the number of columns and columns names will vary but will be type INT (Data, col2,col3,col4………….n)
So it could look like
Date , TS-Sales, Budget , Toms sales
01-Jan-14,100,120,300
02-Jan-14,80,150,300
03-Jan-14,100,20,180
Turned to this
01-jan-14, 02-jan-14, 03-jan-14
100,80,100
120,150,20
300,300,180
Or even just the date and a SUM
What I want is to be able to sum al the columns but without knowing the name and the amount columns to start with this is a manually processes. How could I automate this?
I have an issue where I have multiple rows of data and I need to reduce a dollar amount by a fixed maximum. I am going to throw some code in here to give a rudimentary idea of the data and what the final result should be.
declare @tbl table
(LineNum int,
Code varchar(2),
Amt money,
MaxAmt money
[Code] ....
I need to run an update so that the result of the following query:
select LineNum, Code, Amt, MaxAmt from
@tblLooks like this:
LineNum Code Amt MaxAmt
----------- ---- --------------------- ---------------------
1 AA 10.00 50.00
2 AA 20.00 50.00
3 AA 20.00 50.00
(3 row(s) affected)
I have tried cursors but got unexpected results or the MaxAmt always defaulted to the original even if I updated it. This seems like a simple problem but I have been banging my head against the wall for 2 days now. I've written some pretty complicated updates with less effort than this and I must have some mental block that is keeping me from figuring this out.
I have a routine that generates an HTML email and sends it just fine, but one of the columns ends up with 4 decimal places for a column datatype of money. How can I get the script to output only 2 decimal places for the amount column from the select statement?
Here's the script:
declare @tableHTML nvarchar(max) ;
set @tableHTML =
N'<h1>Revenue Report</h1>' +
N'<table border="1">' +
N'<tr><th>Amount</th><th>Index</th><th>CompObj</th><th>Rev Type</th><th>Program</th>'+
CAST ((SELECT td=SUM(dbo.tblAllocations.Amount),'',
[code]....
Given the following example;
declare @CustIfno table (AccountNumber int, StoreID int, Distance decimal(14,10))
insert into @CustIfno values ('1','44','2.145223'),('1','45','4.567834'),
('1','46','8.4325654'),('2','44','7.8754345'),('2','45','1.54654323'),
('2','46','11.5436543'), ('3','44','9.145223'),('3','45','8.567834'),
('3','46','17.4325654'),('4','44','7.8754345'),('4','45','1.54654323'),
('4','46','11.5436543')
How can I show the shortest Distance by AccountID and StoreID. Results would look like this;
AccountNumberStoreID Distance
1 44 2.1452230000
2 45 1.5465432300
3 45 8.5678340000
4 45 1.5465432300
During install of SQL Server 2005, we can of course use a domain account or the built-in system account for running the services. I lean toward domain for obvious reaons but would like to know a +/- to each option and why I'd choose one over the other and what consequences or limitations one may encounter if I choose one over the other.
View 6 Replies View Related hi allCan somebody please explain to me what does it mean when someone saids use joint
indices to join tables?thanks in advance
CREATE TABLE [dbo].[SEL](
[MAIN_ID] [int] NULL,
[DATE_TAKEN] [smalldatetime] NULL,
[TIME] [int] NULL,
[DAILY_RAINFALL] [int] NULL
) ON [PRIMARY]
insert into SEL
values(194,'6/1/2006 12:00:00 AM',730,11)
insert into SEL
values(194,'6/1/2006 12:00:00 AM',930,4)
insert into SEL
values(194,'6/1/2006 12:00:00 AM',1830,10)
insert into SEL
values(194,'6/1/2006 12:00:00 AM',1930,20)
insert into SEL
values(194,'6/1/2006 12:00:00 AM',2130,14)
insert into SEL
values(194,'6/1/2006 12:00:00 AM',2230,0)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',730,22)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',930,43)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',1830,0)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',1930,54)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',2130,21)
insert into SEL
values(195,'6/1/2006 12:00:00 AM',2230,6)
CREATE TABLE [dbo].[station_info](
[STATE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[STATION_NAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MAIN_ID] [int] NOT NULL
) ON [PRIMARY]
insert into station_info
values('SEL','PUCHONG',196)
insert into station_info
values('JHR','BulohKasap',5)
Above script showing table as follow
SEL
MAIN_ID | DATE_TAKEN | TIME | DAILY_RAINFALL
--------------------------------------------------------
194 | 6/1/2006 12:00:00 AM | 730 | 11
194 | 6/1/2006 12:00:00 AM | 930 | 4
..........
..........
202 | 6/1/2006 12:00:00 AM | 450 | 23
..........
..........
*This table storing DAILY_RAINFALL everyday from time to time for each MAIN_ID.
station_info
STATE | STATION_NAME | MAIN_ID
---------------------------------------
SEL | Puchong | 194
JHR | BulohKasap | 5
.........
.........
*This table storing MAIN_ID description. Main_ID is a primary key.
1. 1 day having many TIME. So, we only take which time having MAX(DAILY_RAINFALL) per day per MAIN_ID to do the SUM(DAILY_RAINFALL) for that month.
So far, i've this query
SELECT CAST(CAST(m.Month AS varchar(2)) + '/1/' + CAST(m.Year AS varchar(4)) AS datetime) AS DATE_TAKEN,m.MAIN_ID,m.DAILY_RAINFALL
FROM
(
SELECT MONTH(t.Date_Taken) AS Month,
YEAR(t.Date_Taken) AS Year,
t.Main_ID,
SUM(t.Daily_Rainfall) As Daily_Rainfall
FROM
(
SELECT t1.MAIN_ID,t1.DATE_TAKEN,t1.TIME,
t1.DAILY_RAINFALL
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY DATE_TAKEN,MAIN_ID
ORDER BY DAILY_RAINFALL DESC) AS RowNo,* FROM dbo.SEL)t1
INNER JOIN dbo.STATION_INFO t2 ON t2.MAIN_ID=t1.MAIN_ID AND
t1.RowNo=1
AND t1.DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-12,GETDATE()),101)
AND t1.DATE_TAKEN<=CONVERT(VARCHAR(10), GETDATE(), 101)
WHERE t2.STATE='SEL')t
GROUP BY MONTH(t.Date_Taken),YEAR(t.Date_Taken),t.Main_ID)m
ORDER BY Main_ID,Date_Taken
*Assume GETDATE()=6/10/2007
After run above SQL, I got below resultset,
DATE_TAKEN |MAIN_ID|DAILY_RAINFALL
---------------------------------------------------
2006-06-01 00:00:00.000 |194 |329
2006-07-01 00:00:00.000 |194 |160
2006-08-01 00:00:00.000 |194 |388
2007-04-01 00:00:00.000 |194 |394
...........
...........
2006-06-01 00:00:00.000 |195 |145
2006-07-01 00:00:00.000 |195 |82
2006-08-01 00:00:00.000 |195 |225
...........
...........
I'm stuck when joint table below. The purpose of joint is to pickup [cumrf1year] column
(if DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-12,GETDATE()),101)
AND t1.DATE_TAKEN<=CONVERT(VARCHAR(10), GETDATE(), 101))
and pickup [month_year] value depend on DATE_TAKEN (if DATE_TAKEN=6/1/2006, [month_year]=6/30/2006, if DATE_TAKEN=7/1/2006, [month_year]=7/31/2006).
MAIN_ID is foreign key to relate [longterm_rf_temp] table below.
CREATE TABLE [dbo].[longterm_rf_temp](
[main_id] [int] NOT NULL,
[month_year] [datetime] NULL,
[cumrf1mth] [float] NULL,
[cumrf3mth] [float] NULL,
[cumrf6mth] [float] NULL,
[cumrf9mth] [float] NULL,
[cumrf1year] [float] NULL
) ON [PRIMARY]
insert into longterm_rf_temp
values(194,'6/30/2006',207.94,550.7,850.7,1150.7,1450.7)
insert into longterm_rf_temp
values(194,'7/31/2006',200.64,590.4,858.7,1260.7,1550.7)
insert into longterm_rf_temp
values(194,'8/30/2006',222.64,390.4,958.7,1460.7,1750.7)
insert into longterm_rf_temp
values(195,'6/30/2006',217.94,550.7,840.7,1150.7,1324.7)
insert into longterm_rf_temp
values(195,'7/31/2006',202.64,590.4,858.7,1260.7,1659.7)
insert into longterm_rf_temp
values(195,'8/30/2006',222.64,490.4,958.7,1460.7,1733.7)
After joint, I should get below resultset,
DATE_TAKEN |MAIN_ID|DAILY_RAINFALL | [cumrf1year]| DiFF | DEV
----------------------------------------------------------------------------------
2006-06-01 00:00:00.000 |194 |329 | 1450.7 | -1121.7 | -0.773
2006-07-01 00:00:00.000 |194 |160 | 1550.7 | -1390.7 | -0.896
2006-08-01 00:00:00.000 |194 |388 | 1750.7
2007-04-01 00:00:00.000 |194 |394 | ......
...........
...........
2006-06-01 00:00:00.000 |195 |145 | 1324.7
2006-07-01 00:00:00.000 |195 |82 | 1659.7
2006-08-01 00:00:00.000 |195 |225 | 1733.7
...........
...........
This is the formula,
DIFF = DAILY_RAINFALL - [cumrf1year]
DEV = DIFF/[cumrf1year]
I almost give up to adjust the above SQL but still fail. Please help me, i'm really-really stuck.
I have these 2 queries that I need to combine into one. What is the best way of doing it? This website is made up of 293 tables so it gets confusing.
(Query 1)
SELECT category_products.category, category_products.product, category_products.position, data.base_price, data.custom_description, models.manufacturer_id, models.custom_search_text, models.name, models.image_location
FROM category_products, data, models
WHERE category_products.category = '36'
AND category_products.product = data.model_id
AND data.model_id = models.id
AND data.active = 'y'
$manufacturer_id=$data["manufacturer_id"];
(Query 2)
SELECT inventory_types.manufacturer_id, inventory_types.default_vendor_id, vendors.id, vendors.name
FROM inventory_types, vendors
WHERE inventory_types.manufacturer_id = '$manufacturer_id'
AND inventory_types.default_vendor_id = vendors.id
I have two tables tbl1 and tbl2, which I do a full outer join between tbl1 and tbl2 on recordId field. The recordId field is not a key in either of the tables.
If there is one row each for a recordId 123 in both tables, the select query would return one combined row.
If tble1 had two rows for recordId 123, and tbl2 had one row for the same, it would return to rows repeating the data in tbl2.
If tbl2 had two rows and bl1 had one row, it would return two rows in output repeating the data in tbl1.
Is the above correct? Would the result be different if it was an inner join instead of full outer join?
Is it ever possible that one of the two records with recordId 123 will be dropped from the result?
Hi Guys
Am new to sql, and I wold appreciate help with optimising the folloing example. The result of the example should be to list a result with details of the Column names:
OPBal| Receipt| IssTrns| Transfer| ClBal
SELECT dbo.inventory.location, dbo.inventory.itemnum,
(select sum(dbo.matrectrans.linecost) where dbo.matrectrans.issuetype LIKE 'RECEIPT' ) As Receipt,
( select sum(dbo.matrectrans.linecost)where dbo.matrectrans.issuetype LIKE 'TRANSFER' ) As Transfer,
( select(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost)where dbo.IST_ITEMDETAIL.logdate='2006-07-20' ) As OpBal,
( select (IST_ITEMDETAIL.curbal*IST_ITEMDETAIL.avgcost)where IST_ITEMDETAIL.logdate='2006-08-21' ) As ClBal,
( sum(matusetrans.linecost) ) As IssTrnf
FROM dbo.matrectrans, dbo.matusetrans, dbo.IST_ITEMDETAIL , ( dbo.inventory inner JOIN dbo.item
ON dbo.inventory.itemnum = dbo.item.itemnum AND dbo.inventory.orgid = dbo.item.orgid )
WHERE dbo.inventory.location = dbo.matusetrans.storeloc
AND dbo.inventory.itemnum = dbo.matrectrans.itemnum AND dbo.inventory.siteid = dbo.matrectrans.siteid
OR dbo.inventory.location = dbo.matrectrans.tostoreloc AND dbo.inventory.itemnum = dbo.matusetrans.itemnum
AND dbo.inventory.siteid = dbo.matusetrans.siteid OR dbo.inventory.location = dbo.matrectrans.fromstoreloc
OR
dbo.inventory.location = dbo.ist_itemdetail.location AND dbo.inventory.itemnum = dbo.ist_itemdetail.itemnum
GROUP BY dbo.inventory.location, dbo.inventory.itemnum,dbo.matrectrans.issuetype,(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost),
dbo.IST_ITEMDETAIL.logdate,dbo.IST_ITEMDETAIL.curbal,dbo.IST_ITEMDETAIL.avgcost
I'm creating a temporary table in a Sql 2005 stored procedure that contains the transaction amount entered in a period <= the period the user enters.
I can return that amount in my result set. But I also need to separate out by account the amounts just in the period = the period the user enters. There can be many entries or no entries in any period. I populate the temporary table this way:
SELECT
t.gl7accountsid,
a.accountnumber,
a.description,
a.category,
t.POSTDATE,
t.poststatus,
t.TRANSACTIONTYPE,
t.AMOUNT,
case
when t.transactiontype=2 then amount * (-1)
else amount
end as transamount,
t.ENCUMBRANCESTATUS,
t.gl7fiscalperiodsid
FROM
UrsinusCollege.dbo.gl7accounts a
join
ursinuscollege.dbo.gl7transactions t on
a.gl7accountsid=t.gl7accountsid
where
(t.gl7fiscalperiodsid >= 97
And
t.gl7fiscalperiodsid<=@FiscalPeriod_identifier)
And poststatus in (2,3)
and left(a.accountnumber,5) between '2-110' and '2-999'
And right(a.accountnumber,4) > 7149
And not(right(a.accountnumber,4)) in ('7171','7897')
order by a.accountnumber
Later I create a temporary table that contains budget information. I join these 2 temporary tables to produce my result set. But I don't know how to get the information for just one period. For example, if the user enters 99 as the FiscalPeriod_identifier, I need a separate field that contains only those amounts(if any) that were entered for each account in Period 99.
Can anyone help? It may be that I am not seeing the forest for the trees, but I can't figure it out.
Thanks very much.
Sue
Hi all,
I have a table named Prescription that consists of attributes like PatientId, MedicineCode, MedicineName, Prices of different drugs, quantity of different drugs(e.g 1,2,3,10), date .
I would like to get a summary of the total number and amount of different drugs in a specific period, the total amount of each type of drug.
I kindly request for help.
Thanx in advance.
Ronnie
I have been running a script in SQL Server 2000 as sa also as a Active Directory user who has administrator rights (I tested both approaches SQL Server then Windows Authentication) in Query Analyser which grants execute rights to the stored procedures within the database instance and Query Analyser does not give any errors when I run the script. I have made sure that each transaction has a go after it. I then return to Enterprise Manager, check the rights (I apply them to roles so that when we create another SQL Server user we just grant him/her rights to the role) and discover that the role has not been granted the rights. I seems to be occurring only with 2 of the procedures. Is there a known bug that might be causing this?
yours sincerely
Craig Hoy
I have several DTS jobs that runs well as a job with my nt login account for the SQL agent service startup account, but if I use the System account
they fail with this error.
" Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider"
The data has change access to the System account under the NT security.
Thank you in advanced.
Jorge
Hi all, i hope you can help me.
Basically a dts package has been setup that pulls in data from another companies server, this data requires to be on-demand i.e individual users can pull in updates of the data when they require it.
I am using xp_cmdshell and dtsrun to pull in the data. This obviouly works fine for me as i am a member of sysadmin.
Books online quotes " SQL Server Agent proxy accounts allow SQL Server users who do not belong to the sysadmin fixed server role to execute xp_cmdshell"
So i went to the SQL Server Agent Properties 'Job System' tab and unchecked 'Non-sysadmin job step proxy account' and entered a proxy account.
The proxy account has been setup as a Windows user with local administrator privilages and even a member of the sysadmin server role - just in case.
Now when i log onto the db with my test account - a non-sysadmin - and attempt to run the stored proc to import the data i recieved the message 'EXECUTE permission denied on object 'xp_cmdshell', database 'master', owner 'dbo' '
hmm... so basically i have either misunderstood BoL or there is something not quite right in my setup.
I have search the net for a few days now and yet i can find no solution.
Can anyone help?
Hi there,BOL notes that in order for replication agents to run properly, theSQLServerAgent must run as a domain account which has privledges to loginto the other machines involved in replication (under "SecurityConsiderations" and elsewhere). This makes sense; however, I waswondering if there were any repercussions to using duplicate localaccounts to establish replication where a domain was not available.Anotherwords, create a local windows account "johndoe" on both machines(with the same password), grant that account access to SQL Server onboth machines, and then have SQL Server Agent run as "johndoe" on bothmachines. I do not feel this is an ideal solution but I havecircumstances under which I may not have a domain available; mypreliminary tests seem to work.Also, are there any similar considerations regarding the MSSQLSERVERservice, or can I always leave that as local system?Dave
View 1 Replies View RelatedI have a situation that I have discovered in our QA database that I need to resolve. When I looked at the Activity Monitor for our server, I discovered that a process is running under a domain user account for one of our .Net applications. The problem is that that domain user account has not been created as a SQL login account on the server. I am trying to figure out how someone can log in to the database server with a domain user account that has not been added to SQL Server as a login account.
Does anyone have any insight on this? I don't like the idea of someone being able to create domain account that can access the database without me granting them specific access.
- Larry
In the full recovery model, if i run a transaction that inserts 10MB of data into a table, then 10 MB of data is moved in the data file. Does this mean then that the log file will grow by exactly 10MB as well?
I understand that all transactions are logged to the log file to enable rollback and point in time recovery, but what is actually physically stored in the log file for this transactions record? Is it the text of the command from the transaction or the actual physical data from that transaction?
I ask because say if I have two drives, one with 5MB/s write speed for the log file and one with 10MB/s write speed for the data file, if I start trying to insert 10 MB of data per second into the table, am I going to be limited to 5MB/s by the log file drive, or is SQL server not going to try and log all 10 MB each second to the log file?
I have a field that divides one field by the sum of two others. However, when both of the two latter fields are 0's, I get a divide by zero error and the output halts. How do I get this so that for these records I put a NULL or UNDEFINED or something in there at least so I can see the rest of the output. Thanks.
View 1 Replies View RelatedI have this sql posted below which sometimes gets a divide by zero error. How can you get around this error please
SELECT
COUNT(DISTINCT dbo.safety_obs_data.form_id) AS TotalObs,
SUM(dbo.safety_obs_data.safe) AS TotalSafe,
SUM(dbo.safety_obs_data.unsafe) AS TotalUnsafe,
CONVERT(decimal(18, 2), (SUM(CASE WHEN (safe) * 100.0) / (SUM(safe) + SUM(unsafe)))AS [% Safe],
SUM(CASE WHEN unobserved = 1 THEN 1 ELSE NULL END) AS NotSeen,
SUM(CASE WHEN made_safe = 1 THEN 1 ELSE NULL END) AS TotalMadeSafe,
SUM(CASE WHEN sap_note = 1 THEN 1 ELSE NULL END) AS TotalSAPNote,
SUM(CASE WHEN honk = 1 THEN 1 ELSE NULL END) AS TotalHonks,
dbo.Employee.emp_user_id
FROM
dbo.safety_obs_data
INNER JOIN dbo.Employee ON dbo.safety_obs_data.create_by_emp_no = dbo.Employee.emp_no
WHERE
(dbo.safety_obs_data.create_dte BETWEEN CONVERT(DATETIME, @start, 102) AND CONVERT(DATETIME, @end, 102))
GROUP BY dbo.Employee.emp_user_id
SELECT CASE WHEN (SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END) + SUM(CASE WHEN Rspec_Vulgar = 'N' THEN 1 ELSE 0 END))
= 0 THEN '0' ELSE (SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END) * 1.0) / (SUM(CASE WHEN Rspec_Vulgar = 'Y' THEN 1 ELSE 0 END)
+ SUM(CASE WHEN Rspec_Rude = 'Y' THEN 1 ELSE 0 END)) END AS Rspec_Vulgar
FROM dbo.TekliveQuery
---
Note:
How can I avoid the the error message if the dividend and the divisor are both 0?
When I specify a formula between Computed Column Specification, I have twozero values, getting Divide by Zero error, any idea how can I avoid this? Istill want SQL Server to display Zero if it is 0/0, is this possible in SQLServer database?ThanksJ.
View 3 Replies View RelatedHi,
In the BI developement, in a matrix i'm trying to avoid division by zero, but even if i test the field before the division, i've got the error everytime.
I put the folowing test condition in the field, but i've got an error.
Ex : iif(Turnover <> 0, Cost/Turnover,0)
But i've got error message when the turnover is 0.
Thanks for your help.
1 ALTER PROCEDURE links_GetAllLinks
2 @startRowIndex INT,
3 @maximumRows INT,
4 @sortExpression nvarchar(50)
5 AS
6
7
8
9
10
11 SET @startRowIndex = @startRowIndex + 1
12 --SELECT @TopicsFrom = CASE @TopicsDays WHEN '1' THEN DATEADD(day,-1,getdate()) WHEN '2' THEN DATEADD(day,-7,getdate()) WHEN '3' THEN DATEADD(day,-14,getdate()) WHEN '4' THEN DATEADD(month,-1,getdate()) WHEN '5' THEN DATEADD(month,-3,getdate()) WHEN '6' THEN DATEADD(month,-6,getdate()) WHEN '7' THEN DATEADD(year,-1,getdate()) ELSE DATEADD(year,-1,getdate()) END
13 -- populate the table CAST(getdate() as int)
14
15
16 IF @sortExpression LIKE ''
17 BEGIN
18 SET @sortExpression = 'links_Links.link_date'
19 END
20 DECLARE @sql nvarchar(4000)
21 SET @sql = 'Declare @Links TABLE
22 (RowNumber INT,
23 link_id INT,
24 cat_id INT,
25 cat_title VARCHAR(100),
26 sub_id INT,
27 sub_name VARCHAR(100),
28 link_aproved BIT,
29 link_name VARCHAR(100),
30 link_url varchar(100),
31 link_desc varchar(500),
32 link_date datetime,
33 link_rating INT,
34 link_ratingscount INT,
35 link_ratingvalue INT,
36 link_poster uniqueidentifier,
37 link_comments INT,
38 hit_date datetime);INSERT INTO @Links
39 SELECT ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + '), links_Links.link_id, links_Links.cat_id, links_Categories.cat_title, links_Links.sub_id, links_SubCategories.sub_name, links_Links.link_aproved, links_Links.link_name, links_Links.link_url, links_Links.link_desc, links_Links.link_date, links_Links.link_rating, links_Links.link_ratingscount, (links_Links.link_rating/links_Links.link_ratingscount) as link_ratingvalue, links_Links.link_poster, links_Links.link_comments, links_Links.hit_date
40 FROM links_Links INNER JOIN
41 links_Categories ON links_Categories.cat_id = links_Links.cat_id INNER JOIN
42 links_SubCategories ON links_SubCategories.sub_id = links_Links.sub_id
43 WHERE links_Links.link_aproved = 1;SELECT * from @Links
44 WHERE RowNumber BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) + ' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' + CONVERT(nvarchar(10), @maximumRows) + ') - 1'
45
46
47
48 EXEC sp_executesql @sql
49
50
51 RETURN at row 39, i define a field by dividing to values. I use these for ratings (finding the mean). The problem is, when a new link is submited, it has no ratings, so it performs a divide by zero. how do I check if the column "link_ratingscount" is zero? if it is, then return a zero, and if it is not zero, then do the divide?
Hi all,
I am having a terrible time with a stored procedure. I think it has something to do with a divide by zero issue. Here is my sproc:
ALTER PROCEDURE [dbo].[GetPlayers]
@TeamID int
AS
SELECT PlayerID,
TeamID,
FirstName,
LastName,
FirstName + ' ' + LastName As Name,
"SPct" =
CASE (SELECT Count(PlayerID) FROM GameDetail WHERE AtBatID IN(1, 2, 3, 4) AND GameDetail.PlayerID = Player.PlayerID)
WHEN 0 THEN 0
ELSE
Cast
( Cast
( (
(SELECT Count(PlayerID) FROM GameDetail WHERE GameDetail.PlayerID = Player.PlayerID AND AtBatID = '1') +
(SELECT Count(PlayerID) FROM GameDetail WHERE GameDetail.PlayerID = Player.PlayerID AND AtBatID = '2') +
(SELECT Count(PlayerID) FROM GameDetail WHERE GameDetail.PlayerID = Player.PlayerID AND AtBatID = '3') +
(SELECT Count(PlayerID) FROM GameDetail WHERE GameDetail.PlayerID = Player.PlayerID AND AtBatID = '4')
/
(SELECT Count(PlayerID) FROM GameDetail WHERE AtBatID IN(1, 2, 3, 4) AND GameDetail.PlayerID = Player.PlayerID
) * 1.0
) As int
) As Decimal(4,3))
End
FROM Player
WHERE TeamID = @TeamID
ORDER BY FirstName, LastName
Here is the error I get when I run this:
Msg 8115, Level 16, State 8, Procedure GetPlayers, Line 9
Arithmetic overflow error converting int to data type numeric.
So the results should look like: .333 or .500 or .000
It's driving me crazy. I have tried everything but cannot get the desired results. I'm hoping someone can help me.
Thanks,
Bob
I know you can resolve "divide by zero" messages with a CASE statement.
However, the T-SQL docs suggest that I should be able to simply turn off both the error message and the effect of the error with
SET ARITHIGNORE ON
and
SET ARITHABORT OFF
I am trying to execute these two statements within a stored proc but I need their effect to reach to a select statment that is a sql string (as in 'EXECUTE (@SQLSTRING)' within the same stored proc.
Am I wrong to expect these statements to be able to deal with "divide by zeroes"?
I haven't been able to make it work so I would appreciate any pointers that someone might have.
Many thanks.
let say
1/2 = 0.5
anyone know how to set it become 0.5? by default the value will become 0,
this is from a SQL playbook
i don't understand the logic of DIVIDE when you use more than one NOT EXISTS
such as this query
select distinct itemname from qdel del where not exists (select * from qspl
where not exists (select * from qitem where itemtype='n' and not exists
(select * from qdel where qdel.itemname=qitem.itemname
and qdel.splno=qspl.splno)) and not exists (select * from qdel
where qdel.itemname = del.itemname and qdel.splno=qspl.splno))
i don't understand how can NOT EXISTS code brings out the results of the existing rows in those not exists lines
like it says NOT EXISTS itemtype 'n' etc. but that is the results!