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)
 need help - multiple rows

Author  Topic 

tmeans11
Starting Member

3 Posts
Posted - 2009-01-18 : 16:22:36
I've never put a query on a forum before. I am learning sql and wondering if anyone can give me some tips. Is there anyway in my query to only show the rows with the top dateofentry or max dateofentry? My query is showing multiple rows for each patient record dateofentry. I only want to see the max dateofentry for each row...
___________________________________________________________
Drop table #tmp1
select guarantor.guarantorid, guarantor.first, guarantor.middle, guarantor.last,
guarantor.address1, guarantor.address2, guarantor.city, guarantor.state,
guarantor.zip, guarantor.ssn, guarantor.phone1, guarantor.phone2, guarantor.lastStatement,
patientprofileagg.patbalance, paymentmethod.dateofentry

into #tmp1
from guarantor
inner join patientprofile on guarantor.guarantorid = patientprofile.guarantorid
inner join patientprofileagg on patientprofile.patientprofileid = patientprofileagg.patientprofileid
Inner join patientvisit on patientprofile.patientprofileid = patientvisit.patientprofileid
inner join VisitTransactions ON patientvisit.patientvisitid = visittransactions.patientvisitid
inner join transactions on visittransactions.visittransactionsid = transactions.visittransactionsid
INNER JOIN PaymentMethod ON visittransactions.paymentmethodid = PaymentMethod.paymentmethodid

Where patientprofileagg.patbalance > 50.00
and transactions.type = 't'
and guarantor.lastStatement is not null
AND transactions.action = 't'
and patientprofile.deathdate is null
AND PaymentMethod.dateofentry >= '2008-02-01'
and PaymentMethod.dateofentry <'2008-08-31'

order by guarantor.last Asc

select *
from #tmp1
_____________________________________________________
Can anyone help me? I can also include a spreadsheet if that will help. Basically I want to see one row per patient with the max payment date...I am at a loss...

timmy
Master Smack Fu Yak Hacker

1242 Posts
Posted - 2009-01-18 : 21:37:52
Hi,

What I normally do in this situation is create a temp table containing the ID (guarantor ID in your case), and the max date of entry. Then you link this new table to the existing table by the ID and MaxDateOfEntry. A bit hard without a diagram but I hope you get my drift.

Cheers,

Tim
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-01-18 : 23:04:42
show some sample data and explain what you want
Go to Top of Page

raky
Aged Yak Warrior

767 Posts
Posted - 2009-01-24 : 00:35:33
quote:
Originally posted by tmeans11

I've never put a query on a forum before. I am learning sql and wondering if anyone can give me some tips. Is there anyway in my query to only show the rows with the top dateofentry or max dateofentry? My query is showing multiple rows for each patient record dateofentry. I only want to see the max dateofentry for each row...
___________________________________________________________
Drop table #tmp1
select guarantor.guarantorid, guarantor.first, guarantor.middle, guarantor.last,
guarantor.address1, guarantor.address2, guarantor.city, guarantor.state,
guarantor.zip, guarantor.ssn, guarantor.phone1, guarantor.phone2, guarantor.lastStatement,
patientprofileagg.patbalance, paymentmethod.dateofentry

into #tmp1
from guarantor
inner join patientprofile on guarantor.guarantorid = patientprofile.guarantorid
inner join patientprofileagg on patientprofile.patientprofileid = patientprofileagg.patientprofileid
Inner join patientvisit on patientprofile.patientprofileid = patientvisit.patientprofileid
inner join VisitTransactions ON patientvisit.patientvisitid = visittransactions.patientvisitid
inner join transactions on visittransactions.visittransactionsid = transactions.visittransactionsid
INNER JOIN PaymentMethod ON visittransactions.paymentmethodid = PaymentMethod.paymentmethodid

Where patientprofileagg.patbalance > 50.00
and transactions.type = 't'
and guarantor.lastStatement is not null
AND transactions.action = 't'
and patientprofile.deathdate is null
AND PaymentMethod.dateofentry >= '2008-02-01'
and PaymentMethod.dateofentry <'2008-08-31'

order by guarantor.last Asc

select *
from #tmp1
_____________________________________________________
Can anyone help me? I can also include a spreadsheet if that will help. Basically I want to see one row per patient with the max payment date...I am at a loss...


Iam not producing exact solution for ur problem but iam giving an example which is some what similar to your description. Please look into it. It may be helpful..


declare @temp table ( patientid int, Patientname varchar(40), Dateofentry datetime)
insert into @temp
select 1,'George',Getdate()-100 union all
select 2,'Dean',Getdate()-20 union all
select 1,'George',Getdate()-5 union all
select 2,'Dean',Getdate()-2


select patientid,patientname, dateofentry
from ( select row_number() over (partition by patientid order by dateofentry desc ) as sno,* from @temp )t
where t.sno = 1
Go to Top of Page

bklr
Master Smack Fu Yak Hacker

1693 Posts
Posted - 2009-01-24 : 00:48:53
quote:
Originally posted by raky

quote:
Originally posted by tmeans11

I've never put a query on a forum before. I am learning sql and wondering if anyone can give me some tips. Is there anyway in my query to only show the rows with the top dateofentry or max dateofentry? My query is showing multiple rows for each patient record dateofentry. I only want to see the max dateofentry for each row...
___________________________________________________________
Drop table #tmp1
select guarantor.guarantorid, guarantor.first, guarantor.middle, guarantor.last,
guarantor.address1, guarantor.address2, guarantor.city, guarantor.state,
guarantor.zip, guarantor.ssn, guarantor.phone1, guarantor.phone2, guarantor.lastStatement,
patientprofileagg.patbalance, paymentmethod.dateofentry

into #tmp1
from guarantor
inner join patientprofile on guarantor.guarantorid = patientprofile.guarantorid
inner join patientprofileagg on patientprofile.patientprofileid = patientprofileagg.patientprofileid
Inner join patientvisit on patientprofile.patientprofileid = patientvisit.patientprofileid
inner join VisitTransactions ON patientvisit.patientvisitid = visittransactions.patientvisitid
inner join transactions on visittransactions.visittransactionsid = transactions.visittransactionsid
INNER JOIN PaymentMethod ON visittransactions.paymentmethodid = PaymentMethod.paymentmethodid

Where patientprofileagg.patbalance > 50.00
and transactions.type = 't'
and guarantor.lastStatement is not null
AND transactions.action = 't'
and patientprofile.deathdate is null
AND PaymentMethod.dateofentry >= '2008-02-01'
and PaymentMethod.dateofentry <'2008-08-31'

order by guarantor.last Asc

select *
from #tmp1
_____________________________________________________
Can anyone help me? I can also include a spreadsheet if that will help. Basically I want to see one row per patient with the max payment date...I am at a loss...


Iam not producing exact solution for ur problem but iam giving an example which is some what similar to your description. Please look into it. It may be helpful..


declare @temp table ( patientid int, Patientname varchar(40), Dateofentry datetime)
insert into @temp
select 1,'George',Getdate()-100 union all
select 2,'Dean',Getdate()-20 union all
select 1,'George',Getdate()-5 union all
select 2,'Dean',Getdate()-2


select patientid,patientname, dateofentry
from ( select row_number() over (partition by patientid order by dateofentry desc ) as sno,* from @temp )t
where t.sno = 1


hi raky,
Row_number will not work in sql2000 i think so
Go to Top of Page

raky
Aged Yak Warrior

767 Posts
Posted - 2009-01-24 : 00:53:14
quote:
Originally posted by bklr

quote:
Originally posted by raky

quote:
Originally posted by tmeans11

I've never put a query on a forum before. I am learning sql and wondering if anyone can give me some tips. Is there anyway in my query to only show the rows with the top dateofentry or max dateofentry? My query is showing multiple rows for each patient record dateofentry. I only want to see the max dateofentry for each row...
___________________________________________________________
Drop table #tmp1
select guarantor.guarantorid, guarantor.first, guarantor.middle, guarantor.last,
guarantor.address1, guarantor.address2, guarantor.city, guarantor.state,
guarantor.zip, guarantor.ssn, guarantor.phone1, guarantor.phone2, guarantor.lastStatement,
patientprofileagg.patbalance, paymentmethod.dateofentry

into #tmp1
from guarantor
inner join patientprofile on guarantor.guarantorid = patientprofile.guarantorid
inner join patientprofileagg on patientprofile.patientprofileid = patientprofileagg.patientprofileid
Inner join patientvisit on patientprofile.patientprofileid = patientvisit.patientprofileid
inner join VisitTransactions ON patientvisit.patientvisitid = visittransactions.patientvisitid
inner join transactions on visittransactions.visittransactionsid = transactions.visittransactionsid
INNER JOIN PaymentMethod ON visittransactions.paymentmethodid = PaymentMethod.paymentmethodid

Where patientprofileagg.patbalance > 50.00
and transactions.type = 't'
and guarantor.lastStatement is not null
AND transactions.action = 't'
and patientprofile.deathdate is null
AND PaymentMethod.dateofentry >= '2008-02-01'
and PaymentMethod.dateofentry <'2008-08-31'

order by guarantor.last Asc

select *
from #tmp1
_____________________________________________________
Can anyone help me? I can also include a spreadsheet if that will help. Basically I want to see one row per patient with the max payment date...I am at a loss...


Iam not producing exact solution for ur problem but iam giving an example which is some what similar to your description. Please look into it. It may be helpful..


declare @temp table ( patientid int, Patientname varchar(40), Dateofentry datetime)
insert into @temp
select 1,'George',Getdate()-100 union all
select 2,'Dean',Getdate()-20 union all
select 1,'George',Getdate()-5 union all
select 2,'Dean',Getdate()-2


select patientid,patientname, dateofentry
from ( select row_number() over (partition by patientid order by dateofentry desc ) as sno,* from @temp )t
where t.sno = 1


hi raky,
Row_number will not work in sql2000 i think so


If row_number() doesnot work then try this

declare @temp table ( patientid int, Patientname varchar(40), Dateofentry datetime)
insert into @temp
select 1,'George',Getdate()-100 union all
select 2,'Dean',Getdate()-20 union all
select 1,'George',Getdate()-5 union all
select 2,'Dean',Getdate()-2


select t1.patientid,t1.patientname,t1.dateofentry
from @temp t1
inner join ( select patientid, max(dateofentry) as doe
from @temp
group by patientid
) t2 on t2.patientid = t1.patientid and t1.dateofentry = t2.doe
order by t1.patientid
Go to Top of Page

tmeans11
Starting Member

3 Posts
Posted - 2009-01-27 : 20:39:59
Hey Raky - thanks so much I will give it a try, Wow all of you are awesome! I only hope one day I will be able to help someone... I still have a little more learning to do. thanks again!
Go to Top of Page

raky
Aged Yak Warrior

767 Posts
Posted - 2009-01-28 : 06:11:19
welcome....
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site