SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


update records based on condition


update records based on condition

Author
Message
riya_dave
riya_dave
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 367
hi

i have table , which can have millions of rows.


Member primarycondition primaryintensity secondarycondition secondaryintensity
M2345 hf 1
M2345 COPD 1
M2345 CAD 1
M2345 dia 1
M2345 Ast 1
M2345 hf 2
M2345 COPD 2
M2345 CAD 2
M2345 dia 2
M2345 Ast 2


what i need to do is if member has more than 1 condition, then
i need to make 1 as a primary and another one secondary condition. with same primary intensity
based on this order


hf
COPD
CAD
dia
Ast


so the result will be


Member primarycondition primaryintensity secondarycondition secondaryIntensity
M2345 hf 1
M2345 COPD 1
M2345 CAD 1
M2345 dia 1
M2345 Ast 1
M2345 hf 2
M2345 COPD 2
M2345 CAD 2
M2345 dia 2
M2345 Ast 2


i need to do it for all member,if member has more than 1 primary condition.


any help?
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63442 Visits: 17966
I have tried to help you on so many threads and once again you haven't posted any details. If you would actually post ddl and sample data along with the desired output you will find that you receive answers a LOT faster and with a lot less posts. Please do yourself, and everyone on these forums a favor, if you are going to post asking for help then please make the effort to post ddl and sample data. Take a few minutes and read the first link in my signature.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
riya_dave
riya_dave
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 367
this is my DDL

create table member
(memberid int,
primarycondition varchar(50),
PrimaryIntensity int,
secondarycondition varchar(50),
Secondaryintensioty int)

sample data i already provided in my previous thread
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73059 Visits: 40959
you DID NOT post readily consumable sample data.

your sample headers had five columns, but the data only appears in three.
how is anyone supposed to be able to use that?

i ran what you posted thru my macro and came up with this:

;With MySampleData([Member],[primarycondition],[primaryintensity],[secondarycondition],[secondaryintensity])
AS
(
SELECT 'M2345','hf','1' UNION ALL
SELECT 'M2345','COPD','1' UNION ALL
SELECT 'M2345','CAD','1' UNION ALL
SELECT 'M2345','dia','1' UNION ALL
SELECT 'M2345','Ast','1' UNION ALL
SELECT 'M2345','hf','2' UNION ALL
SELECT 'M2345','COPD','2' UNION ALL
SELECT 'M2345','CAD','2' UNION ALL
SELECT 'M2345','dia','2' UNION ALL
SELECT 'M2345','Ast','2'
)
SELECT * FROM MySampleData



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63442 Visits: 17966
riya_dave (8/1/2013)
this is my DDL

create table member
(memberid int,
primarycondition varchar(50),
PrimaryIntensity int,
secondarycondition varchar(50),
Secondaryintensioty int)

sample data i already provided in my previous thread


I am not a paid employee of this site. I do not want to go digging around through all your posts to find the information that you should be providing. I say this because I truly want to help, but you don't seem willing to put in even as much effort to a solution to your problems as you are expecting of other to do. I know that there a number of people around these forums who see certain posters names and simply skip on to the next one because they know the post is not going to have the details to offer much help without a lot of effort. Your name is certainly on that list. You need to make an effort at making it easy on the folks who are offering their advice free of charge.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63442 Visits: 17966
Lowell (8/1/2013)
you DID NOT post readily consumable sample data.

your sample headers had five columns, but the data only appears in three.
how is anyone supposed to be able to use that?

i ran what you posted thru my macro and came up with this:

;With MySampleData([Member],[primarycondition],[primaryintensity],[secondarycondition],[secondaryintensity])
AS
(
SELECT 'M2345','hf','1' UNION ALL
SELECT 'M2345','COPD','1' UNION ALL
SELECT 'M2345','CAD','1' UNION ALL
SELECT 'M2345','dia','1' UNION ALL
SELECT 'M2345','Ast','1' UNION ALL
SELECT 'M2345','hf','2' UNION ALL
SELECT 'M2345','COPD','2' UNION ALL
SELECT 'M2345','CAD','2' UNION ALL
SELECT 'M2345','dia','2' UNION ALL
SELECT 'M2345','Ast','2'
)
SELECT * FROM MySampleData



You might also notice the "data" matches exactly what is stated as desired output. ;-)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
riya_dave
riya_dave
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 367
my desire out put will be
Member primarycondition primaryintensity secondarycondition secondaryIntensity
M2345 hf 1
M2345 COPD 1
M2345 CAD 1
M2345 dia 1
M2345 Ast 1
M2345 hf 2
M2345 COPD 2
M2345 CAD 2
M2345 dia 2
M2345 Ast 2

it not taking spaces
but hf and associated primaryintensity will be under primary condition and primaryintensity
other will goes under secondarycondition and secondary intensity
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63442 Visits: 17966
riya_dave (8/1/2013)
my desire out put will be
Member primarycondition primaryintensity secondarycondition secondaryIntensity
M2345 hf 1
M2345 COPD 1
M2345 CAD 1
M2345 dia 1
M2345 Ast 1
M2345 hf 2
M2345 COPD 2
M2345 CAD 2
M2345 dia 2
M2345 Ast 2


Since that matches exactly to what you said your data is like I guess you could select the rows from your table. Nothing changed between your table data and your output.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42454 Visits: 19838
Here you go, it generates the expected output based on your sample data.
;With MySampleData([Member],[primarycondition],[primaryintensity],[secondarycondition],[secondaryintensity])
AS
(
SELECT 'M2345','hf','1','','' UNION ALL
SELECT 'M2345','COPD','1','','' UNION ALL
SELECT 'M2345','CAD','1','','' UNION ALL
SELECT 'M2345','dia','1','','' UNION ALL
SELECT 'M2345','Ast','1','','' UNION ALL
SELECT 'M2345','hf','2','','' UNION ALL
SELECT 'M2345','COPD','2','','' UNION ALL
SELECT 'M2345','CAD','2','','' UNION ALL
SELECT 'M2345','dia','2','','' UNION ALL
SELECT 'M2345','Ast','2','',''
)
SELECT * FROM MySampleData



By the way, I couldn't use your DDL because M2345 is not an int.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
riya_dave
riya_dave
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 367
thisis not the result that i want

i have five PRIMARY condition ,
hf,
COPD,
CAD,
dia,
ast

primary intensity could be 1,2, 3

now i want to check suppose if i have all 5 condition with same primary Intensity,
HF and that intensity should be under primarycondition and primaryintensity
other 4 should be move to secondarycondition and secondaryintensity
and for that condition primarycondition and primaryintensity should update to null,

now suppose i have just 3 primary condition , CAD,dia and ast with same intenstty
then CAD and associated intensity should be primarycondition and primaryIntensity
and dia and ast should be move to secondarycondition and secondaryIntensity
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search