(Less Than) Vs. (Less Than Or Equal To)

Sep 24, 2007



Assume ID is an Integer field, which is faster (or is there any difference at all)?




Code Snippet

select * from <MyTable> where ID < 51

--or

select * from <MyTable> where ID <=50




View 3 Replies


ADVERTISEMENT

How To Show Two Dataset With Equal &&amp; Non Equal Of Multiple Selection.

Jun 14, 2006

Dear Friends,

In my report, I am having Listbox for users to choose Country, City & Company.
The user can choose Country. Based on the country selection, cities will be listed out.
Based on the city selection, Companies will be listed out.
They can choose companies.

Now, I have to show two set of results.

A. List of Companies as per selection ( dataset with equal to selection )

B. List of Companies which are not selected ( ie dataset with not equal to selection )

I have created a dataset with all companies and filter it by selection. When I tried with the filter option in the Dataset, I am able to check for only one value and not for multiple value. If the selection is one company, then I can filter it. But if they choose 5 companies, I am not not able to filter it. Is there any other option I can try out.


Please advice. Thanks.

warm regards
Rakin
Singapore.

View 6 Replies View Related

Why Don't These Equal

Jan 10, 2008

Can someone tell me why these do not equal? The rec_day is 10 ?
WHERE (CAST(DATEPART(Q, GETDATE()) * 3 - 2 AS VARCHAR(2)) + '/' + CAST(rec_day AS VARCHAR(2)) + '/' + CAST(YEAR(GETDATE()) AS VARCHAR(4))) = GETDATE()

View 4 Replies View Related

Not Equal To

Apr 26, 2006

on sql server
!= and <> are the same?

cause i cant see != on books online.

View 6 Replies View Related

Equal ??

Jun 19, 2006

hi
does http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764584332.html = http://www.wrox.com/WileyCDA/WroxTitle/productCd-076457955X.html ??
and what is the difference ??
and which one of these you advice me to read for beginner ??
thank you

View 3 Replies View Related

Less Than Or Equal To Getdate?

Jun 2, 2008

SELECT website
FROM dbo.wce_contact
WHERE (NOT (Mail1Date IS NULL))
AND (Mail2Date IS NULL)
AND (Mail3Date IS NULL)
AND (Mail4Date IS NULL)
AND (Mail5Date IS NULL)
AND (Mail6Date IS NULL)
AND (Mail7Date IS NULL)
AND (Mail8Date IS NULL)
AND (Mail9Date IS NULL)
AND (Mail10Date IS NULL)
AND (Mail11Date IS NULL)
AND (Mail12Date IS NULL)
AND (Mail13Date IS NULL)
AND (Mail14Date IS NULL)
AND (Mail15Date IS NULL)
AND (Mail16Date IS NULL)
AND (IDStatus IS NULL OR IDStatus = '')
AND (NOT (Task LIKE '%x%') OR Task IS NULL)
AND (NOT (ExpressEmail IS NULL OR ExpressEmail = ''))
AND (NOT (WebSite IS NULL OR WebSite = ''))
AND (Mail17Date IS NULL OR Mail17Date = '')
AND (Mail18Date IS NULL)
AND (Mail1Date <= '20080421')
AND (NOT (RECORDOWNER = 'lbm') OR RECORDOWNER IS NULL)

How would i get the part in red to say mail1date less than or equal to 42 days back from todays date?

View 7 Replies View Related

Not Equal Joins

Jan 23, 2007

Hi,

I need to select all database names that are not contained in table tsm.dbo.DB_NOT_to_Backup, but are present in master..sysdatabases.
Can this be done using not equal joins?

I tried the query below, but it got me nowehere:

select a.name
from master..sysdatabases a inner join tsm.dbo.DB_NOT_to_Backup c on a.name<>c.DBname
order by a.name

G

View 3 Replies View Related

How Do You Find Not Equal Fields?

May 17, 2007

