Hi All,
I am trying to calculate a aggregated measure value "quantity" only for the maximum date coming for each "source"(dimension) values from a table daily_snapshot. for ex- the column source has three dfferent values "source1","source2" and "source3" and for each of it the maximum date fetched from col1(Date) is different and i want the aggregation of quantity field only for max date fetched out for each source field values. I have wriiten the script below but it is quite long and slow. Any way of restructing it and making it better. please help.
SELECT col1, id, description, quantity, a.source, DEPT, location FROM
(SELECT source, MAX(col1) AS max_source_date FROM
(SELECT *,
CASE WHEN source IN ('source1') THEN PARSE_DATE('%m/%d/%Y',c_day)
WHEN source = 'source2' and substr(c_day,0,4) not in ('2019','2020') THEN PARSE_DATE('%m/%d/%Y',c_day)
WHEN source = 'source2' and substr(c_day,0,4) in ('2019','2020') THEN PARSE_DATE('%Y/%m/%d',substr(c_day,0,10))
ELSE PARSE_DATE('%Y-%m-%d',c_day) END AS col1
FROM daily_snapshot WHERE
NOT c_day LIKE '%.%' AND c_day <> "") GROUP BY 1) a,
(SELECT source, quantity,
col1,id, description, local_manufacturer AS DEPT,
location
FROM (SELECT *,
CASE WHEN source IN ('source1') THEN PARSE_DATE('%m/%d/%Y',c_day)
WHEN source = 'source2' and substr(c_day,0,4) not in ('2019','2020') THEN PARSE_DATE('%m/%d/%Y',c_day)
WHEN source = 'source3' and substr(c_day,0,4) in ('2019','2020') THEN PARSE_DATE('%Y/%m/%d',substr(c_day,0,10))
ELSE PARSE_DATE('%Y-%m-%d',c_day) END AS col1
FROM daily_snapshot WHERE
NOT c_day LIKE '%.%' AND c_day <> "")) b
WHERE a.source = b.source AND a.id = b.col1
GROUP BY 1,2,3,4,5,6,7