Find Max Value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Find Max Value

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-14 : 10:52:58

I have this query below that when runs returns a 0 value when I know that it should be higher than 10.

From looking at the line
AND (WL_DATA.[Waiting List Name] IN ('PD,PDD,PP,PGA,PBCS,PBCN,PDOS'))
I know that if I was to look at the WL Name individually then I return the values for each one, but what I want to do is combine them all and return the Max Value from all those WL Name, where am I going wrong?


SELECT TOP 1 COALESCE(MAX(jez.WL_DATA.[Weeks Waiting]),0) AS [Max]
FROM	WL_RANGES
LEFT JOIN WL_DATA
ON WL_DATA.[Weeks Range] = WL_RANGES.[Weeks Range]
AND (WL_DATA.[Waiting List Name]  IN ('PD,PDD,PP,PGA,PBCS,PBCN,PDOS'))
AND (WL_DATA.[Month] = 'September')
AND (WL_DATA.[Year] = '2008')
AND (WL_DATA.[Generated Date] = '14/10/2008')
AND (WL_DATA.[Date Removed from Waiting List] IS NULL)
GROUP BY WL_RANGES.[Weeks Range Title], WL_RANGES.[Weeks Range]
ORDER BY [Max] DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-14 : 10:59:11

are you looking for max of all values from table or max of value per each WL_RANGES.[Weeks Range Title], WL_RANGES.[Weeks Range]
group?

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-15 : 03:36:05

No I just want the Max value of all values

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-15 : 03:40:41

What is the result of this?

SELECT MAX(jez.WL_DATA.[Weeks Waiting]) AS [Max]
FROM WL_RANGES
LEFT JOIN WL_DATA
ON WL_DATA.[Weeks Range] = WL_RANGES.[Weeks Range]
AND (WL_DATA.[Waiting List Name] IN ('PD,PDD,PP,PGA,PBCS,PBCN,PDOS'))
AND (WL_DATA.[Month] = 'September')
AND (WL_DATA.[Year] = '2008')
AND (WL_DATA.[Generated Date] = '14/10/2008')
AND (WL_DATA.[Date Removed from Waiting List] IS NULL)
GROUP BY WL_RANGES.[Weeks Range Title], WL_RANGES.[Weeks Range]

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-15 : 03:49:03

?? AND (WL_DATA.[Waiting List Name] IN ('PD,PDD,PP,PGA,PBCS,PBCN,PDOS')) ??

AND WL_DATA.[Waiting List Name] IN ('PD', 'PDD', 'PP', 'PGA', 'PBCS', 'PBCN', 'PDOS')

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 03:58:51

quote:
Originally posted by JezLisle

No I just want the Max value of all values

then i tink what you want is this

SELECT MAX(COALESCE(jez.WL_DATA.[Weeks Waiting],0)) AS [Max]
FROM	WL_RANGES
LEFT JOIN WL_DATA
ON WL_DATA.[Weeks Range] = WL_RANGES.[Weeks Range]
AND (WL_DATA.[Waiting List Name]  IN ('PD,PDD,PP,PGA,PBCS,PBCN,PDOS'))
AND (WL_DATA.[Month] = 'September')
AND (WL_DATA.[Year] = '2008')
AND (WL_DATA.[Generated Date] = '14/10/2008')
AND (WL_DATA.[Date Removed from Waiting List] IS NULL)

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-15 : 04:21:17

quote:
Originally posted by madhivanan

What is the result of this?

SELECT MAX(jez.WL_DATA.[Weeks Waiting]) AS [Max]
FROM WL_RANGES
LEFT JOIN WL_DATA
ON WL_DATA.[Weeks Range] = WL_RANGES.[Weeks Range]
AND (WL_DATA.[Waiting List Name] IN ('PD,PDD,PP,PGA,PBCS,PBCN,PDOS'))
AND (WL_DATA.[Month] = 'September')
AND (WL_DATA.[Year] = '2008')
AND (WL_DATA.[Generated Date] = '14/10/2008')
AND (WL_DATA.[Date Removed from Waiting List] IS NULL)
GROUP BY WL_RANGES.[Weeks Range Title], WL_RANGES.[Weeks Range]

Madhivanan

Failing to plan is Planning to fail

The result of this query is 0 (zero)

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-15 : 04:22:24

quote:

Originally posted by visakh16

quote:
Originally posted by JezLisle

No I just want the Max value of all values

then i tink what you want is this

SELECT MAX(COALESCE(jez.WL_DATA.[Weeks Waiting],0)) AS [Max]
FROM	WL_RANGES
LEFT JOIN WL_DATA
ON WL_DATA.[Weeks Range] = WL_RANGES.[Weeks Range]
AND (WL_DATA.[Waiting List Name]  IN ('PD,PDD,PP,PGA,PBCS,PBCN,PDOS'))
AND (WL_DATA.[Month] = 'September')
AND (WL_DATA.[Year] = '2008')
AND (WL_DATA.[Generated Date] = '14/10/2008')
AND (WL_DATA.[Date Removed from Waiting List] IS NULL)

Thanks for this, I've tried this and again the result is 0 (zero)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-15 : 04:22:30

See post made 10/15/2008 : 03:49:03

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-15 : 04:42:36

Thanks for that when I build it as this code then it works great...


SELECT TOP 1 COALESCE(MAX(jez.WL_DATA.[Weeks Waiting]),0) AS [Max]
FROM	WL_RANGES
LEFT JOIN WL_DATA
ON WL_DATA.[Weeks Range] = WL_RANGES.[Weeks Range]
AND WL_DATA.[Waiting List Name] IN ('PD', 'PDD', 'PP', 'PGA', 'PBCS', 'PBCN', 'PDOS')
AND (WL_DATA.[Month] = 'September')
AND (WL_DATA.[Year] = '2008')
AND (WL_DATA.[Generated Date] = '14/10/2008')
AND (WL_DATA.[Date Removed from Waiting List] IS NULL)
GROUP BY WL_RANGES.[Weeks Range Title], WL_RANGES.[Weeks Range]
ORDER BY [Max] DESC

My problem is that once I've done that I have now added it into the actual report I need. This is the code below, this is the error message I get it this

Msg 8144, Level 16, State 2, Procedure sp_LongWks_WLName_Report, Line 0
Procedure or function sp_LongWks_WLName_Report has too many arguments specified.

I feel its because I'm putting the WL Name as this in the EXEC Procedure


EXEC sp_LongWks_WLName_Report 'September', '2008', '14/10/2008', 'PD', 'PDD', 'PP', 'PGA', 'PBCS', 'PBCN', 'PDOS'


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go








ALTER PROCEDURE [jez].[sp_LongWks_WLName_Report]
	@pMonth VARCHAR(25),
	@pYear VARCHAR(4),
	@pDateTime DATETIME,
	@pWLName VARCHAR(25)
AS
SET NOCOUNT ON
DECLARE @WLList VARCHAR(170)
DECLARE @sep VARCHAR(1)
DECLARE @SQLString NVARCHAR(1000)
DECLARE @SQLParam NVARCHAR(100)
DECLARE @posn INT
DECLARE @leng INT
PRINT @pWLName
SET @sep = ','
SET @WLList = ''
SET @posn = CHARINDEX(@sep, @pWLName,1)
IF(@posn <> 0) BEGIN
    SET @leng = LEN(@pWLName)
    WHILE (@leng<>0) BEGIN
        IF(@posn = 0) BEGIN
            SET @WLList = @WLList + CHAR(39) + RTRIM(@pWLName) + CHAR(39)
            SET @leng = 0
        END
        ELSE BEGIN
            SET @WLList = @WLList + CHAR(39) + SUBSTRING(@pWLName,1,@posn-1) + CHAR(39) + ','
            SET @pWLName = SUBSTRING(@pWLName, @posn+1, @leng - (@posn-1))
            SET @leng = LEN(@pWLName)
            SET @posn = CHARINDEX(@sep, @pWLName,1)
        END
    END
END
ELSE IF(@posn=0) BEGIN
    SET @WLList = CHAR(39) + @pWLName + CHAR(39)
END
SET @SQLString = N'SELECT TOP 1 COALESCE(MAX(jez.WL_DATA.[Weeks Waiting]),0) AS [Max]  ' +
                            'FROM jez.WL_RANGES ' +
							'LEFT JOIN jez.WL_DATA ' +
							'ON jez.WL_DATA.[Weeks Range] = jez.WL_RANGES.[Weeks Range] ' +
							'AND jez.WL_DATA.[Waiting List Name] IN (' + @WLList +') ' +
							'AND jez.WL_DATA.[Month] = (' + CHAR(39) +@pMonth + CHAR(39) + ') ' +
							'AND jez.WL_DATA.[Year] = (' + CHAR(39) + @pYear + CHAR(39) + ') ' +
							'AND jez.WL_DATA.[Generated Date] =CONVERT(DATETIME, @pDateTimeI, 111) ' +
							'AND jez.WL_DATA.[Date Removed from Waiting List] IS NULL '+
							'GROUP BY jez.WL_RANGES.[Weeks Range Title], jez.WL_RANGES.[Weeks Range] ' +
							'ORDER BY jez.WL_RANGES.[Weeks Range] DESC '
