General

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-04-2016
07:21 AM

11-04-2016
07:21 AM

I am trying to pull four columns using following query but getting invalid date error.

Does anyone know a solution for it?

Is it something to do with using between?? if so then with what should i replace it?

thank you,

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 20690 StartFragment: 314 EndFragment: 20658 StartSelection: 314 EndSelection: 314SyntaxEditor Code Snippet

CREATE MULTISET VOLATILE TABLE MLR_BY_NTWRK AS ( SELECT PRER_I.IPA AS "IPA", PRER_G.GRP AS "GRP", **bleep**.PRV_NAME, **bleep**.NPI,SUM("Member Months") AS "Member Months",SUM(Premium) AS "Premium",SUM("Medical Expense") AS "Medical Expense",SUM (IP) AS "IP",SUM(OP) AS "OP",SUM(**bleep**) AS "**bleep**",SUM(SPEC) AS "Specialist", SUM("**bleep** CAP") AS "**bleep** Capitation",SUM("Other PHY CAP") AS "Other Physician Cap", SUM("NURSING FACILITY") AS "Nursing Facility", SUM(RX) AS "Rx", SUM("ANCILLIARY SERVICES") AS "Ancillary Services", SUM("OTHER MEDICAL") AS "Other Medical", CASE WHEN SUM("Rg 5 Rev")=0 THEN 0 ELSE SUM("Rg 5 Exp")/SUM("Rg 5 Rev") END as "Rg 5 MLR", CASE WHEN SUM("Rg 6 Rev")=0 THEN 0 ELSE SUM("Rg 6 Exp")/SUM("Rg 6 Rev") END as "Rg 6 MLR", CASE WHEN SUM("Rg 7 Rev")=0 THEN 0 ELSE SUM("Rg 7 Exp")/SUM("Rg 7 Rev") END as "Rg 7 MLR", CASE WHEN SUM("Rg 11 Rev")=0 THEN 0 ELSE SUM("Rg 11 Exp")/SUM("Rg 11 Rev") END as "Rg 11 MLR", CASE WHEN SUM("NON-MMA Rev")=0 THEN 0 ELSE SUM("NON-MMA Exp")/SUM("NON-MMA Rev") END as "NON-MMA MLR" FROM PCP_TBL_CMPRSS **bleep** LEFT JOIN PROD_MME.CMC_PRPR_PROV PRPR ON **bleep**.SRC_PRV_ID = PRPR.PRPR_ID LEFT JOIN PROVIDER_GRP PRER_G ON **bleep**.SRC_PRV_ID = PRER_G.SRC_SYS_PROVIDER_NBR AND **bleep**.SVC_DT between PRER_G.GRP_EFF AND PRER_G.TERM_DT LEFT JOIN PROVIDER_IPA PRER_I ON **bleep**.SRC_PRV_ID = PRER_I.SRC_SYS_PROVIDER_NBR AND **bleep**.SVC_DT between PRER_I.IPA_EFF AND PRER_I.TERM_DT GROUP BY 1,2,3,4--,5,6,7--,8,9,10,11 --ORDER BY 1,2,3,4 desc)WITH DATA ON COMMIT PRESERVE ROWS;

1 REPLY

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

11-04-2016
11:20 AM

11-04-2016
11:20 AM

Stemetha,

the between is valid,

please share the DDL for your tables

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.