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:
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>
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?
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.
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 have a stored proc. in that proc i need to select a value based on which one is greater. Here is a non working example
select name, if (truck1.age > truck2.age, truck1.age, truck2.age) from person left join truck truck1 on truck1.make = person.make left join truck truck2 on truck2.make = person.make
I was comparing diffrent columns and within those columns there are specific values. I want to get the greter values using SQL 2000 i want something like these but i think there was an error in scripting
CASE WHEN a > b,c,d,e THEN a WHEN b > a,c,d,e THEN b WHEN c > a,b,d,e THEN c WHEN d > a,b,c,e THEN d WHEN e > a,b,c,d THEN e END
Please help. Is there any possible way to implement this? Thank you.
Here is the first part of a query for MySQL that I am trying to get working on MSSQL:
Code:
SELECT n.*, round((n.rgt-n.lft-1)/2,0) AS childs, count(*)+(n.lft>1) AS level, ((min(p.rgt)-n.rgt-(n.lft>1))/2) > 0 AS lower, (( (n.lft-max(p.lft)>1) )) AS upper FROM table n ...
But, I get this error message:
Server: Msg 170, Level 15, State 1, Line 3 Line 3: Incorrect syntax near '>'.
I created this unique codes and I need all [FRMDAT] field set to "12/31/2014" in the MKLOPT table, where the [JOBCOD] in the VALUE list BELOW have a [FRMDAT] that is currently (greater than) > "12/31/2014"
im supposed to output the companies that have commission rates highter than company "Industrial Appparatus". is there some whay to modify this code so that it will work? commissionrate > ALL(Select commissionRate From salescompanydomestic Where companyName = 'Industrial Appparatus')
I have a query pulling all records with a disconnect date and a transaction date. However, I would like to retrieve any records that have a transaction date greater than 30 days from the disconnect date. I have been unable to figure out the correct formula to use. I think I need to use the datediff function in SQL, but I've never really used this function before.
Problem: I want to set compatibility_level only when it is greater than 110.
Solution: Select the compatibility level and if it is greater than 110, I alter database set compatibility level=110
ISSUE Irrespective of IF Exist statement the alter database statement is executed all the time.
Here is the sql statement
IF EXISTS ( SELECT * FROM sys.databases where compatibility_level >110 AND name='mydatabase' ) BEGIN ALTER DATABASE mydatabase SET COMPATIBILITY_LEVEL = 110 END
Trying to set up a column in a grouped matrix that displays a count of all record over a specificed number.
The field I am counting are response time of transaction and I want to count how many were over 500 milliseconds. I though it would be something like this...
Code Snippet
=Count(Fields!ResponseTime.Value > "500")
However, this appears to just return the count of all rows and ignores the "500" part.
Am I missing something? If someone could post a alternate code snippet, that would be great.
I'm using and Execute Sql Task to get a count of the record in the table: How can I make the workflow to stop if it doesn;t meet the count requirement and continue if it does to the next flow. I'm looking at expression...but a bit comfused about using it.
I have a column of varchar(2000) but when I use it in a select statement I only get the first 255 characters displayed. (all the data is there as I can see different parts using substring) How do I get the complete column to display?
I think I'm trying to do a simple query on maximum date.
I've got 100 tools that have been used over the past three years. Some of the tools are used almost every day. Other tools haven't been used for a month, while other tools haven't been used for a year or more.
Ultimately I'm trying to just find the list of tools whose latest date of use was a year ago.
I have a list of tools and a list of times each tool was used.
I think I'm going to have to do a search that for each tool what was the times it was used. That I can do.
What I'm not sure of is how to then pull only the latest date for each tool.
Once I get that I can then do a query off that result to pull the "oldest latest" date of use.
