Retrieving Maximum and Minimum Timestamp of a certain day from a set of timestamps [SOLVED]
-
Hello everyone,
I'm new on these forums and hopefully this topic is at the right place.
I have a rather annoying problem, which I can't solve and can't find any solution around the Web...
I have a query that returns information based on an input range of dates. In other words, the user will provide a start and end dates and the query will retrieve information based of that range.
One of the columns the query will return will have a timestamp format, like so:
aaaa-mm-dd hh:mm:ssNow, what I'd like to do would be to get, from this column, all the timestamps grouped by each day. Here's an exemple:
The column has for instance these rows:
2017-03-27 09:24:00.000
2017-03-27 09:48:00.000
2017-03-27 09:49:00.000
2017-03-27 12:27:00.000
2017-03-27 15:22:00.000
2017-03-28 09:32:00.000
2017-03-28 09:34:00.000
2017-03-28 09:59:00.000
2017-03-28 10:14:00.000
2017-03-29 00:00:00.000
2017-03-29 09:26:00.000
2017-03-29 09:29:00.000
2017-03-29 09:31:00.000
2017-03-29 10:24:00.000
2017-03-29 10:26:00.000
2017-03-29 10:28:00.000
2017-03-29 10:32:00.000
2017-03-29 11:26:00.000
2017-03-29 11:43:00.000
2017-03-29 12:07:00.000
2017-03-29 12:29:00.000
2017-03-29 15:16:00.000So we have 3 diferent days (27, 28 and 29) and there's multiple timestamps for each of these days, so I'd like to only retrieve the maximum and minimum timestamps grouped by each day in two columns, like so:
Min_Timestamp
2017-03-27 09:24:00.000
2017-03-28 09:32:00.000
2017-03-29 00:00:00.000Max_Timestamp
2017-03-27 15:22:00.000
2017-03-28 10:14:00.000
2017-03-29 15:16:00.000Can anyone point out on how I do this?
-
WITH Times AS (
SELECT *
FROM (VALUES
('2017-03-27 09:24:00.000'),
('2017-03-27 09:48:00.000'),
('2017-03-27 09:49:00.000'),
('2017-03-27 12:27:00.000'),
('2017-03-27 15:22:00.000'),
('2017-03-28 09:32:00.000'),
('2017-03-28 09:34:00.000'),
('2017-03-28 09:59:00.000'),
('2017-03-28 10:14:00.000'),
('2017-03-29 00:00:00.000'),
('2017-03-29 09:26:00.000'),
('2017-03-29 09:29:00.000'),
('2017-03-29 09:31:00.000'),
('2017-03-29 10:24:00.000'),
('2017-03-29 10:26:00.000'),
('2017-03-29 10:28:00.000'),
('2017-03-29 10:32:00.000'),
('2017-03-29 11:26:00.000'),
('2017-03-29 11:43:00.000'),
('2017-03-29 12:07:00.000'),
('2017-03-29 12:29:00.000'),
('2017-03-29 15:16:00.000')
) AS DTS (DateTimeStamp)
)
SELECT CAST(DateTimeStamp AS date) AS DateStamp,
MIN(DateTimeStamp) AS Min_DateTimeStamp,
MAX(DateTimeStamp) AS Max_DateTimeStamp
FROM Times
GROUP BY CAST(DateTimeStamp AS date);Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
CREATE TABLE #TestData (
SampleTime DATETIME
);INSERT INTO #TestData ( SampleTime )
VALUES ( '2017-03-27 09:24:00.000' )
, ( '2017-03-27 09:48:00.000' )
, ( '2017-03-27 09:49:00.000' )
, ( '2017-03-27 12:27:00.000' )
, ( '2017-03-27 15:22:00.000' )
, ( '2017-03-28 09:32:00.000' )
, ( '2017-03-28 09:34:00.000' )
, ( '2017-03-28 09:59:00.000' )
, ( '2017-03-28 10:14:00.000' )
, ( '2017-03-29 00:00:00.000' )
, ( '2017-03-29 09:26:00.000' )
, ( '2017-03-29 09:29:00.000' )
, ( '2017-03-29 09:31:00.000' )
, ( '2017-03-29 10:24:00.000' )
, ( '2017-03-29 10:26:00.000' )
, ( '2017-03-29 10:28:00.000' )
, ( '2017-03-29 10:32:00.000' )
, ( '2017-03-29 11:26:00.000' )
, ( '2017-03-29 11:43:00.000' )
, ( '2017-03-29 12:07:00.000' )
, ( '2017-03-29 12:29:00.000' )
, ( '2017-03-29 15:16:00.000' );SELECT
SampleDate = CAST(SampleTime AS DATE)
, minTime = MIN(td.SampleTime)
, maxTime = MIN(td.SampleTime)
FROM #TestData AS td
GROUP BY CAST(SampleTime AS DATE); -
@thom-2 A
That works great, but I can't have manually input values into the SQL.
The timestamps come from a column that is populated with my query.
This column doesn't always have the same values.
So, in order for your sample to work, I'd need to replace those values with the name of the column, but I'm not being able to do that, as I'm getting a syntax error.To better explain myself, here's a preview of my query is:
SELECT a.id, b.code, a.validtimestamp
FROM a WITH (NOLOCK)
INNER JOIN b WITH (NOLOCK)
ON a.id_a = b.id_a
INNER JOIN c WITH (NOLOCK)
ON b.id_b = c.id_b
INNER JOIN d WITH (NOLOCK)
ON c.id_c = d.id_c
WHERE a.code LIKE '%-REP-%'
AND a.code NOT LIKE '%-PRT-%'
AND b.valid = 1
AND a.validtimestamp BETWEEN '20170327' AND '20170401'The column validtimestamp contains the set of timestamps I've presented on my op post. The values of this column are always variable, so how do I use your sample code with my query?
@DesNorton
I can't create tables, as I'm going to "infuse" the sql query with an excel sheet, using an ODBC connection. - joao.neto_87 - Friday, April 7, 2017 7:52 AM
When asking questions, we need the full picture or our answer could be as incomplete as your question. As we only had a list of values, both myself and Des made the assumption that it was a single column. The temp table and values are there are you didn't provide any kind of sample data, thus we had to create our own, to be able to test.
You would need to adjust either my, or Des', SELECT statement to use the data from your table, rather than the sample data we used. Have a go yourself, and if you get stuck post what you tried and we'll put you back on the right track.
Also, on a unrelated note, what is your reasoning for using NOLOCK?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - joao.neto_87 - Friday, April 7, 2017 7:52 AM
You don't understand. Neither the CTE with the data that Thom used nor the table that Des Norton used actually have anything to do with the solution other than just being test data. We do like to test things before we post.
You would simply take the queries that both offered after each of those and substitute your own table.Shifting gears, I don't use the word NEVER very often in this business but there is one place for sure that I say NEVER. NEVER use BETWEEN to limit temporal ranges especially in cases like yours where there are times included in each date. The WHOLE dates you used in your BETWEEN won't give you the all of the data for 2017401. It will only give you up to and including midnight of that day effectively missing all of the rest of the rows for that day. Even if the dates in the table were whole dates, if someone gets the religion and changes the datatype to include times, you're screwed using BETWEEN.
You're code for temporal range limits should always (another word I don't use often but it's important here) should always follow the format of...
WHERE SomeDateColumn >= @StartDate
AND SomeDateColumn < DATEADD(dd,1,@EndDate)
That way, you guaranteed to get all of the time value based rows for the desired end date no matter what the resolution of the temporal datatype of SomeDateColumn is.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Thom A - Friday, April 7, 2017 8:01 AMjoao.neto_87 - Friday, April 7, 2017 7:52 AM
When asking questions, we need the full picture or our answer could be as incomplete as your question. As we only had a list of values, both myself and Des made the assumption that it was a single column. The temp table and values are there are you didn't provide any kind of sample data, thus we had to create our own, to be able to test.
You would need to adjust either my, or Des', SELECT statement to use the data from your table, rather than the sample data we used. Have a go yourself, and if you get stuck post what you tried and we'll put you back on the right track.
Also, on a unrelated note, what is your reasoning for using NOLOCK?
My apologies,
I'm not a major on SQL, even though I do understand a bit of it and so the edges on explaining problems are a bit too rough yet.
Anyway, I've tried merging your sample code with my query and so far I have this:WITH Times AS (SELECT a.validtimestamp
FROM a WITH (NOLOCK)
INNER JOIN b AS RECL WITH (NOLOCK)
ON a.id_a = b.id_a
INNER JOIN c
ON b.id_b = c.id_b
INNER JOIN d
ON c.id_c = d.id_c
WHERE a.code LIKE '%-REP-%'
AND a.code NOT LIKE '%-PRT-%'
AND b.valid = 1
AND a.validtimestamp BETWEEN '20170327' AND '20170401')SELECT a.id,
b.code,
(SELECT MIN(validtimestamp) AS Min_DateTimeStamp
FROM Times
GROUP BY CAST(validtimestamp AS date)) AS Min_DTS
FROM a WITH (NOLOCK)
INNER JOIN b WITH (NOLOCK)
ON a.id_a = b.id_a
INNER JOIN c WITH (NOLOCK)
ON b.id_b = c.id_b
INNER JOIN d WITH (NOLOCK)
ON c.id_c = d.id_c
WHERE a.code LIKE '%-REP-%'
AND a.code NOT LIKE '%-PRT-%'
AND b.valid = 1
AND a.validtimestamp BETWEEN '20170327' AND '20170401'Running the whole sql from top to bottom, I get the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
It makes sense, because I'm pretty much trying to return more that one timestamp per row on the same column... and this is where I'm stumped.Regarding the NOLOCK clause, I'm extracting information from tables on the production server on real time, and in order to not lock any records that other users might be trying to use at the time, I always use this clause.
Is trying to generate a query based on tables that are stored on a production server a bad practice? Maybe, but at least I can always rely on real test cases. In any case, if anything goes wrong, that's what backups are there for
@jeff Moden
Hey there,
Regarding the between clause, don't sweat it
Trust me when I say that I know full well the horrors this little treasure brings when working with temporal values, but since I'm just doing some tests within a random date timeline, it's ok if I don't get all the timestamps -
Does seem you've missed the aim I'm afraid.
Firstly, this is the SQL you should be looking for:
SELECT CAST(a.validtimestamp as date) AS DateStamp,
MIN(a.validtimestamp) AS Min_TimeStamp,
MAX(a.validtimestamp) AS Max_TimeStamp,
FROM a
INNER JOIN b ON a.id_a = b.id_a
INNER JOIN c ON b.id_b = c.id_b
INNER JOIN d ON c.id_c = d.id_c
WHERE a.code LIKE '%-REP-%'
AND a.code NOT LIKE '%-PRT-%'
AND b.valid = 1
GROUP BY CAST(a.validtimestamp as date);Notice that what i've done here is taken your select statement and applied the logic that both myself and Des used. Firstly we have CAST the timestamp field to a date and the added it to the GROUP BY clause. This means that every timestamp on the day will have the same DateStamp. For example 2017-04-07 17:15:13 and 2017-04-07 14:47:59 are both 2017-04-07 as a date.
Then we add the other two columns, with the MIN and MAX operators, to get (unsurprisingly) your min and max values.
I've also removed your NOLOCKs for the moment, as I want to ask, do you understand the implications of using NOLOCK? Using it doesn't actually technically stop you locking a table, also it can cause your data to be inaccurate. Is this something you would be happy with?
EDIT: Microsoft article on NOLOCK: https://msdn.microsoft.com/en-us/library/ms190805.aspx
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Friday, April 7, 2017 10:18 AM
Hello,
Your sample only works if you don't have any other columns, besides the timestamps :S
The columns that I exemplified on my query should also be displayed (id and code). If I add these columns on the select statement, I'll also have to add them on the group by clause, obviously.
However, by doing so, I lose the min and max timestamps logic, as I have to add 15 other columns to the select statement aswell to the group by clause.I'll probably need to separate this "timestamp query" from my main one
Regarding, the NOLOCK clause, I've explained the reason behind its use.
- joao.neto_87 - Friday, April 7, 2017 10:46 AMHello,
Your sample only works if you don't have any other columns, besides the timestamps :S
The columns that I exemplified on my query should also be displayed (id and code). If I add these columns on the select statement, I'll also have to add them on the group by clause, obviously.
However, by doing so, I lose the min and max timestamps logic, as I have to add 15 other columns to the select statement aswell to the group by clause.I'll probably need to separate this "timestamp query" from my main one
Regarding, the NOLOCK clause, I've explained the reason behind its use.
I don't have access to your data, so I don't know what your other columns look like. If you could supply consumable sample data, and your expected output, then someone can give you the answer. At the moment the only data I have to work with is a list of date and times, so the answers you receive are for a list of date and times.
Take a look at the link in my signature on how to provide sample data and expected outputs. Post back with what we need, and someone will be provide you with a comprehensive answer. Otherwise, as I said above, the answers you receive will be as incomplete as the questions and/or data you provide.
So, you understand that your data you base your result on may be dirty, duplicate, etc? If it is permissible for the data to be inaccurate, and whoever is receiving the data from your SQL is aware of that, then that's fine; otherwise, you should consider its use VERY carefully.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Friday, April 7, 2017 11:03 AMjoao.neto_87 - Friday, April 7, 2017 10:46 AMHello,
Your sample only works if you don't have any other columns, besides the timestamps :S
The columns that I exemplified on my query should also be displayed (id and code). If I add these columns on the select statement, I'll also have to add them on the group by clause, obviously.
However, by doing so, I lose the min and max timestamps logic, as I have to add 15 other columns to the select statement aswell to the group by clause.I'll probably need to separate this "timestamp query" from my main one
Regarding, the NOLOCK clause, I've explained the reason behind its use.
I don't have access to your data, so I don't know what your other columns look like. If you could supply consumable sample data, and your expected output, then someone can give you the answer. At the moment the only data I have to work with is a list of date and times, so the answers you receive are for a list of date and times.
Take a look at the link in my signature on how to provide sample data and expected outputs. Post back with what we need, and someone will be provide you with a comprehensive answer. Otherwise, as I said above, the answers you receive will be as incomplete as the questions and/or data you provide.
So, you understand that your data you base your result on may be dirty, duplicate, etc? If it is permissible for the data to be inaccurate, and whoever is receiving the data from your SQL is aware of that, then that's fine; otherwise, you should consider its use VERY carefully.
I thought I gave the details needed on my second post, but anyway, I've attached a rough sample of the results from my query, without the min and max timestamp logic.
Those columns cannot be excluded from my query, so I have to somehow input the min and max timestamp columns along with those. It doesn't really matter if the timestamp values are repated along the columns, as long as the values correspond to the min/max timestamps of each day.
-
As I'm leaving the office now, I'll finish this later, but to save another user creating the sample data:
USE TestDB;
GOCREATE TABLE #Sample
(RECEPÇÃO int,
DATACRIAÇÃO datetime,
ESTADO varchar(100),
DATAHORARECEPÇÃO datetime,
QTDPALETESENVIADA decimal(12,2),
QTDPALETESRECEBIDA decimal(12,2),
QTDPALETESMANUSEADASRECEBIDA decimal(12,2),
QTDPALETESFINALRECEBIDA decimal(12,2),
PRODUTOCODIGO int,
QTDSTOCK decimal(12,2),
QTDVOLUMESENVIADA decimal(12,2),
QTDVOLUMESRECEBIDA decimal(12,2));
GOINSERT INTO #Sample
VALUES
(30563,'20170329 00:00:00.000','Sujeita Conferência','20170331 12:03:00.000',5.00,5.00,0.00,5.00,13018094,0.00,0.00,0.00),
(30563,'20170329 00:00:00.000','Sujeita Conferência','20170331 12:03:00.000',5.00,5.00,0.00,5.00,13018194,75.00,75.00,75.00),
(30564,'20170329 00:00:00.000','Sujeita Conferência','20170331 12:04:00.000',5.00,5.00,0.00,5.00,13018094,260.00,260.00,260.00),
(30564,'20170329 00:00:00.000','Sujeita Conferência','20170331 12:04:00.000',5.00,5.00,0.00,5.00,13018194,0.00,0.00,0.00),
(30551,'20170329 00:00:00.000','Sujeita Conferência','20170331 12:54:00.000',37.00,37.00,0.00,0.00,13022294,0.00,0.00,0.00),
(30551,'20170329 00:00:00.000','Sujeita Conferência','20170331 12:54:00.000',37.00,37.00,0.00,0.00,13022394,0.00,0.00,0.00),
(30551,'20170329 00:00:00.000','Sujeita Conferência','20170331 12:54:00.000',37.00,37.00,0.00,0.00,13023694,0.00,0.00,0.00),
(30551,'20170329 00:00:00.000','Sujeita Conferência','20170331 12:54:00.000',37.00,37.00,0.00,0.00,13023794,0.00,0.00,0.00),
(30551,'20170329 00:00:00.000','Sujeita Conferência','20170331 12:54:00.000',37.00,37.00,0.00,0.00,13023894,0.00,0.00,0.00),
(30551,'20170329 00:00:00.000','Sujeita Conferência','20170331 12:54:00.000',37.00,37.00,0.00,0.00,13023994,550.00,550.00,550.00);
GOSELECT *
FROM #Sample;
GODROP TABLE #Sample;
GOThom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
So, after getting home, I noticed that you don't actually explain what you want your expected output to be. In your earlier query you only reference code and id, but then there's a lot more columns, so makes me wonder if you need all of them?
Here's a few answers that might be what you're after, if not, I really need expected output for you sample data.
SELECT *,
MIN(DATAHORARECEPÇÃO) OVER (ORDER BY DATAHORARECEPÇÃO) AS MIN_DATAHORARECEPÇÃO,
MAX(DATAHORARECEPÇÃO) OVER (ORDER BY DATAHORARECEPÇÃO) AS MAX_DATAHORARECEPÇÃO
FROM #Sample;SELECT RECEPÇÃO, ESTADO,
CAST(DATAHORARECEPÇÃO AS date) AS DATE_DATAHORARECEPÇÃO,
MIN(DATAHORARECEPÇÃO) AS MIN_DATAHORARECEPÇÃO,
MAX(DATAHORARECEPÇÃO) AS MAX_DATAHORARECEPÇÃO
FROM #Sample
GROUP BY RECEPÇÃO, ESTADO, CAST(DATAHORARECEPÇÃO AS date);Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Hello and thank you for your time.
As I mentioned on my previous post and according to the attachment I provided, I need all those columns. None of them can be taken out.
The samples you've shared on your last post will not help, because you're only targetting a single table and a single column that's not target of an aggregation (i.e. min or max)
To my understanding, the logic changes when you have multiple columns on the select statement, because there will dozens if not hundreds of repeateable rows with little difference between them, such as the columns that hold quantities (as I've shown on the attached sample), with the key column being "PRODOUTCODIGO".
I don't know if I'm explaning myself well... if there's any questions let me know. -
If ther'es multiple tables, i need multiple tables in my sample data. I can only work with what I'm given.
Initially you asked for the min and max for any given day, which myself and Des gave in our answers. Then you said that every column needed to be included from your sample data, which I did above.
However, now you're saying there's more tables involved, however, the sample data has only ever been a single column or single table. I really can't give you an answer without having ALL the the details. That means consumable sample data (see my signature), and an expected output. Otherwise, everything i am doing is guesswork.Perhaps what you are after is a OVER and partition by clause. This isn't your data, it's my own sample that I created, but maybe this is what you want. It uses two tables, and gives you the max and min timestamp for the transaction of that day regardless of customer:
USE DevTestDB;
GOCREATE TABLE #Policy
(PolicyID char(10),
CustomerName varchar(50),
CustomerPostCode varchar(10));
GOCREATE TABLE #Transaction
(TransactionID int IDENTITY(1,1),
PolicyID char(10),
TransactionValue decimal(12,2),
TransactionTime datetime);
GOINSERT INTO #Policy
VALUES
('XXXX01HB01', 'Mr Smith', 'AB12 2BA'),
('XXYD01HB01', 'Mr Bloggs', 'ZA1 2OP'),
('MASD74FD02', 'Ms Brown', 'E1 1JA');
GOINSERT INTO #Transaction (PolicyID, TransactionValue, TransactionTime)
VALUES
('XXXX01HB01', 100.24, '20170401 07:59:59.000'),
('XXYD01HB01', 89.54, '20170401 10:24:12.000'),
('XXYD01HB01', 05.24, '20170401 17:13:48.000'),
('MASD74FD02', 1025.95, '20170402 12:14:34.000'),
('XXXX01HB01', 7.59, '20170405 11:34:31.000'),
('MASD74FD02', 212.35, '20170405 14:14:57.000');
GOSELECT *,
MIN(T.TransactionTime) OVER (PARTITION BY CAST(T.TransactionTime as date)) AS Min_TransactionTime,
MAX(T.TransactionTime) OVER (PARTITION BY CAST(T.TransactionTime as date)) AS Max_TransactionTime
FROM #Policy P
JOIN #Transaction T On P.PolicyID = T.PolicyID;GO
DROP TABLE #Policy;
DROP TABLE #Transaction;As I said, if this doesn't give you the type if thing you want you need to provide both comprehensive and consumable data (like I have in the above sample, not in a spreadsheet), and what you expect your data set to look like at the end (that can be in a spreadsheet if you want, as I just need to see the resultset you want from your data). Otherwise, I really can't help you any further.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply