MAX of value - Display Occurrence when max happened


#1

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!


#2

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.


#3

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.


#4

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


#5

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.


#6

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