Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS SQL Server - Query Where Condition

Posted on 2014-11-17
7
Medium Priority
?
298 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

916 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