T-SQL (SS2K8) :: Where Clause Integer Greater Than
Jul 21, 2014
When I use the following SQL with the variable @CutOFFTID the second select statement query takes several minutes. If i run it replacing the value in the where clause with the acutal value of the variable it runs instantly.
DECLARE @CutOffTid int
SELECT @CutOffTid = isnull(max(ccas_id),0)
FROMdbo.fbs_trans (NOLOCK)
print 'Cut Off ID is ' + cast(@CutOffTid as char)
DECLARE @MinPeriod int
SELECT @MinPeriod = Min(period)
FROM agr.dbo.atrans
WHERE agrtid >@CutOffTid
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 '>'.
It returns '2080-11-20 00:42:44.000'. This is dead on except for the year (which should be 2014). I was thinking maybe my bigint value of 3499288964 was milliseconds or microseconds so I adjusted the seconds value (86400) in the select statement to reflect milliseconds (86400000) with no success and microseconds (86400000000) with no success as both of those gave incorrect results. Closest I got was with the seconds (86400) which of course returns the incorrect year.
We're upgrading our SQL Server database from 2005 to 2012.I ran the Upgrade Advisory report and got this issue "Non-integer constants are not allowed in the ORDER BY clause in 90" because of the script below
SELECT gp.BRAND+' <> '+gp.CATEGORY AS 'full name', gp.PRODCODE, gp.CATEGORY FROM dbo.GFK_PRODUCT gp ORDER BY 'full name'
I tried running the same query in our test SQL Server 2012 and it ran successfully. Now I'm confuse if i still need to change it.I google the issue a bit and came across this link and mentioned this.
1.) Non-integer constants are ... constants that are not integer number.
Example: 'string1' represents a string constant 0x01 represents a varbinary constant {ts '2015-02-26 06:00:00'} represents a datetime constant
1.23 represents a numeric constants
2) So single quotes are used to define a string constants / character string constants but SQL Server allows also to use single quotation marks use also as column identifier delimiter: SELECT ... expression AS 'Column1' FROM ...
In this context is clear that 'Column1' is a column identifier but when used in ORDER BY : ORDER BY 'Column1' it generates confusion because SQL Server doesn't knows if it represents a string literal (character string constant) or it represents a column identifier / column name."Do I still need to change the existing code even though it's working fine in 2012? If yes, it is because of best practice reason or it will total get deprecated/not working in the future version?
As a DBA, I am working on a project where an ETL process(SSIS) takes a long time to aggregate and process the raw data.
I figured out few things where the package selects the data from my biggest 200 GB unpartitioned table which has a datekey column but the package converts its each row to an integer value leading to massive scans and high CPU.
Example: the package passed two values 20140714 and 4 which means it wants to grab data from my biggest table which belongs between 20140714 04:00:00 and 20140714 05:00:00.
It leads to massive implicit conversions and I am trying to change this.
To minimize the number of changes, what I am trying to do is to convert 20140714 and 4 to a datetime format variable.
Select Convert(DATETIME, LEFT(20170714, 8)) which gives me a date value but I am stuck at appending time(HH:00:00) to it.
I am dealing with what I believe is Oracle that is the source of a SQL View.
I am seeing a data type of Integer in the View, but I am not able to see what makes up that View. When I query the View, I can see that an Integer data type column is storing a blank space. I use ISNUMERIC(ColumnName) = 0 and there are a lot of rows that show as a zero length blank space, or text, or something. I just know that it is not an Integer.
I have attempted to CAST and Convert this value, but it will not. I have changed the data type on the table that is being inserted in too, and it still fails with a Conversion error. I have tried REPLACE(), but still the same conversion error.
I have someone telling me that I should have put my AND statement in the join instead of the where part, so is there a difference in the where I put it. Is there a difference in the results in any way between the two here in the results in anyway?
Example: Given a query: But let’s take a simple one here:
Query(1) Select ct1.Name, ct1.address, ct1.city, ct1.state, ct1.zipcode From cutTableA ct1 Left join cutTableA ct2 On ct1.ID = ct2.ID Where ct1.zipcode = '14124' AND ct1.Name = 'Bob'
Query(2) Select ct1.Name, ct1.address, ct1.city, ct1.state, ct1.zipcode From cutTableA ct1 Left join cutTableA ct2 On ct1.ID = ct2.ID AND ct1.zipcode = '14124' Where ct1.Name = 'Bob'
I need to write a SQL script where a join condition is using date columns (effective_date, ineffective_date). The effective date columns can be slightly different (e.g. differ by a day) for some rows of data. I need the join condition to accommodate these date differences and return these rows of data as well.
I have a table which uses multiple joins to create another table but it turns out that the effective_date which is used in the join to match row together does not work all the time since some of the dates for the effective date column are out of sync meaning records that show data as missing even when the other table contains the data. I tried the SQL script below using the BETWEEN clause but it returning 6 rows instead of 3–
Insert into deltavalues values ('1002', 2000,5000) Insert into deltavalues values ('1003', 1000,0) Insert into deltavalues values ('1004', 500,0) Insert into deltavalues values ('1005', 1000,2000)
--I have below query , basically getting emnployee table balance between deltavalues table baseval column and highval column
select * from employee e join deltavalues d on e.planid = d.planid where e.balance between d.baseval and d.highval
but here what i am requesting you is how can i change the highvalue to not include if it is 0
for example above example plan 1003, 1004 has highbal as 0 so i want to write a query to get employee data between 1000 and anyvalue (> 1000 - no limit)
same way for 1004 between 500 and no end values (infinity)
I am supposed to delete all rows from USER and its child tables based on PracticeID = '55' filter condition.
I have dynamically generated queries to delete child table first followed by parent. Table [EncounterSignOff] si child and [User] is parent.
I would like to know, whether the Query 1 is valid for deleting child records?
Query:1
DELETE Top(100000) FROM [dbo].[EncounterSignOff] FROM [dbo].[User] INNER JOIN [dbo].[EncounterSignOff] ON [User].[UserID] = [EncounterSignOff].[UserId] WHERE [User].PracticeID = '55';
Query:2
DELETE Top(100000) FROM [dbo].[User] WHERE [User].PracticeID = '55';
I have a population split between two vendors. One gets last names between A and R, the other the rest. Now, on a given date vendor 1 gets everybody.
I can accomplish this with a case statement on the upper range (R or Z), but it seems I should be able to do this without testing at all after the turnover date.
A small bit of the code:
declare @get_date datetime = convert(char(10),getdate(),101) select top 10 pt.pt_id, pt.last_name fromsmsmir.mir_acct a join smsmir.mir_pt pt on (a.src_sys_id = pt.src_sys_id and a.pt_id = pt.pt_id and a.from_file_ind = pt.from_file_ind
[Code] ....
Seems I should be able to not test the last name after the turnover date, but I can't figure out how.
I've been experiencing difficulty with pulling records using a where clause date range. I'm using this:
select * from dbo.ACCTING_TRANSACTION_hISTORY where ath_postype = 'NTC' or ath_postype='NTD' and
ath_postdate >= '2013-01-01 00:00:00' and ath_postdate <= '2013-01-05 23:59:59'
I've also tried variations of this without the time portion of the ath_postdate field (of type datetime) , but it still seems to be pulling records from 2009, etc.
I am wanting to get the job name based on sys.sysProcesses.[Program_name] column. Why is this query not returning any results even though the 2nd substringed guids are found the the sysJobs table?
SELECTCASE WHEN RTRIM([program_name]) LIKE 'SQLAgent - TSQL JobStep (Job %' THEN J.Name ELSE RTRIM([program_name]) END ProgramName , Val1.UqID , Val1.UqIDStr
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 want to change Set clause of Update Statement dynamically based on some condition.
Basically i have 2 Update statments having same FROM clause and same JOIN clause.
Only diff is SET clause and 1 Where condition.
So i am trying to combine 2 Update statements into 1 and trying to avoid visit to same table twice.
Update t Set CASE **WHEN Isnull(td.IsPosted, 0) = 0 THEN t.AODYD = td.ODYD** *ELSE t.DAODYD = td.ODYD* END From #ReportData As t Join @CIR AS tmp On t.RowId = tmp.Max_RowId
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 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 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.
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
