Hi all,
I am hoping someone may be able to help with this query!
I am trying to pull out the data which gives me my individual sites and the “Location” and “Custom” tags that are applied to that site, I am hoping someone is able to see what I am missing. I have some SQL code that pulls out the sites and the tags applied to the devices of that site however this is causing some issues for us with regards to reporting. That code is below:
WITH UniqueTags AS (
SELECT
ds.name AS site_name,
dt.tag_type,
ARRAY_AGG(DISTINCT dt.tag_name) AS tags
FROM
dim_site ds
JOIN dim_site_asset dsa ON ds.site_id = dsa.site_id
JOIN dim_asset da ON dsa.asset_id = da.asset_id
JOIN dim_tag_asset dta ON da.asset_id = dta.asset_id
JOIN dim_tag dt ON dta.tag_id = dt.tag_id
GROUP BY
ds.name, dt.tag_type
),
AggregatedTags AS (
SELECT
site_name,
ARRAY_TO_STRING(ARRAY_AGG(DISTINCT tag) FILTER (WHERE tag_type = ‘LOCATION’), ', ') AS “Location Tags”,
ARRAY_TO_STRING(ARRAY_AGG(DISTINCT tag) FILTER (WHERE tag_type = ‘CUSTOM’), ', ') AS “Custom Tags”
FROM
UniqueTags,
UNNEST(tags) AS tag
GROUP BY
site_name
)
SELECT
site_name AS “Site Name”,
“Location Tags”,
“Custom Tags”
FROM
AggregatedTags
ORDER BY
“Site Name”;
Any help would be really appreciated! And thank you in advance!