Group by within a date range

[iren]

Doing query in Access ,I am trying to count adm_mem_num grouped by clm_model amd by clm_prod_level.
I also need it to be summarized within admit_dt_day range of 2002

Query failed and Access generated the following SQL behind the scene

SELECT USER_V_CLAIM_STD_PRODUCT.CLM_MODEL, USER_V_CLAIM_STD_PRODUCT.CLM_PROD_LEVEL3_CD, USER_V_ADMIT_DATE.ADMIT_DT_DAY, Count(USER_V_ADMISSION_TAB.ADM_MEM_NUM) AS CountOfADM_MEM_NUM
FROM USER_V_CLAIM_STD_PRODUCT INNER JOIN ((USER_V_MEDICAL_SERVICE INNER JOIN USER_V_ADMIT_DATE ON USER_V_MEDICAL_SERVICE.ADMIT_DT_KEY = USER_V_ADMIT_DATE.ADMIT_DT_KEY) INNER JOIN USER_V_ADMISSION_TAB ON USER_V_MEDICAL_SERVICE.ADM_KEY = USER_V_ADMISSION_TAB.ADM_KEY) ON USER_V_CLAIM_STD_PRODUCT.CLM_STD_PRODUCT_KEY = USER_V_MEDICAL_SERVICE.CLM_STD_PRODUCT_KEY
GROUP BY USER_V_CLAIM_STD_PRODUCT.CLM_MODEL, USER_V_CLAIM_STD_PRODUCT.CLM_PROD_LEVEL3_CD, USER_V_ADMIT_DATE.ADMIT_DT_DAY
HAVING (((USER_V_ADMIT_DATE.ADMIT_DT_DAY) Between #1/1/2002# And #12/31/2002#));

What I am doing wrong?

Thnk you!

Iren

 

[Crowley16]

what is the error you are getting?

--------------------
Procrastinate Now!

 

[iren]

ODBC -call failed
{ibm][cli dRIVER][DB2/AIX64] SQL 0952N Processing was cancelled due to an interrupt.
SQLSTATE=57014 (#-952)

*****************************

 

[SkipVought]

Hi,

What's the data type of ADMIT_DT_DAY?

Skip,

_[red][/red]

 

[iren]

It is a date/time type like 1/1/2002

 

[SkipVought]

Just because it LOOKS like a date, it is not necessarily Date/Time.

You did check the table design, I assume.

Skip,

_[red][/red]

 

[iren]

Skip, I double checked a design view. It is date/time type!

I assume...maybe I did smth wrong with Grouping by ADMIT_DT_DAY? Actually I need to group by by clm_model amd by clm_prod_level.

However how could I involve ADMIT_DT_DAY otherwise....

 

[PHV]

Perhaps this ?
SELECT USER_V_CLAIM_STD_PRODUCT.CLM_MODEL, USER_V_CLAIM_STD_PRODUCT.CLM_PROD_LEVEL3_CD, Count(USER_V_ADMISSION_TAB.ADM_MEM_NUM) AS CountOfADM_MEM_NUM
FROM USER_V_CLAIM_STD_PRODUCT INNER JOIN ((USER_V_MEDICAL_SERVICE INNER JOIN USER_V_ADMIT_DATE ON USER_V_MEDICAL_SERVICE.ADMIT_DT_KEY = USER_V_ADMIT_DATE.ADMIT_DT_KEY) INNER JOIN USER_V_ADMISSION_TAB ON USER_V_MEDICAL_SERVICE.ADM_KEY = USER_V_ADMISSION_TAB.ADM_KEY) ON USER_V_CLAIM_STD_PRODUCT.CLM_STD_PRODUCT_KEY = USER_V_MEDICAL_SERVICE.CLM_STD_PRODUCT_KEY
WHERE Year(USER_V_ADMIT_DATE.ADMIT_DT_DAY) = 2002
GROUP BY USER_V_CLAIM_STD_PRODUCT.CLM_MODEL, USER_V_CLAIM_STD_PRODUCT.CLM_PROD_LEVEL3_CD


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[iren]

PHV,

I got the same message as before

 

[lespaul]

are you running this against a DB2 database? That's what your error message suggests to me:

[tt]ODBC -call failed
{ibm][cli dRIVER][DB2/AIX64] SQL 0952N Processing was cancelled due to an interrupt.
SQLSTATE=57014 (#-952)[/tt]

have you checked the ODBC connection to make sure it's connected? Are you using a passthrough query? The # date delimiter is specific to Access, other database don't use it.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

Are you playing with linked tables or a passthru query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[iren]

Leslie and PHV,

I am running query against DB2 Datawarehouse throught ODBC.
I do it like link tables (I even do not know what passthrough query are).

I had checked ODBC connection with each of the tables involved and works (I am able to review tables)

 

[PHV]

Perhaps an ODBC TimeOut issue ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

If you have a DB2 table LINKED in an Access database, then the Access SQL syntax will work in a non-ODBC Access Query.

However, if you are coding using and ODBC connection to DB2, then the systax will NOT be the Access SQL syntax.
[tt]
HAVING (((USER_V_ADMIT_DATE.ADMIT_DT_DAY) Between Date('1/1/2002') And Date('12/31/2002')));

[/tt]


Skip,

_[red][/red]