What Is The Best Way To Concotanate Two Integer Fields?
Dec 14, 2006
one of our marketing people downloads the DNC list from the FTC or one of the other F agencies. he then screens his cold calling list against it. he wants to put it into sql to make it faster.
it's a text file and imports 2 fields. The area code and the phone number. I want to import it as integers so i can put some logic to automatically screen his list against it. I can't concontanate two integers into a third column so I'm looking for some advice.
should i import them as strings originally, dump to a flat file and then import the full phone number as an integer? Or is there a better way?
Yesterday we ran into a very surprising problem: a colleague had developed a view which calculated a numbero of domain functions on an integer field (sum, count, standard deviation, average).
We were amazed to find that the view always returned an integer value for the average (not for the std dev, though). This was obviously wrong. We even tried changing the function to Sum(fld)/Count(fld) where "fld" was the fieldname, but we still got this strange curious result.
I got around the problem using the CONVERT function to change the integer value into a floating (e.g. Avg(CONVERT(FLOAT(5),fld)), but this is so strange that I'm sure we'll have the same problem again and again.
Now, can anyone tell me why the SQL engine is so incredibly stupid to presume that the average of a series of integer values is necessarily an integer???
How do we convert both of them into a single SQL DateTime field such as "2015-07-16 01:23:45.000" so that it can be used in a join restricting to a date time in a different SQL File that properly has the DateTime in it?
This works well for converting the transDate Part in the select statement:
dbo.IntegerToDate(at.transDate) as transDate
* That returns: "2015-07-16 00:00:00.000"
* The resulting data must work directly in a Microsoft SQL Server Management Studio Query using either using the "on" statement or part of the "where" clause. In other words, NOT as a stored procedure!
Also must be able to be used as a date difference calculation when comparing the 2 files Within say + or - 5 seconds.
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu". can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
Hello !I'm trying to update one table field with another table searched firstdate record.getting some problem.If anyone have experience similar thing or have any idea about it,please guide.Sample case is given below.Thanks in adv.T.S.Negi--Sample caseDROP TABLE TEST1DROP TABLE TEST2CREATE TABLE TEST1(CUST_CD VARCHAR(10),BOOKING_DATE DATETIME,BOOKPHONE_NO VARCHAR(10))CREATE TABLE TEST2(CUST_CD VARCHAR(10),ENTRY_DATE DATETIME,FIRSTPHONE_NO VARCHAR(10))DELETE FROM TEST1INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')DELETE FROM TEST2INSERT INTO TEST2 VALUES('C1',GETDATE(),'')INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')--SELECT * FROM TEST1--SELECT * FROM TEST2/*Sample dataTEST1CUST_CD BOOKING_DATE BOOKPHONE_NOC12005-04-08 21:46:47.78011111111C12005-04-13 21:46:47.78022222222C12005-04-18 21:46:47.78044444444C12005-04-19 21:46:47.78033333333TEST2CUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.800C12005-04-05 21:46:47.800C12005-04-14 21:46:47.800C12005-04-15 21:46:47.800DESIRED RESULTCUST_CD ENTRY_DATE FIRSTPHONE_NOC12005-04-03 21:46:47.80011111111C12005-04-05 21:46:47.80011111111C12005-04-14 21:46:47.80044444444C12005-04-15 21:46:47.80044444444*/
I am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
I would like to search a table for a phrase, or for a partial phrase,eg on table product - for name or description, or name + descprition.How does one say select * from product where name + description like%phrase%or contains phraseCurrently I can get where name, or where descriotion like %phrase%,eg, where name like krups, or where description like coffee makerBut if I search for where name like %krups coffee maker% i get noresults. krups is in the name field, coffee maker is in thedescription field.Thanks,-M
Pardon me if this question is too elementary. I am trying to create a trigger that will cause certain datafields to be updated with values from other data fields in the same row when a certain column, created specifically to fire the trigger, is updated. The purpose of this is to reduce data entry by field personnel.I think I have the create trigger statement correct, but I'm a little confused on the update statement.
In a nutshell, how can I write something like: UPDATE "TABLENAME" SET DATAFIELD1 = DATAFIELD2 WHERE RECORDNUMBER = (THE SAME RECORD NUMBER)
I do know that I have to ensure that sp_dboption Recursive Triggers value is set to false, thanks.
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
Hi people My users are having troubles with link to default drillthrough report when reports are exported to excel (they REALLY don't like this behavior ), so I decided set all of them disabled in report model, this work fine, but calculated field in reports has this drillthrough link.
Let me show you the situation. Entity Product has an UnitaryCost field, I set the EnableDrillthrough Property in False so when I export a report with this field, no link is shown.
But if I create in the report a calculated field Round(UnitaryCost) this field has a Drillthrough Link
Is this the standard and expected behavior? or its simply a BUG?
Have I done something wrong in my model? and in this case, How I can correct this?
My question is, i guess, a simple one: When is it more convenient to use a uniqueid Data Type instead of a smallint, tinyint, bigint, etc (any type of int) when the field is gonna be the primary key for the table?
i have an autonumber field (primary key) and another integer field as part of a table. What i want to do is when a record is created, the default value of the integer field should be the_autonumber+1000 for eg record with pk 82 will have an integer field that's automatically 1082. Would it be possible to do this ? Thanks in advance.
Public Function insertReport(ByVal userID As String, ByVal taskID As Integer) As DataSet Dim ds As New DataSet Dim da As New SqlDataAdapter("INSERT INTO report(userID, taskID) VALUES ('" + userID + "', " + taskID + ")", cn) cn.Open() da.Fill(ds) cn.Close() Return ds End Function Above is my code I used to invoke when inserting a record. But i receive an error message when i try to insert the integer. I cannot see where the problem lies..my datatype in sql server is int. Can anyone see what is wrong with it. Thank you in advance.
I have an sql statmetn that counts all the votes in the table. i need this to calculate the quota. My problem is how to i get the value(i.e the count of the votes) into the area thats colored red below? SqlCommand SqlCmd1 = new SqlCommand("SELECT count(vote)FROM PRTest", SqlCon1); int quota = (count(Vote) + 1) / ((11) + 1); Response.Write(quota);
I use MS SQL Server 2005...Is there a structural advantage/disadvantage with using GUID as oposed to an integer?(also I use the sqltableprofileprovider and it doesnt seem to work with uniqueidentifiers)
I have a situation where I will have to insert a value(whole number) into the table where the the value is more than what the Integer can hold , I was wondering is there any other datatype which i can use other than integer
I had a field called camp is integer data type. I just found it had a default value, but the value is " (0) ", not just " 0 ". It should be 0, right? why it use (0), are they same? thanks.
In my database I have a table called "users" with a varchar-field that holds categori-id's commaseparated, collected from my other table "category". I do this to control access for my users, some users are only allowed access to some categories. I would like to run a statement sort of like this:select categoryname from category where catid in (select categories from user where userid = 12)Naturally, because catid and the field categories have different datatypes I get the error "Syntax error converting the varchar value '340, 344, 356' to a column of data type int."
Is there any way I can bypass this keeping the commaseparated values and without making a new normalized table instead?
(same question is also posted at sql-server-performance.com forums)
Can I put a constraint on an integer column that will only allow a certain range of numbers to be entered, or do I have to put that into the application layer only?
I'd like the range to be 0 to 30 as the only allowable values. The only thing that I could think of was to create another table and populate with 0,1,2...,30 and put a foreign key on the new column that wouldn't allow anything not in that list but I was wondering if there was a better way.
philippe writes "Hello , i am just starting with sql...
I have a table: Table_user
Inside a column user_no (integer) with value like 35678 (about 8000 rows) I would like to modifie all rows of this Table_user.user_no with a value of 60 at the begining of each value. For example : before 35678 will be 6035678 Its'a concatenation,but it's an integer value . so do i need to convert tehm to varchar first
I am building an application that counnts clicks on an ad. I use the integer field to count them but someone told me there is a limit number this field can count - is that correct? if so, is there a solution for my problem? Thanks and happy 2007! R
Trying to write the most effective UDF to convert INT to Datetime. We have a column from a table on AS400 that is a INT type. Some are 4, 5, 6 ,7 digits. I have the 4 digits right. I need to fix it for 5 and 6 digits.
ALTER FUNCTION IntegerToDatetime (@int INT) RETURNS DATETIME AS BEGIN DECLARE @IntegerToDatetime int DECLARE @time DATETIME SET @time = '2001-01-01' SET @IntegerToDatetime = CASE WHEN LEN(@int) = 7 THEN '20' + CAST(SUBSTRING(CAST(@int AS CHAR(7)),2,2) AS int) + '-' + CAST(SUBSTRING(CAST(@int AS CHAR(7)),4,2) AS int) + '-' + CAST(SUBSTRING(CAST(@int AS CHAR(7)),6,2) AS int) WHEN LEN(@int) = 6 THEN '19' + CAST(SUBSTRING(CAST(@int AS CHAR(6)),1,2) AS int) + '-' + CAST(SUBSTRING(CAST(@int AS CHAR(6)),3,2) AS int) + '-' + CAST(SUBSTRING(CAST(@int AS CHAR(6)),5,2) AS int) WHEN LEN(@int) = 5 THEN '200' + CAST(SUBSTRING(CAST(@int AS CHAR(5)),1,1) AS int) + '-' + CAST(SUBSTRING(CAST(@int AS CHAR(5)),2,2) AS int) + '-' + CAST(SUBSTRING(CAST(@int AS CHAR(5)),4,2) AS int) WHEN LEN(@int) = 4 THEN cast(@time AS INT)
I wanted to hear from you - if you've used any unique integer generation technique in the context of a disconnected smart client/local data store and have been successful with it.
