October 17, 2010 at 6:47 pm
Heh... screw it. If you really insist on doing it using RBAR, do it in C#. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2010 at 2:14 pm
Here is the CODE for the first procedure:
ALTER procedure [dbo].[TP_PatientNote]
(
@clientkey int
,@op__docid int
,@DOS DATETIME
)
as
--PATIENT NOTE DATA
DECLARE @AGE INT
DECLARE @DOB DATETIME
DECLARE @ATTENDED VARCHAR (40)
DECLARE @CHARGEKEY INT
DECLARE @CLINICIAN VARCHAR(40)
DECLARE @FULLNAME VARCHAR(40)
DECLARE @PLAN_NOTE VARCHAR(MAX)
DECLARE @SUBOBJ VARCHAR(MAX)
DECLARE @VISITDESC VARCHAR(100)
DECLARE @SUPSIGN_FULLNAME VARCHAR(40)
DECLARE @SUPSIGN_CREDTYPE VARCHAR(10)
DECLARE @BPD INT
DECLARE @BMP INT
DECLARE @CGAS INT
DECLARE @BPS INT
DECLARE @PNOP__DOCID INT
--CURRENT MEDICATIONS AS OF PROGRESSNOTE DOS
DECLARE @MEDS_DESC VARCHAR(40)
DECLARE @INSTRUCTIONS VARCHAR(300)
DECLARE @DATE_FORM DATETIME
DECLARE @ENDDATE DATETIME
DECLARE @M_CLIENTKEY INT
DECLARE @M_STAFFKEY INT
DECLARE @ORDERTYPE VARCHAR(10)
DECLARE @PILLAMOUNT INT
DECLARE @REFILLS VARCHAR(20)
DECLARE @DOSE1 VARCHAR(30)
DECLARE @ORDERED_BY VARCHAR(41)
DECLARE @M_OPDOCID int
--stored procedure fields
DECLARE @ROWCOUNT int
DECLARE @MAXROWS int
delete from FD__PATIENTNOTE_RPT
SET @ROWCOUNT =1
declare @patient_note table
(ROWIDINT IDENTITY (1,1),
PNop__docid int,
dos datetime,
clientkey int,
AGE INT,
DOB DATETIME,
ATTENDED VARCHAR (40),
CHARGEKEY INT,
CLINICIAN VARCHAR(40),
FULLNAME VARCHAR(40),
PLAN_NOTE VARCHAR(MAX),
SUBOBJ VARCHAR(MAX),
VISITDESC VARCHAR(100),
SUPSIGN_FULLNAME VARCHAR(40),
SUPSIGN_CREDTYPE VARCHAR(10),
BPD INT,
CGAS INT,
BPS INT
)
insert into @patient_note
select
op__docid,
dos ,
clientkey,
AGE ,
DOB ,
ATTENDED,
CHARGEKEY,
CLINICIAN,
FULLNAME,
PLAN_NOTE,
SUBOBJ,
VISITDESC,
SUPSIGN_FULLNAME,
SUPSIGN_CREDTYPE ,
BPD,
--BMP,
CGAS,
BPS
from fd__patient_note
where op__docid=@op__docid
--and clientkey=@clientkey
--and dos=@dos
select @maxrows =@@rowcount
while @rowcount <=@maxrows
begin
select
@PNOP__DOCID=PNOP__DOCID
,@AGE =age
,@DOB =dob
,@ATTENDED=attended
,@CHARGEKEY =chargekey
,@CLINICIAN =clinician
,@FULLNAME =fullname
,@PLAN_NOTE = plan_note
,@SUBOBJ = subobj
,@VISITDESC = visitdesc
,@SUPSIGN_FULLNAME = supsign_fullname
,@SUPSIGN_CREDTYPE = supsign_credtype
,@BPD = bpd
--,@BMP = bmp
,@CGAS =cgas
,@BPS = bpd
,@CLIENTKEY=CLIENTKEY
from @patient_note
where rowid = @rowcount
exec tp_PN_CURRENT_MEDS
@clientkey
,@dos
INSERT INTO FD__PATIENTNOTE_RPT
(PN_OPDOCID
,PN_CLIENTKEY
,AGE
,DOB
,ATTENDED
,CHARGEKEY
,CLINICIAN
,FULLNAME
,PLAN_NOTE
,SUBOBJ
,VISITDESC
,SUPSIGN_FULLNAME
,SUPSIGN_CREDTYPE
,BPD
,CGAS
,BPS
)
VALUES
(
@PNOP__DOCID
,@CLIENTKEY
,@AGE
,@DOB
,@ATTENDED
,@CHARGEKEY
,@CLINICIAN
,@FULLNAME
,@PLAN_NOTE
,@SUBOBJ
,@VISITDESC
,@SUPSIGN_FULLNAME
,@SUPSIGN_CREDTYPE
,@BPD
--,@BMP = bmp
,@CGAS
,@BPS
)
select @rowcount=@rowcount + 1
end
select *
from FD__PATIENTNOTE_RPT
_____________________________________________________________________________________
Here is the code for the second procedure:
ALTER procedure [dbo].[TP_PN_Current_Meds]
@clientkey int,
@dos datetime
as
--set @clientkey= 61876
--set @dos = '3/3/2010'
DECLARE @MEDS_DESC VARCHAR(40)
DECLARE @INSTRUCTIONS VARCHAR(300)
DECLARE @DATE_FORM DATETIME
DECLARE @ENDDATE DATETIME
DECLARE @M_CLIENTKEY INT
DECLARE @M_STAFFKEY INT
DECLARE @ORDERTYPE VARCHAR(10)
DECLARE @PILLAMOUNT INT
DECLARE @REFILLS VARCHAR(20)
DECLARE @DOSE1 VARCHAR(30)
DECLARE @ORDERED_BY VARCHAR(41)
DECLARE @M_OPDOCID int
DECLARE @ROWCOUNT int
DECLARE @MAXROWS int
SET @ROWCOUNT =1
declare @currentmeds table
(ROWIDINT IDENTITY (1,1),
meds_desc varchar(40)
,instructions varchar(300)
,date_form datetime
,enddate datetime
,clientkey int
,staffkey int
,ordertype varchar(10)
,pillamount int
,refills varchar(20)
,dose1 varchar(30)
,ordered_by varchar(41)
,op__docid int
)
insert @currentmeds
select MEDS_DESC,INSTRUCTIONS,DATE_FORM,enddate,ClientKey,staffkey,ordertype
,pillamount, refills, dose1, ordered_by,op__docid
from fd__medication
where clientkey=@clientkey and
@dos between convert(datetime,convert(varchar(20),Date_form, 101)) and isnull (enddate , (getdate() ))
select @maxrows =@@rowcount
while @rowcount <=@maxrows
begin
select @meds_desc=meds_desc
,@instructions=instructions
,@date_form=date_form
,@enddate=enddate
,@m_clientkey=clientkey
,@m_staffkey=staffkey
,@ordertype=ordertype
,@pillamount =pillamount
,@refills=refills
,@dose1 =dose1
,@ordered_by=ordered_by
,@m_opdocid=op__docid
from @currentmeds
where rowid = @rowcount
insert into fd__patientnote_rpt
(meds_desc
,instructions
,date_form
,enddate
,m_clientkey
,m_staffkey
,m_opdocid
,ordertype
,pillamount
,refills
.dose1
,ordered_by
)
values
(@meds_desc
,@instructions
,@date_form
,@enddate
,@m_clientkey
,@m_staffkey
,@m_opdocid
,@ordertype
,@pillamount
,@refills
,@dose1
--,@ordered_by
)
select @rowcount=@rowcount + 1
end
_________________________________________________________________________________
Basicall the results from the second procedure, i was hoping to put into a cursor into the first procedure because the results from the first procedure will always be one record. The second procedure can have more than one records.
Does that sound clear?
October 19, 2010 at 5:49 am
Quick note that has nothing to do with resolving your problem, but could save you a lot of typing. You don't have to Declare each variable on a separate statement. A simple:
Declare Var1 int, Var2 varchar(10), Var3 Char(1)
Does the job just as well. The diff is you save typing "Declare" a million times.
October 19, 2010 at 6:12 am
A few notes. Use Truncate instead of Delete. Truncate logs only the extents being deleted, not the entire record like Delete does. Hence, it's quicker. Also, be aware that using it on a patient by patient basis like this will remove records from your report table every time a new patient is updated. Is this expected behavior?
I don't understand why you need to use loops and table variables. They seem to make no difference to the result set. But if you post sample data and the actual DDL behind the tables being inserted into, I could test this proc to make sure it's doing what you need it to do.
Lastly, is it correct that you're inserting both record sets into the same table? And that one record set is only supposed to have one result by the other has multiple record sets?
If so, you need to seriously consider a redesign of your schema. That's not best database practices.
Here's my version, combining your two procs into one:
ALTER procedure [dbo].[TP_PatientNote]
(@clientkey int,@op__docid int,@DOS DATETIME )
as
Truncate Table FD__PATIENTNOTE_RPT; --Takes less time than a full delete
INSERT INTO FD__PATIENTNOTE_RPT
(PN_OPDOCID
,PN_CLIENTKEY
,AGE
,DOB
,ATTENDED
,CHARGEKEY
,CLINICIAN
,FULLNAME
,PLAN_NOTE
,SUBOBJ
,VISITDESC
,SUPSIGN_FULLNAME
,SUPSIGN_CREDTYPE
,BPD
,CGAS
,BPS
)
(SELECT
PNOP__DOCID
,CLIENTKEY
,AGE
,DOB
,ATTENDED
,CHARGEKEY
,CLINICIAN
,FULLNAME
,PLAN_NOTE
,SUBOBJ
,VISITDESC
,SUPSIGN_FULLNAME
,SUPSIGN_CREDTYPE
,BPD
--,BMP
,CGAS
,BPS
from fd__patient_note
where op__docid=@op__docid);
insert into fd__patientnote_rpt
(meds_desc
,instructions
,date_form
,enddate
,m_clientkey
,m_staffkey
,m_opdocid
,ordertype
,pillamount
,refills
.dose1
,ordered_by
)
(select MEDS_DESC,INSTRUCTIONS,DATE_FORM,enddate,ClientKey,staffkey,ordertype
,pillamount, refills, dose1, ordered_by
from fd__medication
where clientkey=@clientkey and
@dos between convert(datetime,convert(varchar(20),Date_form, 101)) and isnull (enddate , (getdate() )) );
select *
from FD__PATIENTNOTE_RPT;
GO
October 19, 2010 at 7:19 am
Thank You very much for your help. I am dumping these records into one table to then use for a Crystal Report, thats why I am deleting my results before the proc executes. Basically its just a temp table so I can display all my results via Crystal Reports. Since the first proc is a one result set, and the second is multiple results, I can use the grouping option on Crystal to make sure my results looks like one data set. Does that make sense?
I would love to add some results and ddl, but unforutantely, I am dealing with patient data. I can dump some dummy data to display instead. Here is the DDL for the temp table.
CREATE TABLE [dbo].[FD__PATIENTNOTE_RPT](
[PN_OPDOCID] [int] NULL,
[PN_CLIENTKEY] [int] NULL,
[AGE] [int] NULL,
[DOB] [datetime] NULL,
[ATTENDED] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CHARGEKEY] [int] NULL,
[CLINICIAN] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FULLNAME] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PLAN_NOTE] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SUBOBJ] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VISITDESC] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SUPSIGN_FULLNAME] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SUPSIGN_CREDTYPE] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BPD] [int] NULL,
[BMP] [int] NULL,
[CGAS] [int] NULL,
[BPS] [int] NULL,
[MEDS_DESC] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[INSTRUCTIONS] [varchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DATE_FORM] [datetime] NULL,
[ENDDATE] [datetime] NULL,
[M_CLIENTKEY] [int] NULL,
[M_STAFFKEY] [int] NULL,
[M_OPDOCID] [int] NULL,
[ORDERTYPE] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PILLAMOUNT] [int] NULL,
[REFILLS] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DOSE1] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ORDERED_BY] [varchar](41) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
October 19, 2010 at 10:25 am
Marv-1058651 (10/19/2010)
I would love to add some results and ddl, but unforutantely, I am dealing with patient data. I can dump some dummy data to display instead. Here is the DDL for the temp table.
Sample Data, Dummy Data, same thing. I scrub my own data before posting because of privacy laws, so I totally get that. Just come up with something that approximates the feel of several records that we can use to verify the solutions we're helping you with.
Expected output would help too.
October 19, 2010 at 11:06 pm
Marv-1058651 (10/19/2010)
I am dumping these records into one table to then use for a Crystal Report, thats why I am deleting my results before the proc executes.
It doesn't solve your current problems but let me ask... what's going to happen when two people run the same report at almost the same time?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply