Is there a way to join data on a date between a start and end date?

WHM
WHM Contributor
in Connectors

This is really a data flow question. I have assignments in one table that have a start and end date. I am trying to join this to daily activity.

set one look like

code start_date end_date

sm    1/1/2015 2/1/2015

asc   2/2/2015  6/2/2015

 

then there is daily activity and I need to see how much daily activity happened under each code...

Best Answer

  • RosanneBroersma
    RosanneBroersma Contributor
    Answer ✓

    Hi,

     

    if I understand correctly, you'd want to join daily activity for a certain code to the assignments table?

     

    If so, and you have dataflows enabled in your instance, here's how you can join:

     

    select 

    assignments.code,

    count(*) as Nr_daily_activity

    from

    assignments

    join 

    daily_activity on (assignments.code = daily_activity.code and daily_activity.date between assignments.start_date and assignments.end_date)

    group by assignments.code

     

    Hope this helps?

     

    Thanks,

    Rosanne

    Rosanne Broersma

Answers

  • RosanneBroersma
    RosanneBroersma Contributor
    Answer ✓

    Hi,

     

    if I understand correctly, you'd want to join daily activity for a certain code to the assignments table?

     

    If so, and you have dataflows enabled in your instance, here's how you can join:

     

    select 

    assignments.code,

    count(*) as Nr_daily_activity

    from

    assignments

    join 

    daily_activity on (assignments.code = daily_activity.code and daily_activity.date between assignments.start_date and assignments.end_date)

    group by assignments.code

     

    Hope this helps?

     

    Thanks,

    Rosanne

    Rosanne Broersma
  • WHM
    WHM Contributor

    I thought I had replied to myself... I had forgotten there were SQL options. I was trying to use the "magic" option. We are dumping entire table structures so I should be able to use tons of SQL already written directly against the database to create my data sets. We are just getting Domo in and trying to figure out the best ways to get data in and useful.

     

    Thanks for the help!

Categories