Returning 0 for null Criteria Value

[ecwhite]

Hello,

Please help, i am out of ideas.

I have a query that i am pulling list of patients in certain DRG Codes (Data
Type is Number), i list the DRG Codes that i am interested in under the query
criteria. I also have a secound layer that i use built off the 1st query just
to show a count of patients in each DRG Group and i displayed how it looks
below

my query looks like this, please note i had to use like in query instead of
"IN() because i don't get data with "IN('34','35',....).

SELECT A.BI_ID, A.DG_COD, D.DRG_DESC, P.DISCH_DATE

FROM ([Admi] AS A INNER JOIN [PT] AS P ON (A.HOSP_ID = P.HOSP_ID) AND
(A.PT_ID = P.PT_ID)) INNER JOIN [DG Ref] AS D ON A.DG_COD = D.DG_COD
WHERE (((P.DIS_DATE) Between #10/1/2007# And #12/31/2007#))
GROUP BY A.BI_ID, A.DG_CODE, D.DG_DESC, P.DISCH_DATE

HAVING (((A.DG_COD) Like 34 Or (A.DG_COD) Like 35 Or (A.DG_COD))))
ORDER BY A.BI_ID;

Problem:
I want if there is no patient for DRG 34, Let DRG 34 show up on my list with 0

That way when i do a count of all the patients in different DRG groups, it
will show up as 0 like this in my second query.

Result from second query that only does count

DRG DRG DESC Count
34 iv 0
35 test 10
37 test 0
50 test 200

Thanks for all your help.

 

[ecwhite]

Hello Dale,

1. I tried no. 1 like you said, it worked so number 1 is resolved

2. Yes I will always be intrested in those DRG Codes because the groupings
don't change for a long time.

3. Can you please show me an example using my code on what you just
explained to get it to work? I will keep working on it but an example will be
very helpful using my code.

Thanks,
EC

EC,

A couple of questions.

1. The reason your In clause is not working is that you are wrapping the
numbers in quotes, even though in the previous paragraph you indicate that
they are a numeric data type. Try IN (34, 35, 37, 50)

2. Are you always going to be interested in just these DRG codes? or will
the codes you are interested in vary? If they will vary, you might want to
consider putting those codes in a temporary table. Then, you can link that
table to the others, using it as the base table to get the other info. In
any case, in order to get all 4 of the DRG codes, regardless of whether they
have any patients associated with them, you are going to have to use your [DG
Ref] table as the base table for the query, and join it to the other two
tables using a left join.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Hello,

Please help, i am out of ideas.

I have a query that i am pulling list of patients in certain DRG Codes (Data
Type is Number), i list the DRG Codes that i am interested in under the query
criteria. I also have a secound layer that i use built off the 1st query just
to show a count of patients in each DRG Group and i displayed how it looks
below

my query looks like this, please note i had to use like in query instead of
"IN() because i don't get data with "IN('34','35',....).

SELECT A.BI_ID, A.DG_COD, D.DRG_DESC, P.DISCH_DATE

FROM ([Admi] AS A INNER JOIN [PT] AS P ON (A.HOSP_ID = P.HOSP_ID) AND
(A.PT_ID = P.PT_ID)) INNER JOIN [DG Ref] AS D ON A.DG_COD = D.DG_COD
WHERE (((P.DIS_DATE) Between #10/1/2007# And #12/31/2007#))
GROUP BY A.BI_ID, A.DG_CODE, D.DG_DESC, P.DISCH_DATE

HAVING (((A.DG_COD) Like 34 Or (A.DG_COD) Like 35 Or (A.DG_COD))))
ORDER BY A.BI_ID;

Problem:
I want if there is no patient for DRG 34, Let DRG 34 show up on my list with 0

That way when i do a count of all the patients in different DRG groups, it
will show up as 0 like this in my second query.

Result from second query that only does count

DRG DRG DESC Count
34 iv 0
35 test 10
37 test 0
50 test 200

Thanks for all your help.

 

[Dale Fye]

EC,

A couple of questions.

1. The reason your In clause is not working is that you are wrapping the
numbers in quotes, even though in the previous paragraph you indicate that
they are a numeric data type. Try IN (34, 35, 37, 50)

2. Are you always going to be interested in just these DRG codes? or will
the codes you are interested in vary? If they will vary, you might want to
consider putting those codes in a temporary table. Then, you can link that
table to the others, using it as the base table to get the other info. In
any case, in order to get all 4 of the DRG codes, regardless of whether they
have any patients associated with them, you are going to have to use your [DG
Ref] table as the base table for the query, and join it to the other two
tables using a left join.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[ecwhite]

Hello Dale,

I just want to let you know that I got it to work so thank you.

Thanks,
EC.