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

View 9 Replies


ADVERTISEMENT

Using Greater Than '&>' In SELECT Clause

Jan 10, 2006

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 '>'.

Is there a way to convert this? Thanks

View 2 Replies View Related

Transact SQL :: Case In Where Clause With Column Comparison With Greater Than Or No Filter

Sep 24, 2015

For Below example when @x=1 to retrieve col>0 rows or all rows.

With out another if else blocks or Dynamic sql to solve only in where clause.

select 0 col into #x
union
select 1 col
union
select 2 col

declare @x INT =1
SELECT * FROM #x
where col>CASE WHEN @x=1 THEN 0 ELSE (col=col) END

--here in case i want to compare only when @x=1 then col>0 other wise select all rows with out filter

View 5 Replies View Related

T-SQL (SS2K8) :: Getting UTC Date From Integer?

Dec 29, 2014

I'm trying to get the UTC date from an integer. See my SQL below:

Declare @unix_date bigint
Set @unix_date = 3499288964
SELECT dateadd(day, @unix_date/(86400), dateadd(second, @unix_date
% (86400), '19700101'))

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.

View 2 Replies View Related

T-SQL (SS2K8) :: Rounding Decimal To Nearest Integer Value?

Jun 28, 2015

how to round the nearest value after round of decimal and return integer.

declare @data decimal(18,2)
set @data = 5.55

-- output

select '5'
set @data = 5.58
-- output
select '6'

View 3 Replies View Related

Setup And Upgrade :: Clarification On Non-integer Constants Are Not Allowed In ORDER BY Clause In 90

May 15, 2015

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?

View 4 Replies View Related

T-SQL (SS2K8) :: Converting Integer Values To Date-time?

Jul 15, 2014

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.

View 4 Replies View Related

T-SQL (SS2K8) :: Blank Space In Integer Data Type Column View

Apr 4, 2014

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.

View 1 Replies View Related

T-SQL (SS2K8) :: Performance Of OR In Where Clause

Oct 17, 2014

I had a pretty simple query like

select * from table_A
where email in (select email from table_B)
or
email not in (select email from table_c)

It ran for a very long time.

But if I ran the select with either of the conditions by itself it took just a second. Combining both conditions really slowed it down.

View 4 Replies View Related

T-SQL (SS2K8) :: AND Statement In Where Clause

Nov 13, 2014

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'

View 4 Replies View Related

T-SQL (SS2K8) :: Add IF Condition To WHERE Clause

Feb 5, 2015

I want to add an exception to the WHERE clause below.

Something like:

IF @IDHuis = 'WH' THEN
add the extra condtion:
AND D.AfdelingZPT <> 'A01'

-- HRS VAST
INSERT INTO @T (JAAR, WEEK, HrsVast)

[Code] .....

View 9 Replies View Related

T-SQL (SS2K8) :: Join On Clause With IN Statement

Apr 25, 2014

I've come across a piece of code which i have never seen before.

