October 5, 2011 at 2:50 pm
trying to get started with mdx
trying to write a simple (well, not simple enough for me) query to list premium from insurance policies, by state that are in effect as of a specific date.
each policy has a effective date and an expiration date. I need to find all of them that the requested date falls between those two dates.
This returns premium by state for all policies
select
{[Measures].[WRITTENPREMIUM]} on columns,
{[DIMGEOGRAPHY].[Hierarchy].[State]} on rows
from [EDW DWH]
there is a time role playing dimension set up for policyeffectivedate and policyexpirationdate
i tried adding a where clause
where ([POLICYEFFECTIVEDATE].[CALENDARDATE]:[POLICYEXPIRATIONDATE].[CALENDARDATE])
i get an error
Range operator ( : ) operands have different hierarchies; they must be the same.
fair enough, i think i actually understand that message.
but i'm totally stuck on how to do this.
in sql it would look like this:
select INSUREDSTATE, SUM(writtenpremium)
from dimpolicy p
join FACTPOLICYTRANS t on p.POLICYSEQID = t.POLICYSEQID
where GETDATE() between POLICYEFFECTIVEDATE and POLICYEXPIRATIONDATE
group by INSUREDSTATE
October 6, 2011 at 12:58 pm
i think i got it
select
[Measures].[WRITTENPREMIUM] on columns,
non empty
[DIMGEOGRAPHY].[Hierarchy].[STATE]
on rows
from [EDW DWH]
where (
{null:[POLICYEFFECTIVEDATE].[CALENDARDATE].&[2010-05-10T00:00:00]},
{[POLICYEXPIRATIONDATE].[CALENDARDATE].&[2010-05-10T00:00:00]:null}
)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply