Cursor Smashing- Who's Interested? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

Cursor Smashing- Who's Interested?

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-04-29 : 15:26:53

I am having the following problem and am considering a cursor solution.
I need to generate a text file that looks like this:
1|S|N|0845B02|CENTRAL HEALTH CENTER|03/05/2003|
2|P|S|3309004||03/30/2002|M|JOHN|DOE|||||||||||||||||||||||||||
3|M|S|3309004||03/30/2002|M|JOHN|DOE||||||||||||||||01/01/2003|20|V|DR WOO|WANG|123456|||||
4|P|S|1412613||12/15/2001|M|MIKE|GREEN|||||||||||||||||||||||||||
5|M|S|1412613||12/15/2001|M|MIKE|GREEN||||||||||||||||01/02/2003|20|V|DR MARCUS|WELBY|123456|||||
6|P|S|1412613||12/15/2001|M|JOE|BLACK|||||||||||||||||||||||||||
7|M|S|1412613||12/15/2001|M|JOE|BLACK||||||||||||||||01/02/2003|51|V|DR MARCUS|WELBY|123456|||||
8|P|S|3524801||11/01/2001|F|JANE|DOE|||||||||||||||||||||||||||
9|M|S|3524801||11/01/2001|F|JANE|DOE||||||||||||||||01/02/2003|03|V|DR PATCH|ADAMS|123456|||||
10|P|S|3668500||06/25/2002|M|ROB|ROO|||||||||||||||||||||||||||
11|M|S|3668500||06/25/2002|M|ROB|ROO||||||||||||||||01/02/2003|20|V|DR PATCH|ADAMS|123456|||||
12|P|S|3524801||11/01/2001|F|JANE|DOE|||||||||||||||||||||||||||
13|M|S|3524801||11/01/2001|F|JANE|DOE||||||||||||||||01/02/2003|51|V|DR PATCH|ADAMS|123456|||||

Basically, the file has a header and footer, and then sets of M and P rows relating to patient immunizations. (This is for the city registry). I have the following stored proc.
CREATE PROCEDURE GenerateDOHImmuneList @SvcDate DATETIME,@FileNumber SMALLINT
AS
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
DECLARE @License CHAR(7),@FacilityName CHAR(50),@Delim CHAR(1),@ErrMsg CHAR(100)
SET @License = ''
SET @FacilityName = ''
SET @Delim = '|'
IF @License = '' OR @FacilityName = '' GOTO NoFacInfo
CREATE TABLE #DOH (RowID INT IDENTITY(1,1), Contents VARCHAR(500))
INSERT INTO #DOH (Contents) VALUES(@Delim + 'S' + @Delim + 'N' + @Delim + @License + @Delim + RTRIM(@FacilityName) + @Delim + CONVERT(CHAR(10),getdate(),101) + @Delim)
INSERT INTO #DOH (Contents) SELECT @Delim + 'P' + @Delim + 'S' + @Delim + LTRIM(STR(P.Account)) + @Delim + /*Skip Medicaid Number*/ @Delim + CONVERT(CHAR(10),P.DOB,101) + @Delim
+ P.Sex + @Delim + RTRIM(P.FirstName) + @Delim + RTRIM(P.LastName) + REPLICATE(@Delim,27) FROM Patients P INNER JOIN PatImmune I ON P.ID = I.Patid WHERE I.ImmuDate >= @SvcDate
INSERT INTO #DOH (Contents) SELECT @Delim + 'M' + @Delim + 'S' + @Delim + LTRIM(STR(P.Account)) + @Delim + /*Skip Medicaid Number*/ @Delim + CONVERT(CHAR(10),P.DOB,101) + @Delim
+ P.Sex + @Delim + RTRIM(P.FirstName) + @Delim + RTRIM(P.LastName) + REPLICATE(@Delim,16) + CONVERT(CHAR(10),I.ImmuDate,101) + @Delim + (SELECT RIGHT(STR(CIRCode),4) FROM DOHImmune WHERE
CPT = I.ImmuType) + @Delim + 'V' + @Delim + RTRIM(D.FirstName) + @Delim + RTRIM(D.LastName) + @Delim + RTRIM(D.StateLic) + REPLICATE(@Delim,5) FROM Patients P INNER JOIN Patimmune I ON I.Patid
= P.ID INNER JOIN Vouchersdetail VD ON VD.ProcCode = I.ImmuType AND VD.ServiceDate = I.ImmuDate INNER JOIN Vouchers V ON V.VoucherNo = VD.Voucher INNER JOIN Doctors D ON V.DrCode =
D.Code WHERE I.ImmuDate >= @SvcDate
INSERT INTO #DOH (Contents) VALUES (@Delim + 'U')
SET NOCOUNT OFF
SET CONCAT_NULL_YIELDS_NULL ON
--SELECT * FROM #DOH
--DROP TABLE #DOH
RETURN

NoFacInfo:
BEGIN
SET @ErrMsg = 'The Facility Code And/Or Facility Name Is Not Defined In Procedure GenerateDOHImmuneList.'
RAISERROR(@ErrMsg,18,1)
RETURN
END

The problem is: The order is not correct.
One possible fix: Use a cursor.

Your input is welcome!

Sarah Berger MCSD

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-29 : 15:50:31

Uhh?

quote:

The problem is: The order is not correct.

You can't do

ORDER BY ROWId, Contents?

Brett

8-)

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-29 : 15:57:13

Have you read these

http://www.sqlteam.com/item.asp?ItemID=4722

All you need is a query that returns rows like

3 M S 3309004 NULL 03/30/2002 M JOHN DOE NULL etcc....

using '' instead of null of course

then do like article says

you'll have to use -t switch of the bcp command to specify row terminator.

so the question is really how do we write a query that returns rows like
3 M S 3309004 NULL 03/30/2002 M JOHN DOE NULL etcc....

If you show us the dml and some sample data and results we can help you write it. It would be alot easier than reading the post and making it up as we go.

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-29 : 16:08:22

Ok, I see what you're talking about...you need to keep certain rows together (header at top, data in the middle, trailer at the end)...now I just added a "RowGroup" Column and order by that, but don't select it. If you need any other order, like for example a key in the row, add it as another group, and don't select it. Just use it in the order by.

I had to reformat your code to see it (my brain is very angry at me for staring at the post).

Use [ code ] ...some code [ /code ] tags to show as formatted...

anyway:


CREATE PROCEDURE GenerateDOHImmuneList @SvcDate DATETIME,@FileNumber SMALLINT 
AS 
SET NOCOUNT ON 
SET CONCAT_NULL_YIELDS_NULL OFF 
DECLARE @License CHAR(7),@FacilityName CHAR(50),@Delim CHAR(1),@ErrMsg CHAR(100) 
SET @License = '' 
SET @FacilityName = '' 
SET @Delim = '|' 
IF @License = '' OR @FacilityName = '' GOTO NoFacInfo 

CREATE TABLE #DOH (RowGroup Int, RowID INT IDENTITY(1,1), Contents VARCHAR(500)) 

INSERT INTO #DOH (RowGroup, Contents) 
     VALUES(1, @Delim + 'S' + @Delim + 'N' + @Delim + @License + @Delim + RTRIM(@FacilityName) + @Delim 
+ CONVERT(CHAR(10),getdate(),101) + @Delim) 

INSERT INTO #DOH (RowGroup, Contents) 
     SELECT 2, @Delim + 'P' + @Delim + 'S' + @Delim + LTRIM(STR(P.Account)) + @Delim 
+ /*Skip Medicaid Number*/ @Delim + CONVERT(CHAR(10),P.DOB,101) + @Delim 
+ P.Sex + @Delim + RTRIM(P.FirstName) + @Delim + RTRIM(P.LastName) + REPLICATE(@Delim,27) 
       FROM Patients P INNER JOIN PatImmune I ON P.ID = I.Patid 
      WHERE I.ImmuDate >= @SvcDate 

INSERT INTO #DOH (RowGroup, Contents) 
     SELECT 3, @Delim + 'M' + @Delim + 'S' + @Delim + LTRIM(STR(P.Account)) + @Delim 
+ /*Skip Medicaid Number*/ @Delim + CONVERT(CHAR(10),P.DOB,101) + @Delim 
+ P.Sex + @Delim + RTRIM(P.FirstName) + @Delim + RTRIM(P.LastName) + REPLICATE(@Delim,16) 
+ CONVERT(CHAR(10),I.ImmuDate,101) + @Delim 
+ (SELECT RIGHT(STR(CIRCode),4) FROM DOHImmune WHERE CPT = I.ImmuType) 
+ @Delim + 'V' + @Delim + RTRIM(D.FirstName) + @Delim + RTRIM(D.LastName) + @Delim + RTRIM(D.StateLic) 
+ REPLICATE(@Delim,5) FROM Patients P 
INNER JOIN Patimmune I ON I.Patid = P.ID 
INNER JOIN Vouchersdetail VD ON VD.ProcCode = I.ImmuType AND VD.ServiceDate = I.ImmuDate 
INNER JOIN Vouchers V ON V.VoucherNo = VD.Voucher 
INNER JOIN Doctors D ON V.DrCode = D.Code 
     WHERE I.ImmuDate >= @SvcDate 

INSERT INTO #DOH (RowGroup, Contents) VALUES (4, @Delim + 'U') 

SET NOCOUNT OFF 
SET CONCAT_NULL_YIELDS_NULL ON 
--SELECT RowId, Contents FROM #DOH Order By RowGroup, RowId 
--DROP TABLE #DOH 
RETURN 

NoFacInfo: 
BEGIN 
SET @ErrMsg = 'The Facility Code And/Or Facility Name Is Not Defined In Procedure GenerateDOHImmuneList.' 
RAISERROR(@ErrMsg,18,1) 
RETURN 
END

Brett

8-)

Edited by - x002548 on 04/29/2003 16:10:17

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-29 : 16:12:20

Your using the same first part of your join

FROM Patients P INNER JOIN PatImmune I ON P.ID = I.Patid

FROM Patients P INNER JOIN Patimmune I ON I.Patid = P.ID

same where clause
WHERE I.ImmuDate >= @SvcDate

What prevents you from combining it into one statement.
If you showed ddl and sample data we could help you simplify.

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-04-29 : 16:13:38

To Brett:
Order by doesn't work.
Using the Insert I provided in the sp doesn't insert the rows in correct order according to the Rowid, it does this: (Please notice the M and P pairs aren't correct)
1|S|N|0845c01|ODA Health Center|04/29/2003|
2|M|S|1876|||F|Two|Sample||||||||||||||||04/28/2003| 47|V|Steven|Mermelstein||||||
3|M|S|521000||01/01/1900||LORETTA|DOELGER||||||||||||||||03/06/2003||V|Barry|Bules||||||
4|M|S|8765000||01/01/1900||PEARLIE|TRIPP||||||||||||||||03/12/2003| 47|V|Itchie|Statfeild||||||
5|M|S|8765000||01/01/1900||PEARLIE|TRIPP||||||||||||||||03/12/2003||V|Itchie|Statfeild||||||
6|P|S|8765000||01/01/1900||PEARLIE|TRIPP|||||||||||||||||||||||||||
7|P|S|521000||01/01/1900||LORETTA|DOELGER|||||||||||||||||||||||||||
8|P|S|9479000||01/22/1980|F|YELIZAVETA|VERKHOVSKY|||||||||||||||||||||||||||
9|P|S|1153100||02/12/1989|M|VICTOR|NUNBERG|||||||||||||||||||||||||||
10|P|S|9747000||06/15/1986|F|CHERYL|NUNBERG|||||||||||||||||||||||||||
11|P|S|1876|||F|Two|Sample|||||||||||||||||||||||||||
12|P|S|7873200||07/18/1981|F|JUDY|MUND|||||||||||||||||||||||||||
13|U

and ordering by contents will put all the M's before all the P's instead of doing sets.

To Valter:
Thanks, but it's not just a CSV. If you'll look closely, the M rows have a different number of columns (values) than the P rows (36 vs. 35), not including the header (row 1) that has only 6 columns, and the footer (row 13) that has only 1 column.
Also, while some information from the M row is the same as the P row, most of the information isn't, so it's not really a regular table structure.

The data structure is very normalized, and not too difficult to comprehend:
One to Many from Patients To Patimmune (A table tracking patient immunizations)
One To Many from Doctors To Vouchers (Vouchers = Think Northwind..Orders, each voucher has one doctor)
Many To One Vouchersdetail to Vouchers: Think Northwind..Orderdetails
Many To One PatImmune to DOHImmune: This table maps the ImmuType code from Patimmune into a DOH code, each code is in DOHImmune once.

The results are below, in a nutshell: The P row has the patient demographics, the M row has that, plus the immunization info. such as a DOH code, the performing doctor, the immunization date, and the doctor's license.

Sarah Berger MCSD

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-29 : 16:20:10

You know you're not ordering by contents.

What columns do you wish to order by?

Just add them as columns to your temp table. DO NOT select them, just use them to order by.

Brett

8-)

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-04-29 : 16:21:14

Brett,
I just tried using a grouping column, but it does the same thing: orders the P's before the M's. I need alternating rows of M's and P's. An M and a P form a set. And the Rowid must be sequential so that can't be thrown off.

BTW, I am trying to BCP all this data, which is why I want to use only one contents column.

Sarah Berger MCSD

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-04-29 : 16:25:18

ORDER BY won't help. There is NO ordering column I need to use, except for the header and footer that are fixed. The data may be in any order, as long as the M and P sets match.

The issue: The data must be inserted in the temp table in the right order, otherwise the RowId column is useless to me.

Sarah Berger MCSD

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-04-29 : 16:33:05

None of the dates or numbers need any particular order.
M comes before P, and then P, and then M again. Alternating order.
One M, One P, One M, One P.

Hey, Valter! One More Post, You Get A Custom Title!!! Congrats!!

[/beer]

Sarah Berger MCSD

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-29 : 16:47:19

It looks like you want column 4 to be ordered, even though they can come out in any order, you still need to pair them up, which infers ordering.

quote:

ORDER BY won't help. There is NO ordering column I need to use, except for the header and footer that are fixed. The data may be in any order, as long as the M and P sets match.

Anyway here's a simpler example of what I'm trying to convey (couldn't test it though, seems like QA and EM have amnesia right now...I try the Create table and it just goes bye-bye...ever happen to you guys/gals?)


-- For example I add a colum for M or N or P, and it looks like your 4th column is the first part of the "key"
-- 3309004
CREATE TABLE #bkTemp (RowGroup Int, RowOrder char(1), ColId Int IDENTITY(1,1), Col4 varchar(7), Contents varchar(10))
GO

-- Header Row
INSERT INTO #bkTemp (RowGroup, RowOrder, Col4, Contents) SELECT 1,'A','0845B02','1|S|N|0845B02|CENTRAL HEALTH CENTER|03/05/2003|'

--Data Rows
INSERT INTO #bkTemp (RowGroup, RowOrder, Col4, Contents) SELECT 2,'P','3309004','2|P|S|3309004||03/30/2002|M|JOHN|DOE|||||||||||||||||||||||||||'
INSERT INTO #bkTemp (RowGroup, RowOrder, Col4, Contents) SELECT 2,'M','3309004','3|M|S|3309004||03/30/2002|M|JOHN|DOE||||||||||||||||01/01/2003|20|V|DR WOO|WANG|123456|||||'

-- Trailer Rows
INSERT INTO #bkTemp (RowGroup, RowOrder, Col4, Contents) SELECT 3,'Z','3309004','Some Trailer Information'
GO
SELECT Contents From #bkTemp Order by RowGroup, RowOrder, Col4
Go
DROP TABLE bk_Temp

Brett

8-)

EDIT: Come on Valter, push it over the top (and is that like the year 2000 or 2001 being the millenium?)

Edited by - x002548 on 04/29/2003 16:50:00

Edited by - x002548 on 04/29/2003 16:51:18

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-04-29 : 16:57:50

The catch: Firstly, using the Rowgroup, Roworder, Col4, contents Order will put M before P, which is wrong. (Oops, I see I posted this wrong previously. I'm sorry. It is P before M)
Second, the Col4 you are referring to is actually the patient's account number, nothing more. Theoretically, as you can see in my first data sample with patient 3524801 (Jane Doe), if a patient has two entry sets, you will get first two M's, then two P's. Totally bad.

I think I'll just use a cursor... (deep heartfelt sigh...) My consolation: cursors were created for a reason, weren't they?

Sarah Berger MCSD

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-29 : 18:53:31

I think you'll be able to finish from here.

DECLARE @SvcDate AS DATETIME

SELECT @SvcDate = '20020101'

DECLARE @Patients TABLE ([ID] INTEGER, Account NVARCHAR(25), DOB DATETIME, Sex NVARCHAR(1), FirstName NVARCHAR(25), LastName NVARCHAR(25))
DECLARE @PatImmune TABLE (PatID INTEGER, ImmuType NVARCHAR(25), ImmuDate DATETIME)
DECLARE @VouchersDetail TABLE (Voucher INTEGER, ProcCode NVARCHAR(25), ServiceDate DATETIME)
DECLARE @Vouchers TABLE (VoucherNo INTEGER,DrCode NVARCHAR(1))
DECLARE @Doctors TABLE (Code NVARCHAR(1),StateLic NVARCHAR(25),FirstName NVARCHAR(25),LastName NVARCHAR(25))
DECLARE @DOHImmune TABLE (CPT NVARCHAR(25), CIRCode NVARCHAR(10))

DECLARE @OutputTable TABLE
(
RowID INT IDENTITY(1,1),
Col1 NVARCHAR(1),
Col2 NVARCHAR(1),
Col3 NVARCHAR(25),
Col4 NVARCHAR(25),
Col5 NVARCHAR(10),
Col6 NVARCHAR(25),
Col7 NVARCHAR(25),
Col8 NVARCHAR(10),
Col9 NVARCHAR(25),
Col10 NVARCHAR(1),
Col11 NVARCHAR(25),
Col12 NVARCHAR(25),
Col13 NVARCHAR(25)
)

/*
2|P|S|3309004||03/30/2002|M|JOHN|DOE|||||||||||||||||||||||||||
3|M|S|3309004||03/30/2002|M|JOHN|DOE||||||||||||||||01/01/2003|20|V|DR WOO|WANG|123456|||||
4|P|S|1412613||12/15/2001|M|MIKE|GREEN|||||||||||||||||||||||||||
5|M|S|1412613||12/15/2001|M|MIKE|GREEN||||||||||||||||01/02/2003|20|V|DR MARCUS|WELBY|123456|||||
6|P|S|1412613||12/15/2001|M|JOE|BLACK|||||||||||||||||||||||||||
7|M|S|1412613||12/15/2001|M|JOE|BLACK||||||||||||||||01/02/2003|51|V|DR MARCUS|WELBY|123456|||||
*/
INSERT INTO @Patients ([ID],Account,DOB,Sex,FirstName,LastName)
SELECT 1, '3309004','20020330','M','JOHN','DOE' UNION ALL
SELECT 2, '1412613','20011215','M','MIKE','GREEN' UNION ALL
SELECT 3, '1412613','20011215','M','JOE','BLACK'

INSERT INTO @PatImmune (PatID, ImmuType, ImmuDate)
SELECT 1, 'TYPE1', '20030101' UNION ALL
SELECT 2, 'TYPE1', '20030102' UNION ALL
SELECT 3, 'TYPE2', '20030102'

INSERT INTO @VouchersDetail (Voucher,ProcCode,ServiceDate)
SELECT 1,'TYPE1','20030101' UNION ALL
SELECT 2,'TYPE1','20030102' UNION ALL
SELECT 3,'TYPE2','20030102'

INSERT INTO @Vouchers (VoucherNo, DrCode)
SELECT 1, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 3, 'B'

INSERT INTO @Doctors (Code, StateLic, FirstName, LastName)
SELECT 'A', '123456','DR WOO', 'WANG' UNION ALL
SELECT 'B', '123456','DR MARCUS', 'WELBY'

INSERT INTO @DOHImmune (CPT, CIRCode)
SELECT 'TYPE1', '20' UNION ALL
SELECT 'TYPE2', '51'

INSERT INTO @OutputTable (Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13)
SELECT A.Col1, A.Col2, A.Col3, A.Col4, A.Col5, A.Col6, A.Col7, A.Col8, A.Col9, A.Col10, A.Col11, A.Col12, A.Col13
FROM
(
SELECT 'S' Col1,'N' Col2,'0845B02' Col3,'CENTRAL HEALTH CENTER' Col4,'03/05/2003' Col5,
'' Col6,'' Col7,'' Col8,'' Col9,'' Col10,'' Col11, '' Col12, '' Col13, '' Col14, 1 Col15
UNION ALL
SELECT
'P' Col1, 'S' Col2, LTRIM(STR(P.Account)) Col3, CONVERT(CHAR(10),P.DOB,101) Col4, P.Sex Col5,
RTRIM(P.FirstName) Col6, RTRIM(P.LastName) Col7, '' Col8, '' Col9, '' Col10,
'' Col11, '' Col12, '' Col13, STR(P.[ID]) Col14, 2 Col15
FROM
@Patients P
INNER JOIN @PatImmune I ON P.ID = I.Patid
WHERE I.ImmuDate >= @SvcDate
UNION ALL
SELECT
'M' Col1, 'S' Col2, LTRIM(STR(P.Account)) Col3,CONVERT(CHAR(10),P.DOB,101) Col4,
P.Sex Col5, RTRIM(P.FirstName) Col6, RTRIM(P.LastName) Col7,
LTRIM(CONVERT(CHAR(10),I.ImmuDate,101)) Col8, LTRIM(RIGHT(STR(DI.CIRCode),4)) Col9,
'V' Col10, RTRIM(D.FirstName) Col11, RTRIM(D.LastName) Col12, RTRIM(D.StateLic) Col13, STR(P.[ID]) Col14, 2 Col15
FROM @Patients P
INNER JOIN @Patimmune I ON I.Patid = P.ID
INNER JOIN @DOHImmune DI ON DI.CPT = I.ImmuType
INNER JOIN @Vouchersdetail VD ON VD.ProcCode = I.ImmuType AND VD.ServiceDate = I.ImmuDate
INNER JOIN @Vouchers V ON V.VoucherNo = VD.Voucher
INNER JOIN @Doctors D ON V.DrCode = D.Code WHERE I.ImmuDate >= @SvcDate
UNION ALL
SELECT
'U' Col1, '' Col2, '' Col3, '' Col4, '' Col5,
'' Col6,'' Col7,'' Col8,'' Col9,'' Col10,
'' Col11, '' Col12, '' Col13, '' Col14, 3 Col15
) A
ORDER BY A.Col15,A.Col14,A.Col4,Col3,Col1 DESC

SELECT * FROM @OutputTable

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-29 : 18:54:53

And his 1000th post is here!!!

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-30 : 09:54:19

I guess he wanted to save 1000 with a serious post...way to go Valter!!!

Sarah, I understand (well not really) all the caveats of your file requirement.

My question to you is, now what do you do with it...I mean if it's the input to a process, how does the processing application handle this file?

I mean, I get Jane Doe (with the P's and the M's in the correct order) what happens when she shows up again? esp. with different info...

Just curious

PS Did I say way to go Valter!!!!

Brett

8-)

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-04-30 : 11:42:43

Thanks A Zillion, Valter! That's Perfect!!
Hey, and Congrats on becoming a Flowing Fount of Yak Knowledge!

Actually, I think Valter should get a custom title. This title is already carried by Page47, I think.

To Brett: A text file in this format gets submitted to the NYC Department of Health by pediatricians for an immunization registry, so they keep track of children's immunizations. Their system reads it out into some kind of db, I don't know what. This pipe-delimited format is their own idea.
Each vaccination represents a P and M set, so if Jane Doe has two different vaccinations, that should be two separate sets. Also, their system has no problem with duplicate data, should they happen to get it, they just discard it.

Sarah Berger MCSD

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-30 : 14:29:02

So then Jane Doe's 12th and 13th records will be ignored...correct?

btw, do you work in the city?

Brett

8-)

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-04-30 : 16:40:50

Not Really. Jane Doe's lines 8 & 9 have a 03 in the vaccination column (the one before the V) representing an MMR shot, and the 12 & 13 set has a 51, representing a HepB-HIB shot, so it's not duplicate data.

I don't work in the city (NYC), nor do I work for the City. I work for a medical software vendor, although I am just finishing a large project.

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources