How do I UPDATE using a HAVING 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 2000 Forums

Transact-SQL (2000)

How do I UPDATE using a HAVING statement

Author

Topic

Angelflower
Starting Member

3 Posts

Posted - 2008-11-18 : 12:01:58

Hello...

Using the the below temp table I am trying to compare the 1st enrollment segment for the member to the @AnchorStart. If that difference is <= 45 days then I want to set the continuous column to "True" otherwise I want it to stay as "False". I am having trouble writing the Update Statement. I have highlighted the rows that need to be evaluated against the @AnchorStart. All other rows need to remain "False". Please note that the enrollid1 is only a dummie name as this number will always be different for each segment for each member.

-- ========= find member's enrollment segments ===============

if object_id('tempdb..#memberEnrollments') IS NOT NULL BEGIN DROP TABLE #memberEnrollments END

Declare @AnchorStart smalldatetime, @AnchorEnd smalldatetime, @StartAge int, @EndAge int, @Days int

--Set date range for Anchor Date (yyyymmdd) as the measurement year

SET @AnchorStart = '20070101'

SET @AnchorEnd = '20071231'

--Set age range

SET @StartAge = '24'

SET @EndAge = '64'

--Set 45 day gap for commercial and a 1 month gap for Medicaid (30 days)

Set @Days = '45'

SET nocount on

CREATE TABLE #memberEnrollments (enrollment int identity(1,1),

memid varchar(50),

enrollid varchar(50),

effdate datetime,

termdate datetime,

[Age Category] varchar(15),

continuous varchar(5))

-- the commented out INSERT below creates the following data (de-identified)

INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID1','enrollid1','1/1/2006','1/31/2006','25 - 29','False')

INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID2','enrollid1','1/2/2007','1/31/2007','01 - 04','False')

INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID2','enrollid2','2/1/2007','4/30/2007','01 - 04','False')

INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID2','enrollid3','5/1/2007','8/31/2007','01 - 04','False')

INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID3','enrollid1','1/1/2007','1/31/2007','05 - 10','False')

INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID3','enrollid2','2/1/2007','3/31/2007','05 - 10','False')

INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID3','enrollid3','5/1/2007','12/31/2007','05 - 10','False')

INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID4','enrollid1','3/1/2007','12/31/2007','05 - 10','False')

INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID5','enrollid1','1/1/2007','10/31/2007','05 - 10','False')

INSERT INTO #memberEnrollments (memid,enrollid,effdate,termdate,[Age Category],continuous) VALUES ('memID6','enrollid1','2/1/2007','11/30/2007','05 - 10','False')

-- ========== instead of the data above, return a true/false value to the [Continuously Enrolled] column for the 1st enrollment segment that is less than 45 days from Anchor date ======

UPDATE #memberEnrollments
SET continuous = 'True '
FROM #memberEnrollments LEFT JOIN
(SELECT memid,enrollid, effdate,termdate,[Age Category],continuous
FROM #memberEnrollments
WHERE memid is not null
GROUP BY memid,enrollid,effdate,termdate,[Age Category],continuous
HAVING datediff(dd,min(effdate),@AnchorStart)<=@Days) AS y ON y.memid = #memberEnrollments.memid
WHERE y.memid IS NOT NULL

SELECT * FROM #memberEnrollments

Expected Results:

1 memID1 enrollid1 2006-01-01 00:00:00.000 2006-01-31 00:00:00.000 25 - 29 False
2 memID2 enrollid1 2007-01-02 00:00:00.000 2007-01-31 00:00:00.000 01 - 04 True
3 memID2 enrollid2 2007-02-01 00:00:00.000 2007-04-30 00:00:00.000 01 - 04 False
4 memID2 enrollid3 2007-05-01 00:00:00.000 2007-08-31 00:00:00.000 01 - 04 False
5 memID3 enrollid1 2007-01-01 00:00:00.000 2007-01-31 00:00:00.000 05 - 10 True
6 memID3 enrollid2 2007-02-01 00:00:00.000 2007-03-31 00:00:00.000 05 - 10 False
7 memID3 enrollid3 2007-05-01 00:00:00.000 2007-12-31 00:00:00.000 05 - 10 False
8 memID4 enrollid1 2007-03-01 00:00:00.000 2007-12-31 00:00:00.000 05 - 10 False
9 memID5 enrollid1 2007-01-01 00:00:00.000 2007-10-31 00:00:00.000 05 - 10 True
10 memID6 enrollid1 2007-02-01 00:00:00.000 2007-11-30 00:00:00.000 05 - 10 True

Live well... be happy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 12:22:37

UPDATE me
SET me.continuous = 'True '
FROM #memberEnrollments me
INNER JOIN
(SELECT memid,MIN(effdate) AS MinDate
FROM #memberEnrollments
GROUP BY memid)me1
ON me1.memid=me.memid
AND me1.MinDate=me.effdate
WHERE me.effdate>DATEADD(dd,-1 * @days,@AnchorStart)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources