asked on

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?

ASKER CERTIFIED SOLUTION

Argenti

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Peter Kiprop

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

soozh

ASKER

Pthepebble - your Query refers to a msr_Date column in "B" which does not exist.

I am testing Argenti's solution

SOLUTION

Peter Kiprop

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial