?
Solved

MS SQL Server - Query Where Condition

Posted on 2014-11-17
7
Medium Priority
?
304 Views
Last Modified: 2016-02-13
Hi Experts,

I have this bit of code:
update CAQH_MAIN
set
STAT_CD = 'COMPLETED',
STAT_TS = Getdate(),
CAQH_USR_KY = NULL,
VALDTN_STAT = 'D',
VALDTN_RSN = 'NONG',
VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
Where GRP_NR IN (470014, 470025, 470038, 470050, 470062, 470075, 470086, 470098, 470110, 470122)

Open in new window


This will need to be done for all records coming in.

Now I need to add more (or have a separate code) with the condition:


We will also need to run this for all records that are currently sitting STAT_CD in ('AVAILABLE', 'PENDING', 'WORKING'). For the existing records, leave the CAQH_USR_KY alone.

Thanks for any help
update CAQH_MAIN
set
STAT_CD = 'COMPLETED',
STAT_TS = Getdate(),
CAQH_USR_KY = NULL,
VALDTN_STAT = 'D',
VALDTN_RSN = 'NONG',
VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
Where GRP_NR IN (470014, 470025, 470038, 470050, 470062, 470075, 470086, 470098, 470110, 470122)

Open in new window

0
Comment
Question by:Amour22015
7 Comments
 
LVL 13

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 200 total points
ID: 40447196
Maybe I'm missing something, but I'm not seeing the difference between the 2 queries you posted. It also appears that the extra condition you're referring to is not mentioned.
0
 
LVL 22

Accepted Solution

by:
plusone3055 earned 1000 total points
ID: 40447255
update CAQH_MAIN
set
STAT_CD = 'COMPLETED',
STAT_TS = Getdate(),
CAQH_USR_KY = NULL,
VALDTN_STAT = 'D',
VALDTN_RSN = 'NONG',
VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
Where GRP_NR IN (470014, 470025, 470038, 470050, 470062, 470075, 470086, 470098, 470110, 470122)
AND WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING')

Open in new window

0
 

Author Comment

by:Amour22015
ID: 40447278
Hi,

This is the only add on to the first query, the second query is only a copy of the first:

Now I need to add more (or have a separate code) with the condition:


 We will also need to run this for all records that are currently sitting STAT_CD in ('AVAILABLE', 'PENDING', 'WORKING'). For the existing records, leave the CAQH_USR_KY alone.

This is NOT a AND condition.

Thanks for any help...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Amour22015
ID: 40447314
Looks like it would have to be something like:
If New RECORD
Begin
update CAQH_MAIN
set
STAT_CD = 'COMPLETED',
STAT_TS = Getdate(),
CAQH_USR_KY = NULL,
VALDTN_STAT = 'D',
VALDTN_RSN = 'NONG',
VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING')
Else
update CAQH_MAIN
set
STAT_CD = 'COMPLETED',
STAT_TS = Getdate(),
VALDTN_STAT = 'D',
VALDTN_RSN = 'NONG',
VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
Where GRP_NR IN (470014, 470025, 470038, 470050, 470062, 470075, 470086, 470098, 470110, 470122)
AND WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING')
END

Open in new window


But it still looks like I am going to need more help?
0
 
LVL 9

Assisted Solution

by:Valliappan AN
Valliappan AN earned 800 total points
ID: 40449267
May be something like:

update CAQH_MAIN
set
STAT_CD = 'COMPLETED',
STAT_TS = Getdate(),
CAQH_USR_KY = NULL,
VALDTN_STAT = 'D',
VALDTN_RSN = 'NONG',
VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
Where GRP_NR IN (470014, 470025, 470038, 470050, 470062, 470075, 470086, 470098, 470110, 470122)

update CAQH_MAIN
set
STAT_CD = 'AVAILABLE',
STAT_TS = Getdate(),
VALDTN_STAT = 'D',
VALDTN_RSN = 'NONG',
VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
Where GRP_NR IN (470014, 470025, 470038, 470050, 470062, 470075, 470086, 470098, 470110, 470122)   --change your where conditions here

update CAQH_MAIN
set
STAT_CD = 'PENDING',
STAT_TS = Getdate(),
VALDTN_STAT = 'D',
VALDTN_RSN = 'NONG',
VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
Where GRP_NR IN (470014, 470025, 470038, 470050, 470062, 470075, 470086, 470098, 470110, 470122)   --change your where conditions here

update CAQH_MAIN
set
STAT_CD = 'WORKING',
STAT_TS = Getdate(),
VALDTN_STAT = 'D',
VALDTN_RSN = 'NONG',
VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
Where GRP_NR IN (470014, 470025, 470038, 470050, 470062, 470075, 470086, 470098, 470110, 470122)   --change your where conditions here

Please give more details on your requirements, if that's not what you require.

Cheers
0
 

Author Comment

by:Amour22015
ID: 40449725
Ok,

Still looking for the correct answer, it would be something like:
If New RECORD
Begin
update CAQH_MAIN
set
STAT_CD = 'COMPLETED',
STAT_TS = Getdate(),
CAQH_USR_KY = NULL,
VALDTN_STAT = 'D',
VALDTN_RSN = 'NONG',
VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING') OR Where GRP_NR IN (470014, 470025, 470038, 470050, 470062, 470075, 470086, 470098, 470110, 470122)
Else
update CAQH_MAIN
set
STAT_CD = 'COMPLETED',
STAT_TS = Getdate(),
VALDTN_STAT = 'D',
VALDTN_RSN = 'NONG',
VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING')
END

Open in new window


Notice that I am looking for:
If New Record
Else
Not New Record

Please help and thanks.
0
 

Author Closing Comment

by:Amour22015
ID: 40452817
Great thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question