Added complexities to an Update Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Added complexities to an Update Statement

Author

Topic

LaurieCox

158 Posts

Posted - 2011-03-15 : 10:47:25

Hi

A while back I posted this topic: Update statement syntax ... and got a great answer (thanks Lamprey). Well now I need to add an additional twist to the problem.

I have added a column (StartDate) to the #TestData_1 table. So now I have the following two tables:


CREATE TABLE #TestData_1(
       PATID varchar(10) NULL,
       EpisodeNumber int NULL,
       StartDate datetime Null,
       DiagnosisType varchar(10) NULL)

INSERT INTO #TestData_1 (PATID,EpisodeNumber,StartDate)
SELECT  '1018', 26, '2010-01-01' UNION ALL
SELECT  '32181', 4, '2010-04-01'   UNION ALL
SELECT  '32181', 4, '2011-02-01'   UNION ALL
SELECT  '32181', 4, '2010-03-22'   UNION ALL
SELECT  '32181', 4, '2011-01-01'   UNION ALL
SELECT  '43497', 1, '2010-01-01'   UNION ALL
SELECT  '603', 38, '2010-01-01'   UNION ALL
SELECT  '603', 49, '2011-02-15'

CREATE TABLE #TestData_2(
       PATID varchar(10) NULL,
       EpisodeNumber int NULL)

INSERT INTO #TestData_2
SELECT  '603', 38  UNION ALL
SELECT  '603', 38  UNION ALL
SELECT  '603', 39  UNION ALL
SELECT  '603', 40  UNION ALL
SELECT  '603', 41  UNION ALL
SELECT  '603', 3  UNION ALL
SELECT  '43497', 1  UNION ALL
SELECT  '1018', 27  UNION ALL
SELECT  '1018', 29  UNION ALL
SELECT  '1018', 30  UNION ALL
SELECT  '1018', 31

My original requirement was:


If at least one record exists in #TestData_2 for a given PATID/EpisodeNumber then 
   DiagnosisType = 'U' 
Else
   DiagnosisType = 'A'
End if

My new requirement (the twist) is: If more than one record exists in #TestData_1 for a given PATID/EpisodeNumber (and does not exist in #TestData_2) then only the earliest dated one should have a Diagnosis Type of 'A' all the rest (in the given PATID/EpisodeNumber) should have a Diagnosis Type of 'U'. This changes my expected output to this:


PATID      EpisodeNumber StartDate  DiagnosisType
---------- ------------- ------------------------
1018       26            2010-01-01 A
32181      4             2010-04-01 U
32181      4             2011-02-01 U
32181      4             2010-03-22 A
32181      4             2011-01-01 U
43497      1             2010-01-01 U
603        38            2010-01-01 U
603        49            2011-02-15 A

(Red donates change from original expected results).

Right now I am thinking in terms of running two update statements. The first one from the original problem and then one that somehow finds all the rows (in TestData_1) where there are two or more rows for a given PATID/EpisodeNumber where DiagnosisType is set to 'A' for all rows and update all but the minimum StartDate row to 'U'.

There are two issues with this solution:

I am not sure how to do that (though I am working on it).

It might not be the most efficient way to do this.

Any help would be much appreciated.

Thanks,

Laurie

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-03-15 : 11:34:57

[code]
WITH Grps
AS
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY PATID, EpisodeNumber ORDER BY StartDate) AS RowNum
,COUNT(*) OVER (PARTITION BY PATID, EpisodeNumber) AS GrpCnt
,CASE
WHEN EXISTS
(
SELECT *
FROM #TestData_2 T2
WHERE T2.PATID = T1.PATID
AND T2.EpisodeNumber = T1.EpisodeNumber
)
THEN 'U'
ELSE 'A'
END AS DType
FROM #TestData_1 T1
)
UPDATE Grps
SET DiagnosisType =
CASE
WHEN DType = 'A' AND GrpCnt > 1 AND RowNum > 1
THEN 'U'
ELSE DType
END
[/code]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-15 : 11:35:19

UPDATE tgt
SET DiagnosisType = CASE WHEN ( (t3.Recs > 1 and tgt.StartDate = t3.MinStartDate) )
or (t3.recs = 1 and t3.episodenumber is not null) THEN 'A' ELSE 'U' END

FROM
#testdata_1 tgt

LEFT JOIN

(
select patid,episodeNumber, MIN(startdate) OVER(partition by patid,episodeNumber) as MinStartDate
,count(*) over(partition by patid) as Recs
from #TestData_1 t1
where not exists(select * from #TestData_2 t2 where t1.patid = t2.patid and t1.episodenumber = t2.episodenumber)

) t3

ON
tgt.PATID = t3.PatID
and tgt.episodeNumber = t3.episodeNumber

Jim

Everyday I learn something that somebody else already knew

LaurieCox

158 Posts

Posted - 2011-03-15 : 12:11:44

Wow, that was quick. Thanks jimf and Ifor.

This gives me two different solutions and the chance to increase my sql knowledge. I am going to have to pour over both so that I understand how each one works (especially the cte one, I seem to have a hard time wrapping by brain around the concept).

Also, if anybody is interested, I would love to see discussion on the relative merits of both solutions.

Again thanks for the replies,

Laurie

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-03-15 : 12:27:45

I have just noticed that my effort can be reduced to following, although I doubt it will make any difference to the efficiency:


;WITH Grps
AS
(
	SELECT * 
		,ROW_NUMBER() OVER (PARTITION BY PATID, EpisodeNumber ORDER BY StartDate) AS RowNum
		,CASE
			WHEN EXISTS
			(
				SELECT *
				FROM #TestData_2 T2
				WHERE T2.PATID = T1.PATID
					AND T2.EpisodeNumber = T1.EpisodeNumber
			)
			THEN 'U'
			ELSE 'A'
		END AS DType
	FROM #TestData_1 T1
)
UPDATE Grps
SET DiagnosisType =
	CASE 
		WHEN DType = 'A' AND RowNum > 1
		THEN 'U'
		ELSE DType
	END

You can check the relative efficiency by:
1. putting both statements into a query window.
2. pressing Ctrl-M, to include the actual execution plan
3. running the query

The execution plan will show the relative cost of each query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources