Skip to Content
0
Former Member
Oct 31, 2012 at 12:39 AM

How to show Amount between two Dates?????

30 Views

Hi All,

i am working on a cross tab an i am stuck on a problem. My table looks like the one below.

Here i have MembNo; EffDate; ExpDate; Tier; Premium Amount and Monthly date.

Here i have to show Monthly premium of the Member.

Please have a look at EffDat; Exp Dat and Date Column showing Premium Amount beneith it.

Member had PlanA From 1/1/2012 to 2/31/2012 from which his amount was $502 so

I want to show $502 under Date Column 01/2012 and 02/2012 and rest to show 0 in 03/2012 and 04/2012 (in Row 1)

Then Member changed to PlanB from 3/1/2012 to 4/31/2012 for which his amount was $700, here

I want to show Amount under date column 03/2012 and 04/2012 and 0 in first two. (in Row 2)

01/2012 Tier 02/2012 Tier 03/2012 Tier 04/2012 Tier

MembNo EffDate ExpDate

00123 1/1/2012 2/31/2012 $502 1005 $502 1005 $502 1005 $502 1005

00123 3/1/2012 4/31/2012 $700 1005 $700 1005 $700 1005 $700 1005

i want my table to look like one below:

01/2012 Tier 02/2012 Tier 03/2012 Tier 04/2012 Tier

MembNo EffDate ExpDat

00123 1/1/2012 2/31/2012 $502 1005 $502 1005 0 1005 0 1005

00123 3/1/2012 4/31/2012 0 1005 0 1005 $700 1005 $700 1005

Please suggest how to solve this problem.

Thank You