Need help with a part of my stored procedure

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

Need help with a part of my stored procedure

Author

Topic

PGallahe
Starting Member

10 Posts

Posted - 2011-10-21 : 13:57:01

I am trying to collect the last five order dates, if available, for a patient and I want each of the dates to be in its own column.

Example

Patient OrderDat1 OrderDat2 OrderDat3 OrderDat4 OrderDat5

Not all patients with have an order and if they do there may not be five previous or there could be more than five.

Any help is appreciated.

DBAPBFL
Starting Member

11 Posts

Posted - 2011-10-21 : 16:34:27

I did it for 3. it this works for you, you can modify it for 5 so you understand the code...

create table #PatientOrders (Patient varchar(10),OrderDate datetime)
insert #PatientOrders values ('Patient1','1/1/2011')
insert #PatientOrders values ('Patient1','1/2/2011')
insert #PatientOrders values ('Patient2','2/1/2011')
insert #PatientOrders values ('Patient2','2/2/2011')
insert #PatientOrders values ('Patient2','2/3/2011')
insert #PatientOrders values ('Patient2','2/4/2011')
select * from #PatientOrders

select *
from
(select
Patient
,'OrderDat'+ cast(row_number() over (partition by Patient order by OrderDate) as varchar) as OrderDateNum
,OrderDate
from #PatientOrders) as s
pivot
(
max(OrderDate)
for OrderDateNum in ([OrderDat1],[OrderDat2],[OrderDat3])
) AS p

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-22 : 04:39:51

quote:
Originally posted by DBAPBFL

I did it for 3. it this works for you, you can modify it for 5 so you understand the code...

create table #PatientOrders (Patient varchar(10),OrderDate datetime)
insert #PatientOrders values ('Patient1','1/1/2011')
insert #PatientOrders values ('Patient1','1/2/2011')
insert #PatientOrders values ('Patient2','2/1/2011')
insert #PatientOrders values ('Patient2','2/2/2011')
insert #PatientOrders values ('Patient2','2/3/2011')
insert #PatientOrders values ('Patient2','2/4/2011')
select * from #PatientOrders

select *
from
(select
Patient
,'OrderDat'+ cast(row_number() over (partition by Patient order by OrderDate desc ) as varchar) as OrderDateNum
,OrderDate
from #PatientOrders) as s
pivot
(
max(OrderDate)
for OrderDateNum in ([OrderDat1],[OrderDat2],[OrderDat3])
) AS p

as it says last x dates

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-22 : 04:43:11

quote:
Originally posted by DBAPBFL

I did it for 3. it this works for you, you can modify it for 5 so you understand the code...

create table #PatientOrders (Patient varchar(10),OrderDate datetime)
insert #PatientOrders values ('Patient1','1/1/2011')
insert #PatientOrders values ('Patient1','1/2/2011')
insert #PatientOrders values ('Patient2','2/1/2011')
insert #PatientOrders values ('Patient2','2/2/2011')
insert #PatientOrders values ('Patient2','2/3/2011')
insert #PatientOrders values ('Patient2','2/4/2011')
select * from #PatientOrders

select *
from
(select
Patient
,'OrderDat'+ cast(row_number() over (partition by Patient order by OrderDate) as varchar) as OrderDateNum
,OrderDate
from #PatientOrders) as s
pivot
(
max(OrderDate)
for OrderDateNum in ([OrderDat1],[OrderDat2],[OrderDat3])
) AS p

also its better to specify the length when casting to varchar though in this case it wont cause much harm

http://visakhm.blogspot.com/2010/02/importance-of-specifying-length-in.html

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

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-22 : 08:51:08

you can specify varchar(max)

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-22 : 09:08:32

quote:
Originally posted by jassi.singh

you can specify varchar(max)

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

only if you're sure that you need to store data above 8000 character limit

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

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-23 : 04:35:16

if you are sure about the character limit then you can limit to 4000

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-23 : 10:52:53

quote:
Originally posted by jassi.singh

if you are sure about the character limit then you can limit to 4000

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Hmm... not necessarily. there's no mandatory condition that it should have minimum 4000 characters length. The best bet is it depends on your data

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-24 : 11:58:25

[code]select *
from
(select
Patient
,'OrderDat'+ cast(dense_rank() over (partition by Patient order by OrderDate desc) as varchar(3)) as OrderDateNum
,OrderDate
from #PatientOrders) as s
pivot
(
max(OrderDate)
for OrderDateNum in ([OrderDat1],[OrderDat2],[OrderDat3])
) AS p
[/code]

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

PGallahe
Starting Member

10 Posts

Posted - 2011-10-24 : 12:03:44

Thank you, this works perfectly. I was able to tweek it a bit as I might have multiple orders for a patient in a day and also I wanted them to be in descending order. Got it all done and it looks great. I will now incorportate it into the rest of my stored procedure.

I would be very happy to mark that your answer was appropriate but not sure how to do that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-24 : 12:07:02

quote:
Originally posted by PGallahe

Thank you, this works perfectly. I was able to tweek it a bit as I might have multiple orders for a patient in a day and also I wanted them to be in descending order. Got it all done and it looks great. I will now incorportate it into the rest of my stored procedure.

I would be very happy to mark that your answer was appropriate but not sure how to do that.

not any direct way to do that other than appending [solved] to topic title

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

PGallahe
Starting Member

10 Posts

Posted - 2011-10-24 : 12:52:27

Ok, now I am stumped again. I am not sure how to join this information with the rest of the data in my stored procedure.

I have multiple selects where I am gathering information into several declared tables so that I can output all the needed information. How do I join this data with the others? I hope all this makes sense.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-24 : 13:36:26

would you mind showing your rest of stored proc?
best bet is to put this in a temporary table and join to others on common fields

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

PGallahe
Starting Member

10 Posts

Posted - 2011-10-24 : 13:52:05

Here is what I have:
Declare @Patient Table
(
PatientOid int
,PatientVisitOid int
,CollectedDT datetime
,UserAbbrName varchar(100)
,FindingAbbr varchar(100)
,Value varchar(100)
,FindingName varchar(100)
,PatientAccountID varchar(20)
,POLST varchar(10)
,DNRDT1 datetime
,DNRDT2 datetime
,DNRDT3 datetime
,DNRDT4 datetime
,DNRDT5 datetime
)

Declare @DNR Table
(
PatientOid int
,OrderDT datetime
)

Insert into @DNR
select
PatientOid = P.PatientOid
,OrderDT = o.StatusEnteredDateTime
From @Patient p
Inner Join Order o with(Nolock)
on p.PatientOid = o.Patient_oid
where o.OrderAbbreviation in ('1010011', '1010002', '1010001')

insert into @Patient
select
PatientOid = p.PatientOid
,PatientVisitOid = p.PatientVisitOid
,CollectedDT = ha.CollectedDT
,UserAbbrName = ha.UserAbbrName
,FindingAbbr = O.FindingAbbr
,Value = o.Value
,FindingName = o.FindingName
,PatientAccountID = p.PatientAccountID
,POLST = ''
,DNRDT1 = NULL
,DNRDT2 = NULL
,DNRDT3 = NULL
,DNRDT4 = NULL
,DNRDT5 = NULL
From Assessment a with(Nolock)
Inner Join Observation o with(nolock)
on a.Patient_Oid = o.Patient_oid
and a.AssessmentID = o.AssessmentID
and a.IsVersioned = o.IsVersioned
Where o.FindingAbbr = 'A_HomeEnviron'
AND (O.Value like '%Assisted Living%' or
o.Value like '%SNF%' or
o.Value like '%Nursing%' or
o.Value like '%ECF%')
and a.AssessmentStatus = 'Complete'

Update @Patient
Set POLST = o.Value
From @Patient p
Inner Join Assessment a with(Nolock)
on p.PatientOid = a.Patient_oid
and p.PatientVisitOid = a.PatientVisit_oid
Inner Join Observation o with(nolock)
on p.PatientOID = o.Patient_oid
and a.AssessmentID = o.AssessmentID
and a.IsVersioned = o.IsVersioned
Where o.FindingAbbr = 'A_DB_ADB POLST'
and o.Value <> ' '
and a.AssessmentStatus = 'Complete'

select *
from
(select
PatientOid
,'OrderDat'+ cast(row_number() over (partition by PatientOid Order by OrderDT desc) as varchar) as OrderDateNum
,OrderDT
from @DNR
Group by PatientOid
,OrderDT) as s
pivot
(
max(OrderDT)
for OrderDateNum in ([OrderDat1],[OrderDat2],[OrderDat3], [OrderDat4], [OrderDat5])
) AS p

What I need is to have the dates some how or another be part of the @Patient declared table. There will not be a one to one on the @patient information and the @dnr information, there will be more in @patient.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 00:03:13

so how will be data from @Patient after these additions? can you show sample structure with data?

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

PGallahe
Starting Member

10 Posts

Posted - 2011-10-25 : 11:48:39

I want the data to look like this:

I want to have the data look like this:

CollectedDT UserAbbrName FindingAbbr Value FindingName PatientAccountID POLST DNRDT1 DNRDT2 DNRDT3 DNRDT4 DNRDT5

DNRDT1 through DNRDT5 will represent the data that is in [OrderDat1],[OrderDat2],[OrderDat3], [OrderDat4], [OrderDat5]. I hope that makes sense.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-26 : 00:56:27

replace last pivot statement with this


update p
set p.DNRDT1 = m.[OrderDat1] 
,p.DNRDT2 = m.[OrderDat2]
,p.DNRDT3 = m.[OrderDat3]
,p.DNRDT4 = m.[OrderDat4]
,p.DNRDT5 = m.[OrderDat5]
from @patient p
join(
select *
from
(select
PatientOid
,'OrderDat'+ cast(row_number() over (partition by PatientOid Order by OrderDT desc) as varchar) as OrderDateNum
,OrderDT
from @DNR
Group by PatientOid
,OrderDT) as s
pivot
(
max(OrderDT)
for OrderDateNum in ([OrderDat1],[OrderDat2],[OrderDat3], [OrderDat4], [OrderDat5])
) AS p
)m
on m.PatientOid = p.PatientOid

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

PGallahe
Starting Member

10 Posts

Posted - 2011-10-27 : 15:53:11

Again, thank you so much. This resolved the problem and I am getting exactly what I want. I just wish that I understood the code - in time I guess.

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-27 : 16:24:25

why isn't this a presentation issue?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources