Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

update records based on condition Expand / Collapse
Author
Message
Posted Thursday, August 1, 2013 12:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 4, 2013 8:13 AM
Points: 190, 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?
Post #1480094
Posted Thursday, August 1, 2013 1:09 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 13,082, Visits: 12,547
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 Moden's 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)
Post #1480111
Posted Thursday, August 1, 2013 1:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 4, 2013 8:13 AM
Points: 190, 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
Post #1480115
Posted Thursday, August 1, 2013 1:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 12,905, Visits: 32,161
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1480116
Posted Thursday, August 1, 2013 1:49 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 13,082, Visits: 12,547
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 Moden's 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)
Post #1480119
Posted Thursday, August 1, 2013 1:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 13,082, Visits: 12,547
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 Moden's 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)
Post #1480120
Posted Thursday, August 1, 2013 2:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 4, 2013 8:13 AM
Points: 190, 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
Post #1480140
Posted Thursday, August 1, 2013 2:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 13,082, Visits: 12,547
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 Moden's 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)
Post #1480142
Posted Thursday, August 1, 2013 2:38 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:24 PM
Points: 3,771, Visits: 8,459
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1480144
Posted Thursday, August 1, 2013 2:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 4, 2013 8:13 AM
Points: 190, 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






Post #1480156
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse