I am having difficulty trying to figure out how to compare two integers stored in a table to return a third. I have two integer fields in one table and two in another like this:
Table1.SomeNumber1 = 1
Table1.SomeNumber2 = 2
Table2.SomeNumber1 = 2
Table2.SomeNumber2 = 1
I need to be able to compare the first number from the first table to the first number in the second table. If the values are different I need to set a variable or field to 0. If the numbers are the same I need to set my variable or field to 1.
I need to follow the same procedure comparing the second number in the first table to the second number in the second table. In addition, I need to be able to do it in a single select statement.
Does anyone have any ideas on how this could be done? Thank you for any help you may be able to provide.
I'm writing a stored procedure where one of the arguments (WHERE area) really only needs to be used in some circumstances. I.e., when the procedure is passed a USER_ID it needs to check that against the database, but in some instances I'll send 0 instead of a real USER_ID, and in those cases it should return all records regardless of the ID.
Here's what I've got: ... and b.user_ID = CASE @user_ID WHEN 0 THEN '%' ELSE @user_ID ... ...the problem being the '%' part. That won't work on an integer column.
The code below has this line SET @SOGallons = @ODTGallons
I need it to add the Current value of @SOGallons to the newly selected value of @ODTGallons and set that as the new value of @SOGallons.
I've tried SET @SOGallons = @SOGallons + @ODTGallons
SET @SOGalTemp = @SOGallons SET @SOGallons= @SOGalTemp + @ODTGallons
Neither Worked
<CODE> FROM [CSITSS].[dbo].[Orderdt] as ODT LEFT OUTER JOIN [CSITSS].[dbo].[Orddtcom] as OCOM ON ODT.[Companydiv] = OCOM.[Companydiv] AND ODT.[OrderNumber] = OCOM.[OrderNumber] AND ODT.[Sequence] = OCOM.[Sequence] WHERE ODT.[Companydiv]= 'GLPC-TRANS' AND ODT.[OrderNumber] = @OrdNum AND ([LineType] = 'IP' OR [LineType] = 'SO' OR [LineType] = 'DL' OR [LineType] = 'PU')
OPEN TC1
FETCH NEXT FROM TC1 INTO @LT, @ODTGallons, @ODTComm WHILE @@FETCH_STATUS=0 BEGIN IF @LT = 'SO' BEGIN SET @SplitTest = 1 SET @SOGallons = @ODTGallons IF @SOGallons > 0 BEGIN SET @SOGalTest = 1 END ELSE BEGIN SET @SOGalTest = 0 END IF @SplitTest <> @SOGalTest BEGIN SET @SOGalTest = 0 END END ELSE BEGIN SET @SOGalTest = 1 END FETCH NEXT FROM TC1 INTO @LT, @ODTGallons, @ODTComm END CLOSE TC1 DEALLOCATE TC1</CODE>
I have a table which measures the changes in a feedback rating, measured by an integer. Most of my records are the same. Only the primary key & the timestamp change.
How do I query just the changes?
Example dataset:
idrating 15 25 35 45 56 66
[code]....
There are 20 rows & 5 changes. The query I want will result in just those that are different from the ones before them:
1 2 3 * (unscheduled visit) (should be 3.01) * (unscheduled visit) (should be 3.02) Basically when there is an unscheduled visit, it should take the previous visit number and add .01
I'm wondering if there is a function in SQL that works like SUBSTRING function but for integers. Like for example if I have a number like 20010112 and I want to cut it to the first for digits so that it reads 2001?
It seems I am facing again an unsurmountable problem It should be so simple but one has to spend hours researching how to handle it. The MSDN help on this subject is increadibly obscure.
I have input parameters @months int, @days int, @years int in a stored procedure.
All I want to do is to get a DateTime variable out of them.
DECLARE @dated DateTime.
Thus I want @dated to be set to a DateTime value with month = @months, day = @days and year = @years. The MSDN help says that no CAST should be used since the conversion from int to DateTime should be implicit!!
No examples are given. They seem to show how to CAST or CONVERT varchar to DateTime. Shall I first convert my int to varchar?
It is rudiculous. I've tried dozens of variants. Please help.
I am trying use the decimal data type for a field in SQL Server. When I input the values below, they round off. 73.827 Rounds to 74 1925.1 Rounds to 1925 119.79 Rounds to 120 What am I missing? Access never gave me this issue. Do you see any reason this would happen? I am entering the values into the table directly!
I have a table with below data. Requirement is to replace all integers with continuous 6 or more occurrences with 'x'. Less than 6 occurrences should not be replaced.
create table t1(name varchar (100)) GO INsert into t1 select '1234ABC123456XYZ1234567890ADS' GO INsert into t1 select 'cbv736456XYZ543534534545XLS' GO
Why is it that, despite what is said in the sketchy SQL Help content, it appears to be impossible to cast a string to an integer in the Expression Builder to generate a value for a variable? More specifically, why does the following expression cause an error?
I'm iterating over files and using the name of a given file as an ID for an operation. I simply want to grab a file name using the Foreach Loop Container and process that file, while at the same time use the name in another operation. The file name will be something like "2.txt" (full path something like "c:somethingsomething2.txt"). I can use string functions to return the file name, which is a number as a string, and it should be no problem to cast that number as a string to a number (an Int32). SQL Server 2005 help has a chart that indicates such a cast is legal.
Maybe it's a crazy thing to be doing. Maybe I have to go about this a completely different way, but casting from "2" to 2 should be possible in the Expression Builder.
I was told that, when possible, use integer fields for the equality comparison in INNER JOINS. Today someone suggested that using character fields that are indexed should be just as efficient. What do you think?
I am working with a database named €œDocuments€? that contains 4 categories of text documents, each having its own number designation in an integer datatype column named SectionTypeId:
1 = Text 2 = Report 3 = Background 4 = Index
I would like to create a new column named €œDocType€? in which the integer data type for each document is replaced with a varchar data type letter (1 = T, 2 = R, 3 = B, 4 = I). I was able to easily create the new column and cast the data type from integer to varchar:
--CREATE NEW COLUMN €œDocType€? WITH VARCHAR DATATYPE
ALTER TABLE FullDocuments ADD DocType VARCHAR(1) NULL Go
--UPDATE NEW COLUMN WITH CAST STRING
UPDATE FullDocuments SET DocType = CAST(SectionTypeID AS VARCHAR(1)) Go
But I have problems with the REPLACE method for replacing the numbers with letters. First I tried this based on the examples in MSDN Library:
--REPLACE NUMBERS WITH LETTERS
UPDATE Fulldocuments REPLACE (DocType,"1","T")
Which produced an error message: €œIncorrect syntax near 'REPLACE'.€?
Thinking that the datatype may be the problem, I tried this to convert to DT_WSTR data type prior to replace:
My ERP software stores all dates as integers. So originally, I wrote a T-SQL function to convert these integer dates to normal people dates in the query I use as the recordset for my report. Well...that worked fine on 1,000 rows, but NOT for 100,000. So I've figured out that if I convert my normal person date parameter to an integer date, then SQL only has to convert my 1 parameter instead of having to convert 100,000 fields, (actually, 300,000 because I have 3 date columns).
So my question is, what is the best way to do this? This is what I have so far:
SET @Macola = Cast(Datepart(yy,@MacolaDate) as varchar) + Cast(Datepart(mm,@MacolaDate) as varchar) + Cast(Datepart(dd,@MacolaDate) as varchar)
However, I want the leading zeros for the month and day. For example if I enter '1/1/2004' into this function, it returns 200411, but I need it to return 20040101.
Any suggestions would be greatly apprectiated. Thank you.
I have a wrong €œdbo.Samples€? table: SampleID SampleName Matrix SampleType ChemGroup ProjectID 1 Blueriver01 Soil QA VOCs 1 7 Greentree01 Water Primary VOCs 1 8 Greentree02 Water Duplicate VOCs 1 9 Greentree03 Water QA VOCs 2 10 Greentree11 Soil Primary VOCs 1 11 Greentree11 Soil Duplicate VOCs 1 12 Greentree11 Soil QA VOCs 3 13 Redrock01 Water Primary VOCs 1 14 Redrock02 Water Duplicate VOCs 1 15 Redrock03 Water QA VOCs 2 16 Redrock11 Soil Primary VOCs 1 17 Redrock12 Soil Duplicate VOCs 1 18 Redrock13 Soil QA VOCs 3
I used the following sql code to correct the wrong ProjectIds:
USE ChemDatabase GO ALTER TABLE Samples SET ProjectID = 4 WHERE SampleID = 7 SET ProjectID = 4 WHERE SampleID = 8 SET ProjectID = 5 WHERE SampleID = 9 SET ProjectID = 4 WHERE SampleID = 10 SET ProjectID = 4 WHERE SampleID = 11 SET ProjectID = 6 WHERE SampleID = 12 SET ProjectID = 7 WHERE SampleID = 13 SET ProjectID = 7 WHERE SampleID = 14 SET ProjectID = 8 WHERE SampleID = 15 SET ProjectID = 7 WHERE SampleID = 16 SET ProjectID = 7 WHERE SampleID = 17 SET ProjectID = 9 WHERE SampleID = 18 GO
I got the following error message: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'SET'.
Please help and tell me what it is the right syntax for my €˜SET€™ used in this sql code. I think there are more mistakes in this set of sql code. Please enlighten me and advise me how to make this set of code right.
I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables. The SELECT looks like this: SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge WHERE ClientNumber = 138238 AND CaseNumber = 1 AND ProviderNumber = 89 The TreatmentStatus column is a tinyint. When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2. But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1. Why?
I hvae a stored procedure that has this at the end of it: BEGIN EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, '' END SET NOCOUNT OFF
SELECT Something FROM Something Joins….. Where Something = Something now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure. What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?
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 have soma ado.net code that inserts 7 parameters in a database ( a date, 6 integers). I also use a self incrementing ID but the date is set as primary key because for each series of 6 numbers of a certain date there may only be 1 entry. Moreover only 1 entry of 6 integers is possible for 2 days of the week, (tue and fr). I manage to insert a row of data in the database, where the date is set as smalldatetime and displays as follows: 1/05/2007 0:00:00 in the table. I want to retrieve the series of numbers for a certain date that has been entered (without taking in account the hours and seconds). A where clause seems to be needed but I don’t know the syntax or don’t find the right function I use the following code to insert the row :
and the following code to get the row back (to put in arraylist):
“SELECT C1, C2, C3, C4, C5, C6 FROM Series WHERE (LDate = Today())� WHERE LDate = '" + DateTime.Today.ToString() + "'"
Which is the correct syntax? Is there a better way to insert and select based on the date?
I don’t get any error messages and the code executes fine but I only get an empty datatable in my dataset (the table isn’t looped for rows I noticed while debugging). Today’s date is in the database but isn’t found by my tsql code I think.
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?
Hi, I have a table with 3 columns inside - record_id (int) - user_id (varchar) - login_date (date) it is a many-to-many relationship table that record login date of users
Now, I want if I want to COUNT the users who login before 31 May 2008, I would use SELECT COUNT(*) FROM table1 WHERE login_date < '2008-31-05'
That's works
But the problem is I want to split the result to - How many people visit the site only once? - How many people visit the site only twice? - How many people visit the site three time?
I have a field in database money. When I enter value for it the amount entered is for example 20.000. How can I compare this value with noraml vaules i.e. like 20 in my search engine. Will I need to convert it to varchar and then compare it or is there some other way. Also if I need to convert it to varchar, how can I do it?
<< The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. >>
ok ... it was my error mess, but i just wrote this code :
*********************************************************** Dim conPubs As SqlConnection Dim cmdSelect As SqlCommand Dim strSelect As String conPubs = New SqlConnection ("Server=localhost;uid=ggggg;pwd=uuuu;database=eeee") strSelect = "Select username From userpass Where username = '" & usernametxt.Text & "' And password = '" & passtxt.Text & "'" cmdSelect = New SqlCommand( strSelect, conPubs ) conPubs.Open()
if Not(cmdSelect.ExecuteScalar() IS Nothing) then 'findeduser.text response.redirect("welcome.aspx") conPubs.Close() else msg.text="ID or Pass is incorrect!!" conPubs.Close() end if ***********************************************************
I am wondering how I would create a SELECT that will select the most recent date from one of two tables. For example, table1 has a field called LastUpdate and table 2 has a field called LastUpdate. I need to grab only the most recent date. I tried this using an inner join...but that didn't work because it only picks the lastupdate form one table only. talbe1 and table2 are tied by table2.table1id. Can anyone help?
Hi All, I have a database field (datestamp) which returns the date the records were inserted into the database. The datestamp was created with the now(); function in .net and is in the following format: 5/23/2006 2:27:45 AM I basically want to return all records that were inputted more than 28 days ago. I have had alook though some other posts and below is the closest query that i could find but unfortunately it does not work for me. SELECT id, datestampFROM table WHERE datestamp > DateAdd(d, 28, GetDate()) Thanks in advance, Jake
Hi , I have three tables T1 , T2 AND T3. T3 is having fields as a combination of T1 and T2 fields.How can I compare T1 and T2 field values with T3 FIELD VALUES.
How do i compare 2 dates without comparing their Time portions. I have a Datetime column in my table which for example has values :-
6/6/2001 7:23:42 PM 6/6/2001 8:01:08 PM and so on....
I have a Stored Procedure in which i am trying to fetch values from this table. The SP has 2 DateTime parameters @DateFrom and @Dateto. This SP is called from a Web page. On the Web Page the user just enters mm/dd/yyyy values for the 2 Date parameters. Therefore the Time portions defaults to 12:00 AM. So when the user passes 6/6/2001 as Date From and 6/6/2001 as Date To values, he doesn't get back the 2 records mentioned above since the Time portion makes the 2 records fall out of the date range.
Is there a way to just do a comparison on the Date portions i.e. give me all the records for 6/6/2001 irrespective of the time. I don't wan't to do individual comparisons for Day then Month and then Year.
