Can We Put 2 Conditions In Inner Join

Oct 24, 2006

pls:
1/ can we do it this way:
inner join Table2 ON table1.fld1=table2.fld21 AND table1.fld12=table2.fld22
2/also:
what s the difference between join , iner join and left join
Thanks .

View 4 Replies


ADVERTISEMENT

JOIN Efficiency Using Multiple ON Conditions Versus WHERE Conditions

Jan 10, 2008

My question is fairly simple. When I join between two tables, I always use the ON syntax. For example:


SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId)


No problems there. However, if I then decide to further filter the selection based on some trait of the UserRole, I have two options: I can add the condition as a WHERE statement, or I can add the condition within the ON block.

--Version 1:

SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId)
WHERE

UserRoles.Active = 'TRUE'


-- Version 2

SELECT

*
FROM

Users

JOIN UserRoles

ON (Users.UserRoleId = UserRoles.UserRoleId

AND UserRoles.Active = 'TRUE')


So, the question is, which is faster/better, if either? The Query Analyzer shows the two queries have the exact same execution plan, which makes sense, since they're both joining the same tables. However, I'm wondering if adding the condition in the ON statement results in fewer rows the JOIN statement initially needs to join up, thus reducing the overall initial size of the results table before the WHERE conditions are applied.

So is there a difference, performance wise? I imagine that if Users had a thousand records, and UserRoles had 10 records, then the JOIN would create a cartesian product of the two tables, resulting in 10,000 records in the table before the WHERE conditions are applied. However, if only three of the UserRoles is set to Active, would that mean that the resulting table, before applying WHERE conditions, would only contain 3000 records?

Thanks for whatever information you can provide.

View 7 Replies View Related

Update Using Join Conditions

Apr 7, 2008

I have three table For example
Employee (Empid , Empname , Esal)
Department (Deptid , Deptname , empid )
Staff (staffid , Staffname , Empid)

It is just example
how can i update Empname whose staffid =1 accor to staffid)
using Join Conditions :- Pls help me out ..
or
how to update data using JOIN Conditions







Yaman

View 2 Replies View Related

Left Outer Join With Two Conditions

Feb 11, 2008

Dear All,
I am not sure this is the correct forum for my issue, still im going on.....
I have a sql query which uses left outer join and where clause
select I.ir_id, I.ir_title,I. ir_b_type, I.ir_label, I.ir_createdby, I.ir_createdon, B.mstatus, B.bstatus from BR_IR I left outer join BR_BS Bon I.ir_id = B.bs_ir_id where I.ir_tr_rel = 'V03.03.06' order by I.ir_id desc
this works perfect. Now I want to exclude few records from the result on condn 'bstatus <> 'Yes'.  So now my query will be
select I.ir_id, I.ir_title, I.ir_b_type, I.ir_label, I.ir_createdby, I.ir_createdon, B.mstatus, B.bstatus from BR_IR  I left outer join BR_BS Bon I.ir_id = B.bs_ir_id where I.ir_tr_rel = 'V03.03.06' and B.bstatus <> 'Yes'order by I.ir_id desc
The below query will not return the left table records which doesnt have a matching one in the right table. It works something like inner join.
Could you please anyone tell me,where I am wrong?
Thanks,
Girija

View 3 Replies View Related

Left Outer Join And Conditions

Mar 17, 2008

I am using Transact-Sql 2005, and I'm trying to do a left outer join, including only certain accounts. The account number is x-xxx-xxxx-xxxx.
I want to include only accounts where the last 4 digits are > 7149, and the first 5 digits are between 2-110 and 2-999, or are equal to 8-001.

This is my code, based on 2 temporary tables I have previously populated:

select
b.gl7accountsid,
b.accountnumber,
t.description,
t.category,
t.postdate,
t.poststatus,
t.transactiontype,
t.transamount,
coalesce(t.transamount,0) as TransactionAmount,
t.encumbrancestatus,
t.gl7fiscalperiodsid,
b.budamount,
b.gl7budgetscenariosid

from
#budgets b
left outer
join
#transactions t
on t.accountnumber=b.accountnumber
And right(t.accountnumber,4) > 7149
and (left(t.accountnumber,5) between '2-110' and '2-999' or left(t.accountnumber,5)='8-001')
order by b.accountnumber


I keep getting accounts with the last 4 digits > 7149, and also accounts whose numbers don't fall into the desired group of the first 5 digits.

I'm fairly new to SQL, and I'm thinking there must be a way to do what I want to do, but I don't know what it is.

Can anyone help?

Thanks very much ,

Sue

View 5 Replies View Related

Adding Conditions In The ON Clause Of A JOIN

Feb 12, 2008

Hi Faculties,I have two queries which give me the same output.-- Query 1SELECT prod.name, cat.nameFROM products prod INNER JOIN categories catON prod.category_id = cat.idWHERE cat.id = 1;-- Query 2SELECT prod.name, cat.nameFROM products prod INNER JOIN categories catON prod.category_id = cat.id AND cat.id = 1;The first query uses the WHERE clause and the second one has all theconditions in the ON clause. Is there anthing wrong with the secondapproach in terms of performance? Please suggest.Thanks in advanceJackal