SET @SQLParam = N'@pDateTimeI DATETIME'
PRINT @SQLString
EXEC sp_executesql @SQLString, @SQLParam, @pDateTimeI = @pDateTime
SET NOCOUNT OFF

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-15 : 04:48:01

EXEC sp_LongWks_WLName_Report 'September', '2008', '14/10/2008', '''PD'', ''PDD'', ''PP'', ''PGA'', ''PBCS'', ''PBCN'', ''PDOS'''

Madhi will come in a few minutes with a link describing the causes of multiple delimeters.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 04:49:13

it should be this

EXEC sp_LongWks_WLName_Report 'September', '2008', '14/10/2008','PD, PDD,PP,PGA,PBCS,PBCN,PDOS'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 04:51:46

[code]set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [jez].[sp_LongWks_WLName_Report]
@pMonth VARCHAR(25),
@pYear VARCHAR(4),
@pDateTime DATETIME,
@pWLName VARCHAR(25)
AS
SET NOCOUNT ON
DECLARE @WLList VARCHAR(170)
DECLARE @sep VARCHAR(1)
DECLARE @SQLString NVARCHAR(1000)
DECLARE @SQLParam NVARCHAR(100)
DECLARE @posn INT
DECLARE @leng INT
PRINT @pWLName
SET @sep = ','
SET @WLList = ''
SET @posn = CHARINDEX(@sep, @pWLName,1)
IF(@posn <> 0) BEGIN
SET @leng = LEN(@pWLName)
WHILE (@leng<>0) BEGIN
IF(@posn = 0) BEGIN
SET @WLList = @WLList + CHAR(39) + RTRIM(@pWLName) + CHAR(39)
SET @leng = 0
END
ELSE BEGIN
SET @WLList = @WLList + CHAR(39) + SUBSTRING(@pWLName,1,@posn-1) + CHAR(39) + ','
SET @pWLName = SUBSTRING(@pWLName, @posn+1, @leng - (@posn-1))
SET @leng = LEN(@pWLName)
SET @posn = CHARINDEX(@sep, @pWLName,1)
END
END
END
ELSE IF(@posn=0) BEGIN
SET @WLList = CHAR(39) + @pWLName + CHAR(39)
END
SET @SQLString = N'SELECT TOP 1 COALESCE(MAX(jez.WL_DATA.[Weeks Waiting]),0) AS [Max] ' +
'FROM jez.WL_RANGES ' +
'LEFT JOIN jez.WL_DATA ' +
'ON jez.WL_DATA.[Weeks Range] = jez.WL_RANGES.[Weeks Range] ' +
'AND jez.WL_DATA.[Waiting List Name] IN (''' + REPLACE(@WLList,',',''',''') +''') ' +
'AND jez.WL_DATA.[Month] = (' + CHAR(39) +@pMonth + CHAR(39) + ') ' +
'AND jez.WL_DATA.[Year] = (' + CHAR(39) + @pYear + CHAR(39) + ') ' +
'AND jez.WL_DATA.[Generated Date] =CONVERT(DATETIME, @pDateTimeI, 111) ' +
'AND jez.WL_DATA.[Date Removed from Waiting List] IS NULL '+
'GROUP BY jez.WL_RANGES.[Weeks Range Title], jez.WL_RANGES.[Weeks Range] ' +
'ORDER BY jez.WL_RANGES.[Weeks Range] DESC '
SET @SQLParam = N'@pDateTimeI DATETIME'
PRINT @SQLString
EXEC sp_executesql @SQLString, @SQLParam, @pDateTimeI = @pDateTime
SET NOCOUNT OFF[/code]

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-15 : 05:06:13

Thanks I have tried the input of the line
'AND jez.WL_DATA.[Waiting List Name] IN (''' + REPLACE(@WLList,',',''',''') +''') ' +
when I run the EXEC sp_LongWks_WLName_Report 'September', '2008', '14/10/2008', 'PD,PDD,PP,PGA,PBCS,PBCN,PDOS'
I get this error message and dont understand what the problem is as I have specified all correct WL Name in the EXEC Statement

PD,PDD,PP,PGA,PBCS,PBCN,P
SELECT TOP 1 COALESCE(MAX(jez.WL_DATA.[Weeks Waiting]),0) AS [Max] FROM jez.WL_RANGES LEFT JOIN jez.WL_DATA ON jez.WL_DATA.[Weeks Range] = jez.WL_RANGES.[Weeks Range] AND jez.WL_DATA.[Waiting List Name] IN (''PD'',''PDD'',''PP'',''PGA'',''PBCS'',''PBCN'',''P'') AND jez.WL_DATA.[Month] = ('September') AND jez.WL_DATA.[Year] = ('2008') AND jez.WL_DATA.[Generated Date] =CONVERT(DATETIME, @pDateTimeI, 111) AND jez.WL_DATA.[Date Removed from Waiting List] IS NULL GROUP BY jez.WL_RANGES.[Weeks Range Title], jez.WL_RANGES.[Weeks Range] ORDER BY jez.WL_RANGES.[Weeks Range] DESC
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'PD'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-15 : 05:17:07

[code]DECLARE @param VARCHAR(200),
@SQL VARCHAR(8000),
@pDateTimeI DATETIME,
@pMonth VARCHAR(25),
@pYear VARCHAR(4)

SELECT @param = 'PD,PDD,PP,PGA,PBCS,PBCN,PDOS',
@pDateTimeI = GETDATE(),
@pMonth = 'September',
@pYear = '2008',
@SQL = 'SELECT TOP 1 COALESCE(MAX(jez.WL_DATA.[Weeks Waiting]),0) AS [Max]
FROM jez.WL_RANGES
LEFT JOIN jez.WL_DATA ON jez.WL_DATA.[Weeks Range] = jez.WL_RANGES.[Weeks Range]
AND jez.WL_DATA.[Waiting List Name] IN (''' + REPLACE(@param, ',', ''', ''') + ''')
AND jez.WL_DATA.[Month] = ' + QUOTENAME(@pMonth, '''') + '
AND jez.WL_DATA.[Year] = ' + QUOTENAME(@pYear, '''') + '
AND jez.WL_DATA.[Generated Date] = ''' + CONVERT(CHAR(10), @pDateTimeI, 111) + '''
AND jez.WL_DATA.[Date Removed from Waiting List] IS NULL
GROUP BY jez.WL_RANGES.[Weeks Range Title],
jez.WL_RANGES.[Weeks Range]
ORDER BY jez.WL_RANGES.[Weeks Range] DESC'

PRINT @SQL[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-15 : 05:25:58

As Madhi has not yet posted the famous link, i'm taking the liberty of posting it for him

. Refer below to understand about single quotes

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-15 : 05:52:50

quote:

Originally posted by Peso

DECLARE	@param VARCHAR(200),
	@SQL VARCHAR(8000),
	@pDateTimeI DATETIME,
	@pMonth VARCHAR(25),
	@pYear VARCHAR(4)

SELECT	@param = 'PD,PDD,PP,PGA,PBCS,PBCN,PDOS',
	@pDateTimeI = GETDATE(),
	@pMonth = 'September',
	@pYear = '2008',
	@SQL = 'SELECT TOP 1	COALESCE(MAX(jez.WL_DATA.[Weeks Waiting]),0) AS [Max]
FROM		jez.WL_RANGES
LEFT JOIN	jez.WL_DATA ON jez.WL_DATA.[Weeks Range] = jez.WL_RANGES.[Weeks Range]
			AND jez.WL_DATA.[Waiting List Name] IN (''' + REPLACE(@param, ',', ''', ''') + ''')
			AND jez.WL_DATA.[Month] = ' + QUOTENAME(@pMonth, '''') + '
			AND jez.WL_DATA.[Year] = ' + QUOTENAME(@pYear, '''') + '
			AND jez.WL_DATA.[Generated Date] = ''' + CONVERT(CHAR(10), @pDateTimeI, 111) + '''
			AND jez.WL_DATA.[Date Removed from Waiting List] IS NULL
GROUP BY	jez.WL_RANGES.[Weeks Range Title],
		jez.WL_RANGES.[Weeks Range]
ORDER BY	jez.WL_RANGES.[Weeks Range] DESC'

PRINT @SQL

E 12°55'05.63"
N 56°04'39.26"

This prints out the SQL and when running it I get the same results as when I used the SQL in earlier threads, which is great... I need to understand how I can use this in the thread posted Posted - 10/15/2008 : 04:42:36

This is because what I have used in the WL Name field will change constantly from eith 1 WL Name or multiple different WL Name, how is it possible to take the SQL in the Quote into this procedure?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-15 : 06:10:47

Use this in your stored procedure

DECLARE	@param VARCHAR(200),
	@SQL VARCHAR(8000),
	@pDateTimeI DATETIME,
	@pMonth VARCHAR(25),
	@pYear VARCHAR(4)

SELECT	@param = 'PD,PDD,PP,PGA,PBCS,PBCN,PDOS',
	@pDateTimeI = GETDATE(),
	@pMonth = 'September',
	@pYear = '2008',
	@SQL = 'SELECT TOP 1	COALESCE(MAX(jez.WL_DATA.[Weeks Waiting]),0) AS [Max]
FROM		jez.WL_RANGES
LEFT JOIN	jez.WL_DATA ON jez.WL_DATA.[Weeks Range] = jez.WL_RANGES.[Weeks Range]
			AND jez.WL_DATA.[Waiting List Name] IN (''' + REPLACE(@param, ',', ''', ''') + ''')
			AND jez.WL_DATA.[Month] = ' + QUOTENAME(@pMonth, '''') + '
			AND jez.WL_DATA.[Year] = ' + QUOTENAME(@pYear, '''') + '
			AND jez.WL_DATA.[Generated Date] = ''' + CONVERT(CHAR(10), @pDateTimeI, 111) + '''
			AND jez.WL_DATA.[Date Removed from Waiting List] IS NULL
GROUP BY	jez.WL_RANGES.[Weeks Range Title],
		jez.WL_RANGES.[Weeks Range]
ORDER BY	jez.WL_RANGES.[Weeks Range] DESC'

PRINT @SQL

And call the stored procedure as before, like this

EXEC sp_LongWks_WLName_Report 'September', '2008', '14/10/2008', 'PD, PDD,PP,PGA,PBCS,PBCN,PDOS'

The procedure will format the IN string for you...

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-15 : 06:16:50

But the problem being is that in your thread you have specified
@param = 'PD,PDD,PP,PGA,PBCS,PBCN,PDOS' in the Stored Procedure. The only place I want to call these from 'PD, PDD,PP,PGA,PBCS,PBCN,PDOS' is the EXEC Statement as its these that will change, I just want 1 Stored Procedure and have multiple EXEC Statements

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-15 : 06:25:23

[code]ALTER PROCEDURE jez.sp_LongWks_WLName_Report
(
@pMonth VARCHAR(25),
@pYear VARCHAR(4),
@pDateTime DATETIME,
@pWLName VARCHAR(25)
)
AS

SET NOCOUNT ON

SET @SQL = 'SELECT TOP 1 COALESCE(MAX(jez.WL_DATA.[Weeks Waiting]), 0) AS [Max]
FROM jez.WL_RANGES
LEFT JOIN jez.WL_DATA ON jez.WL_DATA.[Weeks Range] = jez.WL_RANGES.[Weeks Range]
AND jez.WL_DATA.[Waiting List Name] IN (''' + REPLACE(@pWLName, ',', ''', ''') + ''')
AND jez.WL_DATA.[Month] = ' + QUOTENAME(@pMonth, '''') + '
AND jez.WL_DATA.[Year] = ' + QUOTENAME(@pYear, '''') + '
AND jez.WL_DATA.[Generated Date] = ''' + CONVERT(CHAR(10), @pDateTime, 111) + '''
AND jez.WL_DATA.[Date Removed from Waiting List] IS NULL
GROUP BY jez.WL_RANGES.[Weeks Range Title],
jez.WL_RANGES.[Weeks Range]
ORDER BY jez.WL_RANGES.[Weeks Range] DESC'

PRINT @SQL
EXEC (@SQL)
GO[/code]Call the stored procedure with[code]EXEC sp_LongWks_WLName_Report 'September', '2008', '14/10/2008', 'PD, PDD,PP,PGA,PBCS,PBCN,PDOS'[/code]

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-15 : 09:03:25

quote:
Originally posted by visakh16

As Madhi has not yet posted the famous link, i'm taking the liberty of posting it for him . Refer below to understand about single quotes

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

I would also post
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/03/15/splitting-csv-to-columns.aspx

Madhivanan

Failing to plan is Planning to fail

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources