I need to filter out the start date and end date and get a duration for this.

[patro]

I already have page with table filter in it and easy heading macro

So My question here  I have target completed date and Start date and Ageing 

and when I write this command SELECT *, (T1.'Target completion date' - T1.'Start date') / "24h" AS 'Ageing' FROM T1 ,  it is not giving any output 

I have created a table transformer and I have placed this table in that and saved it. It display nothing in Ageing

but when I only create a table transformer and paste a table with no macro/filter as the 2nd pic and 3rd pic with same command SELECT *, (T1.'Target completion date' - T1.'Start date') / "24h" AS 'Ageing' FROM T1  , the Ageing is displayed.

Please help in this thanks in advance

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

Hi @patro ,

The macro sequence "source table <- Table Transformer <- Table Filter" is a standard common case, so everything should be working fine.

You may check if the date format in the not working Table Transformer macro corresponds the date format used in the table. Go to the Settings -> Date Format and choose the "d M yy" option.

You may also use the autocomplete while typing in your query: type "T1." and choose the required columns from the dropdown menu.

Please try these hints and tell me if they helped.

Besides (it doesn't concern the case) you don't need to create a blank "Ageing" column in your table. This column will be automatically created and filled in by the query.

[patro]

hi @Katerina Kovriga _Stiltsoft_ 

The date format is this as highlighted

[Katerina Kovriga _Stiltsoft_]

You need to check the format inside the Table Transformer macro: go to the Settings tab -> Date Format and choose the "d M yy" option.

[patro]

It worked thanks a lot 

[patro]

Hello, Though the difference date are obtained with the above solution provided.

Now it is observed that from 5th Oct to 24th oct, it should give 14 days in Ageing(Number of days )column excluding the weekends and Days should display 20

when I used this query 

SELECT *,
'Days' -
2 * (('Days' / 7)::integer) -
CASE
WHEN 'Days' % 7 = 0 THEN 0
WHEN 'Start date'::Date->getDay() = 0 THEN 1
WHEN 'Start date'::Date->getDay() + 'Days' % 7 = 7 THEN 1
WHEN 'Start date'::Date->getDay() + 'Days' % 7 > 7 THEN 2
ELSE 0
END AS 'Ageing(Number of days)'
FROM
(SELECT *,
ROUND((T1.'Target completion date' - T1.'Start date') / "1d") + 1 AS 'Days'
FROM T1)

Its giving this 

Could you Please in this. where am I going wrong

[patro]

[patro]

hi @Katerina Kovriga _Stiltsoft_ 

Got ageing date correctly but need to filter out the workdays only 

Now it is showing with respect to seven days but need it for 5 days to exclude weekends

Query is this:

select *,
CASE WHEN T1.'Target completion date' IS NOT NULL AND T1.'Start date' IS NOT NULL THEN
ROUND(('Target completion date' - 'Start date' + "24h") / "24h") + " days"
ELSE "" END
as 'Ageing(Number of days)' from T1

[Katerina Kovriga _Stiltsoft_]

Hi @patro ,

I've recreated an example from our documentation and it's working fine for me:

SELECT *,
'Days' -
2 * (('Days' / 7)::integer) -
CASE
WHEN 'Days' % 7 = 0 THEN 0
WHEN 'Start date'::Date->getDay() = 0 THEN 1
WHEN 'Start date'::Date->getDay() + 'Days' % 7 = 7 THEN 1
WHEN 'Start date'::Date->getDay() + 'Days' % 7 > 7 THEN 2
ELSE 0
END AS 'Work days'
FROM
(SELECT *,
ROUND((T1.'Target completion date' - T1.'Start date') / "1d") + 1 AS 'Days'
FROM T1)

Try to set the date format as any other that differs from your original format in the table: M dd, yy, for example.

If it doesn't help, please raise a support request: attach the page storage format (upper right corner of the page -> menu ... -> View storage format). We'll recreate your table and look into the issue.

[patro]

@Katerina Kovriga _Stiltsoft_ it didn't work I have created a issue and attached the query 

I even changed the date format 

Please find the ticket number

https://jsd.stiltsoft.com/servicedesk/customer/portal/2/TFCS-1937

[Katerina Kovriga _Stiltsoft_]

Hi @patro ,

Thank you, I see the ticket and that you are already engaged in conversation with Nikita - he is one of our support engineers. The issue should be solved soon. Thank you for reaching out!