The following SQL statement returns the correct totals except that
the total value is shifted on decimal place to the right. i.e. a real
total of 955.68 is displayed as 9,556.80. The total_ar field is a money type.
Any help would be appreciated.
Mark
/* AR report Total Greater than 365 days sorted by Dept */
select
a.dept as `Department `,
` A/R 365+ `=sum(case when datediff(day,c.bill_date, getdate()) > 365 then b.total_ar else 0 end)
from
hbm_persnl a, blt_billm b, blt_bill c,hbl_dept d,hbm_matter e
where
e.matter_uno = b.matter_uno and
a.empl_uno = e.bill_empl_uno and
b.bill_tran_uno = c.tran_uno and
b.ar_status = `O` and
e.status_code=`OPEN`
I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?
I wanted to convert a dataset from vb.net (2.0) to an .XLS file, by MS Jet. My national standard is using decimal commas, not decimal points for numbers signing the beginning of decimal places. But the MS Jet Engine uses decimal point,in default. Therefore, in the Excel file only string formatted cells can welcome this data, not number formatted. How can I solve or get around this problem? (with jet if it possible) iviczl
I'd like to convert a Decimal value into a string so that the entireoriginal value and length remains intact but there is no decimal point.For example, the decimal value 6.250 is selected as 06250.Can this be done?
I am designing some reports for a German branch of my company and need to replace decimal point with a comma and the thousand comma seperator with a decimal point.
e.g. ‚¬1,500,123.00 to ‚¬1.500.123,00
Is there a property that I can change in the report designer to allow this to happen or is this something I need to convert in a Stored Proc.
I have got txt file and I am trying to import this file into the database. For that action I use SQL Server Import and Export Wizard. I use Locale Czech and CodePage 1250. In the text file is the column in that format: 18152.65 - it is number with decimal point. When I use BCP utility for importing data, I use datatype decimal(10,2) and everything is OK. But when I try to use Import and Export Wizard, I choose for that column datatype numeric (DT_NUMERIC - precision 10, scale 2), the Import doesn't start and occurs the error:
- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "PRED_CEL " returned status value 2 and status text "The value could not be converted because of a potential loss of data.". (SQL Server Import and Export Wizard)
PRINT cast(111 as decimal(38,35)) / 23 --> 4.82608695652173913043478260869565217
then here
DECLARE @Zähler decimal(38,35) , @Nenner decimal(38,35) SET @Zähler = 111.0 SET @Nenner = 23.0 PRINT cast(@Zähler as decimal(38,35)) / @Nenner --> 4.826086
Of course, in the upper part 23 is implicitly an integer, in the lower example it is declared as decimal. But what if I need to devide by 23.5? Why is dividing by an decimal reducing the results decimal positions?
I am importing an excel spreadsheet into a MS SQL database table. When the spreadsheet is finished importing, I am noticing that some values that were brought in resemble something like this 1.41666666666667. Other values may be shorter or only have 1 digit. The problem is another web application that pulls this data for use in online forms only allows up to 2 digits. How can I round all of the numbers like the above to 2 decimals and replace the existing values?
I know there is the rounding function that could be used like so:
SELECT ROUND ([Hrs Total 2],2) FROM AnnualClassifiedPAFs
How do I then take that rounded value and insert it back into the records?
Hi.. I have a column in the data base with the type Float, I want to limit the number of digits after decimal point to 2 when I display the value in ASP.NET but I don't know how!? the number that appear after calculation llike "93.333333" I use decimal(2,2) as data type but an error accour and this is the message "- Unable to modify table. Arithmetic overflow error converting float to data type numeric.The statement has been terminated." Can you help me.. thanks
I have created a simple package that uses a sql command to pull data from an oracle database and inserts the data into a sql 2005 table. Some of the data fields that i am pulling from contain two digits after the decimal point, however this data is lost when it gets into sql. I have even tried putting the data into a flat file, and still the data is lost.
In the package I have a ole db source connection which is the oracle database and when i do the preview i see all the data I need. I am very confused and tried a number of things to get the data into sql, but none work. Any ideas would be very helpful.
First off, I appreciate the time that those of you reading and responding to this request are offering. My quesiton is a theoretical and hopefully simple one, and yet I have been unable to find an answer to it on other searches or sources.
Here's the situation. I am working with SQL Server 2005 on a Windows Server 2003 machine. I have a series of databases, all of which are in Full recovery mode, using a backup device for the full database backups and a separate device for the log backups. The full backups are run every four days during non-business hours. The log backups are run every half hour.
Last week, one of my coworkers found that some rarely-used data was unavailable, and wanted to restore a database to a point in time where the data was available. He told me that point in time was some time back in November.
To accomplish this, I restored the database (in a separate database, as to not overwrite my production database) using the Point in Time Recovery option. I selected November from the "To a point in time" window (I should note that this window is always grey, never white like most active windows, it seems), and the full database backup and the subsequent logs all became available in the "Select the backup sets to restore" window.
I then tried a bevy of different options from the "Options" screen. However, every restore succeeds (ie: it doesn't error out), but seems to be bringing the database back to a current point in time. It's never actually going back to the point in time I specify.
My questions are as follows:
a) Is it possible to do a point in time recovery to a point in time BEFORE the last full database backup?
b) If so, what options would you recommend I use? (ie: "Overwrite the existing database", restore with recovery, etc etc).
I again appreciate any and all advice I receive, and I look forward to hearing from anyone and everyone on this topic. Thank you.
Is there a problem with stability when one uses too many text (memo) fields?I'm having a problem with data from one record occasionally ending up inanother record, though apparently not through any user interaction.I'm using SQL 7 and accessing the tables through the ODBC driver. There aretwo tables with a one-to-one relationship -- TableA and TableB. TableB isthe one that's having this occasional problem. TableB has about 30 textfields out of a total of about 50 fields in the table. The problem isusually with one or two text fields containing data from a different record,one that was created close to when the problem record was created.Example: TableB has fields 1-9, say. In one record, field 1 has A, field 2as B, field 3 has C, and so on.In another record (created shortly after the other one), field 1 has AA,field 2 has BB, field 3 has CC, and so on.The user works with the records, everything's fine. Then one day the usernotices that in the second record, field 1 has AA, field 2 has BB, but field3 has C instead of CC. In other words, all data's fine, except for one,maybe two fields, that have data from a previously-created record.At first glance this seems to be a user-interaction thing, that somehow theuser inadvertantly placed data from the older record into the newer one,either through a shortcut, or by having that data on the clipboard, orwhatever. But a recent incident opposes that theory.I have two forms in the front end for editing records (the forms are boundto the ODBC table links). Form1 is bound only to TableA (the one thatdoesn't have the problem); Form2 is bound to a query that is TableA joinedwith TableB.In the recent incident where data shifted, both the record that was affectedand the record from which the data came were both only edited with Form1. Inother words, TableB never came into play; yet its data was somehow affected.When a record is created, the user completes a few fields in a form, andthen a stored procedure creates a record in TableA and then a sister recordin TableB (using the TableA record's autonumber primary key as its primarykey). A couple of user entered values are entered into the TableB record.But if the user is using Form1, they never see the TableB record.In this case, the TableB record's two fields got changed to fields from anearlier record (one which was created a little earlier the same day), eventhough both records were only edited in Form1 (according to the historylog), which doesn't touch TableB.Thus, I'm wondering if there's a possibility that either the SQL database orthe ODBC driver somehow shifted the data from one record into another. Thatseems far-fetched. But, at this point, since a table that the user didn'ttouch somehow had its data changed to data from a different record, I'mtrying to explore all possibilities.Thanks for any insight!Neil
I'm a quantitative securities analyst working with Compustat data(company fiscal reports and pricing feeds).My coworker came across a problem that we fixed, but I'd like tounderstand 'why' it was happening and just don't get it yet.Here's the starting query (reduced to simple prefixes):----INITIAL-----declare @mthDate datetimeset @mthDate = (select max(datadate) from t)declare @wkDate datetimeset @wkDate = (select max(datadate) from z)Select...fromzleft join a on a.idA = z.idA and a.idB = z.idBand a.datadate = z.datadateleft join b on b.idA = z.idA and b.idB = z.idBand b.datadate = @mthDateleft join c on c.idA = z.idA and c.idB = z.idBand c.datadate = @mthDateleft join d on d.idA = z.idA and d.idB = z.idBand d.datadate = z.datadateleft join e on e.idA = z.idAand e.datadate = @mthDateleft join f on f.idA = e.idA and f.datadate=e.date2left join g on g.idA = e.idA and g.datadate=e.date2left join h on h.idA = z.idAleft join k on k.ticker = z.tickerleft join m on m.idA = z.idA and m.idB=z.idBwherez.datadate = @wkDate<..some other expression filters...>and k.ticker is null----END INITIAL-----------As you can see 'z' is the main table that things are linked to viaouter joins (our security master). Table 'k' has a list of securitiesthat we wish not to have results for.There are 77 entries in table k and 4933 in table z for that giventime. We'd expect 4856 to be in this, but no. it's 4400, and then thenext time you run it (no changes whatsover) it's 2312, and so on.Every time you execute you get a different record count.My thought/and fix was to move the (k.ticker) predicate out of thewhere clause and get a differenced set from z using NOT EXISTS:-----AMENDED---------------from(z where not exists(select * from k where k.ticker=y.ticker)) yleft join a on a.idA = y.idA and a.idB = y.idBand a.datadate = y.datadateleft join b on b.idA = y.idA and b.idB = y.idBand b.datadate = @mthDateleft join c on c.idA = y.idA and c.idB = y.idBand c.datadate = @mthDateleft join d on d.idA = y.idA and d.idB = y.idBand d.datadate = y.datadateleft join e on e.idA = y.idAand e.datadate = @mthDateleft join f on f.idA = e.idA and f.datadate=e.date2left join g on g.idA = e.idA and g.datadate=e.date2left join h on h.idA = y.idAleft join k on k.ticker = y.tickerleft join m on m.idA = y.idA and m.idB=y.idBwherey.datadate = @wkDate<..some other expression filters...>------------------------And this works. It's stable now.I'm hoping someone here can help me up the wisdom curve by explainingto me 'why' the recordset kept changing before.My guess is that the cost-based optimizer was resorting the outer joinsand handing back different sets as a result, but i want to understand,and thought i'd come to this group for help.I appreciate your time and look forward to replies.Greg McIntire
I've been working hard to drag my family and the family business into the modern life, took me long enough to get then off a 'coal-powered' Siemens Nixdorf miniframe. Anyway, they went for a package system, on a PC ntwork. Now I've been gradually migrating them again, teasing them (as sometimes you do) with the benefits that could be supplied by my software, written on an SQL 2k back-end.
So anyway, we now have a system that ties into FoxPro as its original system (the bought package), plus SQL 2K..... I'm now upgrading to SQL 2005, and Express for starters....
Is it going to be possible to easily to run queries against both SQL2k and FoxPro, or should I redo my current fix from 2k to 2005 and work from there... at the end of the day, it will all hopefully become a single system, as my new system takes over more and more of the other packages functionality. And on the flip side, if i remake my current SQL2k hook-in, am i going to hit problems when i remake/convert it to SQL Express (and yes, i know Express has limitations, but i need to persuade them to move before they'll pay up the cost of the full version)
I have implemented a application on SQL Server 2005, this application runs huge queries and some stored procedures. Now that client is shifting on SQL Server 64bit edition, what should i need to worry about. What all changes am i expected to handle.
I would also like to know if there are any other issues to be considered.
What I'm after is to create a new [dtP] column where the [dtP] is the [P] value at least 5 seconds later. I would assume I should use the dateadd() function but I've tried [Timestamp] = dateadd(ss,5,[Timestamp]) in my WHERE clause and to no avail. Any suggestions on the best way to proceed?
Kimberly Tripp describes a recipe for switching partitions in and out, thru the use of staging tables, when it comes time to "slide the window" on a partitioned table. She says that the clustered index (on staging) must be the same as that chosen for the partitioned table itself but she doesnt discuss whether or not all of the non clustered indexes need to be the same too once the ALTER TABLE Orders SWITCH PARTITION 1 TO OrdersOctober2002 and ALTER TABLE OrdersOctober2004 SWITCH TO Orders PARTITION 24 run. For the data being switched out, I wouldnt want to do anything extra. For the data being switched in, I'd like to understand if she is implying that all other indexes would be built automatically as a result of the 2nd ALTER statement?
Kimberly's article is at http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm#_Toc79339965
I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?
Example: AutomobileTypeId (PK, decimal(10,0), not null)
I encounter a bug while exporting to excel a matrix. one cell is shifted to right and so i have wrong numbers and empty cell in the middle of the matrix.
instead of :
ABS Recent College Graduate (C)
Male (M) Total Female (F) Male (M) Total
Headcount Headcount Row % Headcount Col % Headcount Headcount Row % Headcount Col % Headcount Headcount Row % Headcount Col % Headcount Headcount Row % Headcount Col % Headcount Headcount Row % Headcount Col %
232 34 54 56 56 5 24 56 56 56 34 23 43 54 56
I get:
ABS Recent College Graduate (C)
Male (M) Total Female (F) Male (M) Total
Headcount Headcount Row % Headcount Col % Headcount Headcount Row % Headcount Col %
Headcount Headcount Row % Headcount Col % Headcount Headcount Row % Headcount Col % Headcount Headcount Row %
232 34 54 56 56 5 24 56 56 56 34 23 43 54 56
does anyone familiar with a solution to this issue? Thanks. Yuval.
I would like to cast (convert) data type decimal(24,4) to decimal(21,4). I could not do this using standard casting function CAST(@variable as decimal(21,4)) or CONVERT(decimal(21,4),@variable) because of the following error: "Arithmetic overflow error converting numeric to data type numeric." Is that because of possible loss of the value?
I'm running SQL Server 2005 on a Server 2003 machine serving both our home network as well as a remote site through a point-to-point T1. While file transfer speeds are up to par, the remote site's interaction with SQL Server (Point of sale system) is very slow. After testing I am certain that it has nothing to do with the actual physical machine in place neither is it an issue with the program itself since speeds are as they are supposed to be over the home network lan. It seems that there might be a packet size issue or something of the sort. Has anyone dealt with this before or have any thoughts?
I am having a file in which amount fields are given in a Packed Decimal format. Can anyone suggest me how I can read this data element from the file and convert it into SQL decimal datatype.
File is a fixed length. All the amount fields are given in Packed Decimal Format and rest of the fields are given in text format. How can i identify and convert only those packed decimals using SQL/.Net.
Example : a row in a file that has some packed decimals 158203508540188236252EUR20BZK0030 Ĺ“& 20060715 0001010100010101
Anybody noticed that SQL Server rounds up if the value is half waybetween two rounded values, but C#'s Decimal.Round(Decimal,Int32)rounds to nearest even number?[color=blue]>From MSDN: "When d is exactly halfway between two rounded values, the[/color]result is the rounded value that has an even digit in the far rightdecimal position. For example, when rounded to two decimals, the value2.345 becomes 2.34 and the value 2.355 becomes 2.36. This process isknown as rounding toward even, or rounding to nearest."I perform the same calculation sometimes on the web server in C# andsometimes at the database in T-SQL, but want to get the same resultfrom both calculations. Could anybody offer any strategies for dealingwith this?Thanks ~ Matt
I normally develop for MySQL and PHP, but we have a new application that runs on MS SQL and I'm having difficulty using the same structure as MySQL to run queries. Can someone here point me to a good resource to learn the basics?
Is it possible to convert the Power Point file to SQL Server Version 7 or 2000? My PowerPoint files have no images or pictures, only lyrics for songs that are used in the church during sunday services.
I was appointed to make a system in VB6 but my first job is to convert the PowerPoint files to SQL Server. There are 2000 songs in the powerpoint right now.
Hello, thanks for reading this. I am trying to learn a bit about debveloping my own website, and I am in need of a basic direction on how to acomplish this task:
I have an local program running on my PC that creates an MS Access database export of my inventory. This export can be directly uploaded to my web server in a zip format. My web site is developed in Coldfusion MX7, and uses a MS SQL 2005 database. What I would like to do is create somewhat of an "Auto update" for my website, exporting my access database and updating the MS SQL database automatically.
I really can use some good direction on this problem. Thank you for any help in advance.
i have a DTS Package a saved when i set up the package. it's in local packages in Enterprise Manager now. is there any way i can simply set up a shortcut on my desktop to execute that DTS, rather then having to nav though Enterprise Manager?