ON T.CT_YEAR in
(
case
.[DBO].[FN_GET_YEAR]
(
CAST
(
C.[YEAR] AS VARCHAR(4)

[Code] ....

The CT_Year column is simply C for current year L for last year, O for Other, N for Next.

The Function simply returns the year value.

View 4 Replies View Related

T-SQL (SS2K8) :: Join Clause With BETWEEN Statement

Aug 12, 2014

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–

select t2.[entity_id]
,t2.[effective_date]
,[company_name]
,[last_accounts_date]
,[s_code]
,[s_code_description]

[Code] .....

View 2 Replies View Related

T-SQL (SS2K8) :: How To Ignore Between Operator End Value If It Is 0 In Where Clause

Apr 9, 2015

create table deltavalues (planid varchar(10), baseval float, highval float)

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)

View 2 Replies View Related

T-SQL (SS2K8) :: Two From Clause In DELETE Statement

Apr 17, 2015

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';

View 5 Replies View Related

T-SQL (SS2K8) :: Case Statement In The Where Clause?

Jun 5, 2015

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.

View 2 Replies View Related

T-SQL (SS2K8) :: Convert Number Of Month In Integer Into How Many Year And Month

Sep 10, 2014

This is my table and data

CVID | WorkExperience
--------------------------------
2838736
68181101
96568122
1135484

I need to convert into this result

CVID | WorkExperience
--------------------------------
283873 years
681818 years 5 months
9656812 years 2 months
1135484 months

View 5 Replies View Related

T-SQL (SS2K8) :: Get Output Of Procedure And Assign It To A Variable Used In WHERE Clause?

Mar 25, 2014

Get output of SQL Procedure and assign it to a variable used in WHERE Clause

Later I want to use this variable in my code in the WHERE Clause

Declare @ProjectNo nvarchar(10)

--Now I want to assign it to output of a storedprocedure which returns only 1 value and use it in the below SELECT query.

SELECT ID from TABLEA where Project in (@ProjectNo)

How to do it. How to assign @ProjectNo to output of storedProcedure called 'GetProjNumber'

View 1 Replies View Related

T-SQL (SS2K8) :: Pulling Incorrect Records Using Date Range In Where Clause

Apr 22, 2014

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.

View 9 Replies View Related

T-SQL (SS2K8) :: Convert Unique Identifier From String To Use In A Join / Where Clause

Jan 6, 2015

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

[Code] ......

View 6 Replies View Related

DB Design :: Convert Integer Date (MMDDYY) And Integer Time (HHMMSS) To DateTime Format?

Jul 20, 2015

Working on a new database where the Date and Time are stored in a Date Time Field.

Then working on an OLDER database file within the same SQL Database contains these 2 items as integers:

transDate = "71615" (July 16, 2015)
transTime = "12345" (01:23:45 AM)

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.

View 3 Replies View Related

T-SQL (SS2K8) :: Change Set Clause Of Update Statement Dynamically Based On Some Condition

May 27, 2015

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

[Code] ....

But CASE statement is not working...

View 7 Replies View Related

SQL Using Greater Than In Select

Jun 18, 2008

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
 
Thanks in advance for any help with this

View 2 Replies View Related

Greater Inputbuffer?

Aug 9, 2001

I'm trying to sniff or view a fairly large query that get's passed to my sql server.

The problem I'm having is that the dbcc inputbuffer only shows the first 255 chars of query and I need to see the whole thing.

Is there another method or a way to increase the size of this so I can see the whole query?

Thanks

- jeff

View 3 Replies View Related

Date Greater Than

Jul 24, 2007

Hi Guys

I have a table with id, date column in it. How do i list all records which has date greater than 30 day?

Thanks


Is this Correct?

SELECT * FROM TABLE WHERE DATEDIFF(DAY, DATE, GETDATE()) > 30

View 9 Replies View Related

Integers Which Are Greater Than Ones Before Them

Aug 29, 2013

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:

idrating
45
56
97
118
189

I use Microsoft SQL 2008

View 2 Replies View Related

Comparing Greater Value In SQL

Feb 6, 2008



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.

View 6 Replies View Related

How To Delete Records That Are Greater Than 1 Day?

Jun 1, 2000

Hello,

I would like to create a job that runs ones a day to delete records in a table that are older than one day. Any ideas?

Thanks,
Nina Melhem

View 2 Replies View Related

If String Greater Than 256Characters..???

Mar 14, 2006

The length of my query string is greater than 255 characters. SQL Server is truncating the query string when I assign it to a Local variable.

What should i do....???

View 4 Replies View Related

Using Greater Than Within Update Statement

Apr 21, 2015

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"

VALUE LIST

PH00059
PH02775
PH03051
PH03305
PH03336
PH03342
PH03371
PH03992
PH03993
PH03994

View 1 Replies View Related

Problem With Greater Than Derived Value

Jun 1, 2008

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')

View 6 Replies View Related

Records Greater Than 30 Days

Sep 10, 2014

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.

View 2 Replies View Related

Set Compatibility Level Only When It Is Greater Than 110?

Mar 5, 2015

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

What is that I am missing here ?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved