I'm pressed for time and reaching out for a hand.
Problem: I've started to encounter a situation where I'm getting multiple rows in my query and this is causing extra rows per Epic in my downstream reports.
My current sql:
SELECT
MATCH_REGEXP(MATCH_REGEXP(T1.'Epic Link'->getView(), "href=\"(.*?)\"")->1, "OPIF-\d{4,}","g") AS 'Project',
FORMATWIKI("{cell:width=175px}","**Progress**: ",
CASE WHEN (T1.'Status') IS NOT NULL THEN T1.'Status'
ELSE "unkown" END,
"\n**Story Link**: ",T1.'Key',
"\n**Promised By**: ",
CASE WHEN (T1.'Planned "Done" Date') IS NOT NULL THEN T1.'Planned "Done" Date'
ELSE T1.'Due' END,"{cell}") AS 'Validate work'
FROM T1
What I'm thinking as a solution: Order the rows and use the row with max date
but there's a typo or a bug in my code here. Table Transformer gives me an error.
WITH CTE AS (
SELECT
MATCH_REGEXP(MATCH_REGEXP(T1.'Epic Link'->getView(), "href=\"(.*?)\"")->1, "OPIF-\d{4,}","g") AS 'Project',
T1.'Status',
T1.'Key',
T1.'Planned "Done" Date',
T1.'Due',
ROW_NUMBER() OVER (PARTITION BY T1.'Epic Link' ORDER BY T1.'Planned "Done" Date' DESC) AS rn
FROM
T1
)
SELECT
Project,
FORMATWIKI(
"{cell:width=175px}",
"**Progress**: ",
CASE
WHEN Status IS NOT NULL THEN Status
ELSE "unknown"
END,
" **Story Link**: ",
Key,
" **Promised By**: ",
CASE
WHEN "Planned \"Done\" Date" IS NOT NULL THEN "Planned \"Done\" Date"
ELSE Due
END,
"{cell}"
) AS 'Validate work'
FROM
CTE
WHERE
rn = 1
... is Table Transformer sql able to handle the partitioning statement?
ROW_NUMBER() OVER (PARTITION BY T1.'Epic Link' ORDER BY T1.'Planned "Done" Date' DESC) AS rn
TIA
