Retrieving Maximum and Minimum Timestamp of a certain day from a set of timestamps [SOLVED]

• joao.neto_87

  Say Hey Kid

  Points: 673

  More actions

  April 10, 2017 at 3:52 am

  #1937395

  Thom A - Monday, April 10, 2017 2:58 AM

  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;
GO

  CREATE TABLE #Policy
  (PolicyID char(10),
  CustomerName varchar(50),
  CustomerPostCode varchar(10));
GO

  CREATE TABLE #Transaction
  (TransactionID int IDENTITY(1,1),
  PolicyID char(10),
  TransactionValue decimal(12,2),
  TransactionTime datetime);
GO

  INSERT INTO #Policy
VALUES
  ('XXXX01HB01', 'Mr Smith', 'AB12 2BA'),
  ('XXYD01HB01', 'Mr Bloggs', 'ZA1 2OP'),
  ('MASD74FD02', 'Ms Brown', 'E1 1JA');
GO

  INSERT 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');
GO

  SELECT *,
       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.
  

  Thank you so much! This was exactly what I was looking for.
  I'll try, in the future, to share more coherent samples in order to explain my problems at hand.