Query syntax

I have two queries that i need to combine.

The first selects a series of  patient ids and dates that i need to use in the second.

Here there are:  This selects the patients and dates:
SELECT [pat_id]
      ,[msr_Date]
 
  FROM [BPSD].[dbo].[Measurements]
  order by pat_id, msr_date

Open in new window


This selects the medicine data and uses pat_id and msr_Date from the first query.

SELECT pat_id, 
count(case mgp_id when 2 then 1 else null end) as [A 11 + A 12],
count(case mgp_id when 23 then 1 else null end) as [N 02 Analgetika],
FROM [BPSD].[dbo].[vw_PatientMedicines]
  where ((pmd_StartDate <= msr_Date) or (pmd_StartDateUnknown = 1)) and pmd_EndDate <= msr_Date
  group by pat_id
  order by pat_id

Open in new window


I want to combine them so that for every pat_id and msr_date i return the result of the second query.

Can someone suggest the syntax?
soozhCEOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ArgentiCommented:
SELECT M.pat_id, M.msr_Date,
count(case mgp_id when 2 then 1 else null end) as [A 11 + A 12],
count(case mgp_id when 23 then 1 else null end) as [N 02 Analgetika],
FROM [BPSD].[dbo].[vw_PatientMedicines] PM
INNER JOIN [BPSD].[dbo].[Measurements] M ON (M.pat_id = PM.pat_id)
WHERE ((pmd_StartDate <= msr_Date) or (pmd_StartDateUnknown = 1)) 
and (pmd_EndDate <= msr_Date)
  group by M.pat_id, M.msr_Date
  order by pat_id, msr_Date

Open in new window


I've updated the query. Please try now.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Peter KipropCommented:
Try
SELECT A.pat_id,A.msr_Date,
count(case B.mgp_id when 2 then 1 else null end) as [A 11 + A 12],
count(case B.mgp_id when 23 then 1 else null end) as [N 02 Analgetika],
FROM  [BPSD].[dbo].[Measurements] A 
INNER JOIN[BPSD].[dbo].[vw_PatientMedicines] B ON (A.pat_id = B.pat_id)
WHERE ((B.pmd_StartDate <= B.msr_Date) or (B.pmd_StartDateUnknown = 1)) 
and (B.pmd_EndDate <=B.msr_Date)
  group by A.pat_id, A.msr_Date
  order by A.pat_id, A.msr_Date

Open in new window


Hope this works
0
soozhCEOAuthor Commented:
Pthepebble - your Query refers to a msr_Date column in "B" which does not exist.

I am testing Argenti's solution
0
Peter KipropCommented:
try this again,
SELECT A.pat_id,A.msr_Date,
count(case B.mgp_id when 2 then 1 else null end) as [A 11 + A 12],
count(case B.mgp_id when 23 then 1 else null end) as [N 02 Analgetika],
FROM  [BPSD].[dbo].[Measurements] A 
INNER JOIN[BPSD].[dbo].[vw_PatientMedicines] B ON (A.pat_id = B.pat_id)
WHERE ((B.pmd_StartDate <= A.msr_Date) or (B.pmd_StartDateUnknown = 1)) 
and (B.pmd_EndDate <=A.msr_Date)
  group by A.pat_id, A.msr_Date
  order by A.pat_id, A.msr_Date

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.