July 10, 2003 at 2:37 pm
My query listed below returns all the event_dates in the database for a person and is it possible that I can only select the first record per people_id? For instance, one person has more than one event_date -- I only want to grab the first record.
Here is my query:
select distinct p.people_id, p.last_name, p.first_name,
c.cases_id as casesid,
ep.start_date,
st.site_id, st.site_name as PfizerSiteName,
ev.event_id, ev.event_date, ev.date_received
from people p
left outer join cases c on p.people_id = c.people_fk
left outer join episode ep on ep.cases_fk = c.cases_id
left outer join service svc on svc.episode_fk = ep.episode_id
left outer join site st on st.site_id = svc.site_fk
left outer join authorizations auth on auth.authorizations_id = svc.authorizations_fk
left outer join contract_product_site cps ON st.site_id = cps.site_fk
left outer join contract_product cp ON cp.contract_product_id = cps.contract_product_fk
left outer join contract cc ON cc.contract_id = cp.contract_fk
left join event ev on ev.authorizations_fk = auth.authorizations_id
where st.site_name like 'PFIZER%' and ep.start_date between '01-01-2003'
and '06-30-2003'
and p.deactivate is null and auth.deactivate is null and
cps.deactivate is null and
cp.deactivate is null and cc.deactivate is null and
ev.deactivate is null and
c.deactivate is null and svc.deactivate is null and ep.deactivate is null
GROUP BY p.people_id, p.last_name, p.first_name,
c.cases_id,
ep.start_date,
st.site_id, st.site_name,
ev.event_id, ev.event_date, ev.date_received
Having ev.event_date is not null
order by st.site_name
Thanks in Advance
July 10, 2003 at 3:43 pm
You might try a correlated sub query like this:
select * from Northwind.dbo.[Order Details] a
where
ProductId = (select top 1 productid from Northwind.dbo.[Order Details]
where a.OrderId = OrderId
order by OrderID, ProductID)
This example shows you the first product for each order
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
July 10, 2003 at 5:16 pm
You have the sub-query a little wrong, thought you wanted one by event_date. Try this:
select a.* from (select p.people_id, p.last_name, p.first_name,
c.cases_id as casesid,
ep.start_date,
st.site_id, st.site_name as PfizerSiteName,
ev.event_id, ev.event_date, ev.date_received
from people p
left outer join cases c on p.people_id = c.people_fk
left outer join episode ep on ep.cases_fk = c.cases_id
left outer join service svc on svc.episode_fk = ep.episode_id
left outer join site st on st.site_id = svc.site_fk
left outer join authorizations auth on auth.authorizations_id = svc.authorizations_fk
left outer join contract_product_site cps ON st.site_id = cps.site_fk
left outer join contract_product cp ON cp.contract_product_id = cps.contract_product_fk
left outer join contract cc ON cc.contract_id = cp.contract_fk
left join event ev on ev.authorizations_fk = auth.authorizations_id
where
st.site_name like 'PFIZER%' and ep.start_date between '01-01-2003'
and ''06-30-2003'' and p.deactivate is null and auth.deactivate is null and
cps.deactivate is null and
cp.deactivate is null and cc.deactivate is null and
ev.deactivate is null and
c.deactivate is null and svc.deactivate is null and ep.deactivate is null
order by st.site_name) a
where
a.event_date = (select top 1 event_date
from
(select p.people_id, p.last_name, p.first_name,
c.cases_id as casesid,
ep.start_date,
st.site_id, st.site_name as PfizerSiteName,
ev.event_id, ev.event_date, ev.date_received
from people p
left outer join cases c on p.people_id = c.people_fk
left outer join episode ep on ep.cases_fk = c.cases_id
left outer join service svc on svc.episode_fk = ep.episode_id
left outer join site st on st.site_id = svc.site_fk
left outer join authorizations auth on auth.authorizations_id = svc.authorizations_fk
left outer join contract_product_site cps ON st.site_id = cps.site_fk
left outer join contract_product cp ON cp.contract_product_id = cps.contract_product_fk
left outer join contract cc ON cc.contract_id = cp.contract_fk
left join event ev on ev.authorizations_fk = auth.authorizations_id
where
st.site_name like 'PFIZER%' and ep.start_date between '01-01-2003'
and ''06-30-2003'' and p.deactivate is null and auth.deactivate is null and
cps.deactivate is null and
cp.deactivate is null and cc.deactivate is null and
ev.deactivate is null and
c.deactivate is null and svc.deactivate is null and ep.deactivate is null
order by st.site_name) b
where a.people_id = b. people_id)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply