SQL Server - Select rows of a "max date" from a table (using ROW_NUMBER() OVER)

I often come across different requirements where I need to get the "top row" where certain criteria exists. It often applies to VAT Rates, Exchange Rates, even historical data where a different record may apply depending on the scenario. For this example, let's use VAT rates.

Let's create sample data using the following:

create table #myvattable (vatname nvarchar(50), effectivedate datetime)
insert #myvattable values('Rate A', '01/01/2009')
insert #myvattable values('Rate B', '01/01/2009')
insert #myvattable values('Rate C', '01/01/2009')
insert #myvattable values('Rate A', '01/02/2009')
insert #myvattable values('Rate B', '01/02/2009')
insert #myvattable values('Rate B', '01/02/2008')


So, we have several instances of the same "Rate" in the table that have different effective dates. Now, let's take this requirement:

I want to retrieve all VAT rates applicable for a certain date. How do we do this?


Step 1 - use a row_number() clause on your dataset:

select * from
(
select row_number() over (partition by a.vatname order by a.vatname, a.effectivedate desc) as rownum,
a.vatname, a.effectivedate
from #myvattable a
)
tbl



This simply puts a row_number clause on each row partitioning by the vatname. This returns a result set similar to this:

rownum vatname effectivedate
------ ------------- -----------------------
1 Rate A 2009-01-02 00:00:00.000
2 Rate A 2009-01-01 00:00:00.000
1 Rate B 2009-01-02 00:00:00.000
2 Rate B 2009-01-01 00:00:00.000
3 Rate B 2008-01-02 00:00:00.000
1 Rate C 2009-01-01 00:00:00.000


Ok, so what does this do for us? It effectively ranks the rows by each vatname so that we can see which ones are most applicable. Basically we want all the 1's.

Step 2 - Filter the rownums.
So our query becomes this:

select * from
(
select row_number() over (partition by a.vatname order by a.vatname, a.effectivedate desc) as rownum,
a.vatname, a.effectivedate
from #myvattable a
)
tbl
where rownum = 1


Ok, pretty much there now. The only step really that is left is... what if someone wants historical data? This query is fixed to the latest most applicable vatrate.


Step 3 - Make the date flexible

select * from
(
select row_number() over (partition by a.vatname order by a.vatname, a.effectivedate desc) as rownum,
a.vatname, a.effectivedate
from #myvattable a
where effectivedate <= @DATEVARIABLE )
tbl
where rownum = 1


So, @DATEVARIABLE could be a parameter/variable... or, even better, a field from another joined table.

How to calculate the width/height of a string in a particular font

- Follow up from the listbox width issue -

This applies to mainly asp.net, but the technique for winforms isn't drastically different. What we need to use is a Graphics object to measure a string width. Here's how:


// Bitmap is solely required for creating the graphics object
Bitmap TextBitmap = new Bitmap(1, 1);
Graphics g = Graphics.FromImage(TextBitmap);

// Create the font relative to the text (let's say Arial 8)
Font myFont = new Font("Arial", 8);
SizeF fontSize = g.MeasureString("This is a test string", myFont);



Done. The values you need are fontSize.Height and fontSize.Width.

Add horizontal scroll ability to asp.net listbox control

Firstly, an asp.net listbox control doesn't give you horizontal scrollbars (booo!). Depending on your requirements, there is a fairly easy way to achieve this without writing a custom control.

Step 1, wrap your listbox in a div:

<div id='hello' style="OVERFLOW: auto; width: 200px; height: 200px;">
<asp:ListBox Rows="6" Width="200px" ID="lstAvailable" runat="server" DataValueField="UserId" SelectionMode="Multiple" Height="200px">
</asp:ListBox>
</div>


Step 2. Because this doesn't completely solve the problem (The listbox is a fixed size so it will still disply scrollbars) on page load let's calculate the size we need the listbox to be. The we can fit all rows at the correct width. Use this function:


private void ResizeListboxes()
{
  // Bitmap is solely required for creating the graphics object
  Bitmap TextBitmap = new Bitmap(1, 1);
  Graphics g = Graphics.FromImage(TextBitmap);

  // Create the font size relative to the list box
  Font listFont = new System.Drawing.Font(lstAvailable.Font.Name, 8);
  SizeF fontSize = new SizeF(200, listFont.Height);

  // Loop through the list and find the widest string taking font type into consideration
  foreach (ListItem li in lstAvailable.Items)
  {
    SizeF currentFontSize = g.MeasureString(li.Text, listFont);
    if (currentFontSize.Width > fontSize.Width)
      fontSize = new SizeF(currentFontSize);
  }

  // Calculate the required height and width of the list box, min of 200 for each
  Int32 calculatedHeight = Convert.ToInt32(lstAvailable.Items.Count * fontSize.Height);
  Int32 calculatedWidth = Convert.ToInt32(fontSize.Width);
  lstAvailable.Width = (calculatedWidth < 200 ? 200 : calculatedWidth);
  lstAvailable.Height = (calculatedHeight < 200 ? 200 : calculatedHeight);

}

Call this on page load and you should be done.

Note: I hardcoded a fontsize of 8. I'm sure you could have gotten this from the listbox too.

Also, some people are not fond of this solution (including me!). I may post a custom control version too at a later stage.

How to create a System.Drawing.Graphics object in Asp.Net

Quick and easy way to do this is create a bitmap and use that:

Bitmap TextBitmap = new Bitmap(1, 1);
Graphics g = Graphics.FromImage(TextBitmap);

How to close a window using ASP.Net server side

After doing what needs to be done you can register some startup script to close your browser window like this:

string closeScript = "<script language="javascript">window.top.close();</script>";
if (!ClientScript.IsStartupScriptRegistered("clientScript"))
  ClientScript.RegisterStartupScript(Page.GetType(), "clientScript", closeScript);

Getting the dialog arguments passed into a web page modal dialog

This is especially useful if you use Microsoft CRM. Because it ties in very closely to how the entity forms and grid views communicate to custom web pages etc.

For example, in Microsoft CRM you can edit the ISVConfig to add a button to the toolbar for the entity grid. Set the WinMode=2 (2 basically means a modal dialog) to allow CRM to provide you with the selected entities. So how do we know what items were selected in the entity list? It passes us in a list of IDs as part of the window.dialogArguments.

So, how do we get access to this? Through javascript. Here's the steps:

1. Create the following javascript function (available from MSCRM help pages):

function loadSelectedListItems() {
  var sGUIDValues = "";
  var selectedValues;
  //Make sure window.dialogArguments is available.
  if (window.dialogArguments) {
    selectedValues = new Array(window.dialogArguments.length - 1);
  }
  else {
    return
  }
  selectedValues = window.dialogArguments;
  if (selectedValues != null) {
    for (i = 0; i < selectedValues.length; i++) {
      sGUIDValues += selectedValues[i] + ",";
    }
    document.getElementById('<%= winDialogArguments.ClientID %>').value = sGUIDValues;
  }
}


2. Use this function as part of the onload of the body:
<body onload="loadSelectedListItems()">

3. Create a new holder object (in my example an asp textbox) in a hidden division on your form:
<div style="visibility: hidden">
   <asp:textbox id="winDialogArguments" runat="server" text="">
</div>



Job done. Just access the Text of the asp textbox server side on the next post back (e.g. click of an OK button) to get the list of IDs.

Web Page Modal Dialog opens new window on postback

If you have a web page that's opening as a Modal Dialog (using window.showModalDialog or similar) and you are finding your button post backs are opening up a new browser window... here's the code that generally stops it happening. At the top of your head tag insert the following:

<base target="_self" />

In some instances this won't work. If not then pop this onload script in the body:

<body onLoad="window.name='myDialogWindow' ......>


and set the base tag to this instead:

<base target="myDialogWindow" />

Posting html code in Blogger

(Read my newer post here before using this method...)

Funny, on my very first post in Blogger I struggled getting the code to display. Want a real easy, non fancy way of getting it to display? Convert your html to html safe code. Here's a really nice site for this:

http://www.htmlconvert.net/

If you want all tags converted then ensure to select the "Convert Tags" or "Convert and Colourize Tags" option.

Edit: Have noticed the above link is not working anymore... will post an update when I find another nice site :)

How to close a window with an asp button (ASP.Net) using Javascript

A really handy way to do this:

<a href="#" onClick="javascript:window.close();">
   <asp:Button ID="btnCancel" Text="Cancel" runat="server" />
</a>