SQL value based on a different row in the table

Transact-SQL
1

Based on the sample data below, I want the value of ProgramKPIMasterId = 7 based on the DataSetNo with the max value of ProgramKPIMasterId = -1 grouped by ProgramKPIId

The example is ProgramKPIId = 2 and the datasetno = 2 based on the max value of 10/12/2023 for ProgramKPIMasterId = -1 AND PogramKPIId = 2

value = 100

Thank you.

Rob

ProgramKPISInfoId ProgramKPIId DataSetNo ProgramKPIMasterId value
6 2 1 0 Test2
7 2 1 -1 10/11/2024
8 2 1 12 Yellow
9 2 1 11 Yellow
10 2 1 9 100
11 2 1 7 80
12 2 1 10 Green
13 2 2 0 Test3
14 2 2 -1 10/12/2023
15 2 2 12 Green
16 2 2 11 Green
17 2 2 9 100
18 2 2 7 100
19 2 2 10 Green
2

What do you mean exactly by "the max value" ?

What you have shown is the sample data or the expected result ? Please provide both sample and also the expected result

3

In future, please provide consumable test data:

SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
CREATE TABLE #t
(
	ProgramKPISInfoId int NOT NULL PRIMARY KEY
	,ProgramKPIId int NOT NULL
	,DataSetNo int NOT NULL
	,ProgramKPIMasterId int NOT NULL
	,[value] varchar(50) NOT NULL
);
INSERT INTO #t
VALUES (6, 2, 1, 0, 'Test2')
	,(7, 2, 1, -1, '10/11/2024')
	,(8, 2, 1, 12, 'Yellow')
	,(9, 2, 1, 11, 'Yellow')
	,(10, 2, 1, 9, '100')
	,(11, 2, 1, 7, '80')
	,(12, 2, 1, 10, 'Green')
	,(13, 2, 2, 0, 'Test3')
	,(14, 2, 2, -1, '10/12/2023')
	,(15, 2, 2, 12, 'Green')
	,(16, 2, 2, 11, 'Green')
	,(17, 2, 2, 9, '100')
	,(18, 2, 2, 7, '100')
	,(19, 2, 2, 10, 'Green');
GO

To me 10/12/2023 looks like the MIN date. The following should get you started:

WITH PivotValues
AS
(
	SELECT ProgramKPIId, DataSetNo
		,MAX(CASE WHEN ProgramKPIMasterId = 0 THEN [Value] END) AS Val00
		,MAX(CASE WHEN ProgramKPIMasterId = -1 THEN TRY_CONVERT(date, [Value], 101) END) AS Valm1
		,MAX(CASE WHEN ProgramKPIMasterId = 12 THEN [Value] END) AS Val12
		,MAX(CASE WHEN ProgramKPIMasterId = 11 THEN [Value] END) AS Val11
		,MAX(CASE WHEN ProgramKPIMasterId = 9 THEN TRY_CAST([Value] AS int) END) AS Val09
		,MAX(CASE WHEN ProgramKPIMasterId = 7 THEN TRY_CAST([Value] AS int) END) AS Val07
		,MAX(CASE WHEN ProgramKPIMasterId = 10 THEN [Value] END) AS Val10
	FROM #t
	GROUP BY ProgramKPIId, DataSetNo
)
,MaxDates
AS
(
	SELECT ProgramKPIId, DataSetNo, Val00, Valm1, Val12, Val11, Val09, Val07, Val10
		,MAX(Valm1) OVER (PARTITION BY ProgramKPIId) AS MaxValm1
	FROM PivotValues
)
SELECT ProgramKPIId, DataSetNo, Val00, Valm1, Val12, Val11, Val09, Val07, Val10
FROM MaxDates
WHERE Valm1 = MaxValm1;
4

Let me try to clarify. I am not sure how to provide consumable data. At the bottom is a sql script I created.

The result I am looking for is as follows:
Each ProgramKPIId will have one or more DataSetNo

Identify the DataSetNo based on the maximum value for ProgramKPIMasterId = -1 for each ProgramKPId.
In the test data above for ProgramKPIId =2, the maximum value for ProgramKPIMasterId = -1 is 10/11/2024. The corresponding DataSetNo = 1.
The corresponding value for ProgramKPIId = 2 AND ProgramKPIMasterId = 7 AND DataSetNo = 1 would be 80.

The result I am looking for is 80.

Any suggestions to improve my posts would be appreciated.

Thank you.

Rob

SQL Script for data
CREATE TABLE [dbo].[ProgramKPISInfo](
[ProgramKPISInfoId] [bigint] IDENTITY(1,1) NOT NULL,
[ProgramKPIId] [bigint] NOT NULL,
[DataSetNo] [bigint] NOT NULL,
[ProgramKPIMasterId] [bigint] NOT NULL,
[value] nvarchar NULL,
[CreatedByUserId] [bigint] NULL,
[CreatedUTCDate] [datetime] NULL,
[ModifiedByUserId] [bigint] NULL,
[ModifiedUTCDate] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[ProgramKPISInfoId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[ProgramKPISInfo] ON

INSERT [dbo].[ProgramKPISInfo] ([ProgramKPISInfoId], [ProgramKPIId], [DataSetNo], [ProgramKPIMasterId], [value], [CreatedByUserId], [CreatedUTCDate], [ModifiedByUserId], [ModifiedUTCDate]) VALUES (6, 2, 1, 0, N'Test2', 105, CAST(N'2023-10-12T15:08:59.987' AS DateTime), NULL, NULL)
INSERT [dbo].[ProgramKPISInfo] ([ProgramKPISInfoId], [ProgramKPIId], [DataSetNo], [ProgramKPIMasterId], [value], [CreatedByUserId], [CreatedUTCDate], [ModifiedByUserId], [ModifiedUTCDate]) VALUES (7, 2, 1, -1, N'10/12/2023 12:00:00 AM', 105, CAST(N'2023-10-12T15:08:59.987' AS DateTime), NULL, NULL)
INSERT [dbo].[ProgramKPISInfo] ([ProgramKPISInfoId], [ProgramKPIId], [DataSetNo], [ProgramKPIMasterId], [value], [CreatedByUserId], [CreatedUTCDate], [ModifiedByUserId], [ModifiedUTCDate]) VALUES (8, 2, 1, 12, N'Yellow', 105, CAST(N'2023-10-12T15:08:59.987' AS DateTime), NULL, NULL)
INSERT [dbo].[ProgramKPISInfo] ([ProgramKPISInfoId], [ProgramKPIId], [DataSetNo], [ProgramKPIMasterId], [value], [CreatedByUserId], [CreatedUTCDate], [ModifiedByUserId], [ModifiedUTCDate]) VALUES (9, 2, 1, 11, N'Yellow', 105, CAST(N'2023-10-12T15:08:59.987' AS DateTime), NULL, NULL)
INSERT [dbo].[ProgramKPISInfo] ([ProgramKPISInfoId], [ProgramKPIId], [DataSetNo], [ProgramKPIMasterId], [value], [CreatedByUserId], [CreatedUTCDate], [ModifiedByUserId], [ModifiedUTCDate]) VALUES (10, 2, 1, 9, N'100', 105, CAST(N'2023-10-12T15:08:59.987' AS DateTime), NULL, NULL)
INSERT [dbo].[ProgramKPISInfo] ([ProgramKPISInfoId], [ProgramKPIId], [DataSetNo], [ProgramKPIMasterId], [value], [CreatedByUserId], [CreatedUTCDate], [ModifiedByUserId], [ModifiedUTCDate]) VALUES (11, 2, 1, 7, N'100.00', 105, CAST(N'2023-10-12T15:08:59.987' AS DateTime), NULL, NULL)
INSERT [dbo].[ProgramKPISInfo] ([ProgramKPISInfoId], [ProgramKPIId], [DataSetNo], [ProgramKPIMasterId], [value], [CreatedByUserId], [CreatedUTCDate], [ModifiedByUserId], [ModifiedUTCDate]) VALUES (12, 2, 1, 10, N'Green', 105, CAST(N'2023-10-12T15:08:59.987' AS DateTime), NULL, NULL)
INSERT [dbo].[ProgramKPISInfo] ([ProgramKPISInfoId], [ProgramKPIId], [DataSetNo], [ProgramKPIMasterId], [value], [CreatedByUserId], [CreatedUTCDate], [ModifiedByUserId], [ModifiedUTCDate]) VALUES (13, 2, 2, 0, N'Test3', 105, CAST(N'2023-10-12T15:09:30.953' AS DateTime), NULL, NULL)
INSERT [dbo].[ProgramKPISInfo] ([ProgramKPISInfoId], [ProgramKPIId], [DataSetNo], [ProgramKPIMasterId], [value], [CreatedByUserId], [CreatedUTCDate], [ModifiedByUserId], [ModifiedUTCDate]) VALUES (14, 2, 2, -1, N'10/12/2023 12:00:00 AM', 105, CAST(N'2023-10-12T15:09:30.953' AS DateTime), NULL, NULL)
INSERT [dbo].[ProgramKPISInfo] ([ProgramKPISInfoId], [ProgramKPIId], [DataSetNo], [ProgramKPIMasterId], [value], [CreatedByUserId], [CreatedUTCDate], [ModifiedByUserId], [ModifiedUTCDate]) VALUES (15, 2, 2, 12, N'Green', 105, CAST(N'2023-10-12T15:09:30.953' AS DateTime), NULL, NULL)
INSERT [dbo].[ProgramKPISInfo] ([ProgramKPISInfoId], [ProgramKPIId], [DataSetNo], [ProgramKPIMasterId], [value], [CreatedByUserId], [CreatedUTCDate], [ModifiedByUserId], [ModifiedUTCDate]) VALUES (16, 2, 2, 11, N'Green', 105, CAST(N'2023-10-12T15:09:30.953' AS DateTime), NULL, NULL)
INSERT [dbo].[ProgramKPISInfo] ([ProgramKPISInfoId], [ProgramKPIId], [DataSetNo], [ProgramKPIMasterId], [value], [CreatedByUserId], [CreatedUTCDate], [ModifiedByUserId], [ModifiedUTCDate]) VALUES (17, 2, 2, 9, N'100', 105, CAST(N'2023-10-12T15:09:30.953' AS DateTime), NULL, NULL)
INSERT [dbo].[ProgramKPISInfo] ([ProgramKPISInfoId], [ProgramKPIId], [DataSetNo], [ProgramKPIMasterId], [value], [CreatedByUserId], [CreatedUTCDate], [ModifiedByUserId], [ModifiedUTCDate]) VALUES (18, 2, 2, 7, N'100.00', 105, CAST(N'2023-10-12T15:09:30.953' AS DateTime), NULL, NULL)
INSERT [dbo].[ProgramKPISInfo] ([ProgramKPISInfoId], [ProgramKPIId], [DataSetNo], [ProgramKPIMasterId], [value], [CreatedByUserId], [CreatedUTCDate], [ModifiedByUserId], [ModifiedUTCDate]) VALUES (19, 2, 2, 10, N'Green', 105, CAST(N'2023-10-12T15:09:30.953' AS DateTime), NULL, NULL)
SET IDENTITY_INSERT [dbo].[ProgramKPISInfo] OFF
GO
ALTER TABLE [dbo].[ProgramKPISInfo] WITH CHECK ADD FOREIGN KEY([CreatedByUserId])
REFERENCES [dbo].[User] ([UserId])
GO
ALTER TABLE [dbo].[ProgramKPISInfo] WITH CHECK ADD FOREIGN KEY([ModifiedByUserId])
REFERENCES [dbo].[User] ([UserId])
GO

5

When someone takes the trouble to provide consumable test data for you, and then suggests some code, it might be an idea to run it before replying. Maybe you think not running suggested code will make people more likely to reply to any future questions?!

6

Ifor,
My apologies. I did run the code before posting. It resulted in two rows that displayed a different datasetno and the other columns were the same values. This was my first post ever so I posted again in an attempt to answer the first response and clarify my result. I don't know what consumable data is.

I need a sql expert to help with a large project.

Rob

7

Okay.

Consumable test data is simply data that anyone can use to write a query. (ie No one else can see your actual data, data types etc.) In this case I created a temporary table, #t, and populated it with the data you posted. If you run:

select * from #t order by ProgramKPISInfoId;

you should see the data you posted. Also:

  1. temporary tables are best when posting online as it will save people having to drop real tables from their systems.
  2. you only need include columns from the real table needed to obtain the result.

If you run the query I posted it will return one row - I suspect you did not include the last WHERE clause. (Obviously if you only need the value for ProgramKPIMasterId = 7 then you only need to pivot when ProgramKPIMasterId is -1 or 7.)

When working with a new system I would try to avoid key/value pairs. (I might sometimes use them for user defined fields.) Try a relational design to at least third normal form. One of the problems with your data is the need to hold dates as strings. If you really need to do this then you should use either ISO (YYYYMMDD) or ODBC (YYYY-MM-DD) formats which are generally unambiguous and sort correctly. How a date is displayed to users is a problem for the front end, not the database.

If you are going to be posting more questions on forums I would suggest you search for something like 'How to post a SQL question on a public forum'.

1 Like