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


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
-
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 Broersma1
Answers
-
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 Broersma1 -
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!
0
Categories
- 10.5K All Categories
- 3 Connect
- 913 Connectors
- 250 Workbench
- 458 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 182 Visualize
- 249 Beast Mode
- 2.1K Charting
- 11 Variables
- 16 Automate
- 354 APIs & Domo Developer
- 88 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 245 Distribute
- 62 Domo Everywhere
- 242 Scheduled Reports
- 20 Manage
- 41 Governance & Security
- 168 Product Ideas
- 1.2K Ideas Exchange
- 9 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive