How to do generate_series in a SQL dataflow

datadiva

Hi,

 

I'm trying to elimate the need for a database between our production system and Domo by doing all ETL work within Domo (I know this may take longer to process).  The database is PostgreSQL and some of the queries that I need to do in Domo use a function called generate_series, but it appears there is not a simple MySQL equivalent to this.  I'm trying to do a SQL dataflow for the ETL, but need help figuring out how to code that function to work in Domo.  Here's one of the simpler examples where this function is being used:

 

SELECT id, owner, date_trunc('MONTH',day)::DATE AS date, SUM(amount/days)AS splitamount
FROM(SELECT*,end_date-start_date+1 AS days,generate_series(start_date, end_date, INTERVAL '1 day')::DATE AS DAY
FROM opportunity) k
WHERE NOT EXISTS(SELECT oppty_id
FROM orders t
WHERE t.oppty_id=k.id)
GROUP BY 1,2,3;

 

Can anyone help advise how to make the same thing happen in MySQL?

MarkSnodgrass

Domo has a connector called Domo Dimensions that is a data table of all calendar dates that is useful for this type of work. If you search for calendar in the connectors section of the App Store, it should show up. If it doesn't ask your CSM to enable it. This can save you some work when needing to build a table full of dates. 

RobertoBis

Have you tried this?

 

select date from (
    select
        date_format(
        adddate(start_date, @num:=@num+1),
        '%Y-%m-%d'
    ) date
    from
        orders,
    (select @num:=-1) num
) as dt

 

Hope this can help

 

MarkSnodgrass

Domo has a connector called Domo Dimensions that is a data table of all calendar dates that is useful for this type of work. If you search for calendar in the connectors section of the App Store, it should show up. If it doesn't ask your CSM to enable it. This can save you some work when needing to build a table full of dates. 

datadiva

The trouble with the first possibility is that I need to have a specific end date as well as a start date.  This serires is used towards calculating when and how long a campaign ran to understand the revenue generated from it. 

 

I'll look into the Domo Dimensions as that sounds like it might be a good option and help with the other SQL statements I need to work out for this too.