asked on

Filtering by MaxDate < ParameterDate

I have a table with 3 PKs {OptType=byte, OptCode=Char(2), EffectiveDate=Date}

I want to match on the Left, Right or Both on OptCode ; exactly on OptType, and the greatest EffectiveDate less than the date I pass in (my revision control).

Is there a cleaner query than what I'm doing?

		Shared Function GetImageIDs(ByVal OptType As Byte, ByVal OptionCode As String, ByVal EffectiveDate As Date) As Integer()
			Dim retVal As Integer()
			Using cmd As New SqlCommand("SELECT ImageID FROM tblOptionMaster " & _
			  "WHERE OptType=@OptType AND " & _
			  "(OptCode=@OptCode AND EffectiveDate=(SELECT Max(EffectiveDate) FROM tblOptionMaster WHERE OptType=@OptType AND OptCode=@OptCode AND EffectiveDate<=@EffDate) " & _
			  "OR OptCode=@OptCode1 AND EffectiveDate=(SELECT Max(EffectiveDate) FROM tblOptionMaster WHERE OptType=@OptType AND OptCode=@OptCode1 AND EffectiveDate<=@EffDate) " & _
			  "OR OptCode=@OptCode2 AND EffectiveDate=(SELECT Max(EffectiveDate) FROM tblOptionMaster WHERE OptType=@OptType AND OptCode=@OptCode2 AND EffectiveDate<=@EffDate) )", _
			  Globals.cnnAZ200)

				cmd.Parameters.Add(New SqlParameter("@OptType", OptType))
				cmd.Parameters.Add(New SqlParameter("@OptCode", OptionCode))
				cmd.Parameters.Add(New SqlParameter("@OptCode1", OptionCode(0) & "*"))
				cmd.Parameters.Add(New SqlParameter("@OptCode2", "*" & OptionCode(1)))
				Using rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
					'Either 1 or 2 results will be returned, default 0
					ReDim retVal(2)
					Dim i As Integer = 0
					While rdr.Read
						retVal(i) = rdr.GetInt32(0)
						i += 1
					End While
				End Using
				Return retVal
			End Using
		End Function

Open in new window

Just to clarify, when I'm matching on Left or Right, my OptCode will have that * in it. It's not a mistaken wildcard or anything.

Scott Pletcher

>> Is there a cleaner query than what I'm doing? <<

I believe so, as below.

I only coded the straight SQL -- I'm not VB anymore, so you'll likely be much better at converting it accurately to the other syntax than I am :-).

SELECT ImageID
FROM (
      SELECT
            ImageID, EffectiveDate,
            ROW_NUMBER() OVER(ORDER BY EffectiveDate DESC) AS row_num
      FROM tblOptionMaster
      WHERE
            OptType = @OptType AND
            (OptCode IN (@OptCode, @OptCode1, @OptCode2)) AND
            EffectiveDate <= @EffDate
) AS derived
WHERE
row_num BETWEEN 1 AND 2 --optional, to limit to max of 2 rows returned
ORDER BY
EffectiveDate DESC

Ryan

ASKER

The optional part I don't need, as the data will either match OptCode OR (Opt1 AND Opt2), never all 3, so by design it will be 0,1 or 2 results

I think this has separated Max(EffectiveDate) from the OptCode, whereas it could differ for Opt1 and Opt2 (they got revised at different times). When I run it with example data, it pulls results from every row having EffectiveDate < EffDate.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ryan

ASKER

Just needed to change the WHERE Row_num=1 to return the newest EffectiveDate.

Never have seen this method before, it's really slick.

Scott Pletcher

Yep, the "windowing functions", like ROW_NUMBER(), are sweet!

I thought from your original code that you were looking for 2 rows per OptCode, but for only one, yes, row_num = 1 is perfect.

Ryan

ASKER

I should either get 1 result if OptCode is 2 Letters, or 2 results if its finds the left and right sides, it'll never match all 3. Minor details. The example you gave me got me where I needed to be.

I learned SQL in Access, which kind of limited my exposure code like this, and makes me miss the aggregate function First().

Filtering by MaxDate &lt; ParameterDate

Filtering by MaxDate < ParameterDate