@mlauber71 I changed the if to a CASE condition:
Select b.*, c.“releasedate”,
DENSE_RANK() OVER(PARTITION BY b.originboardid ORDER BY TO_DATE(SUBSTRING(b.sprintenddate,0,9), ‘DD/Mon/YY’) DESC) AS “rank_sprint”,
DENSE_RANK() OVER(PARTITION BY b.originboardid ORDER BY TO_DATE(c.releasedate, ‘YYYY-MM-DD’) DESC) AS “rank_release”,
RANK() OVER (ORDER BY b.issueid, b.sprintid DESC) as “rank_issue”,
MAX
(CASE
WHEN(b.issueorigin=‘completed’) AND (b.changeto=‘In Progress’) and (b.changefield=‘status’) THEN max(TO_DATE(SUBSTRING(b.changecreation,0,10),‘YYYY-MM-DD’)) OVER(b.issueid,b.sprintid)
END) OVER (b.issueid,b.sprintid) as “lastinprogress”
from digitalplatforms.issues_braze b
Left join jira.releases c
On b.version_id=c.versionid
And now Amazon (500310) Invalid operation: syntax error at or near “b”
Position: 536;
and if I just perform:
Select b.*,
MAX
(CASE
WHEN(b.issueorigin=‘completed’) AND (b.changeto=‘In Progress’) and (b.changefield=‘status’) THEN max(TO_DATE(SUBSTRING(b.changecreation,0,10),‘YYYY-MM-DD’)) OVER(b.issueid,b.sprintid)
END) OVER (b.issueid,b.sprintid) as “lastinprogress”
from digitalplatforms.issues_braze b
Left join jira.releases c
On b.version_id=c.versionid
I get the error: Amazon(500310) Invalid operation: syntax error at or near “b”
Position: 201;