Many Start-End Times columns in a Table

[jasonseril]

Hello All,

In our project, one of the tables has several "Start Time and End Time" columns.  Like:

         ID,

         A_Start_Time,

         A_End_Time,

         B_Start_Time,

         B_End_Time,

         C_Start_Time,

         C_End_Time, 

          DimensionA,

          etc..

         ...

And the user wanted to be able to select a date range (say "From-To") using the start-end time pair.  I know if there is only one start-end time pair, we can make a link table with the calendar table.  My question is what to do with the above columns?  Which set-analysis fits to this case?

thank you in advance and more power!

Best regards,

Jason

[AbhijitBansode]

Just an idea, you can have 3 master calendars for three pairs of start and end dates.

[jasonseril]

Hello Abhijit,

i checked the table again and there are like, so many Start-End Time pairs, so do i have to create a lot of master calendars too?

Thanks

[#4]

Hi ,

If possible merge all Start_Date & another End_Date in new field respectively to form Single cal along with respective ID Field...

Else

For set-Analysis purpose create Data-Island approach..

[Colin-Albert]

Can you reorganise your data model so your Project table has fields for ProjectID and Stage (A,B,C...) and a key that combines these values, ProjectStageKey. (ProjectID & '|' & Stage)

Then create a separate table for the Stages that has the ProjectStageKey plus the Start and End dates.

This way you have single fields for the Start and End date pairs, but can link the start and end of each stage to the relevant project.

[AbhijitBansode]

If you have more number of start date end date pairs, then I would recommend what Colin_Albert suggested.

[jasonseril]

Thank you Harshal for this helpful answer, I will try this.

[jasonseril]

Thank you Colin for this helpful answer, I will try this.

[jasonseril]

Hello Colin_Albert,

I'm stuck in the script, can you please give me a sample load script for your solution?

thank you in advance and more power!

BR,

jason

[Colin-Albert]

I would use a for-each loop, which will auto-concatenate the data to a single Stage table.

for each Stage in 'A', 'B','C'

     trace Loading Data for Stage $(Stage) ;

     Stage:

     Load

          ProjectID,

          ProjectID & '|' & $(Stage) as ProjectStageKey,

          '$(Stage)' as Stage,

          $(Stage)_Start_Time as Start_Time,
          $(Stage)_End_Time as End_Time,

          Dimension_$(Stage) as Dimension,

          ... other fields

    from <stage data table>

     where $(Stage)_Start_Time > ''

next Stage ;