Alternative to DATEADD

I am trying to query data for a rolling 90 day period.

Conditions and period-comparison works for some aspects of this, but when I try to calculate percentages I need grand totals that I am having difficulty retrieving.

So I went to create a new Data Model specifically to store the totals I need to use for calculating percent of overall value.

I wrote the following hol-SQL to do so:

select
x.id as "org_id", a.id as "act_id", x.gt as "grand", sum(aov.subtotal)/100 as "spend"
from(
select o.id, sum(ao.subtotal)/100 as gt
from
{{ #public_active_order_version as ao}}
join {{ #public_order as ord}} on ord.id = ao.order_id
join {{ #public_organization as o}} on o.id = ord.organization_id
where ao.delivery_day >= DATEADD(day, -90, GETDATE())
group by o.id) x 
join {{ #public_account as a}} on x.id = a.organization_id
join {{ #public_active_order_version as aov}} on aov.account_id = a.id
where aov.delivery_day >= DATEADD(day, -90, GETDATE())
group by x.id, a.id, x.gt

excuse my shorthand :grin:

But I cannot use DATEADD… is there any way to query your data models for the previous X number of days worth of data?

Hi Stephen,

Could you share a bit more details about your use-case, to help me understand it better?
I.e. What is the business context there? Or what business question do you want to answer by query data for a rolling 90 day period?

About the DATEADD function, we suspect this may be due to the database are you using.
For example: If you are using Postgres, the correct syntax for DATEADD function should be “day_column + INTERVAL '1 day'”.

The function depends on the datasource, so it might be worth checking. However if this still can’t be solved afterwards, please do let us know to see if we can find another way to help!

All the best!

1 Like

Yes this is what I ended up using.

I had to make separate tables that look at the time periods I wanted to reference and compare against.

Very simple and clean solution once understood!

Thanks

1 Like

Great to know it helped, Stephen. Cheers!