I have table 'A' containing Company information including the company zip code.  I have table 'B' which is a table of ZipCodes.  How can I get a result set of all the Zip Codes that exist in 'A' but not in 'B'?
Thank you

View 8 Replies View Related

Set A Stored Procedure Equal To A Value

Sep 24, 2007

Hi, 
I have a stored procedure that returns a string.  How do I assign it to a variable?
I have currently
exec myStoredProc '2'
and I want something like
declare @myVar varchar(25)
set myVar = exec myStoredProc '2'
 
But I can' seem to figure out the syntax for assignment.
Thanks

View 1 Replies View Related

Not Equal Date Comparison

Nov 13, 2000

I'm having a problem with what should be a simple TSQL statement. I have a
table which has a datetime field updated. After the update if I type

select * from patient_medication where rec_status_date = '11/10/2000'
it returns the rows I want.(All the dates have a time of 00:00:00.000.
But if I type
select * from patient_medication where rec_status_date <> '11/10/2000'
or select * from patient_medication where rec_status_date != '11/10/2000'

The rows that have a value are returned, but none of the null values
are returned. Will nulls not work with this comparison?
Thanks

View 1 Replies View Related

Not Equal In Simple Query

Feb 26, 2001

Trying to do a simple query in SQL Server 7.0 to return data where not equal and get a cartesian.

Select person.person_id
from person, psnl
where person.person_id <> prsnl.person_id

Could this be an error with a SQL server setting?

View 2 Replies View Related

Making Fields Equal Each Other...

Jun 28, 2005

Sorry if this is a total n00bie question but...


I have table A and table B

I want a field in table B to be equl to the primary key in table A, and i'm not sure how to do that.

Thanks

View 1 Replies View Related

Conflict In Equal-to-operation

Jul 21, 2006

Hi there,

I am trying to update one table on server A/database A with new records from a server B/database B.

Both databases use the same collation. The key-fields of both table do use the same collation, but not the server.

Help: I was thinking, when both databases and the fields inside the tables do both use the same collation, then there will not upcome any conflict ?

Do I have to adapt the database collation, too ? This would'nt be nice as I am retreiving data from different servers with different server collations...
Anybody else got the same problem ?

Thx in advance

dajm

View 2 Replies View Related

Using Not Equal In Case Expression

Jul 22, 2013

The SUBSTRING function returns @domain.com. I don't want any results to be displayed from my sql statement if it equals @myemail.com. I have other cases too that are not relevant to my question. I'm getting an error that says invalid syntax near <

SELECT DISTINCT CASE CUST_EMLAD_TX
WHEN SUBSTRING(CUST_EMLAD_TX, CHARINDEX('@',CUST_EMLAD_TX), LEN(CUST_EMLAD_TX)) <> '@myemail.com' THEN CUST_EMLAD_TX
END
FROM ...

View 2 Replies View Related

ASSIGN Record Equal

Mar 31, 2006

Good day!

I have the qry, which is suppose to assigned records to active user (almost 15 users) equal, but it doesn’t- sometime it assigned more to some users and less to others. How could I modify my qry to make sure it assigns the records equals to each user? Please, see the qry below. I will appreciate any help

Thk


UPDATE
TBLFRAUDFINDER
SET
DATE_ASSIGNED=GETDATE(),
FRAUDANALYSTASSIGNED=@FRAUDANALYST
WHERE
FRAUDID=@FRAUDID OR
(FRAUDANALYSTASSIGNED IS NULL AND
((INQUIRY_GOVT_NUMBER=@INQUIRY_GOVT_NUMBER) OR
(CUST_NM=@CUST_NM) OR
(ALERT_IDENTIFIER=@ALERT_IDENTIFIER) OR
(ACCT IN (SELECT
ACCT
FROM
TBLFRAUDFINDER
WHERE
INQUIRY_GOVT_NUMBER=@INQUIRY_GOVT_NUMBER OR
CUST_NM=@CUST_NM OR
ALERT_IDENTIFIER=@ALERT_IDENTIFIER))

View 1 Replies View Related

How To Check Two Tables Are Equal

Jul 31, 2007

I have two procedures (old and new) that return queries and have to check that for every possible parameter they return the same data. Any hints?

Thanks, Pablo

View 13 Replies View Related

Transact SQL :: Query With NOT EQUAL TO VALUE

Oct 20, 2015

comparing a value, my sql statement fails at the last bit i.e. )<>0I'm trying to compare the last three characters i.e. not equal to 0? error message

Msg 102, Level 15, State 1, Procedure StoredProcedure, Line 137
Incorrect syntax near '<'.

Basically I'm trying to validate the field name Code to have a letter followed by 3 numbers..

if LEFT (@Code, 1) NOT LIKE '[a-Z]%' OR ISNUMERIC (RIGHT(@Code,3)<> 0

View 2 Replies View Related

Scope_identity Equal To 0 From A Stored Proc As We

Jun 21, 2006

Here is my stored proc:  InsertAccount 

Create PROCEDURE dbo.InsertAccount
@AccRisAsse bit,
@AccHldBll bit,
@Acctaxexm bit
AS
insert into TblAccount(AccRisAsse,AccHldBll,Acctaxexm)
             values(@AccRisAsse,@AccHldBll,@Acctaxexm)

return scope_identity()

GO

And here is the code in .Net to run the proc and get back the scope_identity:

I delcare my sqlCommand sqlcmdInsNeAcc and connection from
system.data.Sqlclient, I open my connection. all goes well then I do:
    sqlcmdInsNeAcc.type=commandtype.storedprocedure      
sqlcmdInsNeAcc.CommandText = "InsertAccount"
            pm =
sqlcmdInsNeAcc.Parameters.Add(New SqlParameter("@AccountNumber",
SqlDbType.BigInt))
            pm.Direction = ParameterDirection.ReturnValue
     Dim kAccountNumber  as integer
           
kAccountNumber = sqlcmdInsNeAcc.Parameters("@AccountNumber").Value

            pm =
sqlcmdInsNeAcc.Parameters.Add(New SqlParameter("@AccRisAsse",
SqlDbType.Bit))
            pm.Value = myvalue1
            pm =
sqlcmdInsNeAcc.Parameters.Add(New SqlParameter("@AccHldBll",
SqlDbType.Bit))
            pm.Value = myvalue2
            pm =
sqlcmdInsNeAcc.Parameters.Add(New SqlParameter("@Acctaxexm",
SqlDbType.Bit))
            pm.Value = myvalue3

sqlcmdInsNeAcc.executenonquery

then when I try to display the value of my returned scope identity from the variable: kAccountNumber
ALL I GET IS ZERO although my row is created in the table with the right Account number

Thanks

View 2 Replies View Related

Equal Between A String And A Table Atribute?

Oct 25, 2007

I
have a if statement and a table with 2 atributes. They are description
and date. I will check if there is in the table some date like
01-01-2008.String test "01-01-2008";if (test.Equals(here I will a SELECT query like SELECT date FROM appointmentTable)){
textbox1.text = "The description on this date is: " + Here I will the descripton of the selected date

View 2 Replies View Related

Recognize NULL Values As Being Equal...

May 1, 2001

Hi,

I'm using SQL SERVER 7.0.


I'm driving myself crazy on this one. I have 2 tables that look like this:


T1T2
C1C2C3C1C2C3
JOE1OTTAWAJOE1TORONTO
MARC1OTTAWAMARC4OTTAWA
GAVINNULLHALIFAXGAVIN3HALIFAX
DARRINNULLHALIFAXDARRIN3HALIFAX
DENISENULLPITTSBURGHDENISENULLPITTSBURGH
LOUISENULLRUSSELLLOUISE2RUSSELL
ANDREA3STITTSVILLEANDREANULLSTITTSVILLE
MARIO66PITTSBURGHGEORGE6KINCARDINE
LARRY6KINCARDINE
MARIO66PITTSBURGH

What I need to do is get all of the records from T2 that don't match EXACTLY to a record in T1. So I figured a LEFT OUTER JOIN should work:

SELECTT2.*
FROMT2 LEFT OUTER JOIN T1
ONT2.C1 = T1.C1
ANDT2.C2 = T1.C2
ANDT2.C3 = T1.C3
WHERET1.C1 IS NULL

But this statement returns the DENISE record when I do this (which has an EXACT match).
So, my thoughts took me to the NULL values in T1.C2 and T2.C2 for this record and I thought that, perhaps, the NULL values aren't being recognized as being equal (as they are UNKNOWN).
So I started digging around and found SET ANSI_NULLS OFF. I tried it but with no luck. Can you offer any insight on this one? What can I do to have NULL values recognized as being equal?

This is the result set that I would like to have returned in this example:

JOE1TORONTO
MARC4OTTAWA
GAVIN3HALIFAX
DARRIN3HALIFAX
LOUISE2RUSSELL
ANDREANULLSTITTSVILLE
GEORGE6KINCARDINE
LARRY6KINCARDINE


I've included a script to build and populate the tables below.

Any help on this will be greatly appreciated.

Thanks in advance,
Darrin

------------------------------------------------
IF EXISTS(
SELECT*
FROMSYSOBJECTS
WHERENAME = 'T1'
)
DROP TABLE T1
GO

IF EXISTS(
SELECT*
FROMSYSOBJECTS
WHERENAME = 'T2'
)
DROP TABLE T2
GO


CREATE TABLE [dbo].[T1] (
[C1] [varchar] (50) NULL ,
[C2] [varchar] (50) NULL ,
[C3] [varchar] (50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[T2] (
[C1] [varchar] (50) NULL ,
[C2] [varchar] (50) NULL ,
[C3] [varchar] (50) NULL
) ON [PRIMARY]
GO


INSERT T1 VALUES('JOE', '1', 'OTTAWA')
INSERT T1 VALUES('MARC', '1', 'OTTAWA')
INSERT T1 VALUES('GAVIN', NULL, 'HALIFAX')
INSERT T1 VALUES('DARRIN', NULL, 'HALIFAX')
INSERT T1 VALUES('DENISE', NULL, 'PITTSBURGH')
INSERT T1 VALUES('LOUISE', NULL, 'RUSSELL')
INSERT T1 VALUES('ANDREA', '3', 'STITTSVILLE')
INSERT T1 VALUES('MARIO', '66', 'PITTSBURGH')
GO


INSERT T2 VALUES('JOE', '1', 'TORONTO')
INSERT T2 VALUES('MARC', '4', 'OTTAWA')
INSERT T2 VALUES('GAVIN', '3', 'HALIFAX')
INSERT T2 VALUES('DARRIN', '3', 'HALIFAX')
INSERT T2 VALUES('DENISE', NULL, 'PITTSBURGH')
INSERT T2 VALUES('LOUISE', '2', 'RUSSELL')
INSERT T2 VALUES('ANDREA', NULL, 'STITTSVILLE')
INSERT T2 VALUES('GEORGE', NULL, 'KINCARDINE')
INSERT T2 VALUES('LARRY', NULL, 'KINCARDINE')
INSERT T2 VALUES('MARIO', '66', 'PITTSBURGH')
GO

View 3 Replies View Related

T-SQL (SS2K8) :: Joining Where Values Do Not Equal One Another

Apr 23, 2014

I have two select statements; one for open purchase orders, one for open customer orders. I would like to be able to combine the query based on i.item in the top statement joined with c.item from the bottom statement. The i.item is related to a specific c.item, but they do not have the same values. In this case I want to join based on.

p.item=i.item where
1001099548=1001099550
84162359=84198545
84532300=84532293
47547523=47547951
305545A3=87433653
87444977=87444975

left side coming from p.item = right side coming from c.item.

Here are my statements.

--#1 OPEN PO's
SELECT p.item
,(p.qty_ordered-p.qty_received) as POQtyRemaining
,i.item
,i.qty_on_hand
,p.po_num

[Code] ....

View 8 Replies View Related

Can I Set A Variable Equal To A Case Statment?

Feb 25, 2008

I have a case statement I need to refer to several times in my select statement and it's quite long. I would like to just refer to if by name. I tried to create a variable for the Case statement (see below) but I get an error that says, "
Sub query returned more than 1 value. This is not permitted when the sub query follows =, !=, <, <= , >, >= or when the sub query is used as an expression."

USE GP05
GO

DECLARE @EmpID nvarchar (6)

SET @EmpID = (SELECT CASE WHEN NOT(dbo.BE010130.EMPID_I IS NULL) THEN dbo.BE010130.EMPID_I
WHEN dbo.BE010130.EMPID_I IS NULL AND NOT(dbo.UPR00500.EMPLOYID IS NULL) AND dbo.UPR00600.EMPLOYID IS NULL THEN dbo.UPR00500.EMPLOYID
WHEN dbo.BE010130.EMPID_I IS NULL AND dbo.UPR00500.EMPLOYID IS NULL AND NOT(dbo.UPR00600.EMPLOYID IS NULL) THEN dbo.UPR00600.EMPLOYID
WHEN dbo.BE010130.EMPID_I IS NULL AND NOT(dbo.UPR00500.EMPLOYID IS NULL) AND NOT(dbo.UPR00600.EMPLOYID IS NULL) THEN dbo.UPR00500.EMPLOYID
END FROM dbo.BE010130 FULL OUTER JOIN dbo.UPR00500 ON dbo.BE010130.EMPID_I = dbo.UPR00500.EMPLOYID AND dbo.BE010130.BENEFIT = dbo.UPR00500.DEDUCTON
FULL OUTER JOIN dbo.UPR00600 ON dbo.BE010130.BENEFIT = dbo.UPR00600.BENEFIT AND dbo.BE010130.EMPID_I = dbo.UPR00600.EMPLOYID)

SELECT @EmpID

I can get around this by simply retyping the case statement when I refer to it in the where clause, but I would prefer not to do this. Can someone point a newbie in the right direction? I have 5 case statements in this query and it starts to look pretty ugly when you have to retype them multiple times.

Thanks!

View 9 Replies View Related

SSRS 2005 - Not Equal Condition

Feb 28, 2008



I have created and deployed a report model by using BI development Studio. When trying to build a tabular report with Report Buider I have to use the "not equal" condition as a filter. The query does not give the expected results.

let's suppose the values for a filed I want to filter on are: 1,2,3,4,5

If i run the filter as: field NotEqual to 6 then the results are not correct


But if instead of using the NotEqual condition I use
field equals to 1
or
field equals to 2
or
field equals to 3
or
field equals to 4
or
field equals to 5




Then it gives the correct results. Ok I now it sounds weird but anyone with similar experience using ther NotEqual condition in a filter?

Thanks,

Alberto

View 1 Replies View Related

Inner Join Matches Non-equal Fields??????

Jun 13, 2006

Hi group,

This morning we had an issue with a simple join between 2 tables e.g. table1 and table2.
Both tables have 1 column called 'fielda' with datatype varchar.

Let's assume the following values:
Table1:
hello
Hello

Table2:
hello

This statement joins the tables:
select a.fielda
from table1 a inner join table2 b on a.fielda = b.fielda


It returns:
hello
Hello

????????????????????????????????????????????????????????
What the $#@#$
Why doesn't it return 1 value:
hello


Any ideas?
The outcome is exactly what we want but I would expected to have to use the following:
select a.fielda

from table1 a inner join table2 b on UPPER(a.fielda) = UPPER(b.fielda)

What if I wanted to return all EXACT matches? Then what? Change collation?

View 5 Replies View Related

Cannot Resolve Collation Conflict For Equal Operation

Aug 10, 2004

Hi, I´m having a critical problem.

I have two databases: persons and cars

In database persons I have a table
named persons_class1 as follows:
person_id char(13) not null [primary key]
name varchar(20) not null

In database cars I have a table
named cars_blue as follows:
car_id char(13) not null [primary key]
model varchar(20) not null
person_id char(13) not null

In my program the initial catalog is: cars
and I´m executing the following SQL:

select cars_blue.model,persons.dbo.persons_class1.name
from cars_blue,persons.dbo.persons_class1
where cars_blue.person_id=persons.dbo.persons_class1.person_id

and the following error occurs:
Cannot resolve collation conflict for equal operation

I also tried like instead of = and the error is:
Cannot resolve collation conflict for like operation

what can I do?

Thanks in advance
Roland

View 1 Replies View Related

Sort Order Defines All E-like Characters As Equal

Apr 4, 2006

Hey,

I want a search of name ='Jose' to find both Jose and José, but with

SELECT * FROM Users WHERE name LIKE '%Jose%'

i only get Jose, in "Inside SQL SQL" an article shows this:

If you want a search of name ='Jose' to find both Jose and José, you should choose accent insensitivity. Such a sort order defines all E-like characters as equal:

E=e=è=É=é=ê=ë

but how shut i do this ???

MSSQL 2000 SP 4
Server Collation: Latin1_General_CI_AS

Windows 2003 Server Standard

Thanks for Help !!!

View 3 Replies View Related

Cannot Resolve Collation Conflict For Equal To Operation.

Jul 1, 2004

Anyone bang into that?

RESTORED a 7.0 Datbase CP 52 to a 2k box

Built a "Privacy" db from scratch on 2k box

Looked at some table scripts and got


OHM - sql 7

SQL_Latin1_General_CP1_CI_AS NULL

Privacy - sql 2k

Latin1_General_CI_AS NULL

[

Ran this and got the error...any help?


select * from privacy..privacy_column p inner join ohm.information_schema.tables t ON p.table_name = t.table_name


Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.

Any ideas?

View 11 Replies View Related

Cannot Resolve Collation Conflict For Equal Operation

Aug 10, 2004

Hi, I´m having a critical problem.

I have two databases: persons and cars

In database persons I have a table
named persons_class1 as follows:
person_id char(13) not null [primary key]
name varchar(20) not null

In database cars I have a table
named cars_blue as follows:
car_id char(13) not null [primary key]
model varchar(20) not null
person_id char(13) not null

In my program the initial catalog is: cars
and I´m executing the following SQL:

select cars_blue.model,persons.dbo.persons_class1.name
from cars_blue,persons.dbo.persons_class1
where cars_blue.person_id=persons.dbo.persons_class1.per son_id

and the following error occurs:
Cannot resolve collation conflict for equal operation

I also tried like instead of = and the error is:
Cannot resolve collation conflict for like operation

what can I do?

Thanks in advance
Roland

View 8 Replies View Related

Cannot Resolve Collation Conflict For Equal To Operation.

Oct 19, 2004

Hi, I have this error when running a query:

Cannot resolve collation conflict for equal to operation.

but the situation is that I can run the query perfectly with one user (windows user) but using an standar user, I've got the error, as far as I know the collation feature applies to db's an objects not to users, what can I do to run this query with the standar user? both users have the same permission on the db. Below is the query attached.

Thank you
Gabriel

SELECT B.EmpSupervisorId, a.Info_ID as A_Info_ID,
ISNULL(a.Owner_SSO_ID,'') as A_Owner_SSO_ID,
ISNULL(a.Ref_SSO_ID,'') as A_Ref_SSO_ID,
ISNULL(a.Prev_Owner_SSO_ID,0) as A_Prev_Owner_SSO_ID,
ISNULL(a.MgmtTypeFlag,0) as A_MgmtTypeFlag,
ISNULL(a.CurrentStatusID, 1) as A_CurrentStatusID,
ISNULL(a.PrevStatusID,0) as A_PrevStatusID,
ISNULL(C.ConTypeOption, '') as C_ConTypeOption,
ISNULL(CAST(a.Last_Status_Update_Date AS VARCHAR),'') as A_Last_Status_Update_Date,
ISNULL(B.sfBUID,'0') as A_Bus_Group_ID, 0 AS A_Bus_Group_Seg_ID,
ISNULL(a.Organization,'') as A_Organization,
ISNULL(a.ContactName,'') as A_ContactName,
ISNULL(A.Title, '') as A_ContactTitle,
ISNULL(a.ContactComName,'') as A_ContactComName, ISNULL(a.Phone,'') as A_Phone,
ISNULL(a.NatureOfOppID,'') as A_NatureOfOppID, ISNULL(a.DealAmount,0) as A_DealAmount, ISNULL(CAST(a.Cust_Contacted_Date AS VARCHAR),'') A_Cust_Contacted_Date, ISNULL(CAST(a.Lead_Qualified_Date AS VARCHAR),'') A_Lead_Qualified_Date, ISNULL(CAST(a.Tran_Processed_Date AS VARCHAR),'') A_Tran_Processed_Date,
ISNULL(CAST(a.Quote_Accepted_Date AS VARCHAR),'') as A_Quote_Accepted_Date,
ISNULL(CAST(a.Approved_By_HFS_Date AS VARCHAR),'') as A_Approved_By_HFS_Date,
ISNULL(CAST(a.Funded_By_HFS_Date AS VARCHAR),'') as A_Funded_By_HFS_Date,
ISNULL(a.Lead_In_Amount,0) as A_Lead_In_Amount,
ISNULL(a.Cust_Contacted_Amount,0) as A_Cust_Contacted_Amount,
ISNULL(a.Lead_Qualified_Amount,0) as A_Lead_Qualified_Amount,
ISNULL(a.Tran_Processed_Amount,0) as A_Tran_Processed_Amount,
ISNULL(a.Quote_Accepted_Amount,0) as A_Quote_Accepted_Amount,
ISNULL(a.Approved_By_HFS_Amount,0) as A_Approved_By_HFS_Amount,
ISNULL(a.Funded_By_HFS_Amount,0) as A_Funded_By_HFS_Amount,
ISNULL(CAST(CreationDate AS VARCHAR),'') as A_CreationDate,
ISNULL(a.BusType,'') as A_BusType,
ISNULL(a.NonHFS_XLink_ContactName,'') as A_NonHFS_XLink_ContactName,
ISNULL(a.NonHFS_XLink_Bus_ID,'0') as A_NonHFS_XLink_Bus_ID,
ISNULL(a.Comments,'') as A_Comments, ISNULL(a.ExistCustomerID,'') as A_ExistCustomerID,
ISNULL(a.FinancialNeedID,'') as A_FinancialNeedID,
ISNULL(a.CampaignID,'') as A_CampaignID,
(GEC_HFS_CORE.dbo.GetBusinessDays(Last_Status_Upda te_Date, getdate())-1) as BusDateDiff, ISNULL(A.DealTypeID, '') as A_DealTypeID
FROM tblInformation a
LEFT OUTER JOIN GEC_HFS_LM_SSOInfo B ON A.Owner_SSO_ID = B.sfSSOID
LEFT OUTER JOIN tblContactType C ON a.Bustype = C.ConType_ID
WHERE a.CurrentStatusID NOT IN (9, 10, 11) AND
ISNULL(a.isNoChange,'NO') = 'NO' AND ISNULL(a.IsDeleted,'NO') = 'NO' AND
DATEDIFF(DAY, Last_Status_Update_Date, GETDATE())<> 0 AND
ISNULL(a.isNonHFS, 'NO') = 'NO'
ORDER BY A_Info_ID

View 3 Replies View Related

Show Values For Temperatures Not Equal To Or Above Threshold?

Feb 10, 2014

The below stored procedure is used to create a vertical benchmark line on the X-Axis which has a hour scale. I use the stored procedure to find out which temperature crosses or equals the threshold temperature (340), then plot the vertical benchmark line at the hour the first temperature is equal to or greater than 340 degrees and less than 1000 degrees.

The logic below works if the temperature is equal to or greater than 340 degrees and less than 1000 degrees. THE ISSUE is I have 8 temperatures if they don't cross the threshold of 340 degrees I need to set a default value for my vertical line. In other words if the temperature is 180 and my threshold is 340 then set my vertical line on the highest temperature close to 340.

I tried removing my Where clause (but then it breaks the logic for those temperatures that are equal to or greater than 340). I tried using Case When but this didn't give me what I want either. I tried UNION as well. All giving me results I don't want.

Here is what I am looking for:

This first example is one where there was a temperature that was equal to or greater than the threshold of 340 degrees. This is CORRECT

Code:
first_to_cross_thresholdAgeSampleDateDiffAgeovenStartTimestampAgeovenCompleteTimestamp
2014-02-04 19:24:003.3500002014-02-04 16:02:13.0002014-02-05 05:02:13.000

If 8 temperatures did not equal or cross the threshold then give me the hour of the highest temperature close to the threshold but do not return 0.

For Example:

temp1 92
temp2 108
temp3 0
temp4 284 <<< this is the closest to the threshold so give me the hour when this occurred.
temp5 2192 *Remember I can only count temperatures less than 1000 degrees. Anything above 1000 degrees mean there is nothing in the oven. So it is false/positive.
temp6 102
temp7 0
temp8 12

Code:

first_to_cross_thresholdAgeSampleDateDiffAgeovenStartTimestampAgeovenCompleteTimestamp
2014-02-05 00:30:001.3000002014-02-05 02:00:13.0002014-02-05 02:00:13.000

Code:
CREATE PROCEDURE [dbo].[AgeScoreCardThreshold_JJ_12232013]
-- Add the parameters for the stored procedure here
@LicenseNumber int = NULL,
@Lot varchar(50) = NULL

[code].....

View 3 Replies View Related

SQL Server 2012 :: Equal Comparison Of Strings

Jun 10, 2014

Is there a limit on the size of the strings on both sides of the '=' sign for string comparison? If I have two varchar(max) strings, will the comparison be done beyond 8,000 characters?

View 1 Replies View Related

SQL Server 2012 :: Combine One Value To Equal Two Values

Feb 13, 2015

I have a report that I am running in visual studio 2010, that gets its data from a few different stored procedures in a SQL 2012 Database. The variables are Date and Office Code. We currently have 6 different office codes. One of the Stored Procedures gets call information for each office. With the report we can select any single office or any combination of offices to compile data.

The problem I have is two of these share phone information, so when you select either one or both of them the same data gets returned. So for example we have office codes of AAAAA, BBBBB, CCCCC, DDDDD, EEEEE, and FFFFF. Now AAAAA, and BBBBB share phone information so if you select office code AAAAA, the phone info that is returned is for AAAAA, and BBBBB, and visa versa. So I am not sure how to accomplish that either in the report or in the stored procedure.

View 1 Replies View Related

Distinct Record Equal To 2 Values From Same Column

Dec 6, 2013

Distinct name that match both subjects (math, science) from classname in level 2 only. Not sure where to even start. Example table below:

name subject level
bob math 2
hank math 1
joe science 2
bob science 2
joe math 2
ben science 2
carl science 1

View 2 Replies View Related







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