October 16, 2014 at 2:43 pm
Hi,
I have a dataset that I need to search for a maximum value of a specified index.
Example of dataset:
Heat Index Heat # Frame Window
1 584 110 110
1 584 102 109
1 584 95 106
1 584 190 112
2 586 100 100
2 586 150 120
2 586 170 130
2 586 112 126
I need to find the maximum value of Frame for each heat and the maximum value of Window for each heat. As you can see each heat has several data points.
Is there a way I can send the dataset to a function? I could then use a loop to test for the maximum of Frame and the maximum of Window for each heat.
Could I use an array?
Any help is greatly appreciated.
October 16, 2014 at 2:59 pm
So using T-SQL isn't an option, because you're using SSRS?
If you can use T-SQL, something like this should work:
SELECT HeatNo, MAX(Frame) AS MaxFrame, MAX(Window) AS MaxWindow
FROM (
SELECT 1 AS Heat_Index, 584 AS HeatNo, 110 AS Frame, 110 AS Window
UNION ALL SELECT 1, 584, 102, 109
UNION ALL SELECT 1, 584, 95, 106
UNION ALL SELECT 1, 584, 190, 112
UNION ALL SELECT 2, 586, 100, 100
UNION ALL SELECT 2, 586, 150, 120
UNION ALL SELECT 2, 586, 170, 130
UNION ALL SELECT 2, 586, 112, 126) data
GROUP BY HeatNo
ORDER BY HeatNo;
Is your expected result something like this?:
HeatNoMaxFrameMaxWindow
584 190 112
586 170 130
October 17, 2014 at 7:57 am
You are right, I am using vb.
Thanks
October 18, 2014 at 10:37 am
Here is a quick and simple window function solution
😎
USE tempdb;
GO
SET NOCOUNT ON;
;WITH BASE_DATA(Heat_Index,HeatNo,Frame,Window) AS
(SELECT * FROM
(VALUES
(1, 584, 110, 110)
,(1, 584, 102, 109)
,(1, 584, 95, 106)
,(1, 584, 190, 112)
,(2, 586, 100, 100)
,(2, 586, 150, 120)
,(2, 586, 170, 130)
,(2, 586, 112, 126)
) AS X(Heat_Index,HeatNo,Frame,Window)
)
/* Find the maximum value of Frame for each heat
and the maximum value of Window for each heat.
This is done by partitioning the set on HeatNo.
*/
SELECT
BD.Heat_Index
,BD.HeatNo
,BD.Frame
,BD.Window
,MAX(BD.Frame) OVER
(
PARTITION BY BD.HeatNo
) AS MAX_Frame
,MAX(BD.Window) OVER
(
PARTITION BY BD.HeatNo
) AS MAX_Window
FROM BASE_DATA BD;
Results
Heat_Index HeatNo Frame Window MAX_Frame MAX_Window
----------- ----------- ----------- ----------- ----------- -----------
1 584 102 109 190 112
1 584 95 106 190 112
1 584 190 112 190 112
1 584 110 110 190 112
2 586 150 120 170 130
2 586 170 130 170 130
2 586 112 126 170 130
2 586 100 100 170 130
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply