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

WHM

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...

RosanneBroersma

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

RosanneBroersma

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

WHM

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!