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
But I cannot use DATEADD… is there any way to query your data models for the previous X number of days worth of data?