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 · Accepted solution]

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").

[kautuk_sahni]

@Vani1012 Did you find the suggestion helpful? Please let us know if more information is required. Otherwise, please mark the answer as correct for posterity. If you have found out solution yourself, please share it with the community.