[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Filtering by MaxDate < ParameterDate

Posted on 2012-09-05
6
Medium Priority
?
550 Views
Last Modified: 2012-09-06
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.
0
Comment
Question by:Ryan
  • 3
  • 3
6 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38368413
>> 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
 
LVL 13

Author Comment

by:Ryan
ID: 38369790
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 38370146
>> 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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 13

Author Comment

by:Ryan
ID: 38372293
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38372557
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
 
LVL 13

Author Comment

by:Ryan
ID: 38372656
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

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question