Get list of page

[Vani1012]

Hi All,

I am currently facing a challenge in identifying specific pages under

 "/content/products/zh-cn"  where the rollout date later than translation date.

To achieve this, I've attempted to construct an SQL query utilizing properties such as cq:lastRolledout and cq:lastTranslationUpdate the query does not yield the desired results.

Notably, I've encountered discrepancies in cases where the cq:lastTranslationUpdate property is absent, yet the page still requires listing based on rolledout date.

Here's the query I've attempted:

first scenario rollout date>translation date

1. SELECT [jcr:path], [cq:lastrolledout], [cq:lasttranslationupdate]
FROM [nt:base]
WHERE ISDESCENDANTNODE('/content/products/zh-cn')
AND [cq:lastrolledout] IS NOT NULL
AND [cq:lasttranslationupdate] IS NOT NULL
AND [cq:lastrolledout] > [cq:lasttranslationupdate]

2 second scenario pages don't have translationdate property

SELECT [jcr:path], [cq:lastrolledout]
FROM [nt:base]
WHERE ISDESCENDANTNODE('/content/products/zh-cn/')

AND [cq:lastrolledout] IS NOT NULL
AND [cq:lasttranslationupdate] IS NULL

Unfortunately, first query is met with a java.text.ParseException, and it fails to execute successfully. Second query not giving proper results

Could anyone kindly provide guidance or an alternative approach to address these scenarios effectively

[David_Fischer92]

If I was presented with this task to figure out what I would do is I would just pull a report of all pages under that path with their translation date and last rollout date into Excel.  And then I would do a CountIF("range of pages and their translation date and rollout data","rollout date>translation date").