To Divide One Long Db Table Into Many Shorter Ones???
Jun 8, 2001
Hello Folks,
I'm looking for the group's collective wisdom on the following issue, I'm sure many have been confronted with it.
I'm designing a db for a website that will utilize SQL Server 7. I have few tables that I think will grow very large row wise and that will be written to an read from frequently. I sense that I might be able to get better perfomance out of the system if I split many of these large tables up (row wise) into many smaller tables.
For example, The website that I'm working on has a "MailingAddresses" db table that contains the mailing addresses for the sites users (subscribers and other misc users - 1 record per user, anticipating 70,000+ users). Each user can update their record, and there will be frequent queries to the tables to get addresses for both internal admin use and for display on public webpages.
The website has five distinct sections that are in some sense like five distinct websites. Each user belongs to only one of these sections and they won't migrate between sections. Therefore I'm considering breaking up the one large "MailingAddresses" tables into five smaller tables, one for each section, i.e., "MAddressesSectionA", ..., "MAddressesSectionE".
These tables will have the same fields and constraints. Also of course there would be the same number of reads and writes in total with the five smaller tables as compared to that for the one big table. Also the combined size of the info in the five smaller tables would be the same as that for the one large table.
Though it's going to more of a pain to manage five tables versus one, I have a hunch it might be easier for the dbms to handle reads and writes with five smaller tables than with one large table...
...Of course this is true when queries are mainly respective to users from just one or two of the sections (as might be the case) - the big table then has the overhead of the address records for users in the other sections. BUT is there any benefit with the five smaller tables route when they are all frequently accessed??? Sure each select query has fewer records to go through, but with all five tables in play the dbms has to deal on average with the same amount of info as in the one big table.
What do you folks think, to divide or not to divide the big table(s) up row wise into smaller tables?
I guess the issue is summed up in this question: In general, can a dbms better handle in memory, and more quickly write to and query access - one BIG table with a+b+...+n records, or N smaller tables with a, b,...,n records respectively?
Thanks for the collective wisdom, - Jerry
View 2 Replies
ADVERTISEMENT
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
Jun 26, 2006
Hi, Experts,
I have a data table from a old system.
There are 10 data fields stored in one row at this table.
How can I seperate those fields into another table to be 10 rows?
Any component in Data Flow can I use?(I have tried several component...)
by the way,
the original table is a large table, contain 3,000,000 rows.
Thanks for your all assistance.
View 13 Replies
View Related
Aug 1, 2006
I have a T-SQL query that is used to pull up some data for once-a-day export, just out of curiosity more then anything, is there a way to make this shorter?SELECT DISTINCT u.userId,u.lastName,u.firstName,u.address1,u.address2,u.city,u.state,u.zip,CASE WHEN u.UserClassID_fk BETWEEN 1 AND 3 AND COALESCE(u.RetailerNumber_fk,0)= 0 THEN 'Corporate'WHEN u.UserClassID_fk BETWEEN 1 AND 3 AND COALESCE(u.RetailerNumber_fk,0)<> 0 THEN 'RETAILER'+ CONVERT(varchar, COALESCE(u.RetailerNumber_fk,0))WHEN u.UserClassID_fk BETWEEN 4 AND 8 AND COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0) = 0THEN 'Corporate'WHEN u.UserClassID_fk BETWEEN 4 AND 8 AND COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0) <> 0THEN 'PLANT'+ CONVERT(varchar, COALESCE(p.plantCode,ap.plantNumber_fk,ps.plantNum ber_fk,0))WHEN u.UserClassID_fk BETWEEN 9 AND 14 AND COALESCE(p.regionNumber_fk,rg.regionNumber,0) = 0THEN 'Corporate'WHEN u.UserClassID_fk BETWEEN 9 AND 14 AND COALESCE(p.regionNumber_fk,rg.regionNumber,0) <> 0THEN 'REGION'+ CONVERT(varchar, COALESCE(p.regionNumber_fk,rg.regionNumber,0))END CPGkeyFROM [...] JOIN [...]I'm hoping there is something in a way of....If userClass Between 1 and 3 ThenIF COALESCE(u.RetailerNumber_fk,0)= 0 Then 'Corporate'ELSE 'RETAILER'+ CONVERT(varchar, COALESCE(u.RetailerNumber_fk,0))End IF as CPGKey,Thanks in advace.
View 1 Replies
View Related
Apr 3, 2008
I need query like this that make a row of numbers (in one execute)
SELECT a from (
SELECT -1 a UNION ALL
SELECT -2 UNION ALL
SELECT -3 UNION ALL
SELECT -4 UNION ALL
SELECT -5 UNION ALL
SELECT -6 UNION ALL
SELECT -7 UNION ALL
SELECT -8 UNION ALL
SELECT -9) t;
View 4 Replies
View Related
Mar 9, 2012
I have a table to which data is being imported via bcp. When I get this data there are some fields nvarchar that has some decimal, for instance 1098.000000 is always 6 zeros..What I want to achieve is to make the above number only 1098
So what I did was this:
LEFT (AMOUNT, LEN(AMOUNT)-7) AS AMOUNT
But i get an error saying invalid lenght parameter passed to the left or substring
View 2 Replies
View Related
Jan 16, 2008
Hello,
Today I ran a query from my ASP page that returned with an error message stating my command had timed out. Fine. I searched the forums (thank god for the internet), and added cmd.CommandTimeout = 120. Great! That solved my problem.
My question is that from everything I read, the command timeout is set to 30 seconds by default. Yet my query runs in 5 seconds or so with the property set, but wouldn't run at all without it. Is it possible my command timeout was set somewhere else to less than 30 seconds? It's almost as if the server THOUGHT it would take longer than 30 seconds, and gave up, before actually running the query (which has all needed indexes to make the query fast). I just don't understand why I was getting this error for what turns out to be a 5 second query. When I shorted the query to 4 seconds or so (reducing the date range for the report), it ran fine in 4 seconds without the timeout error. So basically, going to 5 seconds or more caused this error. Where are the other places that the CommandTimeout property might be set? On the SQL Express database itself? I didn't see any ASP.NET config properties addressing it. Is there somewhere else I can look?
Thanks!
Michael
View 5 Replies
View Related
Jan 8, 2007
Exception ex.Message I am getting in debug mode calling AcquireConnection is much shorter than the logged message in run time because all the errors come to output window in debug. Is there any way to get all these errors description from exception object in debug time to be able to parse it?
Thanks.
View 5 Replies
View Related
Jul 20, 2005
HiThere is an application that runs on sql server.The application selects/updates some few tables frequentlyOnce there is even a select on this table .It blocks other userssometimes for very long.Is there anything that can be done to reduce this?The table has 18000 rows and does not seem to have an indexI thought indexing might help but 18000 rows without an index isno reason for 30 minutes of lock time.I will appreciate your help as usualVince
View 1 Replies
View Related
Jul 20, 2005
I have written a stored procedure to list out all tables in whichrows or the table itself is locked. The only information I amnot able to get is the time when the lock occurred. The way Iwant is that if I run the procedure it should show all lockson a table which are at least 5 or x seconds old. This way I canavoid momentary locks on a table which go away after few seconds.Which table and column of master database has that information?Thanks.--email id is bogus
View 3 Replies
View Related
Aug 16, 2006
Hi,
I am traing to execute this select
SELECT count(*) CM_CERT_VARIANCE_DET_APINV_RELATE
using SQL SERVER ODBC, however, I received this message:
"Identifer too long"
Is there any table length name limitation?
I think, there is, because works well on SQL environment.
I mean, should this table have less then 18 characters? Can I work around this limitation?
cheers,
Alessandro
View 6 Replies
View Related
Mar 8, 2007
I have a long query which I have set off and would like to stop, andrename one of the tables used.My query is due to my lack of understanding of the underlyingstructure of MSSQL-Server...So say and update updates TABLE_A and I stop it, whilst thistransaction is rolling back I attempt to rename TABLE_A to TABLE_A_OLDand rename a different table to become TABLE_A. I am assuming thatthe rollback actions will use the object reference of TABLE_A_OLD andcontinue to rollback the effects on the correct table and not corrupt'new' TABLE_A... or will it not allow me to rename TABLE_A until therollback is complete?Thanks for any help!Steve
View 1 Replies
View Related
Dec 3, 2007
Hi everyone,
I've got an excel file that I want to import into a database table.
The longest text in a cell is 385 characters.
I've made the fields in the table nvarchar(1024).
I created a data flow task for the import.
When I run this task, I get the following error:
[Excel Source [1]] Error: There was an error with output column "Line Text" (52) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".
[Excel Source [1]] Error: The "output column "Line Text" (52)" failed because truncation occurred, and the truncation row disposition on "output column "Line Text" (52)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
is it possible that there is a restriction on the length of the text ?
regards,
Filip
View 8 Replies
View Related
Mar 8, 2005
Dear Participants,
We are using merge replication for multi locational database but we are facing one problem in only one table which is not included in replication-
Table name is xxxxmast has only 39 row static information, but it used by every users for all task as select only information from this table like-
Select fin_year into mem_variable from xxxxmast where co_name = :global_variable
go
Code validation from here only.
Right validation from here only.
Report retrival validation from here only.
It means its usage for select from every user frequentely for so many times but we have to only fetch information from this table.
It was working prior fine but rightnow get problem for while-
Today Dated 08-March this table not accessible fro three times in eight hours-
1st time for 10 minute.
2nd time 10 minutes
3rd time 52 minutes.
Users want to login but at the login time years and other validation from this table, so users awaited for above mention time.
We had have do following by yesterday-
Drop table xxxxmast.
Create table xxxxmast.
Insert required data.
This is realy trouble for our application.
Any help realy great for us.
Thanks
R.Mall
View 3 Replies
View Related
Oct 1, 2007
We have a web-based third-party application that has both background processes and user activity requests running in the same database (SQL Server 2005 SP2). The problem is that a background process will start a long-running transaction and hold an exclusive lock on a few rows in a given table (a small table, <100 rows). The web clients need to scan this same table, but when their "select *" statements get to those locked row(s), the web client queries stall waiting for that exclusive lock to be released. This effectively brings the entire web front end to a halt because all clients must hit this table for each user action. I realize that this is the classic lock condition that multiversioning databases like Oracle, PostgreSQL, SQL Server Compact Edition, and other databases do not suffer because they don't use shared read locks like SQL Server. But since we're on SQL Server for this app, what is the way to get around this problem? Modifying the clients to use WITH (NOLOCK) is not an option... there will be major consistency issues unless the clients run in Read Committed or higher. Any ideas? We could tweak this app if needed. Does SQL Server 2008 introduce multiversioning or at least some mechanism to get around this problem? I did not see it mentioned on the Microsoft site, but maybe I missed it. Thanks in advance.
Austin
View 6 Replies
View Related
May 26, 2008
Hi,
The scenario is the data comes from various sources and its staged into staging database. From this staging database it goes into data warehouse database. Everyday this staging database is truncated and repopulated from various sources.
I've a dimension table called DimCustomers which consists of around 300,000 rows and has lots of different types of SCD columns. It takes around 4-5 hours to load data from staging to this dimension table. Currently I'm using a For Loop container which uses a store proc to extract 15000 rows each time and populate my dimension tables. First couple of loops it goes off quickly but as and when the number reaches half of the count it slows down and hence it takes around 4-5 hours to load data.
What would be the best approach to populate this kind of dimension table.
Thanks
View 7 Replies
View Related
Jan 31, 2005
i need to full-text index a table so that i can easily search the text fields of that table.. the table has about 21,000 rows, and i was wondering how long it might take to full-text index it?
thanks
View 1 Replies
View Related
Sep 25, 2015
We have a proc that adds some fields to a few tables of ours and normally there are no issues. For one of our client databases this process is taking anywhere from 5-10 minutes to add the fields. This causes an issue where the app will timeout waiting. After plugging around and looking at the proc and trying different items i found it to only be for this one database and ONLY when there is data in the table. If i truncate the table and run the same procedure everything is fine. Tables all have same index on 4 columns and the columns being added are not indexed because of the stupid hoops we have to jump thru to pre-pivot data for our reporting package.
View 4 Replies
View Related
Jul 14, 2015
I have several reports that are looking for a code within a certain set of codes or ranges. The specific list of codes to be including is determined by the end user. Currently my "IN" statement can be a hundred lines, listing several ranges, lists of specific codes, etc. I am constantly getting asked what codes does it include, is this code included, etc. Sometimes they'll give me a printed 10 page list of codes and want me to compare to what I have included in the report. Not ideal in the slightest.
What I'd like to do is have a table or a file of some kind somewhere where the end user can view the codes contained, add new ones, and delete ones they no longer want. Then I'd like to be able to just reference that file in my IN statement. Leaving the responsibility of listing the correct codes on them.
View 9 Replies
View Related
Jul 27, 2001
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 Related
May 1, 2008
I 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
View 8 Replies
View Related
Jun 5, 2008
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?
View 5 Replies
View Related
Mar 8, 2006
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 Related
Feb 12, 2007
Hi,
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.
View 3 Replies
View Related
Jul 4, 2007
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?
View 4 Replies
View Related
Mar 31, 2006
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
View 3 Replies
View Related
Oct 19, 2004
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.
View 2 Replies
View Related
Nov 23, 2006
let say
1/2 = 0.5
anyone know how to set it become 0.5? by default the value will become 0,
View 3 Replies
View Related
Dec 5, 2007
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!
View 10 Replies
View Related
May 7, 2008
I would like to divide the value of column_2 by the value of column_3 and have the result returned in a new_column.....
"SELECT column_1, column_2, column_3, (dbo.column_2 "DIVIDED BY" dbo.column_3) AS new_column
FROM dbo"
Do I have to create a 'temp table' to do this?
If you cannot tell, I am VERY new to this and would appreciate any help!!!
Thank you in advance! Nathan
View 4 Replies
View Related
Jun 4, 2008
I'm getting such error. I want the result as blank once it's divided by zero. Is that possible?
Here's my formula:
SELECT (SUM(CASE WHEN Advoc_InfoCorec = 'No' THEN 1 ELSE 0 END) * 1.0 - SUM(CASE WHEN Info_Ofer = 'Y' THEN 1 ELSE 0 END)
* 1.0 + SUM(CASE WHEN Info_ServPolcy = 'Y' THEN 1 ELSE 0 END) + SUM(CASE WHEN Info_TodAdvan = 'Y' THEN 1 ELSE 0 END)
* 1.0 + SUM(CASE WHEN Info_UseOk = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Info_MadChang = 'Y' THEN 1 ELSE 0 END) * 1.0)
/ (SUM(CASE WHEN Info_Ofer = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Info_ServPolcy = 'Y' THEN 1 ELSE 0 END)
* 1.0 + SUM(CASE WHEN Info_TodAdvan = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Info_UseOk = 'Y' THEN 1 ELSE 0 END)
* 1.0 + SUM(CASE WHEN Info_MadChang = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Advoc_InfoCorec = 'No' THEN 1 ELSE 0 END)
* 1.0 - SUM(CASE WHEN Info_Ofer = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Info_ServPolcy = 'Y' THEN 1 ELSE 0 END)
+ SUM(CASE WHEN Info_TodAdvan = 'Y' THEN 1 ELSE 0 END) * 1.0 + SUM(CASE WHEN Info_UseOk = 'Y' THEN 1 ELSE 0 END)
* 1.0 + SUM(CASE WHEN Info_MadChang = 'Y' THEN 1 ELSE 0 END)) * 1.0 AS Info_Other
FROM dbo.TPhones_Oct
View 16 Replies
View Related
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
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
