Blogs


 

I ran into an issue this week where I had written a table function (ITVF) to help convert a datetimeoffset column from UTC to the destination timezone within a query. Generally, we handle all date conversion at the application layer using simple .NET DateTime / TimeZone functions, but in this case we were trying to group by the adjusted date to display a count of events on a particular date. If the GROUP BY was run against the raw data, the resulting set would be grouped by the UTC timezone. As such, we needed to convert the timestamp to the destination timezone before performing the grouping.

When I first wrote the query, I attempted to simply group by the ITVF, but the following error message was thrown:

Msg 144, Level 15, State 1, Line 7
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

My first solution to this involved rewriting the core components of the ITVF statement directly into my query, but this made for an unwieldy and long query. Then last night I had an epiphany – why not just create the converted dataset as a view/subquery. I did a bit of quick research and to simplify my needs with integrating this timezone adjustment into an existing query, I decided on a Common Table Expression (CTE). The CTE allows us to define an expression that can be reused throughout the rest of the query similar to a table or view. The resulting solution is as follows:

DECLARE @timezone varchar(255)
SET @timezone = 'Central Standard Time'; /*.NET Timezone ID*/

WITH AH AS (
    SELECT ID, AttributeID, Value, Status, /* original columns, plus the converted _time */
      (select * from TimezoneOffset.dbo.fn_ConvertToTimezoneTBL(@timezone, Time)) as TimeAdj
    FROM [Alarms].[dbo].[History]
)

select CONVERT(Date, TimeAdj, 1) as DateAdj, count(Value)
from AH
group by CONVERT(Date, TimeAdj, 1)

In this post I wanted to briefly discuss using the ManualResetEvent with BackgroundWorker threads. I often find myself using BackgroundWorkers to perform processing of data without locking up the user interface for a user. Occasionally, I have UI tasks that would need to continue only after the background processing has completed. Instead of wiring up completion events, I often find it easier to simply use a ManualResetEvent to signal back when I can continue processing on the calling thread.

ManualResetEvent mre = new ManualResetEvent(false);
BackgroundWorker bw = new BackgroundWorker();
bw.DoWork += (obj, ea) =>
{
    //Do background work here
    for (int i = 0; i < (100 * 1000); i++)
    {
        Trace.WriteLine("On line: " + i.ToString());
    }

    //Escalate back to UI thread
    this.BeginInvoke((MethodInvoker)delegate
    {
        lbl_Status.Text = "Complete";
    });
    
    //Signal that we can continue
    mre.Set();
};
bw.RunWorkerAsync();

mre.WaitOne(); //wait until the thread completes and signals we can continue
return true;

In the C# .NET example above, a BackgroundWorker thread is kicked off asynchronously and then the calling thread waits to return true until the ManualResetEvent has been signaled (set). This is a very arbitrary example, but it shows how the MRE can easily work across threads enabling you to effectively ‘pause’ your code until the parallel event completes. Sound off in the comments with questions and feedback.

I recently upgraded my work laptop and as part of the process I have been reinstalling all of my favorite software utilities. I had my last laptop for a few years and some of these tools have become an integral part of my daily workflow without me even realizing it. I figured I would share a quick list of these tools as you might find them helpful as well.

  • Google Chrome - by far one of my most used applications on a day-to-day basis. One of my favorite features about Chrome is they have really simplified the customization experience. I simply downloaded Chrome and logged in with my Google account - shortly thereafter all of my favorite extensions, bookmarks, and more started downloading.
  • Google Drive - I have started relying heavily on Google Chrome for storing my most accessed documents. All of my school work and personal document get put into the Google Drive folder on my computer and are instantly accessible from my iPad, Galaxy S3, and more. It was really convenient to get back up and running on my new computer. After installing and setting up Google Drive, all of my files were synced back to my new computer and I was up and running again.
  • FastStone Capture - I take a lot of screenshots and FastStone makes it easy to get quick screenshots of just the content I am interested in. It also has a really handy editor built in which lets you add commentary, draw arrows, and blur things out among many other tools.
  • Actual Multiple Monitors - I use multiple monitors at work and it has always bothered me that Windows does not natively extend the taskbar to the second window. Actual Multiple Monitors (AMM) does a great job of recreating the Windows taskbar on your second screen. What is particularly helpful about this is your applications only show up in the taskbar of the monitor they are running on! No more looking through dozens of icons to find the right window.

Note: All of the above applications were installed on an Ultrabook running Windows 7 64-bit edition and Windows 7 32-bit edition.

Windows Live Writer makes for an excellent blog authoring tool and supports many modern blog platforms like Wordpress, Blogger, Windows Live Spaces and blogs that support RSD (Really Simple Discovery). While Joomla is not supported by Windows Live Writer out of the box, you can easily add support for Writer by installing the XMLRPC plugin by Joomler.

Writer is now part of the Windows Essentials pack and can be installed alongside Movie Maker, Skydrive and other Essentials tools. When setting up Writer to work with Joomla, follow these high level steps.

  • Install the XMLRPC plugin by Joomler via your Joomla Administrator panel
  • Under Extensions > Plugins, enable XMLRPC and System – RSD
  • Open Writer and follow the wizard.

Windows Live Writer Account Options

After following the steps in my XBMC Live Drives Mounted as Odd IDs article, wherein the user modifies the label of the drive and sets it to be recognized as part of the fstab process, I found myself occasionally seeing the message that indicates that Ubuntu can't find the drive. I found it frustrating that I would occasionally reboot my XBMC setup and the Seagate USB harddrives would have issues until I power cycled them.

With a bit of quick research, I found that there is an option as part of the fstab setup called nobootwait which lets the operating system continue to boot up even if the drive isn't present. In order to add this option, take the following steps.

Open the fstab file in your favorite editor:

sudo nano /etc/fstab

Modify the line for your hard drive to add the nobootwait parameter:

UUID=1234-5678           /media/SeagateHDD              ntfs-3g defaults,umask=002,gid=users,nobootwait               0 0

That's all it should take. Next time you reboot, the system should skip past the waiting screen ("S to Skip").

Subcategories

About Me

Josh Lyon Profile Picture

Name: Joshua Lyon
Birthday: August 8, 1985
Location: Valley Ranch (Irving), TX
E-mail: This email address is being protected from spambots. You need JavaScript enabled to view it.
ProfilesGoogle, Twitter