Extract of maximum value of an ID for each line containing date range

PVI · May 7, 2024, 1:01pm

Hello,
Hours passed and whereas I looked many posts on the web, I don't find any source of help to resolve my problem.
I wish to make data treatment in order to extract of maximum value of an ID for each line containing date range. Sometimes, graphics tell more that text, you will find it in attachment!

For that, I've already created a new view that out a date range (start/end) of a specific phase for each production Batch.
Then, I would like to extract a Max Value for Tagname ID in history table for each Batch, using the date range and the tagname.

Today my request is the following but if there is no error (youpi), there is no results!

SELECT        dbo.V_DatesRincagesNep.Batch_Log_ID, dbo.V_DatesRincagesNep.DateTimeStart, dbo.V_DatesRincagesNep.DateTimeEnd, MAX(Runtime.dbo.v_History.Value) AS MaxValCond
FROM            dbo.V_DatesRincagesNep LEFT OUTER JOIN
                         Runtime.dbo.v_History ON Runtime.dbo.v_History.DateTime BETWEEN dbo.V_DatesRincagesNep.DateTimeStart AND dbo.V_DatesRincagesNep.DateTimeEnd
GROUP BY dbo.V_DatesRincagesNep.DateTimeStart, dbo.V_DatesRincagesNep.DateTimeEnd, dbo.V_DatesRincagesNep.Batch_Log_ID, Runtime.dbo.v_History.TagName
HAVING        (Runtime.dbo.v_History.TagName = 'NE5547_CIT_320.fMesure')

The sample data are following:
History data

DateTime	TagName	Value
2024-05-06 00:00:00.0000000	NE5547_FIT_003.fMesure	-0.00520833348855376
2024-05-06 00:00:00.0000000	NE5547_FQIT_001.fMesure	1282
2024-05-06 00:00:00.0000000	NE5547_LT_001.fMesure	-0.217013895511627
2024-05-06 00:00:00.0000000	NE5547_COMPTAGE_PH002_EPU.fMesure	951910.625
2024-05-06 00:00:00.0000000	NE5547_COMPTAGE_PH002_CYCLE.fMesure	3925
2024-05-06 00:00:00.0000000	NE5547_FQIT_003.fMesure	725
2024-05-06 00:00:00.0000000	NE5547_FQIT_002.fMesure	0
2024-05-06 00:00:00.0000000	NE5547_COMPTAGE_PH003_EPU.fMesure	1557940
2024-05-06 00:00:00.0000000	NE5547_COMPTAGE_PH003_CYCLE.fMesure	5403
2024-05-06 00:00:00.0000000	NE5547_COMPTAGE_JJ.fMesure	1

Table Batch with date-ranges

Batch_Log_ID	UnitOrConnection	DateTimeStart	DateTimeEnd
1K2FWV6VZX	NE554801	2024-01-12 08:31:58.000	2024-01-12 08:57:43.000
1K2FX7N4X9	NE554801	2024-01-12 10:09:47.000	2024-01-12 10:25:21.000
1K2FXEEYGS	NE554701	2024-01-12 11:23:38.000	2024-01-12 11:37:07.000
1K2HJ0TQ5L	NE554801	2024-01-15 08:40:14.000	2024-01-15 08:53:16.000
1K2HJ4GF51	NE554701	2024-01-15 10:00:33.000	2024-01-15 10:21:33.000
1K2HJJE8UH	NE554801	2024-01-15 11:32:44.000	2024-01-15 11:48:46.000
1K2HKBMHVZ	NE554701	2024-01-15 16:29:34.000	2024-01-15 16:52:28.000
1K2I3F43LT	NE554701	2024-01-16 09:42:28.000	2024-01-16 09:56:01.000
1K2I3FQLMK	NE554801	2024-01-16 10:51:08.000	2024-01-16 11:03:55.000
1K2I3KS6E6	NE554701	2024-01-16 13:27:24.000	2024-01-16 14:14:22.000

My training and experiences are limited in SQL, and this subject is complex so I hope that I will find help!
Thank you very much for attention!

Pierre

mike01 · May 7, 2024, 1:31pm

Pictures are great for seeing what you have/want, but without DDL and sample data. you're asking us to provide it for you. We can only guess at exactly what you have

PVI · May 7, 2024, 2:09pm

Hi Mike,
Thank for your response, I have edited my post and corrected the display of my request, I don't know if it is what you expect, the copy-past of the data is relatively the same that in my picture, then I represent in my picture only the ID I want to filter !
I have currently build the "Batch table" with each one date range, but I cannot build a new table with MaxValue for a same Tagname ID for each Batch Log ID

PVI · May 7, 2024, 2:13pm

I think the main difficulty for me is that the history table contains values associated with several pieces of data (Tagname ID) and it is the association "between date range" + "Tagname" that I do not understand.

mike01 · May 7, 2024, 2:48pm

Here's a start, but your history data is not within your range data, so the sample data is useless

create table #history (historyDate datetime, tagName varchar(50), TagValue decimal(30,20))
insert into #history values
('2024-05-06','NE5547_FIT_003.fMesure',-0.00520833348855376),
('2024-05-06','NE5547_FQIT_001.fMesure',	1282),
('2024-05-06','NE5547_LT_001.fMesure',	-0.217013895511627),
('2024-05-06','NE5547_COMPTAGE_PH002_EPU.fMesure',	951910.625),
('2024-05-06','NE5547_COMPTAGE_PH002_CYCLE.fMesure',	3925),
('2024-05-06','NE5547_FQIT_003.fMesure',	725),
('2024-05-06','NE5547_FQIT_002.fMesure',	0),
('2024-05-06','NE5547_COMPTAGE_PH003_EPU.fMesure',	1557940),
('2024-05-06','NE5547_COMPTAGE_PH003_CYCLE.fMesure',	5403),
('2024-05-06','NE5547_COMPTAGE_JJ.fMesure',	1)

create table #ranges (logID varchar(20), UnitOrConnection varchar(10), StartDate datetime, endDate datetime)
insert  into #ranges values
('1K2FWV6VZX','NE554801','2024-01-12 08:31:58.000','2024-01-12 08:57:43.000'),
('1K2FX7N4X9','NE554801','2024-01-12 10:09:47.000','2024-01-12 10:25:21.000'),
('1K2FXEEYGS','NE554701','2024-01-12 11:23:38.000','2024-01-12 11:37:07.000'),
('1K2HJ0TQ5L','NE554801','2024-01-15 08:40:14.000','2024-01-15 08:53:16.000'),
('1K2HJ4GF51','NE554701','2024-01-15 10:00:33.000','2024-01-15 10:21:33.000'),
('1K2HJJE8UH','NE554801','2024-01-15 11:32:44.000','2024-01-15 11:48:46.000'),
('1K2HKBMHVZ','NE554701','2024-01-15 16:29:34.000','2024-01-15 16:52:28.000'),
('1K2I3F43LT','NE554701','2024-01-16 09:42:28.000','2024-01-16 09:56:01.000'),
('1K2I3FQLMK','NE554801','2024-01-16 10:51:08.000','2024-01-16 11:03:55.000'),
('1K2I3KS6E6','NE554701','2024-01-16 13:27:24.000','2024-01-16 14:14:22.000')


Select top 100  * 
  from #ranges r
	left join #history h
		on h.historyDate between r.StartDate and r.EndDate

PVI · May 7, 2024, 3:45pm

Thank you very much but I think we don't understand each other for my need!

Indeed, my sample data is not suitable because my tables contain a lot of historical data.
The "batches" table contains 67 rows for the last month.
For a single row (=1 batch) for example, the below exemple DateTime range is approximately 14 minutes.

SELECT TOP (10) [Batch_Log_ID]
      ,[UnitOrConnection]
      ,[DateTimeStart]
      ,[DateTimeEnd]
  FROM [MERCK_PAS_MB].[dbo].[V_DatesRincagesNep]
  where [DateTimeStart] BETWEEN DATEADD(DAY, 1, EOMONTH(GETUTCDATE(), - 2)) AND EOMONTH(GETUTCDATE(), -1)

/*Extract of top 10 */
Batch_Log_ID	UnitOrConnection	DateTimeStart	DateTimeEnd
1K3PZ30UWX	NE554701	2024-04-02 10:35:18.000	2024-04-02 11:00:30.000
1K3PZGL7A0	NE554801	2024-04-02 11:47:02.000	2024-04-02 12:03:15.000
1K3PZLA5I6	NE554701	2024-04-02 13:18:28.000	2024-04-02 13:47:18.000
1K3Q06ED1X	NE554701	2024-04-02 15:57:56.000	2024-04-02 16:15:29.000
1K3QHW2X6B	NE554801	2024-04-03 08:03:52.000	2024-04-03 08:19:16.000
**1K3QI2IU78	NE554701	2024-04-03 09:40:21.000	2024-04-03 09:54:15.000**
1K3QJ1IJLY	NE554801	2024-04-03 14:06:23.000	2024-04-03 14:19:35.000
1K3R1YG1I0	NE554801	2024-04-04 08:26:32.000	2024-04-04 08:43:00.000
1K3R3I3WXU	NE554701	2024-04-04 16:50:09.000	2024-04-04 17:01:15.000
1K3R3J97LR	NE554801	2024-04-04 17:03:24.000	2024-04-04 17:19:41.000

If I report this targeted range on my specific tagname and in the associated date-time range, I obtain 166 rows, of which I wish to extract the max.

exemple here for top 20 rows

SELECT top(20) [DateTime]
      ,[TagName]
      ,[Value]
  FROM [Runtime].[dbo].[v_History]
  where TagName = 'NE5547_CIT_320.fMesure' and [DateTime] between '2024-04-03 09:40:21.000' and '2024-04-03 09:54:15.000'

/*Extract for historical data associated to Batch 1K3QI2IU78
in range 2024-04-03 09:40:21...2024-04-03 09:54:15.000*/
DateTime	TagName	Value
2024-04-03 09:40:21.0000000	NE5547_CIT_320.fMesure	-0.248842597007751
2024-04-03 09:50:44.7220000	NE5547_CIT_320.fMesure	0.517939805984497
2024-04-03 09:50:45.7850000	NE5547_CIT_320.fMesure	1.57118058204651
2024-04-03 09:50:46.6750000	NE5547_CIT_320.fMesure	1.68402779102325
2024-04-03 09:50:47.6280000	NE5547_CIT_320.fMesure	1.70428252220154
2024-04-03 09:50:48.7380000	NE5547_CIT_320.fMesure	1.71296298503876
2024-04-03 09:50:50.8470000	NE5547_CIT_320.fMesure	1.71875
2024-04-03 09:50:51.8000000	NE5547_CIT_320.fMesure	1.72164344787598
2024-04-03 09:50:53.9250000	NE5547_CIT_320.fMesure	1.72453701496124
2024-04-03 09:50:57.0660000	NE5547_CIT_320.fMesure	1.72164344787598
2024-04-03 09:50:57.9720000	NE5547_CIT_320.fMesure	1.71006941795349
2024-04-03 09:50:59.0030000	NE5547_CIT_320.fMesure	1.69849526882172
2024-04-03 09:50:59.8780000	NE5547_CIT_320.fMesure	1.6898148059845
2024-04-03 09:51:00.7380000	NE5547_CIT_320.fMesure	1.68692123889923
2024-04-03 09:51:01.6280000	NE5547_CIT_320.fMesure	1.68402779102325
2024-04-03 09:51:02.6130000	NE5547_CIT_320.fMesure	1.67534720897675
2024-04-03 09:51:03.6280000	NE5547_CIT_320.fMesure	1.66666674613953
2024-04-03 09:51:04.6910000	NE5547_CIT_320.fMesure	1.65798604488373
2024-04-03 09:51:05.8470000	NE5547_CIT_320.fMesure	1.62326395511627
2024-04-03 09:51:06.8310000	NE5547_CIT_320.fMesure	1.53645825386047

Then i need to extract Max Value

SELECT max([Value])
  FROM [Runtime].[dbo].[v_History]
  where TagName = 'NE5547_CIT_320.fMesure' and [DateTime] between '2024-04-03 09:40:21.000' and '2024-04-03 09:54:15.000'

/*Extract*/
1.72453701496124

Finally, the request on my first post is to extract this MaxValue for each "BatchList" row in a new table.

mike01 · May 7, 2024, 5:57pm

this is why YOU need to provide readily consumable ddl and data so that we can help. We are guessing at what you need. If you can provide the data that is relevant to your issue then we can help, otherwise you're going to get alot of this back and forth. It doesn't have to be real data, just something that represents your issue and works (i.e. dates align).

PVI · May 21, 2024, 7:04am

Sorry I don't really know how to export DDL as requested..
I finally resolve my issue with a PowerShell script exporting CSV with sucessive each loop.
Thank you.