Calculating date difference between min and max date in a table

[Green_ Desmond]

I have a simple table, as per the below:

The first part of my calculation, requires obtaining a count of the distinct dates. In my example below there are 6 dates, so that's my first item of data.

I then want to calculate the number of days between the minimum date and the maximum date. So, the minimum date is 9th August and the maximum date is 3rd November. I think that is 87 days.

So, what I need within a table transformer macro is some SQL to dynamically calculate the distinct number of dates, 6 and divide that by the date difference, 87. Here my answer would be 0.068.

I'm falling down at the first part of trying to calculate this! When I use the following:

SELECT MIN('Date') FROM T*

It gives me a very surprising answer, 01 November 2023. Presumably based on 01 being the lowest number.

 

I don't understand this, and ultimately I need a complete solution to getting to that number of distinct dates divided by the min/max dates difference!

[Stiltsoft support · Accepted solution]

Hi @Green_ Desmond ,

We may switch to the more simple "yy-mm-dd" format (please choose it in the Settings tab of your macro as well):

SELECT MAX('Date') AS 'Max Date',
MIN('Date') AS 'Min Date',
DATEDIFF(day, MIN('Date'), MAX('Date')) AS 'Date Difference',
COUNT(DISTINCT('Date')) AS 'Distinct Dates',
ROUND((COUNT(DISTINCT('Date'))/(DATEDIFF(day, MIN('Date'), MAX('Date')))), 3) AS 'Counted Value'
FROM
(SELECT *,
FORMATDATE('Date', "yy-mm-dd") AS 'Date'
FROM T1)

 

[Stiltsoft support]

Hi @Green_ Desmond ,

An update from our side: I'll leave the first answer as it is for other Community members who may come across this question and decide to change the date format that was set for their table - the internal SELECT will do here.

But what concerns your original question, I went to our developers and they've shown me my mistake - maybe you had the same and that's why nothing was working in the first place.

When you set the date format for "03 Nov 2023" and other dates, it should be "dd M yy" and not "d M yy" like I had at first.

The "dd M yy" format corresponds to the "03 Nov 2023" date and "d M yy" format refers to the "3 Nov 2023" (without nulls for one-digit days). 

So, please type in the "dd M yy" date format manually and you won't need the internal SELECT - everything works correctly:

SELECT MAX('Date') AS 'Max Date',
MIN('Date') AS 'Min Date',
DATEDIFF(day, MIN('Date'), MAX('Date')) AS 'Date Difference',
COUNT(DISTINCT('Date')) AS 'Distinct Dates',
ROUND((COUNT(DISTINCT('Date'))/(DATEDIFF(day, MIN('Date'), MAX('Date')))), 3) AS 'Counted Value'
FROM T1

[Green_ Desmond]

Ok, I had it working nicely with your first answer, albeit needed to +1 to include the end date in the calculation.

That's interesting about the date format and behaviour, I'll look at that more closely too!