In our project, one of the tables has several "Start Time and End Time" columns. Like:
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!
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.
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) ;
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 ;