View 6 Replies View Related

Returning Results Left Outer Join With Conditions

Sep 21, 2006

I have a SELECT Statement that I am using that is pulling from two tables.  There won't always be results in the second table so I made a LEFT OUTER JOIN.  The problem I am having is that I need to have three conditions in there:WHERE (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE()))The two conditions from the request table are causing the entire query to return NULL as the value.  I need help trying get a value whether or not there are any results in the request table.Here is the full select statement:SELECT (SELECT SUM(ISNULL(request.request_duration, '0')) AS Expr1
FROM employee LEFT OUTER JOIN
request AS request ON employee.emp_id = request.emp_id
WHERE (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE()))
AS dayspending
FROM employee AS employee_1 LEFT OUTER JOIN
request AS request_1 ON employee_1.emp_id = request_1.emp_id
WHERE (employee_1.emp_id = @emp_id)
GROUP BY employee_1.emp_id, employee_1.emp_begin_accrual, employee_1.emp_accrual_rate, employee_1.emp_fname, employee_1.emp_minitial,
employee_1.emp_lname 

View 2 Replies View Related

SQL Server 2008 :: Join Another Table But With Select Conditions?

Mar 24, 2015

I have this sql....

Select
DISTINCT p.dbPatID, p.dbpatfirstname, p.dbPatLastName,
s.dbSchTypeCnt as SchDetailType, t.dbSchTypeCnt as SchTypeType,
ISNULL(r.dbStatusDesc, 'No Ref') AS dbStatusDesc,
ISNULL(t.dbSchTypeCode, 'No Ref') AS dbSchTypeCode,
ISNULL(t.dbSchTypeDesc, 'No Ref') AS dbSchTypeDesc,

[code]....

however, I only want the lastest a.dbPatApptTime and only when a.dbPFStatus = 1 and a.ClientRef = 'EPS'

So the stand alone sql could be....

Select Top(1) dbPatApptTime as LastVisitDate, dbSchTypeDesc as LastVisitDesc
from appointments
where dbPFStatus = 1 and clientref = 'EPS'
order by dbPatApptTime desc

I'm just not sure how to incorporate that into my sql or whether there is a better way,

View 9 Replies View Related

Can Any One Tell Me The Difference Between Cross Join, Inner Join And Outer Join In Laymans Language

Apr 30, 2008

Hello

Can any one tell me the difference between Cross Join, inner join and outer join in laymans language

by just taking examples of two tables such as Customers and Customer Addresses


Thank You

View 1 Replies View Related

Conditions, Expressions

Aug 7, 2007

I have a table
CREATE TABLE [dbo].[CmnLanguage]( [Id] [char](2) NOT NULL CONSTRAINT PkCmnLanguage_Id PRIMARY KEY, [EnglishName] [varchar](26) NOT NULL, [NativeName] [nvarchar](26) NOT NULL, [DirectionType] [smallint] NOT NULL, [IsVisible] [bit] NOT NULL, [CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(), [ModifiedDateTime] [datetime] NULL)
We will use these 3 queries
select * from CmnLanguage where IsVisible = 0select * from CmnLanguage where IsVisible = 1select * from CmnLanguage
I want to make a method which handles these queries.
But at the back end on Stored Procedures
We have to write 3 queries
Which I don't want to do.
I want to minimize the queries and conditions
and want to just write one for these 3
Can any one do it?

View 2 Replies View Related

HELP....&#34;OR&#34; Conditions In SQL Statements

May 31, 2000

Folks,

I'm having some real problems using the OR condition in a very simple SQL statement and could use your help or insight on where the problem lies, or perhaps a workaround.

I have a large flat table in a SQL 7 database with 10 million + records called "HISTORY". I have not installed either service pack 1 or 2. I'm attempting to run a query that references the following four fields which are all non-clustered keys:

EQUIPMENT_NO TEXT 12
CHASSIS_IN TEXT 12
CHASSIS TEXT 12
SVC_DATE_TIME SMALLDATETIME

Here's the SQL statement:

SELECT * FROM HISTORY WHERE (HISTORY.EQUIPMENT_NO = 'XYZ123' OR HISTORY.CHASSIS = 'XYZ123' OR HISTORY.CHASSIS_IN = 'XYZ123') AND SVC_DATE_TIME >= '01/15/00 00:00:00 AM' AND SVC_DATE_TIME <= '02/28/00 23:59:59 PM'
ORDER BY EQUIPMENT_NO

This query takes 11 min. 5 sec. inder the Query Analyzer and ultimately returns the 31 desired records.

If you remove the SVC_DATE_TIME criteria, about 350 records are returned in a matter of seconds. I've also tried variations on the date syntax such as '01/15/00', etc. with no change in the amount of time to execute.

Other queries such as a simple AND condition combining EQUIPMENT_NO and SVC_DATE_TIME are snappy.

Are there known problems/bugs with "OR" conditions in queries that anyone is aware of, particularly with parentheses; am I composing this query incorrectly? Is there some alternate syntax that would work as expected? I can't see where the query shouldn't execute quickly as expected, particularly with all indexed fields involved. I'm stumped! Lend me your expertise. Thanks much.

Clark R. Farabaugh, Jr.
Financial Systems Analyst
VIT
Norfolk, VA

View 8 Replies View Related

Regarding Aggregate Conditions ..

Nov 29, 2007

Hai frns small help needed.


I have a table called sample and i have the following requirement. i.e i need sum(credit) group by ssn no.

One special condition is as follows:


For each distinct ssn if "flag" has the same CX value,then out of all the records with the same CX value, the highest "credit" value is added to the sum for that "ssn" and the rest are ignored.
If while adding "credit" to the sum and if "credit" value is equal to zero then "sum" value is used for summing else "credit" value is used.
Can any one help me out in trying this logic. I have tried but i could'nt able embed the conditions inbetween the Sql statetment.

Here is the query is used

select * from sample

idssncreditflagsem
11010C90
21014C93
31014.5C92
41013.5C11
51024.2C33
61030C12


select ssn,flag,sum(case credit when 0 then sem else credit end) as sum from sam2
group by ssn,flag


ssn flag sum_val
101C13.5
103C12.0
102C34.2
101C98.5

The above output is wrong one.


Expected output

101 4.5+3.5=8.0
102 4.2
103 2.0


Any help would be appreciated

Regards,

View 5 Replies View Related

Where Clause With Conditions

Mar 19, 2012

Code:
Drop table #table
Drop table #table_with_groupid
-- Prepare test data
CREATE TABLE #table
([Admissions_key] bigint NOT NULL PRIMARY KEY,
MRN nvarchar(10) NOT NULL,

[Code] ....

How can I compare dates with conditions. I only want to Mark C where the difference between adm_datetime and prevsep_datetime is <= 1 otherwise E as well

where datediff(MINUTE,tg.adm_datetime,tg.pre_sep_date)< =1 ??

is it correct ? where should I put this to implement correctly ?

View 1 Replies View Related

Case When Conditions

Oct 14, 2014

I have the following:

MAX(Vernum) AS test,
MAX(Case WHEN A2.AttrID = 2 AND A2.DefID = 10057945 THEN A2.ValStr END) AS TYPE_DOCUMENT

This works not perfect. In many cases I have more then one row and my query takes the max value of column Valstr. Thats is not exactly what I want. I'd like to have the value of Column Valstr of the row where column Vernum has the maximum value.

I've tried many things like:

MAX(Vernum) AS test,
MAX(Case WHEN A2.AttrID = 2 AND A2.DefID = 10057945 AND A2.Vernum=test THEN A2.ValStr END) AS TYPE_DOCUMENT

OR

MAX(Vernum) AS test,
MAX(Case WHEN A2.AttrID = 2 AND A2.DefID = 10057945 AND A2.Vernum=MAX(A2.Vernum) THEN A2.ValStr END) AS TYPE_DOCUMENT

View 1 Replies View Related

Multiple AND Conditions

Sep 12, 2006

Hi All.

Is there a way to have multiple AND conditions on the same field in a database.

EXAMPLE

SELECT * FROM tbl
WHERE field1 = 1 AND field1 = 2 AND field1 = 5

Thanks

View 9 Replies View Related

Procedure Has Many If Else Conditions

Jul 16, 2007

ALTER PROC usp_t_insup_cpa1

@Idint,
@SupervisorIdint,
@BookmarkerIdint,
@PreparerIdint,
@FirmNovarchar(20),
@FirmNamevarchar(30),
@FirstNamevarchar(20),
@MiddleNamevarchar(20),
@LastNamevarchar(20),
@TaxYearvarchar(20),
@TaxSoftwarevarchar(20),
@HomePhonevarchar(20),
@WorkPhonevarchar(20),
@Faxvarchar(20),
@PrimaryEmailvarchar(30),
@SecondaryEmailvarchar(30),
@CountryIdint,
@Statevarchar(20),
@Zipcodevarchar(20),
@Statusint,
@OperatorChar(1) = '',
@RESULTINT OUTPUT
-------------------------
AS

IF @Operator = 'I'
BEGIN

IF NOT EXISTS(SELECT * FROM CPA WHERE PrimaryEmail=@PrimaryEmail or PrimaryEmail=@SecondaryEmail or SecondaryEmail=@PrimaryEmail or SecondaryEmail=@SecondaryEmail )
BEGIN
--select * from o_login
Begin transaction InsCPA

INSERT INTO CPA(SupervisorId,BookmarkerId,PreparerId,FirmNo,FirmName,FirstName,MiddleName,LastName,TaxYear,TaxSoftware,HomePhone,WorkPhone,Fax,PrimaryEmail,SecondaryEmail,CountryId,State,Zipcode,Status)
VALUES(@SupervisorId,@BookmarkerId,@PreparerId,@FirmNo,@FirmName,@FirstName,@MiddleName,@LastName,@TaxYear,@TaxSoftware,@HomePhone,@WorkPhone,@Fax,@PrimaryEmail,@SecondaryEmail,@CountryId,@State,@Zipcode,@Status)

--Error handling
IF @@ERROR <> 0
BEGIN
-- Returns 0 to the calling program to indicate failure.
ROLLBACK TRAN InsCPA
SET @RESULT = 0
END
ELSE
BEGIN
--DECLARE @ID1 INTEGER
-- Returns 1 to the calling program to indicate success.
COMMIT TRAN InsCPA
SET @RESULT = 1
END
END
ELSE
BEGIN
-- Return 2 to the calling program to indicate record already exists.
set @RESULT = 2
END
END

ELSE IF(@Operator='U')
BEGIN
declare @pemail as varchar(30)
declare @semail as varchar(30)
declare @firm as varchar(20)
select @pemail=PrimaryEmail,@semail=SecondaryEmail,@firm=FirmNo from CPA WHERE Id = @Id
--select * from CPA

if(@pemail=@PrimaryEmail) or(@semail=@PrimaryEmail)--or((@semail=@SecondaryEmail)and (@semail=@PrimaryEmail)))
begin

print 'prim1'
if(@semail=@SecondaryEmail)or (@pemail=@SecondaryEmail)
begin
print 'sec1'
if(@firm=@FirmNo)
begin
print'firm'
BEGIN TRANSACTION UpdateCPA
UPDATE CPA
SET SupervisorId=@SupervisorId,
BookmarkerId=@BookmarkerId,
PreparerId=@PreparerId,
FirmNo=@FirmNo,
FirmName=@FirmName,
FirstName=@FirstName,
MiddleName=@MiddleName,
LastName=@LastName,
TaxYear=@TaxYear,
TaxSoftware=@TaxSoftware,
HomePhone=@HomePhone,
WorkPhone=@WorkPhone,
Fax=@Fax,
PrimaryEmail=@PrimaryEmail,
SecondaryEmail=@SecondaryEmail,
CountryId=@CountryId,
State=@State,
Zipcode=@Zipcode,
Status=@Status
WHERE Id = @Id
UPDATE EMPLOYEE
SET FirmNo=@FirmNo
WHERE FirmNo=@firm


IF @@ERROR <> 0
BEGIN
-- Returns 0 to the calling program to indicate failure.
ROLLBACK TRAN UpdateCPA
SET @RESULT = 0

END
ELSE
BEGIN
-- Returns 1 to the calling program to indicate success.
print'1'
COMMIT TRAN UpdateCPA
SET @RESULT = 1

END
END
else
begin
IF NOT EXISTS(SELECT * FROM CPA WHERE FirmNo=@FirmNo)
BEGIN
print'fd'
BEGIN TRANSACTION UpdateCPA
UPDATE CPA
SET SupervisorId=@SupervisorId,
BookmarkerId=@BookmarkerId,
PreparerId=@PreparerId,
FirmNo=@FirmNo,
FirmName=@FirmName,
FirstName=@FirstName,
MiddleName=@MiddleName,
LastName=@LastName,
TaxYear=@TaxYear,
TaxSoftware=@TaxSoftware,
HomePhone=@HomePhone,
WorkPhone=@WorkPhone,
Fax=@Fax,
PrimaryEmail=@PrimaryEmail,
SecondaryEmail=@SecondaryEmail,
CountryId=@CountryId,
State=@State,
Zipcode=@Zipcode,
Status=@Status
WHERE Id = @Id
UPDATE EMPLOYEE
SET FirmNo=@FirmNo
WHERE FirmNo=@firm
IF @@ERROR <> 0
BEGIN
-- Returns 0 to the calling program to indicate failure.
ROLLBACK TRAN UpdateCPA
SET @RESULT = 0

END
ELSE
BEGIN
-- Returns 1 to the calling program to indicate success.
print'1'
COMMIT TRAN UpdateCPA
SET @RESULT = 1

END
end
ELSE
BEGIN
-- Returns 1 to the calling program to indicate success.
print'4'
--COMMIT TRAN UpdateCPA
SET @RESULT = 4

END
end
end
else
begin
IF NOT EXISTS(SELECT * FROM CPA WHERE PrimaryEmail=@SecondaryEmail or SecondaryEmail=@SecondaryEmail)
BEGIN
if(@firm=@FirmNo)
begin
BEGIN TRANSACTION UpdateCPA
UPDATE CPA
SET SupervisorId=@SupervisorId,
BookmarkerId=@BookmarkerId,
PreparerId=@PreparerId,
FirmNo=@FirmNo,
FirmName=@FirmName,
FirstName=@FirstName,
MiddleName=@MiddleName,
LastName=@LastName,
TaxYear=@TaxYear,
TaxSoftware=@TaxSoftware,
HomePhone=@HomePhone,
WorkPhone=@WorkPhone,
Fax=@Fax,
PrimaryEmail=@PrimaryEmail,
SecondaryEmail=@SecondaryEmail,
CountryId=@CountryId,
State=@State,
Zipcode=@Zipcode,
Status=@Status
WHERE Id = @Id
UPDATE EMPLOYEE
SET FirmNo=@FirmNo
WHERE FirmNo=@firm

IF @@ERROR <> 0
BEGIN
-- Returns 0 to the calling program to indicate failure.
ROLLBACK TRAN UpdateCPA
SET @RESULT = 0

END
ELSE
BEGIN
-- Returns 1 to the calling program to indicate success.
print'1'
COMMIT TRAN UpdateCPA
SET @RESULT = 1

END
END
else
begin
IF NOT EXISTS(SELECT * FROM CPA WHERE FirmNo=@FirmNo)
BEGIN
BEGIN TRANSACTION UpdateCPA
UPDATE CPA
SET SupervisorId=@SupervisorId,
BookmarkerId=@BookmarkerId,
PreparerId=@PreparerId,
FirmNo=@FirmNo,
FirmName=@FirmName,
FirstName=@FirstName,
MiddleName=@MiddleName,
LastName=@LastName,
TaxYear=@TaxYear,
TaxSoftware=@TaxSoftware,
HomePhone=@HomePhone,
WorkPhone=@WorkPhone,
Fax=@Fax,
PrimaryEmail=@PrimaryEmail,
SecondaryEmail=@SecondaryEmail,
CountryId=@CountryId,
State=@State,
Zipcode=@Zipcode,
Status=@Status
WHERE Id = @Id
UPDATE EMPLOYEE
SET FirmNo=@FirmNo
WHERE FirmNo=@firm
IF @@ERROR <> 0
BEGIN
-- Returns 0 to the calling program to indicate failure.
ROLLBACK TRAN UpdateCPA
SET @RESULT = 0

END
ELSE
BEGIN
-- Returns 1 to the calling program to indicate success.
print'1'
COMMIT TRAN UpdateCPA
SET @RESULT = 1

END
end
ELSE
BEGIN
-- Returns 1 to the calling program to indicate success.
print'44'
--COMMIT TRAN UpdateCPA
SET @RESULT = 4

END
end
/*
--select * from o_login
Begin transaction InsCPA

UPDATE CPA
SET SupervisorId=@SupervisorId,
BookmarkerId=@BookmarkerId,
PreparerId=@PreparerId,
FirmNo=@FirmNo,
FirmName=@FirmName,
FirstName=@FirstName,
MiddleName=@MiddleName,
LastName=@LastName,
TaxYear=@TaxYear,
TaxSoftware=@TaxSoftware,
HomePhone=@HomePhone,
WorkPhone=@WorkPhone,
Fax=@Fax,
PrimaryEmail=@PrimaryEmail,
SecondaryEmail=@SecondaryEmail,
CountryId=@CountryId,
State=@State,
Zipcode=@Zipcode,
Status=@Status
WHERE Id = @Id
--Error handling
IF @@ERROR <> 0
BEGIN
-- Returns 0 to the calling program to indicate failure.
ROLLBACK TRAN InsCPA
SET @RESULT = 0
END
ELSE
BEGIN
--DECLARE @ID1 INTEGER
-- Returns 1 to the calling program to indicate success.
print'11'
COMMIT TRAN InsCPA
SET @RESULT = 1
END*/
END
ELSE
BEGIN
print 'sec same'
-- Return 2 to the calling program to indicate record already exists.
set @RESULT = 3
END
end
end
else
begin
IF NOT EXISTS(SELECT * FROM CPA WHERE PrimaryEmail=@PrimaryEmail or SecondaryEmail=@PrimaryEmail)
BEGIN
/*--select * from o_login
Begin transaction InsCPA

UPDATE CPA
SET SupervisorId=@SupervisorId,
BookmarkerId=@BookmarkerId,
PreparerId=@PreparerId,
FirmNo=@FirmNo,
FirmName=@FirmName,
FirstName=@FirstName,
MiddleName=@MiddleName,
LastName=@LastName,
TaxYear=@TaxYear,
TaxSoftware=@TaxSoftware,
HomePhone=@HomePhone,
WorkPhone=@WorkPhone,
Fax=@Fax,
PrimaryEmail=@PrimaryEmail,
SecondaryEmail=@SecondaryEmail,
CountryId=@CountryId,
State=@State,
Zipcode=@Zipcode,
Status=@Status
WHERE Id = @Id
--Error handling
IF @@ERROR <> 0
BEGIN
-- Returns 0 to the calling program to indicate failure.
ROLLBACK TRAN InsCPA
SET @RESULT = 0
END
ELSE
BEGIN
--DECLARE @ID1 INTEGER
-- Returns 1 to the calling program to indicate success.
print'111'
COMMIT TRAN InsCPA
SET @RESULT = 1
END*/
if(@firm=@FirmNo)
begin
BEGIN TRANSACTION UpdateCPA
UPDATE CPA
SET SupervisorId=@SupervisorId,
BookmarkerId=@BookmarkerId,
PreparerId=@PreparerId,
FirmNo=@FirmNo,
FirmName=@FirmName,
FirstName=@FirstName,
MiddleName=@MiddleName,
LastName=@LastName,
TaxYear=@TaxYear,
TaxSoftware=@TaxSoftware,
HomePhone=@HomePhone,
WorkPhone=@WorkPhone,
Fax=@Fax,
PrimaryEmail=@PrimaryEmail,
SecondaryEmail=@SecondaryEmail,
CountryId=@CountryId,
State=@State,
Zipcode=@Zipcode,
Status=@Status
WHERE Id = @Id

UPDATE EMPLOYEE
SET FirmNo=@FirmNo
WHERE FirmNo=@firm

IF @@ERROR <> 0
BEGIN
-- Returns 0 to the calling program to indicate failure.
ROLLBACK TRAN UpdateCPA
SET @RESULT = 0

END
ELSE
BEGIN
-- Returns 1 to the calling program to indicate success.
print'1'
COMMIT TRAN UpdateCPA
SET @RESULT = 1

END
END
else
begin
IF NOT EXISTS(SELECT * FROM CPA WHERE FirmNo=@FirmNo)
BEGIN
BEGIN TRANSACTION UpdateCPA
UPDATE CPA
SET SupervisorId=@SupervisorId,
BookmarkerId=@BookmarkerId,
PreparerId=@PreparerId,
FirmNo=@FirmNo,
FirmName=@FirmName,
FirstName=@FirstName,
MiddleName=@MiddleName,
LastName=@LastName,
TaxYear=@TaxYear,
TaxSoftware=@TaxSoftware,
HomePhone=@HomePhone,
WorkPhone=@WorkPhone,
Fax=@Fax,
PrimaryEmail=@PrimaryEmail,
SecondaryEmail=@SecondaryEmail,
CountryId=@CountryId,
State=@State,
Zipcode=@Zipcode,
Status=@Status
WHERE Id = @Id

UPDATE EMPLOYEE
SET FirmNo=@FirmNo
WHERE FirmNo=@firm
IF @@ERROR <> 0
BEGIN
-- Returns 0 to the calling program to indicate failure.
ROLLBACK TRAN UpdateCPA
SET @RESULT = 0

END
ELSE
BEGIN
-- Returns 1 to the calling program to indicate success.
print'1'
COMMIT TRAN UpdateCPA
SET @RESULT = 1

END
end
ELSE
BEGIN
-- Returns 1 to the calling program to indicate success.
print'2'
--COMMIT TRAN UpdateCPA
SET @RESULT = 2

END
end
END
ELSE
BEGIN
print 'prim same'
-- Return 2 to the calling program to indicate record already exists.
set @RESULT = 2
END
end
end

Above procedure has many if else conditions
Is there any way to write
procs other than this process

Malathi Rao

View 1 Replies View Related

Playing About With The AND And OR Conditions

Jul 26, 2007

Hi folks, basically I'm looking for this sort of structure

WHERE (caseA AND caseB) OR (caseC AND caseD) OR (CaseA AND caseD)

but I can't seem to be able to group the AND conditions together any time I try put brackets in SQL Server Enterprise manager removes them on me,

any help would be great,

thanks

View 1 Replies View Related

Where Conditions, Encryption

May 5, 2007

1. Are stored procedures WITH ENCRYPTION slower than the ones withoutencryption?2. Should i put most restrictive conditions first or last in WHERE? Inwhich order does MSSQL execute conditions? Or MSSQL determents whatwould be best and does not bother with the way i sorted conditions?for example:SELECT *FROM [users]WHERE[user_id] = 1 AND[baned] = 0Is "[user_id] = 1" or "[baned] = 0" going to be executed first?

View 2 Replies View Related

Count If Conditions Met

Jul 20, 2005

I am trying to do a summary SQL query. I have 3 fields. If one filed isnull and the other is not null, I want to count how many records thereare. I also want to count the opposite way then count both fields ifthey are both not null. Can I do this within the same query? Helpappreciated.Thanks,Steve*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 2 Replies View Related

Not All Where Conditions Required

Mar 17, 2008

I am trying to do a select with the closest match.



Code Snippet


CREATE TABLE [#Person]

(

[PersonId] tinyint IDENTITY(1,1) NOT NULL

,[Height] char(2) NOT NULL

,[Weight] char(3) NOT NULL

,[Age] varchar(3) NOT NULL

,[HairColor] varchar(7) NOT NULL

,[EyeColor] varchar(7) NOT NULL

);


INSERT INTO [#Person] ([Height],[Weight],[Age],[HairColor],[EyeColor])

VALUES ('71', '150', '23', 'Brown', 'Blue');

INSERT INTO [#Person] ([Height],[Weight],[Age],[HairColor],[EyeColor])

VALUES ('70', '190', '22', 'Blonde', 'Brown');

INSERT INTO [#Person] ([Height],[Weight],[Age],[HairColor],[EyeColor])

VALUES ('69', '140', '20', 'Black', 'Brown');

INSERT INTO [#Person] ([Height],[Weight],[Age],[HairColor],[EyeColor])

VALUES ('65', '150', '21', 'Brown', 'Green');


SELECT * FROM [#Person]

WHERE

([Height] > '66' AND [Height] < '72')

AND ([Weight] > '150' OR [Weight] < '180')

AND ([Age] > '20' OR [Age] < '25')

AND ([HairColor] IN ('Brown', 'Blonde', 'Black'))

AND ([EyeColor] IN ('Blue', 'Brown', 'Green'));


DROP TABLE [#Person];




This simple example works great. But what if I wanted everybody who met 4 of the 5 conditions? I tried to think of a counting solutions, and then order by the count but I could quite get there. Any help would be greatly appreciated. My live data is actual sales records with 20 some odd record types. I am hoping any solutions I find will scale well.

View 8 Replies View Related

How Do I Implement If Then Else Conditions

May 28, 2007

Hi All,



I'm very new to Integration Services. Self Learned this ETL tool based on my prior ETL tool knowledge.



Can you tell me how do write IF Then Else conditions for every column in my source and redirect to a single output?





Hey correction here ... i'm using flat file as my source





Thanks in Advance,

Suresh N

View 7 Replies View Related

Conditions With Trigger

Dec 5, 2007

Hi,

I want to create a trigger that that is invoked when a certain condition is fulfilled. For instance:

I have 2 tables, PS and US with similar table structure. PS table contains a column called 'status'. Whenever the status column is updated from 2 to either 23, 24, 25 or 26, a new row, which contains the same data within the updated row of PS, will be inserted into the table US.

Can this be done? Thanks in advance.

View 4 Replies View Related

Commiting A Trigger Under Certain Conditions

Sep 18, 2007

 I have a sitemapcache that caches nodes with a sqlcachedependency. Everything is working fine but one thing. Every time you visit a forum, the viewcount is changed, therefore raising the trigger and dropping my cache object. How do i make it so that the trigger is only fired if I update the Title or Description field?  ALTER TRIGGER [dbo].[sp_Forums_Topics_AspNet_SqlCacheNotification_Trigger] ON [dbo].[sp_Forums_Topics] FOR INSERT, UPDATE,DELETE AS BEGINSET NOCOUNT ONEXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'sp_Forums_Topics'
END  

View 1 Replies View Related

[SQL] Alias Not Working In Conditions

May 28, 2008

Hi,I have some SQL statements like that SELECT ID, getData(ID) as Alias
FROM table
WHERE Alias = value

GO

SELECT ID, count(something) as amount
FROM table
GROUP BY amount  Why does the alias not work?? In "order by" clause it does

View 2 Replies View Related

Using IF ElSE Like Conditions In An Select Statement

Mar 17, 2006

Hi All,
This is my problem. I need the out put of a sql select statement to be "true" or "false" depending on the actual columns value is positive or negative. Does any one how to do this.
Thanks in advance,
-VJ

View 1 Replies View Related

Select Max Value From Prior Where Conditions

Apr 7, 2006

I can't figure this out for the life of me. Wanted to know if it's possible to select certain date conditions in a query, then later reference those conditions and to only select the max of them.

I need to do this dynamically as I do not know what the max value is. I've provided an example below:

Select var1
From table1
where
(
(Date1 = '11/30/2005')
OR
(Date1 = '12/31/2005')
)
and Date1 = (Max of previously selected values e.g. '12/31/2005')

What I can't figure out is how to dynamically retrieve the max of 11/31/2005 and 12/31/2005. Any ideas are greatly appreciated.

View 1 Replies View Related

Add A New Column To A Table With Conditions?

May 28, 2013

i want to add a column to a table with conditions below :

1- it has a default value=0

2- this column can get null value

3- no constraint should be created

is it possible ?

View 3 Replies View Related

Order For Conditions To Be Processed

Mar 3, 2004

what is order in which conditions are processed for sql query i.e for
select * from table1, table2 where cond1 and cond2 and cond3 which condition will be processed first (i.e. for optimination purpose condition cutting down max no. of row shud be placed first or last?)

View 3 Replies View Related

Accumulating A Value Based On Certain Conditions

Jul 21, 2015

I have an issue accumulating a value based on certain conditions:

I need to sum up the days column as you can see table below. The sum is based on the StartNewFlag.

When the flag is 1, I need to start a new sum from zero, else I need to accumulate to the previous rows.

CREATE TABLE #foo
(
ItemID int,
StartDate datetime,
EndDate datetime,
StartNewFlag bit,

[Code] ....

My Results must look like one of the following tables (either one would work, however the second one is preferred)

Result 1:
ItemID StartDate EndDate StartNewFlag Days TotalDays
1 2015-07-01 00:00:00.000 2015-07-05 00:00:00.000 1 4 5
1 2015-07-08 00:00:00.000 2015-07-09 00:00:00.000 0 1 5
1 2015-07-11 00:00:00.000 2015-07-15 00:00:00.000 1 4 11
1 2015-07-18 00:00:00.000 2015-07-21 00:00:00.000 0 3 11
1 2015-07-22 00:00:00.000 2015-07-26 00:00:00.000 0 4 11

OR

Result 2:
ItemID EndDate TotalDays
1 2015-07-09 00:00:00.000 5
1 2015-07-26 00:00:00.000 11

View 5 Replies View Related

T-SQL (SS2K8) :: Add A Flag When Certain Conditions Are Met?

Aug 27, 2015

I have a table where when an employee works LEAVE1 and LEAVE2 paycodes on the same day then in the new column called 'FLAG' we need to put a 1 for LEAVE1 and a 2 for LEAVE2, when these paycodes are not there on the same day then flag will be NULL as for other paycodes.

Tried using the CASE statements but it always puts a 1 or 2 for the respective paycodes regardless of the fact whether they were together on the same day or not.

WITH SampleData (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS, PAYCODE) AS
(
SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3', 'REG1' UNION ALL
SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '09:00','13:00','4','REG2' UNION ALL
SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '09:00','13:00','4','LEAVE1' UNION ALL
SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '14:00','16:00','2', 'LEAVE2'UNION ALL
SELECT 1234,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'LEAVE1'UNION ALL
SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'REG1'UNION ALL
SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '10:00','12:00','2','LEAVE2'
)

[code].....

Current Results

PersonTransactDate STARTDATEEND_DATEIN_PUNCH OUT_PUNCH HOURS PAYCODE
123408/03/2015 08/03/201508/03/201506:00 13:00 3 REG1
123408/03/2015 08/03/201508/03/201506:00 13:00 4 REG2
123408/04/2015 08/04/201508/04/201509:00 16:00 4 LEAVE1
123408/04/2015 08/04/201508/04/201509:00 16:00 2 LEAVE2
123408/05/2015 08/05/201508/05/201508:00 09:00 1 LEAVE1
455308/05/2015 08/05/201508/05/201508:00 12:00 2 LEAVE2
455308/05/2015 08/05/201508/05/201508:00 12:00 1 REG1

Desired Results

PersonTransactDate STARTDATEEND_DATEIN_PUNCH OUT_PUNCH HOURS PAYCODE FLAG
123408/03/2015 08/03/201508/03/201506:00 13:00 3 REG1 NULL
123408/03/2015 08/03/201508/03/201506:00 13:00 4 REG2 NULL
123408/04/2015 08/04/201508/04/201509:00 16:00 4 LEAVE1 1
123408/04/2015 08/04/201508/04/201509:00 16:00 2 LEAVE2 2
123408/05/2015 08/05/201508/05/201508:00 09:00 1 LEAVE1 NULL
455308/05/2015 08/05/201508/05/201508:00 12:00 2 LEAVE2 NULL
455308/05/2015 08/05/201508/05/201508:00 12:00 1 REG1 NULL

View 2 Replies View Related

Calculate And Save With WHEN Conditions

Aug 31, 2013

Meed to complete this procedure. It fails in the WHEN condition

ALTER PROCEDURE [dbo].[sp_TranpostSupport_Xuat_Nhap_ToTal]

@NO_ AS NVARCHAR(4000),
@FromDate DATETIME,
@ToDate DATETIME,
@TransInvoiceID INT
AS
SELECT x.NO_,x.NAMES,x.ZIPCODE,x.FromDate,x.ToDate,x.Total450c, n.Total450k,

[Code] ....

View 1 Replies View Related

Extraction Of Data On Conditions

Sep 24, 2014

I have a table having code
code
12345678
21345678
45789612
12345678
21345678
21345678
12347586

I want result on the basis of first 2 digit the repeatation of code is count as 1.
code1 code2 count
12 34 2
21 34 1
45 78 1

View 2 Replies View Related

Calculated Field And Conditions

Nov 3, 2014

I have a Vehicle/Driver Inspection Database and I'm trying to create a Traffic Violation Point System, its a system when drivers commit certain traffic offences they will incur driving-offence points.

System: The points for violations that all occurred within the last 12 months of ONE ANOTHER are added together to calculate point total. If the accumulated points is reached 14 points driver is suspended the points will be REMOVED or minus 14 points after the suspension has been served.

Example:

Date of Offence - Number of Points
Aug. 6, 2013 - 6
Feb. 4, 2014 - 4
Apr. 25, 2014 - 4
- 2
May 8, 2014 - 2

Explanation:

On April 25, 2014 two offences incurred and from (Aug 6, 2013 - Apr. 25 2014) 14 points have accumulated (6 + 4 + 4) and suspension is carried out then 14 points is removed. The 2nd offence on Apr. 25, 2014 will be carried forward and added to May 8, 2014 points (total 4 points).

Below are DB Tables:

tbl_Driver
DriverID DriverLicense FirstName LastName

tbl_Event
EventID EventTIme DriverID CarID

tbl_EventViolation
EventViolatinID EventID ViolationID Status (1 = no, 2 = yes radio buttons)

tbl_Violation
ViolationID ViolationName Points

tbl_ViolationClass
ViolationClassID ClassName

I made an SQL Code but to sum the accumulated points only but not the whole condition. See below:

SELECTa.DriverId,
a.DriverLicense,
a.FirstName + ' ' + a.LastName as DriverName,
a.FirstName,
a.LastName,
a.DriverMobileNo1,

[Code] .....

View 2 Replies View Related







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