September 11, 2015 at 8:31 am
I need to update the Denominator column in one row with the value from the Numerator column in a different row.
For example the last row in the table is
c010A92NULL
I need to update the Denominator, which is currently NULL, with the value from the Numerator where the MeasureID=c001 and GroupID=A.
This value is 668 so, the row should look like
c010A92668
create table dbo.TEST
(
MeasureID varchar(10),
GroupID char(1),
Numerator float,
Denominator float
)
insert into dbo.TEST(MeasureID, GroupID, Numerator, Denominator) values( 'c001','A',668,NULL)
insert into dbo.TEST(MeasureID, GroupID, Numerator, Denominator) values( 'c001','T',629,NULL)
insert into dbo.TEST(MeasureID, GroupID, Numerator, Denominator) values( 'c002','A',309,NULL)
insert into dbo.TEST(MeasureID, GroupID, Numerator, Denominator) values( 'c002','T',285,NULL)
insert into dbo.TEST(MeasureID, GroupID, Numerator, Denominator) values( 'c003','A',977,NULL)
insert into dbo.TEST(MeasureID, GroupID, Numerator, Denominator) values( 'c003','T',914,NULL)
insert into dbo.TEST(MeasureID, GroupID, Numerator, Denominator) values( 'c004','A',634,NULL)
insert into dbo.TEST(MeasureID, GroupID, Numerator, Denominator) values( 'c004','T',598,NULL)
insert into dbo.TEST(MeasureID, GroupID, Numerator, Denominator) values( 'c005','A',299,NULL)
insert into dbo.TEST(MeasureID, GroupID, Numerator, Denominator) values( 'c005','T',276,NULL)
insert into dbo.TEST(MeasureID, GroupID, Numerator, Denominator) values( 'c006','A',915,NULL)
insert into dbo.TEST(MeasureID, GroupID, Numerator, Denominator) values( 'c006','T',859,NULL)
insert into dbo.TEST(MeasureID, GroupID, Numerator, Denominator) values( 'c007','A',92,NULL)
insert into dbo.TEST(MeasureID, GroupID, Numerator, Denominator) values( 'c007','T',86,NULL)
insert into dbo.TEST(MeasureID, GroupID, Numerator, Denominator) values( 'c008','A',92,NULL)
insert into dbo.TEST(MeasureID, GroupID, Numerator, Denominator) values( 'c008','T',86,NULL)
insert into dbo.TEST(MeasureID, GroupID, Numerator, Denominator) values( 'c009','A',102,NULL)
insert into dbo.TEST(MeasureID, GroupID, Numerator, Denominator) values( 'c009','T',102,NULL)
insert into dbo.TEST(MeasureID, GroupID, Numerator, Denominator) values( 'c010','A',92,NULL)
September 11, 2015 at 8:39 am
NineIron (9/11/2015)
I need to update the Denominator column in one row with the value from the Numerator column in a different row.For example the last row in the table is
c010A92NULL
I need to update the Denominator, which is currently NULL, with the value from the Numerator where the MeasureID=c001 and GroupID=A.
This value is 668 so, the row should look like
c010A92668
Excellent job posting ddl and sample data. What isn't clear though is what you are trying to do. Why does C010 need to be updated with C001??? There is some logic here to indicate what rows are related but it is not clear from your post.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 11, 2015 at 8:49 am
MeasureID c001 is total discharges from our hospital. MeasureID c010 is Readmission rate. So, I need the Total Discharges in order to calculate the rate.
September 11, 2015 at 8:56 am
NineIron (9/11/2015)
MeasureID c001 is total discharges from our hospital. MeasureID c010 is Readmission rate. So, I need the Total Discharges in order to calculate the rate.
I am sure that makes sense to you but it doesn't make sense to me. What about other rows for example C003??? Help me understand the logic and the query is pretty simple.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 11, 2015 at 9:06 am
Is it as simple as this?
UPDATE dbo.Test
SET Denominator = (
SELECT Numerator
FROM Test t2
WHERE t2.MeasureID = 'c001'
AND t2.GroupID = 'A'
)
WHERE MeasureID = 'C010'
AND GroupID = 'A'
John
September 11, 2015 at 9:09 am
That's it. Thanx.
September 11, 2015 at 9:10 am
Sorry about the confusion.
September 11, 2015 at 9:12 am
John Mitchell-245523 (9/11/2015)
Is it as simple as this?
UPDATE dbo.TestSET Denominator = (
SELECT Numerator
FROM Test t2
WHERE t2.MeasureID = 'c001'
AND t2.GroupID = 'A'
)
WHERE MeasureID = 'C010'
AND GroupID = 'A'
John
Nice John. I actually had coded that and then deleted it from my last post because I figured there was no way it was really that simple. :-D:-D:-D
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply