Counts over time for things that have a start date and end date
Hi, I work for a recruitment company where we have contractors working. Does anyone know how I count the number of contractors out working over time when I have to take into account both a start and an end date? Without this I am unable do do any trends for my senior management!
Many thanks
Zena
Best Answer
-
Thanks Jae, I'll give the SQL a go then!
0
Answers
-
Hey Zena, it's hard to address your question if we don't know how your data is currently structured (what's the granularity? can you provide a sample dataset?) What are the metrics you want to sho in your trend report?
If i had to guess you want to do something like the report "how many contractors were working on March 1 and your data is one row per contractor per contract and the start and end date of their engagement on that contract. if that's the casse you need to JOIN the transactions to a date dimension using MySQL something like...
select
t.*
d.date
FROM
contractor_transactions t
INNER JOIN
date d
ON d.date between t.startDate and t.endDate
make sure your contract strat and end dates are always populated or you'll exclude rows in your JOIN
select
t.*
d.date
FROM
contractor_transactions t
INNER JOIN
date d
ON d.date between t.startDate and coalesce(t.endDate, current_date)
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Hi Jae,
Thanks for coming back to me, Your assumptions about how the data is structured are correct, but I've only used ETL and don't know SQL. Is there anyway to achieve the same result using ETL?
Zena
0 -
Magic ETL does not support non-equi JOINs so ... no. The pseudo code I provided should work if you swap out table and field names.
0 -
Thanks Jae, I'll give the SQL a go then!
0
Categories
- 10.5K All Categories
- 5 Connect
- 915 Connectors
- 250 Workbench
- 459 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 185 Visualize
- 250 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
- 170 Product Ideas
- 1.2K Ideas Exchange
- 10 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive