Solved

MS SQL Server - Query Where Condition

Posted on 2014-11-17
7
273 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 12

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

919 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now