MAX of value - Display Occurrence when max happened

I might be over looking it, and sorry if a re-post.

I’m trying to setup a dashboard that display data based on a time filter.
There will be a table (Question) laying out all the numeric entries, and I also want to display the maximum numeric entry in another table (Question).
What I need to be able to do, is to display the time of occurrence when the peak happened.
I might be missing something or just over looking it, it has been a long day :confused:

Sharing an image. Right box, There is a column showing January, 2019, but I need to be able to show the time of this peak happen. Thank you much in advance for the help!

Hi @michael.brown
I think β€œPeak Per Meter” table might have activated the Pivot function, because there were 3 columns. You have to edit the table and disable Pivot.
Or it might be because of your query, but then you have to show the query.

Thank you very much for your reply.
No harm in sharing my query. It’s simple. Nothing special.
I’ve played around with not displaying as pivot, nothing I have tried will show the time of occurrence.

SELECT DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), CAST(SUBSTRING(p_dtm, 1, 4) + β€˜-’ + SUBSTRING(p_dtm, 5, 2) + β€˜-’ + SUBSTRING(p_dtm, 7, 2) + ’ ’ + SUBSTRING(p_dtm, 9, 2) + β€˜:’ + SUBSTRING(p_dtm, 11, 2) AS SMALLDATETIME)) AS [DateTime]
,p_mtrid AS [MeterID]
,p_usage AS [KWH]
FROM dbo.utsProfile
WHERE (p_mtrid IN (β€˜D8729M’, β€˜D8726M’, β€˜D8728M’, β€˜75273’, β€˜75278’))
AND (p_mtrchn = 1)

This will be the daily, Monthly, so on question.

But I need to be able to have a question that show the max occurrence, with a time stamp.

I got an idea. I’m going to try a few things.
I’ll reply with what I figure out.

Are you using Metabase 0.31.2? It has a feature to define which parts to display of a datetime-type.
So as long as your column p_dtm is a datetime (or timestamp), then you can format it as such.

I have not updated to 0.31.2 yet. I need too. And I will dig into that after I update. Thank you for the heads up. It might not be pretty but this is what I came up with. I’ll share in case you have an opinion. I wrote a new query. It’s a bit of a mess and might be over complicated but, it works. Thank you very much again for all the help. I’ll come back after I get the upgrades in and let you know what I find.

DECLARE @FirstDay SMALLDATETIME
DECLARE @LastDay SMALLDATETIME
SET @FirstDay = DateAdd(β€œd”,-30,GETDATE())
SET @LastDay = DateAdd(β€œd”,+1,GETDATE())

