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

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)