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.
LVL 13
RyanProject Engineer, ElectricalAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
>> I think this has separated Max(EffectiveDate) from the OptCode <<

Oops, sorry, quite right:


SELECT ImageID
FROM (
      SELECT
            ImageID, EffectiveDate,
            ROW_NUMBER() OVER(PARTITION BY OptCode 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
0
 
Scott PletcherSenior DBACommented:
>> 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
0
 
RyanProject Engineer, ElectricalAuthor Commented:
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.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
RyanProject Engineer, ElectricalAuthor Commented:
Just needed to change the WHERE Row_num=1 to return the newest EffectiveDate.

Never have seen this method before, it's really slick.
0
 
Scott PletcherSenior DBACommented:
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.
0
 
RyanProject Engineer, ElectricalAuthor Commented:
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().
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.