Solved

MS SQL Server - Query Where Condition

Posted on 2014-11-17
7
291 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 13

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 50 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 250 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 200 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

Setting up LaraDock for Laravel

Learn how to set up LaraDock in a Laravel project - LaraDock gives us an easy way to run a Laravel application using Docker in a single command.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

630 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