I am performing a series of calculations where accuracy is very important, so have a quick question about single vs double precision variables in SQL 2008.
I'm assuming that there is an easy way to cast a variable that is currently stored as a FLOAT as a DOUBLE prior to these calculations for reduced rounding errors, but I can't seem to find it.
I've tried CAST and CONVERT, but get errors when I try to convert to DOUBLE.
For example...
SELECT CAST(1.0/7.0 AS FLOAT) SELECT CONVERT(FLOAT, 1.0/7.0)
both give the same 6 decimal place approximation, and the 6 decimals make me think this is single precision.
But I get errors if I try to change the word FLOAT to DOUBLE in either one of those commands...
Every night we connect to a remote server using Linked Server and copy details from that database to a loading table, then load it into the 'real' table in our own environment. The remove database we load it from has indexes/primary keys that match the 'real', however the 'loading' table itself does not have any indexes or primary keys, both are SQL Server 2005 machines.
In the loading table we first of all truncate it then do a select insert statement from the remote server, then we then truncate the 'real' table and load iit from the 'loading' table.
The issue is when we attempted to load it into our 'real' table from our loading table there was a duplicate row, and our process failed with a Primary Key violation.
I checked the source with does have the same primary key's in, it did not contain a duplicate row and I checked the loading table and that did contain a duplicate row.
My question this is in what circumstances this could happen ?
Hi all, i've given a table structure with data and the expected result . I want to establish it in SQL server (7.0) If i establish the inner join i get 4 rows (2*2) Please let me know how to get the result Thanx in adv Tarriq
This query is part of a larger query that updates a table that holds statistics for reporting. It yields actual Unit per Minute by plant by month. Some of the plants don't produce anything in certain months, so I'm ending up with a Divide by Zero error. I think I just need to stick another CASE statement in for each month, but that seems like it could get pretty ugly.
Any suggestions on how to improve this?
SELECT FL.REPORT_PLANT, [JAN]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 1 THEN PC.HOURS*60 ELSE 0 END), [FEB]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 2 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 2 THEN PC.HOURS*60 ELSE 0 END), [MAR]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 3 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 3 THEN PC.HOURS*60 ELSE 0 END), [APR]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 4 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 4 THEN PC.HOURS*60 ELSE 0 END), [MAY]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 5 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 5 THEN PC.HOURS*60 ELSE 0 END), [JUN]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 6 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 6 THEN PC.HOURS*60 ELSE 0 END), [JUL]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 7 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 7 THEN PC.HOURS*60 ELSE 0 END), [AUG]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 8 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 8 THEN PC.HOURS*60 ELSE 0 END), [SEP]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 9 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 9 THEN PC.HOURS*60 ELSE 0 END), [OCT]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 10 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 10 THEN PC.HOURS*60 ELSE 0 END), [NOV]=SUM(CASE WHEN MONTH(PC.MNTHYR) = 11 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 11 THEN PC.HOURS*60 ELSE 0 END), [DEC]= SUM(CASE WHEN MONTH(PC.MNTHYR) = 12 THEN PC.TONS * 2000 / PM.EA_WT ELSE 0 END)/ SUM(CASE WHEN MONTH(PC.MNTHYR) = 12 THEN PC.HOURS*60 ELSE 0 END) FROM PRODUCTION_CMPLT PC INNER JOIN FACILITY_LINES FL ON PC.MANUF_SITE = FL.MANUF_SITE AND PC.PROD_LINE = FL.PROD_LINE INNER JOIN PROD_MASTER PM ON PC.PRODUCT=PM.PRODUCT WHERE YEAR(PC.MNTHYR) = YEAR(GETDATE()) AND PM.UOM<>'LB' GROUP BY FL.REPORT_PLANT
This small script seems to eliminate the dupes, but I can't seem to figure out to properly replce the table the with output of the script with all the dupes gone.
select distinct * from dbo.SecurityEventsTest where recordnumber IN (select recordnumber from dbo.SecurityEvents) order by recordnumber
In my query where clause I am using between to get data. Because of time in the data I need to eliminate that and compare, how can I eliminate. my where clause is as below. due to that my query performance is falling down. please help.
CONVERT(DATETIME, CONVERT(CHAR(20), OpportunityDate,110)) BETWEEN CONVERT(CHAR(20),@FDate,110) AND CONVERT(CHAR(20),@TDate,110)
Hi All, I have a SP, which i run inside a for loop. I am running the SP for all the products in a listbox. So for each product i am having the feature extracted through the SP But some features are the same for 2, 3 products. So in the datatable, i am getting the featrues repeated. IS there any way to eliminate the duplicates from datatable, from server side? Hope i am not confusing. Eg: product1 --- test1, test2, test3 product2 --- test2, test4 so the datatable has -- test1, test2, test3, test2, test4 -- i have to eliminate one test2 from this. Any ideas??? Thanks
I'm trying to run a set of DBCC commands to empty and then delete a secondary log file; however, no matter how large I make the primary log file it won't empty the secondary file. Any suggestions?
DBCC SHRINKDATABASE (VE, 10) GO alter database VE modify file (name= VE_log,size = 1200) GO dbcc shrinkfile (VE_log2,EMPTYFILE) GO Cannot shrink log file 3 (VE_Log2) because all logical log files are in use. DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages ------ ------ ----------- ----------- ----------- -------------- 17 3 86848 128 86848 128
Hey, I have some field values entries in my database.. that are spaces like ' '. i wanna eliminate them. When i use IS NOT NULL in query it only eliminates the rows with NULL values so how could i modify the query to eliminate the rows with spaces in the field value..
hi, i have null values in my table , i want to eliminate the null values. ie this is my query select p_type from process_general output: 1.BSB HEATER PACKAGE 2.
so in my output one data and one null field is there. so i want to show output with out that null field, becos i am filling this datas in my combobox.so i need with out null field.please give me query for this,pleaseeeeeeeeeeeee
I've been making great progress but I've hit another road block which a newbie intern like myself can't surpass. What's worse is the fact that no one is in the office today! Maybe someone can point me in the right direction with this SQL:
FROMtblUserDepartment ud INNER JOIN tblRequest rON ud.departmentID = r.departmentID INNER JOIN tblDepartment dON r.departmentID = d.departmentID INNER JOIN tblStatus sON r.reqStatus = s.statusID INNER JOIN tblUser uON r.requserID = u.userID LEFT JOIN tblRequestAssignee ra ON r.requestID = ra.requestID WHEREud.userID= @userID
This works great except for one thing. In tblRequestAssignee, you have 1 primary assignee and can have several other assignees (that are not primary). This is denoted by a bit field "isPrimaryAssignee" in tblRequestAssignee. When I run the query, I see every request I want to but it duplicates requests with more than one assignee. What I'm trying to do is make only the primaryAssignee display if there is one. If there's not, then null is displayed (which is already happening).
Like I said, the query is mostly working right except for this duplicate record that displays when there's 2 assignees. Any help would once again be greatly appreciated.
Hello, I need to eliminate the duplicated rows in sql server 2000, but the duplicate is only for some fields of the row. However, I need all the fields of the row. For example, I have the next structure: Id_type, number_type, date, diagnosis, sex, age, city
After many analysis I get many rows where the tree first field are repeated, so I need to leave only one but with the all another fields. This is because I need only the first time when the diagnosis appear.
I have a table with 68 columns. If all the columns hold the same value except for one which is a datetime column I want to delete all but one of the duplicate rows. Preferably the latest one but that is not important. Can someone show me how to accomplish this?
I have tabelA, Which has 10 columns, I need to select 10 column values only no field names. Is there any way I can select only table values not field names. I don't want to see field name in my query result set. Please let me know. I appreciate your help.
I am importing an Access .mdb file into MS SQL server, and empty fields where the default value is "", change into NULL. This is a problem when I re-export a result set and have to apply a procedure to clean these values. Is there a way to eliminate this? . . . . and what have I missed?
We have rolap based cube .. when we run the report on that it is take more time than expected.I have run the profiler and identified .. same query is duplicated and running multiple times. I have run the query .. it took 10 seconds..how to eliminate duplicate queries .
Working in Sql Server 2005,got 3 different table.Need to fetch the list of persons.A person can belong to different categories.When i am using inner join on tables i am getting the duplicate rows because a perosn can belong to different categories.I want that there should be only onoe row for the person and the different categories he belongs to can come up in single field as comma sepatated string Now the results are like this: firstname lastname adress category abc xyz aaa a abc xyz aaa b abc xyz aaa c I want like below: abc xyz aaa a,b,c I am thinking of using cursors in the stored procedure.Can you provide me the solution of this including the stored procedure..
HI I have three different columns as email1,email2 , email3.I am concatinating these columns into one i.e EMail like select ISNULL(dbo.tblperson.Email1, N'') + ';' + ISNULL(dbo.tblperson.Email2, N'') + ';' + ISNULL(dbo.tblperson.Email3, N'') AS Email from tablename. One eg of the output of the above query when email2,email3 are having null values in the table is : jacky_foo@mfa.gov.sg;; means it is inserting semicoluns whenever there is a null value in the particular column. I want to remove this extra semicolumn whenever there is null value in the column. Please let me know how can i do this
I have a table with 3 fields. when I type select * from test -- I am getting the results as below.
NAME AGE DEPT
AAA 23 AOD BBB 27 NULL CCC NULL NULL DDD 23 POD
DEPT,AGE are displayed with "NULL" WHEN THERE IS NO value for that field . How can I eliminate this. I need space instead of NULL. When I export to text file there also contains NULL. Let me know how can I eliminate this.
SELECT a.Line_Number as Line_Number, Cast(a.election_effective_date as DATE) as election_effective_date, Cast(a.Plan_Year_Effective as DATE) as Plan_year_Effective FROM (SELECT aa.* FROM TEMP_Validation aa WHERE IsDate(aa.Plan_Year_Effective) = 1 AND IsDate(aa.election_effective_Date) = 1) a Where Cast(a.election_effective_date as Date) Not Between Cast(a.plan_year_effective as Date) and DATEADD(DD,-1,DATEADD(YY,1,Cast(a.plan_year_effective as Date)))
There is a malformed date in the Temp data, at line 4932. This is why I created a subquery that checks if the plan year and election effective dates are dates using the "IsDate" function. However, when I execute this I get the error "Conversion failed when converting date and/or time from character string". If I eliminate line 4932 the error no longer occurs. Somehow line 4932 is not being eliminated in the subquery, except if I run JUST the subquery it will not include line 4932. What am I doing wrong on these queries and how can I correctly get the subquery to eliminate the malformed date?
All my users login to my project which is asp.net 1.1(vs2003) based project, now from inside the project, if they try to access any report from (which is on framework 2.0), they have to go through a autentication screen which is related to sql server reporting services.
can you please help, how to override this login screen.
In my employee table has the following fields empid, empFname, empLname, email, city Say it has data like follows: 1, Lucy, Sam, l@some.com, city1 2. Sam, Wite, l@some.com, city2 3. Laura, Mac, l@some.com, city2 4. Stacy, Soo, s@no.com , city1 So in my case I want to show all the column but I want to eliminate multiple email addresses. I tried Distinct but its not workin because here every column is not distinct. So what should I use? In my case I only want to show empID 1, 3, 4. I want to show all the columns
I've been trying to make the following query more performant by breaking it up into smaller pieces.
SELECT MT.A3+MT.A4 AS A34,MT.A3 -- ,M.* FROM Master_TAB M JOIN (SELECT M.A1,t3.A3,t3.A4,M.A6,M.A2,ROW_NUMBER() OVER (PARTITION BY A1,A6,A3,A4 ORDER BY A5 DESC) AS rownum
[Code] ....
I know that the Spill is caused by the Sort but I can't remove the sort (sort can't be done in front end). My master table had 1.7 million rows and almost 200 columns (bad design? I know but can't be changed as there's too much that would be affected) every row is little over 1KB
Here's my attempt...
-- MASTER_TAB has 1.7 million rows and 50 columns CREATE TABLE [dbo].[tmp_ABC]( [A1] [varchar](13) NOT NULL, [A2] [varchar](5) NOT NULL, [A3] [varchar](4) NOT NULL, [A4] [varchar](4) NOT NULL, [A5] [int] NULL ) ON [PRIMARY]
[Code] ...
This is the Query that is causing the Spill (in reality I'm supposed to bring back all 200 columns fro the master table but for debug purposes I limited the columns)
Select c.A3+c.A4 as A34, c.A3, c.A1 -- M.* from tmp_DEF c join MASTER_TAB M on M.A1 = c.A1 and M.A2 = c.A2 order by c.A3, C.A4
if I just run the following I get no spill:
Select c.A3+c.A4 as A34, c.A3, c.A1 from tmp_DEF c order by c.A3, C.A4
as soon as I add the Master table as a Join I get the Spill...
I read many articles, tried many suggested things (creating indexes... clustered, non-clustered) without success. Maybe I'm totally in Left Field and should enhance the performance going another route?
I have a lot of different data flows that need "Derived Column". There are maybe only 5 different such "Derived Column" but they appear many times. Is there a way to eliminate all that double work? It should be something that does not take me more time to do than just duplicating all the "Derived Columns".
I built a package to transform data from flatfile into temp table. Then execute a stored procedure to tranform from the temp table into the real table. Since the real table have primary keys so it goes failed when there're duplicate rows from temp table. I let the temp table has no primary key. If I had to check the temp table for duplicate rows it would be using cursor through the data and the package will get slow. Is there task in SSIS to identify the duplicate data and eliminate it without using cursor ? Thanks in advance,
I am trying to write a function to compare the characters between 2 strings and eliminate the similarities to be able to return at the end the number of differences between them.
Having in mind i need the bigger number of differences to be returned also if a character is repeated in one of the 2 words it will be eliminated once because it exist only one time in other string.
I will give an example below to be more clear
--Start declare @string1 as varchar(50)='imos' declare @string2 as varchar(50)='nasos'; WITH n (n) AS ( SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)
[Code] ....
The differences in first string from second one are 2 (i,m) while the differences in second string from first one are 3(nas). So the function should return 3 in previous example.