SELECT [DateTime], MeterID, KWH FROM (SELECT TOP(1) DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), CAST(SUBSTRING(p_dtm, 1, 4) + β€˜-’ + SUBSTRING(p_dtm, 5, 2) + β€˜-’ + SUBSTRING(p_dtm, 7, 2) + ’ ’ + SUBSTRING(p_dtm, 9, 2) + β€˜:’ + SUBSTRING(p_dtm, 11, 2) AS SMALLDATETIME)) AS [DateTime],p_mtrid AS [MeterID],p_usage AS [KWH] FROM dbo.utsProfile WHERE ((p_mtrid IN (β€˜D8729M’)) AND (p_mtrchn = 1) AND (DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), CAST(SUBSTRING(p_dtm, 1, 4) + β€˜-’ + SUBSTRING(p_dtm, 5, 2) + β€˜-’ + SUBSTRING(p_dtm, 7, 2) + ’ ’ + SUBSTRING(p_dtm, 9, 2) + β€˜:’ + SUBSTRING(p_dtm, 11, 2) AS SMALLDATETIME)) BETWEEN (@FirstDay) AND (@LastDay))) ORDER BY KWH desc) D8729M UNION
SELECT [DateTime], MeterID, KWH FROM (SELECT TOP(1) DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), CAST(SUBSTRING(p_dtm, 1, 4) + β€˜-’ + SUBSTRING(p_dtm, 5, 2) + β€˜-’ + SUBSTRING(p_dtm, 7, 2) + ’ ’ + SUBSTRING(p_dtm, 9, 2) + β€˜:’ + SUBSTRING(p_dtm, 11, 2) AS SMALLDATETIME)) AS [DateTime],p_mtrid AS [MeterID],p_usage AS [KWH] FROM dbo.utsProfile WHERE ((p_mtrid IN (β€˜D8726M’)) AND (p_mtrchn = 1) AND (DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), CAST(SUBSTRING(p_dtm, 1, 4) + β€˜-’ + SUBSTRING(p_dtm, 5, 2) + β€˜-’ + SUBSTRING(p_dtm, 7, 2) + ’ ’ + SUBSTRING(p_dtm, 9, 2) + β€˜:’ + SUBSTRING(p_dtm, 11, 2) AS SMALLDATETIME)) BETWEEN (@FirstDay) AND (@LastDay))) ORDER BY KWH desc) D8726M UNION
SELECT [DateTime], MeterID, KWH FROM (SELECT TOP(1) DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), CAST(SUBSTRING(p_dtm, 1, 4) + β€˜-’ + SUBSTRING(p_dtm, 5, 2) + β€˜-’ + SUBSTRING(p_dtm, 7, 2) + ’ ’ + SUBSTRING(p_dtm, 9, 2) + β€˜:’ + SUBSTRING(p_dtm, 11, 2) AS SMALLDATETIME)) AS [DateTime],p_mtrid AS [MeterID],p_usage AS [KWH] FROM dbo.utsProfile WHERE ((p_mtrid IN (β€˜D8728M’)) AND (p_mtrchn = 1) AND (DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), CAST(SUBSTRING(p_dtm, 1, 4) + β€˜-’ + SUBSTRING(p_dtm, 5, 2) + β€˜-’ + SUBSTRING(p_dtm, 7, 2) + ’ ’ + SUBSTRING(p_dtm, 9, 2) + β€˜:’ + SUBSTRING(p_dtm, 11, 2) AS SMALLDATETIME)) BETWEEN (@FirstDay) AND (@LastDay))) ORDER BY KWH desc) D8728M UNION
SELECT [DateTime], MeterID, KWH FROM (SELECT TOP(1) DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), CAST(SUBSTRING(p_dtm, 1, 4) + β€˜-’ + SUBSTRING(p_dtm, 5, 2) + β€˜-’ + SUBSTRING(p_dtm, 7, 2) + ’ ’ + SUBSTRING(p_dtm, 9, 2) + β€˜:’ + SUBSTRING(p_dtm, 11, 2) AS SMALLDATETIME)) AS [DateTime],p_mtrid AS [MeterID],p_usage AS [KWH] FROM dbo.utsProfile WHERE ((p_mtrid IN (β€˜75273’)) AND (p_mtrchn = 1) AND (DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), CAST(SUBSTRING(p_dtm, 1, 4) + β€˜-’ + SUBSTRING(p_dtm, 5, 2) + β€˜-’ + SUBSTRING(p_dtm, 7, 2) + ’ ’ + SUBSTRING(p_dtm, 9, 2) + β€˜:’ + SUBSTRING(p_dtm, 11, 2) AS SMALLDATETIME)) BETWEEN (@FirstDay) AND (@LastDay))) ORDER BY KWH desc) ID75273 UNION
SELECT [DateTime], MeterID, KWH FROM (SELECT TOP(1) DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), CAST(SUBSTRING(p_dtm, 1, 4) + β€˜-’ + SUBSTRING(p_dtm, 5, 2) + β€˜-’ + SUBSTRING(p_dtm, 7, 2) + ’ ’ + SUBSTRING(p_dtm, 9, 2) + β€˜:’ + SUBSTRING(p_dtm, 11, 2) AS SMALLDATETIME)) AS [DateTime],p_mtrid AS [MeterID],p_usage AS [KWH] FROM dbo.utsProfile WHERE ((p_mtrid IN (β€˜75278’)) AND (p_mtrchn = 1) AND (DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), CAST(SUBSTRING(p_dtm, 1, 4) + β€˜-’ + SUBSTRING(p_dtm, 5, 2) + β€˜-’ + SUBSTRING(p_dtm, 7, 2) + ’ ’ + SUBSTRING(p_dtm, 9, 2) + β€˜:’ + SUBSTRING(p_dtm, 11, 2) AS SMALLDATETIME)) BETWEEN (@FirstDay) AND (@LastDay))) ORDER BY KWH desc) ID75278