1 Reply Latest reply on Oct 3, 2012 7:01 PM by Jonathan Drummey

    Multiple Date Fields not seeing each other

    Ryan Trybus

      Hey Everyone,

       

      I need a quick piece of advice. I have two tables that look like this:

       

       

      Decade

       

      Year
      DecadeDecadeNum
      1/1/19501950-19591
      1/1/19511950-19591

       

      and so on from 1950-2011

       

      I have a population table

       

      Population

       

      STATEDecadePopulation
      AZ1950-19591302161
      AZ1960-19691400000

       

       

      and finally I have more of a fact table

      FACT

       

      STATEReport Date
      AZ2/16/1953
      AZ3/04/1958

       

       

      Now in the Custom SQL I have the Decade.Decade inner joined to Population.Decade and Population.State inner joined with FACT.State

       

      What I'm trying to do is generate a chart where I can should the count of FACT entries per Decade that they fall into. When I try to extract the Year(Report Date) then I find that for every decade it shows every possible year for report date (from 1950-2011)

       

       

       

      So basically:

       

      I'd like the data to look like this:

        

      DecadeYear(Report Date)
      1950-19591953, 1958,1959
      1960-19691962,1963
      1970-19791970,1977

       

       

      but from my data model I'm getting

       

       

      DecadeYear(Report Date)

      1950-1959

      1953,1958,1959,1962,1963,1970,1977
      1960-1969

      1953,1958,1959,1962,1963,1970,1977

      1970-1979

      1953,1958,1959,1962,1963,1970,1977

       

      Could this be a problem with my joins? Should I be making this data association someplace else?

        • 1. Re: Multiple Date Fields not seeing each other
          Jonathan Drummey

          When using inner joins, there will be a row for the combination of [decade]*[population]*[fact] rows that are returned by the join. So you're getting a row for every year within each decade for every decade, for every report date, and that's 62 rows per Report Date.

           

          I think the easiest solution is adding a clause to the query that would be something like YEAR([StateFact$].[ReportDate]) = YEAR([Years$].[Year]). Think of your three tables like a triangle, where you've related X to Y and Y to Z, and you just need one more relate X to Z.

           

          Jonathan