Creating a variable time dimension with load statement

[#1]

The following is a script I am attempting to execute. The script produces an error that the field DISCHARGE_DISPOSITION_DATE does not exist from the statement:

Date(Monthstart("DISCHARGE_DISPOSITION_DATE"),'MMM-YYY') as YearMonth;

I tried with and without quotes around the field name. What would the problem be?

CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=HDW;Data Source=dvdb7;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WCORR896ZB9;Use Encryption for Data=False;Tag with column collation when possible=False];

Load *, FacilityId & INSURANCE_ID as FAC_INS,Date(Monthstart("DISCHARGE_DISPOSITION_DATE"),'MMM-YYY') as YearMonth;

SQL SELECT "ADMIT_ARRIVE_DATE" as Admit_Date,

"DISCHARGE_DISPOSITION_DATE" as Disch_Date,

FacilityId,

"INSURANCE_ID" ,

"PA_ACCOUNT_STATUS_ID" ,

"PATIENT_ACCOUNT_NUMBER" as ACCT_NUM,

"PATIENT_SERVICE_ID",

"PATIENT_STATUS_ID",

"TOTAL_ADJUSTMENTS" as AdjustmentTotal,

"TOTAL_CHARGES" as Charges,

"TOTAL_INTEREST",

"TOTAL_PAYMENTS" as Receipts,

"TOTAL_WRITEOFFS",

("TOTAL_CHARGES"-"TOTAL_ADJUSTMENTS"-"TOTAL_WRITEOFFS"-"TOTAL_INTEREST"-"TOTAL_PAYMENTS") as BALANCE,

month("DISCHARGE_DISPOSITION_DATE") as MONTH,

year("DISCHARGE_DISPOSITION_DATE") as YEAR,

"VISIT_ID" as A_VISIT

FROM HDW.dbo."Aff_VISIT"

Thanks

Glen

[Oleg_Troyansky]

Glen,

you renamed the field in your SQL statement, so you should be referencing it using the new name:

GAPOWERS wrote:
"DISCHARGE_DISPOSITION_DATE" as Disch_Date,

The correct syntax should be:

Date(Monthstart(Disch_Date),'MMM-YYY') as YearMonth;

cheers,

[#3]

Thanks, it cleared up the error. However, I cannot use the derived field to create a list box filter.

Is there another way to achieve this?

Glen

[johnw]

You should be able to use derived fields in list boxes, and what Oleg suggested should have plugged right in. Maybe repost your current script so we can have a look?