Value = 1 for unique patients and 0 for duplicates

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

General SQL Server Forums

New to SQL Server Programming

Value = 1 for unique patients and 0 for duplicates

Author

Topic

cwildeman
Starting Member

40 Posts

Posted - 2011-11-28 : 11:01:43

I have a query that has ConsultDate, AcctNum, WoundLocation and WoundUnit. The AcctNum field will have duplicate values when a patient has more than one wound. I want to create a calculated field called PtCount which will simply be a 1 for the first instance an account number appears and zero for additional records where AcctNum appears again. I need to keep all fields in the query (ConsultDate, AcctNum, WoundLocation and WoundUnit) and add PtCount so a Group By with Min won't work (I don't think). Can someone help?
Thanks,
Chuck

Chuck W

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 11:07:17

just make update like


UPDATE t
SET t.PtCount=1
FROM Table t
INNER JOIN (SELECT AcctNum,MIN(ConsultDate) AS MInDate
            FROM table
            GROUP BY AcctNum)t1
ON t1.AcctNum = t.AcctNum
AND t1.MinDate=t.ConsultDate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cwildeman
Starting Member

40 Posts

Posted - 2011-11-28 : 11:32:08

Thanks for your help. I substituted my table name dbo.HAPU_Input for the word table in your example and came up with the following. I tried to run it but got an error 'Invalid column name 'PtCount'. Any idea what I am doing wrong?

UPDATE t
SET t.PtCount=1
FROM dbo.HAPU_Input t
INNER JOIN (SELECT AcctNum,MIN(ConsultDate) AS MinDate
FROM dbo.HAPU_Input
GROUP BY AcctNum)t1
ON t1.AcctNum = t.AcctNum
AND t1.MinDate=t.ConsultDate

Chuck W

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 11:43:59

quote:
Originally posted by cwildeman

Thanks for your help. I substituted my table name dbo.HAPU_Input for the word table in your example and came up with the following. I tried to run it but got an error 'Invalid column name 'PtCount'. Any idea what I am doing wrong?

UPDATE t
SET t.PtCount=1
FROM dbo.HAPU_Input t
INNER JOIN (SELECT AcctNum,MIN(ConsultDate) AS MinDate
FROM dbo.HAPU_Input
GROUP BY AcctNum)t1
ON t1.AcctNum = t.AcctNum
AND t1.MinDate=t.ConsultDate

Chuck W

have you created that column PtCount yet in table HAPU_Input?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cwildeman
Starting Member

40 Posts

Posted - 2011-11-28 : 12:24:41

Thanks that worked. HAPU_Input is a table where end users will enter several records in each month. I need to create a query that will perform this update once a month. I take it I can run it each month and it will update the new records and leave the old one's alone? Also, do you recommend that I create a stored procedure to run this whenever I need to create more query for reporting purposes? Thanks, Chuck

Chuck W

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 12:41:19

quote:
Originally posted by cwildeman

Thanks that worked. HAPU_Input is a table where end users will enter several records in each month. I need to create a query that will perform this update once a month. I take it I can run it each month and it will update the new records and leave the old one's alone? Also, do you recommend that I create a stored procedure to run this whenever I need to create more query for reporting purposes? Thanks, Chuck

Chuck W

you can add this inside procedure and call it from sql job. The job can be scheduled to run each month. then using date field you can identify new records and do this update

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources