T-SQL (SS2K8) :: Eliminating Divide By Zero Error

Oct 21, 2014

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.

View 6 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Eliminating Duplicates While Insert?

Apr 10, 2014

WITH cte_OrderProjectType AS
(
select Orderid, min(TypeID) , min(CTType) , MIN(Area)
from tableA A inner join
tableB B ON A.PID = B.PID left join
tableC C ON C.TypeID = B.TypeID LEFT JOIN
tableD D ON D.AreaID = B.ID
group by A.orderid
)

This query uses min to eliminate duplicates. It takes 1.30 seconds to complete..

Is there any way I can improve the query performance ?

View 9 Replies View Related

T-SQL (SS2K8) :: Divide Using Formula From Different Table

Jan 23, 2015

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

View 1 Replies View Related

T-SQL (SS2K8) :: How To Divide Amount In Joint Account

Mar 6, 2014

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.

View 4 Replies View Related

Divide By Zero Error

Feb 24, 2006

Hello,

When I execute the following query, I get a Divide by zero error. I believe the Divide By zero error is occuring in the following statement. How do I avoid the error?


ModelDiff = CONVERT(decimal (8, 4), abs(yest.ModelValue-today.ModelValue)/abs(yest.ModelValue))


drop table #MaxArchiveDates
DECLARE @MaxArchiveDate INTEGER
, @EdgeModelID INTEGER
, @DefinitionID INTEGER
SELECT ArchiveDataDate = MAX(ArchiveDataDate) INTO #MaxArchiveDates FROM intldbArchive.dbo.GOModelUniversesSaveD
UNION SELECT ArchiveDataDate = MAX(ArchiveDataDate) FROM intldbArchive.dbo.GOSecurityGroupModelValSaveD
UNION SELECT ArchiveDataDate = MAX(ArchiveDataDate) FROM intldbArchive.dbo.GOSecuritiesSaveD
UNION SELECT ArchiveDataDate = MAX(ArchiveDataDate) FROM intldbArchive.dbo.GOUniverseSecurityMembeSaveD
SELECT @MaxArchiveDate = MIN(ArchiveDataDate) FROM #MaxArchiveDates
SELECT @MaxArchiveDate
SELECT ModelID = today.ModelID
, RelativeUniverseID = today.RelativeUniverseID
, SecurityID = today.SecurityID
, ModelValueT = CONVERT(decimal (8, 4), today.ModelValue)
, ModelValueY = CONVERT(decimal (8, 4), yest.ModelValue)
, ModelDiff = CONVERT(decimal (8, 4), abs(yest.ModelValue-today.ModelValue)/abs(yest.ModelValue))
FROM OptMod.dbo.GO_SecurityGroupModelValues today
JOIN intldbArchive.dbo.GOSecurityGroupModelValSaveD yest
ON today.SecurityID = yest.SecurityID
AND today.ModelID = yest.ModelID
AND yest.ArchiveDataDate = @MaxArchiveDate

Thanks in advance!!!
sqlnovice123

View 1 Replies View Related

Divide By Zero Error

Feb 28, 2006

I am writing a report that deals with time in seconds. I am trying to figure out the average handle time for each call.
To Get this number i use 5 fields. the formula is

(((cs_acd_trk_t + cs_acd_ext_c)/(cs_acd_trk_c + cs_acd_ext_c)) + ((cs_t_acd_wrk)/(cs_acd_trk_c + cs_acd_ext_c))/(cs_acd_trk_c + cs_acd_ext_c))

Which translates to ((Incoming Handle Time) + (Work))/(Number of Incoming Calls)

When it does the calculations some times the cs_acd_trk_c + cs_acd_ext_c (number of incoming calls) is zero.

I dont know how to get around this ... i tried to set that value to <>0 in my query but it threw my other numbers off for number of calls which is a different value on the report.
Any Help on this would be super appreciated.
Thanks,
Mitch

View 2 Replies View Related

Divide By Zero Error Only With Top

Sep 27, 2006

In the following expression, the query executes with no errors (there are sometimes 0's and nulls in IRON and ENERGY):

SELECT * FROM gff2vg

WHERE (gff2vg.ENERGY > 0 and gff2vg.IRON > 0)

order by IRON/ENERGY desc

but when TOP is added there is a divide by zero error. Is there a way to avoid this?

SELECT TOP(64) * FROM gff2vg

WHERE (gff2vg.ENERGY > 0 and gff2vg.IRON > 0)

order by IRON/ENERGY desc



Thanks.

View 7 Replies View Related

#Error Using IIF And Divide By Zero

Jan 19, 2007

I am getting an error in a calculated field that could potentially divide by zero, even though I'm using an IIF. The column displays in the report as "#Error". My expression looks like this:


= IIF(Fields!Qty.Value = 0, "None", Fields!Hours.Value / Fields!Qty.Value)

I have successfully used this approach with INT fields, but this time the Hours field is a NUMERIC(9,2). My workaround is to do this:


IIF(Fields!Qty.Value = 0, "None", IIF(Fields!Qty.Value = 0, 42, Fields!Hours.Value) / Fields!Qty.Value)
I guess the 42 is cast to an INT inside the second IIF and the calculation works.
What's strange is that the division would even be carried out in the event of Qty = 0 from the first IIF, because the expression should just evaluate to "None" and that would be that.
Has anybody run into this problem? Is my workaround the recommended approach?
-Larry

View 14 Replies View Related

Divide By Zero Error SQL SERVER

Jul 31, 2006

Hi
Is there a way to prevent this error?
I'm getting it from query line:
10000 * (SELECT count(filial_cotacao) from negocios_cotacoes where filial_cotacao = cod_filial)) / (22 * 6 * (SELECT count(filial_cotacao) from negocios_cotacoes where filial_cotacao = cod_filial)),1) 'Atingimento Semestre'
Thanks a lot

View 2 Replies View Related

OLAP - Divide By Zero Error??

Sep 5, 2001

Some of my calculated members look like "######" in Excel and I believe it is because of divide by zero errors. I have tried right clicking on the table, selecting "Table Options" and "For error, values, show:" I have enter 0. This is not working.
Does anybody know how to get rid of this.

Thanks,
Ruaidhri.

View 1 Replies View Related

Divide By Zero Error Encountered

Oct 31, 2013

I have one table with columns patientName , Nurse,ArrivalDate, DepartDate . It has all the patiet information for all the Nurses.

I am calculating Average Number of patients per day per nurse by using below query

SELECT Nurse,
COUNT(DISTINCT patientName) AS NoOfPatients,
COUNT(DISTINCT patientName) * 1.0/COUNT(DISTINCT ArrivalDate) AS [AvgNo.ofpatientsPerDay]
FROM Table t
GROUP BY NURSE

but if ArrivalDate is Null I am getting below error saying that Divide by zero error encountered.

View 5 Replies View Related

How To Avoid Divide By Zero Error

Nov 6, 2013

I have some SQL scripts for SQL Views and getting divide by zero error. I am using

COALESCE(Field1,Field2,1)/COALESCE(Field3,Field4,1)

This function can avoid NULL but how I can avoid 0 ?

Is there any function in SQL that can take care both NULL and 0 and replace it with 1.

View 5 Replies View Related

Divide By Zero Error Encountered.

Dec 3, 2007

How can i handle this kind of error in my execution of my storedprocedure? Error:Divide by zero error encountered.

thanks!

Funnyfrog

View 3 Replies View Related

Divide By Zero Error Trapping

Jul 20, 2005

I have the following line in a select statement which comes up with adivide by zero error.CAST(CASE Splinter_StatusWHEN 'SUR' THEN 0ELSE CASE WHEN Sacrifice>=1THEN 3*m.Premium/100-(m.Sacrifice * 3*m.Premium/100)/(m.Gross+m.Sacrifice)ELSE 0ENDEND AS Float)AS Bond2,The error happens on the section (m.Gross + m.Sacrifice) as this canequal zero and throws out the part of the calc that divides by it. Itis correct in some instances that it does so. The full SQL statementhas a large number of these expressions so I need a method I can applyto any line if possible.I know that it is mathmatically correct to error where this value iszero, but what I want to do is set the output of the entire expressionto zero if there is an error.Realistically an error such as this could happen at a few points inthe expression (or one of many others), so I need to find a way ofcatching any error in the expression and setting the return value to0. I thought of using a CASE statement, but wondered if there was abetter way of looking at this as the case statement would have tocheck each variation where it could throw an error.Any ideas ?ThanksRyan

View 3 Replies View Related

Wht Does This Give Divide By 0 Error?

May 24, 2007

I am using iif which works just fine in Access to prevent divide by zero errors but this:



=iif(Fields!oh_ret.Value<>0,(Fields!oh_ret.Value-Fields!oh_cost.Value)/Fields!oh_ret.Value,"")



Fails



As Does:





=iif(Fields!oh_ret.Value=0,"",(Fields!oh_ret.Value-Fields!oh_cost.Value)/Fields!oh_ret.Value)



Why? How do I get around this?

View 4 Replies View Related

Transact SQL :: Divide By Zero Error

Oct 25, 2015

I have an equation 

( ( P.RealisedConsumption / ( NULLIF(( PO.ActualQty * P.QuantityPO ), 0) / NULLIF(1000000, 0) ) ) - 1 ) * 100 AS FibreScrapFactor

This works for the example order that I am looking at.  However if i take out the where clause (so i have all orders) i get divide by zero error.  

If I limit the / 1000000 to 10 which i then have 

( ( P.RealisedConsumption / ( NULLIF(( PO.ActualQty * P.QuantityPO ), 0) / NULLIF(10, 0) ) ) - 1 ) * 100 AS FibreScrapFactor

This works for all orders, but is incorrect I need to divide by a million.  How can I get this to work?

View 3 Replies View Related

Reporting Services :: Divide By Zero Error

Apr 15, 2015

I have SSRS 2012 and have a report with an expression shown below.  I am getting an error of

"[rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox133.Paragraphs[0].TextRuns[0]’ contains an error: Attempted to divide by zero."

Textbox 133 has an expression as shown below and I cannot figure out why I get the error because I thought the IIF would bypass divide by zero.

=IIF(Sum(Fields!PriorQty.Value) = 0.0, 1,(Sum(Fields!CurrentQty.Value) - Sum(Fields!PriorQty.Value)) / Sum(Fields!PriorQty.Value))

View 10 Replies View Related

Transact SQL :: Divide By Zero Error Encountered

Jun 18, 2015

My Code is

SELECT    Client,State,    
CASE WHEN Weight>=11 and Weight<=455 and Height>=110 and Height<=250
  THEN isnull((cast(nullif(Weight,0)/power((NullIf(Weight,0)/100),2) as int)),0) else -1 end as BaseBMIScore
FROM Test100 

And I got following error:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

View 4 Replies View Related

Getting Error When Divide By Zero For Percent Increase

May 8, 2008

Does anyone know how to get over their dreadfull divide by zero error. I keep getting the error, i have changed this code so much. I need to get the percent increase from 2007-2008 ("Total") in this table grouping. I know im getting the error in some of the fields, because sometimes the total is 0 and sometimes the 2007 is 0. What do i do?





Code Snippet

=IIF(Sum(iif(Fields!Column_Text.Value = "Total", Cdbl(Fields!Period_1.Value), 0.0))=0,"0%",
Sum(iif(Fields!Column_Text.Value = "Total", Fields!Period_1.Value,nothing))- Sum(iif(Fields!Column_Text.Value = "2007", Fields!Period_1.Value, nothing)))/ IIF(Sum(iif(Fields!Column_Text.Value = "2007", Fields!Period_1.Value, nothing))=0,1,Sum(iif(Fields!Column_Text.Value = "2007", Fields!Period_1.Value, nothing)))

View 1 Replies View Related

Workaround Divide By Zero Error Encountered Message

Jan 7, 2004

Hey all,

When i exec an sp it runs a sum, sometimes it is possible that there is a 0 value, it then returns an error Divide by zero error encountered
How can i work around this error

My sp code is

CREATE proc CP_avgloss_rings
@mID varchar(10),
@startdate datetime,
@enddate datetime
as
select ((sum(vtp)-(sum(moneyout)))/100) / ((sum(playtime))/ 3600) as avgloss
from dbo.rings
where
machineID = @mID
and convert(varchar,njdate,121)
between convert(varchar,@startdate,121)
and convert(varchar,@enddate,121)
GO


Maybe someone can help me
Cheers Wimmo

View 4 Replies View Related

Divide By Zero Error Encountered - Null Value Eliminated

Mar 19, 2015

I'm getting this error:

Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operation.

this is where i'm getting the error:

CASE WHEN Claims.ClaimTotal > 0 then (ISNULL(Claims.ClaimTotal,0) / ISNULL(TotalPrem.TotalPrem,dbo.TrnPolicy.TotalDue)) else 0 END AS LossRatio

View 3 Replies View Related

Transact SQL :: Divide By Zero Error In Empty Function

Oct 9, 2015

I have a work database where I implemented a table-valued function. One colleague of mine reported to me that this function gave a Divide by Zero error when executed with some specific values given to its arguments (there are a 15 arguments). Then I started debugging, and I introduced some exit points to the function before its end in order to detect the point where the error appeared, since I don't have access to the database server and I cannot use the debugging tools from remote, due to the network configuration of my office. I can only do attempts on the code to try to find a solution.

Since I didn't manage to get rid of this error, I decided to make a silly and desperate attempt: I put a RETURN statement immediately after the BEGIN of the function body, with the idea that the function should not raie any error if it exit immediately after its beginning, despite the fact that this results in an empty table in return.

The result of my attempt is that the Divide by Zero error is still THERE (!), even if my function looks like

ALTER FUNCTION [dbo][<myFuncName>](...parameters...) RETURNS TABLE (...table definition...) AS BEGIN 
RETURN
END 
GO

How I can check it.

View 5 Replies View Related

Transact SQL :: How To Get Percentahe Profit Without Error In Zero Divide

Nov 4, 2015

How can I get the value (pr_profit / pr_sales ) * 100,  (cur_profit / cur_sales) * 100 for %profit. When I am giving zero devide error  is coming . How can I give  if pr_profit = 0 then % profit should be zero.

SELECT  
SUM(CASE WHEN invdate >= {d '2015-01-01' } and invdate <={d '2015-11-03' } Then linetotal Else 0 End) as cur_Sales,
SUM(CASE WHEN invdate >={d '2014-01-01' } and invdate <= {d '2014-11-03' } Then linetotal - linecost Else 0 End) as pr_Profit,
SUM(CASE WHEN invdate >= {d '2015-01-01' } and invdate <={d '2015-11-03' } Then linetotal - linecost Else 0 End) as cur_Profit,
SUM(CASE WHEN invdate >= {d '2015-01-01' } and invdate <={d '2015-11-03' } Then linetotal - linecost Else 0 End) -

[code]...

View 5 Replies View Related

Divide By Zero Error Encountered. Error

Mar 17, 2008

the query is below ;

Select Model, Kota as [Çıkan Anket]
,
(Select Count(*) From dbo.Main Where RuhsatSahibiModel = tt.Model) as [Verilen Adres] , (Select Count(RuhsatSahibiSahisID) From dbo.Main Where DurumKoduID in(1,2,3,4,5,6,9,12,13,14,15,16,17,20,21,22,23,24,25,26,27,28) and RuhsatSahibiModel = tt.Model) as [Donen Adres]

,

(Select Count(*) From dbo.Main Where DurumKoduID = 2 and RuhsatSahibiModel = tt.Model) as [Donen Anket] ,((Select cast(Count(*) as decimal(2,0)) From dbo.Main Where DurumKoduID = 2 and RuhsatSahibiModel = tt.Model ) / (Select top 1 (cast(Kota as decimal(2,0))) From dbo.Model Where Model = tt.Model))*100 as [Oran]

,

(Kota - (Select Count(*) From dbo.Main Where DurumKoduID = 2 and RuhsatSahibiModel = tt.Model)) as [Kalan Anket] , ((Select Count(*) From dbo.Main Where RuhsatSahibiModel = tt.Model)- (Select Count(RuhsatSahibiSahisID) From dbo.Main Where DurumKoduID in(1,2,3,4,5,6,9,12,13,14,15,16,17,20,21,22,23,24,25,26,27,28) and RuhsatSahibiModel = tt.Model)) as [Kalan Adres]

From

(SELECT Model, SUM(Kota) AS Kota FROM dbo.Model GROUP BY Model) as tt where tt.Model in('A3')



it's returning error message : "Divide by zero error encountered."

View 3 Replies View Related

Eliminating Duplicates

Feb 11, 2005

Have a pretty simple wuestion but the answer seems to be evading me:

Here's the DDL for the tables in question:


CREATE TABLE [dbo].[Office] (
[OfficeID] [int] IDENTITY (1, 1) NOT NULL ,
[ParentOfficeID] [int] NOT NULL ,
[WebSiteID] [int] NOT NULL ,
[IsDisplayOnWeb] [bit] NOT NULL ,
[IsDisplayOnAdmin] [bit] NOT NULL ,
[OfficeStatus] [char] (1) NOT NULL ,
[DisplayORD] [smallint] NOT NULL ,
[OfficeTYPE] [varchar] (10) NOT NULL ,
[OfficeNM] [varchar] (50) NOT NULL ,
[OfficeDisplayNM] [varchar] (50) NOT NULL ,
[OfficeADDR1] [varchar] (50) NOT NULL ,
[OfficeADDR2] [varchar] (50) NOT NULL ,
[OfficeCityNM] [varchar] (50) NOT NULL ,
[OfficeStateCD] [char] (2) NOT NULL ,
[OfficePostalCD] [varchar] (15) NOT NULL ,
[OfficeIMG] [varchar] (100) NOT NULL ,
[OfficeIMGPath] [varchar] (100) NOT NULL ,
[RegionID] [int] NOT NULL ,
[OfficeTourURL] [varchar] (255) NULL ,
[GeoAreaID] [int] NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[UpdateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[UpdateByID] [varchar] (50) NOT NULL ,
[OfficeBrandedURL] [varchar] (255) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OfficeManagement] (
[OfficeID] [int] NOT NULL ,
[PersonnelID] [int] NOT NULL ,
[JobTitleID] [int] NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[SeqNBR] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OfficeMls] (
[OfficeID] [int] NOT NULL ,
[SourceID] [int] NOT NULL ,
[OfficeMlsNBR] [varchar] (20) NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[UpdateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[UpdateByID] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Personnel] (
[PersonnelID] [int] IDENTITY (1, 1) NOT NULL ,
[PersonnelDisplayName] [varchar] (100) NOT NULL ,
[FirstNM] [varchar] (50) NOT NULL ,
[PreferredFirstNM] [varchar] (50) NOT NULL ,
[MiddleNM] [varchar] (50) NOT NULL ,
[LastNM] [varchar] (50) NOT NULL ,
[PersonalTaxID] [varchar] (9) NOT NULL ,
[HireDT] [datetime] NOT NULL ,
[TermDT] [datetime] NOT NULL ,
[HomePhoneNBR] [varchar] (15) NULL ,
[HomeADDR1] [varchar] (50) NOT NULL ,
[HomeADDR2] [varchar] (50) NOT NULL ,
[HomeCityNM] [varchar] (50) NOT NULL ,
[HomeStateCD] [char] (2) NOT NULL ,
[HomePostalCD] [varchar] (15) NOT NULL ,
[PersonnelLangCSV] [varchar] (500) NOT NULL ,
[PersonnelSlogan] [varchar] (500) NOT NULL ,
[BGColor] [varchar] (50) NOT NULL ,
[IsEAgent] [bit] NOT NULL ,
[IsArchAgent] [bit] NOT NULL ,
[IsOptOut] [bit] NOT NULL ,
[IsDispOnlyPrefFirstNM] [bit] NOT NULL ,
[IsHideMyListingLink] [bit] NOT NULL ,
[IsPreviewsSpecialist] [bit] NOT NULL ,
[AudioFileNM] [varchar] (100) NULL ,
[iProviderID] [int] NOT NULL ,
[DRENumber] [varchar] (10) NOT NULL ,
[AgentBrandedURL] [varchar] (255) NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[UpdateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[UpdateByID] [varchar] (50) NOT NULL ,
[IsDisplayAwards] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PersonnelMLS] (
[PersonnelID] [int] NOT NULL ,
[SourceID] [int] NOT NULL ,
[AgentMlsNBR] [varchar] (20) NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[UpdateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[UpdateByID] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[Office] ADD
CONSTRAINT [FK_Office_OfficeProfile] FOREIGN KEY
(
[OfficeID]
) REFERENCES [dbo].[OfficeProfile] (
[OfficeID]
) NOT FOR REPLICATION
GO

alter table [dbo].[Office] nocheck constraint [FK_Office_OfficeProfile]
GO

ALTER TABLE [dbo].[OfficeManagement] ADD
CONSTRAINT [FK_OfficeManagement_LookupJobTitle] FOREIGN KEY
(
[JobTitleID]
) REFERENCES [dbo].[LookupJobTitle] (
[JobTitleID]
),
CONSTRAINT [FK_OfficeManagement_Office] FOREIGN KEY
(
[OfficeID]
) REFERENCES [dbo].[Office] (
[OfficeID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_OfficeManagement_Personnel] FOREIGN KEY
(
[PersonnelID]
) REFERENCES [dbo].[Personnel] (
[PersonnelID]
) ON DELETE CASCADE
GO

alter table [dbo].[OfficeManagement] nocheck constraint [FK_OfficeManagement_Office]
GO

ALTER TABLE [dbo].[OfficeMls] ADD
CONSTRAINT [FK_OfficeMls_Office] FOREIGN KEY
(
[OfficeID]
) REFERENCES [dbo].[Office] (
[OfficeID]
) NOT FOR REPLICATION
GO

alter table [dbo].[OfficeMls] nocheck constraint [FK_OfficeMls_Office]
GO

ALTER TABLE [dbo].[PersonnelMLS] ADD
CONSTRAINT [FK_PersonnelMLS_Personnel] FOREIGN KEY
(
[PersonnelID]
) REFERENCES [dbo].[Personnel] (
[PersonnelID]
) NOT FOR REPLICATION
GO

alter table [dbo].[PersonnelMLS] nocheck constraint [FK_PersonnelMLS_Personnel]
GO





Here's the query I'm having trouble with:

SELECT distinct Personnel.PersonnelID,
Personnel.FirstNM,
Personnel.LastNM,
Office.OfficeNM,
Office.OfficeID,
OfficeMls.SourceID AS OfficeBoard,
PersonnelMLS.SourceID AS AgentBoard
FROM Personnel INNER JOIN
OfficeManagement ON
Personnel.PersonnelID = OfficeManagement.PersonnelID
INNER JOIN
Office ON OfficeManagement.OfficeID = Office.OfficeID
INNER JOIN
OfficeMls ON Office.OfficeID = OfficeMls.OfficeID
INNER JOIN
PersonnelMLS ON Personnel.PersonnelID = PersonnelMLS.PersonnelID
where officemls.sourceid <> personnelmls.sourceid
and office.officenm not like ('%admin%')
group by PersonnelMLS.SourceID,
Personnel.PersonnelID,
Personnel.FirstNM,
Personnel.LastNM,
Office.OfficeNM,
Office.OfficeID,
OfficeMls.SourceID
order by office.officenm

What I'm trying to retrieve are those agents who have source id's that are not in the Office's domain of valid source id's. Here's a small portion of the results:

PersonnelID FirstNM LastNM OfficeNM OfficeID OfficeBoard AgentBoard
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------- ----------- -----------
18205 Margaret Peggy Quattro Aventura North 650 906 908
18205 Margaret Peggy Quattro Aventura North 650 918 908
15503 Susan Jordan Blackburn Point 889 920 909
15503 Susan Jordan Blackburn Point 889 921 909
15503 Susan Jordan Blackburn Point 889 921 920
15279 Sandra Humphrey Boca Beach North 890 917 906
15279 Sandra Humphrey Boca Beach North 890 906 917
15279 Sandra Humphrey Boca Beaches 626 917 906
15279 Sandra Humphrey Boca Beaches 626 906 917
13532 Michael Demcho Boca Downtown 735 906 917
14133 Maria Ford Boca Downtown 735 906 917
19126 Michael Silverman Boca Glades Road 736 917 906
18920 Beth Schwartz Boca Glades Road 736 906 917

If you take a look at Sandra Humphries, you'll see she's out of office 626. Office 626 is associated with source id's 907 and 916. Sandra Humphries is also associated with those two source id's , but she shows up in the results.

I know this was AWFULLY long winded, but just wanted to make sure made myself as clear as possible.

Any help would be greatly appreciated.

Thanks in advance!

View 8 Replies View Related

Eliminating Duplicate Rows

Aug 9, 2000

Hi,

I have a table with four columns. like id,lastname,
firstname,acctname. I have duplicate values for the three columns other
than id column. like

ID FirstNameLastname Acctname
1 john hopkins jh
2 john hopkins Jh
3 david webb dw
4 david webb dw
5 david webb dw
6 Dan Kennedy DK

I want to eliminate the duplicate rows. id can be any one of them.
Can any one suggest me with a query by which i can do this.
Thanks in advance
Mohan

View 2 Replies View Related

Eliminating One Database...URGENT

Jun 26, 2000

How do I eliminate others from viewing one of the 2 databases on our production server???Is there any security not to allow all users to including sa and developers not to access one of the 2 databases on our server..
The other of the 2 databases can be accessed....
Please advise

Newbie

View 1 Replies View Related

Eliminating Duplicates In Select

Apr 11, 2008

Hi All,

I need to eliminate Duplicates in my Sql Query, tried to use distinct and that doesn't seem to work, can anybody pls.help.

duplicates are in #ddtempC table, and am writing a query to get a country name from the hash table where hash table has duplicates

hash table contains (THEATER_CODE, COUNTRY_CODE, COUNTRY_NAME).
and trying to write condition on THEATER_CODE and COUNTRY_CODE to get Country_name

and THEATER_CODE AND COUNTRY_CODE HAS DUPLICATES. whenever i do a sub query i get the below error.

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

SELECT USER_FIRSTNAME, USER_LASTNAME,
user_countryCode,
USER_COUNTRY = (SELECT DISTINCT RTRIM(LTRIM(COUNTRY_NAME)) FROM #ddtempC WHERE RTRIM(LTRIM(COUNTRY_CODE)) = USER_COUNTRYCODE AND RTRIM(LTRIM(THEATER_CODE)) = USER_THEATERCODE)
FROM [user]
WHERE USER_USERNAME IS NOT NULL AND User_CreationDate BETWEEN '1/2/2007' AND '4/11/2008'
ORDER BY User_TheaterCode;

Thanks in Advance.

View 3 Replies View Related

Eliminating Duplicate Records

May 13, 2008

Hey There.

I'm in the process of doing a major data clean up and I'm just wondering how I would go about eliminating some redundant data.

The Table Layout

Contracts

CNTRID CONTRACTNUM STARTDATE CUSTOMNUM
=======================================================
0 1234567 091885 A
1 1234567 091885 A
2 1111111 111111 B
3 1234567 081205 A


Equipment

EQUIPID DEVICENAME CNTRID CUSTOMNUM
=======================================================
0 DEVICE1 0 A
1 DEVICE2 2 B
2 DEVICE3 1 A
3 DEVICE4 3 A


You will notice that each customer may have multiple devices. Each device may be tied to a contract, and each contract may have one or more devices tied to it.

In the example above, you will notice in the contracts table the contracts with the IDs 0 and 1.

Fig 1.

CNTRID CONTRACTNUM STARTDATE CUSTOMNUM
=======================================================
0 1234567 091885 A
1 1234567 091885 A


These contracts have the exact same information.

Furthermore, if you look down the table you will notice the contract with the ID 3.

Fig 2.

CNTRID CONTRACTNUM STARTDATE CUSTOMNUM
=======================================================
3 1234567 081205 A

This contract shares the same contract and customer number, but has a different start date.


Now lets take a look devices in the equipment table that refer to these records.

EQUIPID DEVICENAME CNTRID CUSTOMNUM
=======================================================
0 DEVICE1 0 A
2 DEVICE3 1 A
3 DEVICE4 3 A

You will notice that DEVICE1 and DEVICE 3 refer to the contract records that contain identical data. (As shown in 'Fig 1')

My question is as follows:

How do I eliminate the any duplicate records from the contracts table, and update the records in the equipment table with id of the left over contract.

Results Should be as follows:

Contracts

CNTRID CONTRACTNUM STARTDATE CUSTOMNUM
=======================================================
0 1234567 091885 A
2 1111111 111111 B
3 1234567 081205 A


Equipment

EQUIPID DEVICENAME CNTRID CUSTOMNUM
=======================================================
0 DEVICE1 0 A
1 DEVICE2 2 B
2 DEVICE3 0 A
3 DEVICE4 3 A


Any help you may provide would be greatly appreciated!

Thanks
--mike

View 11 Replies View Related

Eliminating A Cartesian Product

Jul 29, 2013

I have a SQL statement with two left outer joins which connects 3 tables. Vendors, Tracking & Activity. For whatever reason, even though each is a one-to-many relationship, I am able to join 2 tables (from Vendors to Tracking) without an issue. when I then join Activity, I get a Cartesian product.I suspected that 'DISTINCT'.

SELECT DISTINCT CASE
WHEN `vendor`.`companyname` IS NULL then 'No Company Assigned'
ELSE `vendor`.`companyname`
END AS companyNameSQL, `tracking`.`pkgTracking`, CASE

[code]....

View 4 Replies View Related

Eliminating Records In A Query

Sep 29, 2014

Need to eliminate certain records from my query. The below is a simple query to illustrate my problem

My Query

Select RequestNo,Event_type from Event_log where Event_type in (10,20)

Data
RequestNo Event_type
123456 10
123457 10
123457 20
123458 10
123459 10
123459 20

This above query returns all requests that meets atleast one criteria. How do i edit my query such that i get requests that meet both criteria and the result set looks like below

Data

RequestNo Event_type
123457 10
123457 20
123459 10
123459 20

View 2 Replies View Related

Eliminating Redundant Data

Jul 20, 2005

edit: this came out longer than I thought, any comments about anythinghere is greatly appreciated. thank you for readingMy system stores millions of records, each with fields like firstname,lastname, email address, city, state, zip, along with any number of userdefined fields. The application allows users to define message templateswith variables. They can then select a template, and for each variablein the template, type in a value or select a field.The system allows you to query for messages you've sent by specifyingcriteria for the variables (not the fields).This requirement has made it difficult to normalize my datamodel at allfor speed. What I have is this:[fieldindex]id int PKname nvarchartype datatype[recordindex]id int PK....[recordvalues]recordid int PKfieldid int PKvalue nvarcharwhenever messages are sent, I store which fields were mapped to whatvariables for that deployment. So the query with a variable criterialooks like this:select coalesce(vm.value, rv.value)from sentmessages sminner join variablemapping vm on vm.deploymentid=sm.deploymentidleft outer join recordvalues rv onrv.recordid=sm.recordid and rv.fieldid=vm.fieldidwhere coalesce(vm.value, rv.value) ....this model works pretty well for searching messages with variablecriteria and looking up variable values for a particular message. thebig problem I have is that the recordvalues table is HUGE, 1 millionrecords with 50 fields each = 50 million recordvalues rows. The value,two int columns plus the two indexes I have on the table make it into abeast. Importing data takes forever. Querying the records (with a fieldcriteria) also takes longer than it should.makes sense, the performance was largely IO bound.I decided to try and cut into that IO. looking at a recordvalues tablewith over 100 million rows in it, there were only about 3 million uniquevalues. so I split the recordvalues table into two tables:[recordvalues]recordid int PKfieldid int PKvalueid int[valueindex]id int PKvalue nvarchar (unique)now, valueindex holds 3 million unique values and recordvaluesreferences them by id. to my suprise this shaved only 500mb off a 4gbdatabase!importing didn't get any faster either, although it's no longer IO boundit appears the cpu as the new bottleneck outweighed the IO bottleneck.this is probably because I haven't optimized the queries for the newtables (was hoping it wouldn't be so hard w/o the IO problem).is there a better way to accomplish what I'm trying to do? (eliminatethe redundant data).. does SQL have built-in constructs to do stuff likethis? It seems like maybe I'm trying to duplicate functionality at ahigh level that may already exist at a lower level.IO is becoming a serious bottleneck.the million record 50 field csv file is only 500mb. I would've thoughtthat after eliminating all the redundant first name, city, last name,etc it would be less data and not 8x more!-GordonPosted Via Usenet.com Premium Usenet Newsgroup Services----------------------------------------------------------** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **----------------------------------------------------------http://www.usenet.com

View 5 Replies View Related

Eliminating Repeating Data Values

May 13, 2008

I am querying several tables and piping the output to an Excel spreadsheet.
Several (not all) columns contain repeating data that I'd prefer not to include on the output. I only want the first row in the set to have that data. Is there a way in the query to do this under SQL 2005?

As an example, my query results are as follows (soory if it does not show correctly):
OWNERBARN ROUTE DESCVEHDIST CASE
BARBAR TRACKING #70328VEH 32832869.941393
BARBAR TRACKING #70328VEH 32832869.941393
BARBAR TRACKING #70328VEH 32832869.941393
DAXDAX TRACKING #9398VEH 39839834.942471
DAXDAX TRACKING #9398VEH 39839834.942471
DAXDAX TRACKING #9398VEH 39839834.942471
TAXTAX TRACKING #2407 40754.391002
TAXTAX TRACKING #2407 40754.391002
TAXTAX TRACKING #2407 40754.391002

I only want the output to be:
OWNERBARN ROUTE DESCVEHDIST CASE
BARBAR TRACKING #70328VEH 32832869.941393


DAXDAX TRACKING #9398VEH 39839834.942471


TAXTAX TRACKING #2407 40754.391002



Thanks,
Walt

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved