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
- All Categories
- 1.2K Product Ideas
- 1.2K Ideas Exchange
- 1.3K Connect
- 1.1K Connectors
- 273 Workbench
- 2 Cloud Amplifier
- 3 Federated
- 2.7K Transform
- 78 SQL DataFlows
- 525 Datasets
- 2.1K Magic ETL
- 2.9K Visualize
- 2.2K Charting
- 435 Beast Mode
- 22 Variables
- 513 Automate
- 115 Apps
- 390 APIs & Domo Developer
- 8 Workflows
- 26 Predict
- 10 Jupyter Workspaces
- 16 R & Python Tiles
- 332 Distribute
- 77 Domo Everywhere
- 255 Scheduled Reports
- 66 Manage
- 66 Governance & Security
- 1 Product Release Questions
- Community Forums
- 40 Getting Started
- 26 Community Member Introductions
- 68 Community Announcements
- 4.8K Archive