SQL Subquery / Function in Group By

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)

ManualResetEvent with BackgroundWorker Threads

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.


Joomla and Windows Live Writer

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


Favorite Software/Tools 2013

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.


Avoid “S to Skip” Message Ubuntu

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”).


Logitech diNovo Mini Status Lights

During the normal operation of the diNovo Edge keyboard, the battery light on the top of the keyboard will indicate the battery status of the keyboard:

  • The light will remain off during normal use, indicating more than 10% charge remaining.
  • A single red light indicates the battery level is critical and will require recharging.

During the charging operation of the diNovo Edge keyboard, the battery light will indicate the battery charge status of the keyboard:

  • As the battery recharges, the light will blink green.
  • When the battery is fully charged, the light will be solid green.

If the light is not on or it is blinking red on the charging cradle contact Logitech Customer Care for your region.


Outlook 2010 – Disable Images in RSS Feeds

When reading RSS feeds in Outlook 2010, I was always bothered when images were automatically downloaded. When reading feeds in my RSS reader, I prefer to focus on the content rather than an image. In particular, I’ve noticed that a number of RSS feeds are now including very large images in their RSS feeds which caused me to have to scroll a full page-length before I was able to read the content.

Outlook has an option to disable image downloads in RSS feeds, but it was a bit tucked away. Here’s how to enable the feature:


Dual Boot Windows XP and Windows 8

After installing Windows 8 on one of my test computers (a Panasonic ToughBook  CF-19), I noticed that I had lost my option to dual boot Windows XP and Windows 8. The new bootscreen for Windows 8 is really nice – it has the metro look, it’s touch friendly, and it’s mouse friendly – but it didn’t automatically add a boot option for my Windows XP installation which was pre-existing on the computer.

Here’s how you can dual boot Windows 8 with a Windows XP option.

  1. Boot into Windows 8
  2. Navigate to the traditional Windows Desktop
  3. Open a file explorer window
    I find the easiest way to do this is just to click the folder icon from the start menu
    1 - Taskbar - File Explorer
  4. Navigate to the “Computer” option
    The easiest way to do this is to click the Computer option in the left side of the file explorer
  5. Click the “System Properties” button in the ribbon at the top
    2 - File Explorer Properties
  6. Click “Advanced system settings” in the left pane
    3 - System Info
  7. In the Startup and Recovery section of the window that pops up, click the “Settings” button (on the Advanced tab of System Properties)
  8. Change the “Default operating system” option from “Windows Developer Preview” to “Earlier Version of Windows”
    4 - Properties5 - Startup and Recovery
  9. Click Ok to close the settings popup, then click Ok again to close the system properties menu
  10. Reboot and you should be presented with an option to boot into Windows XP (Earlier Version of Windows) or into Windows 8 (Windows Developer Preview).



XBMC Remote – Windows Phone 7

It’s here! I’ve finally published my first Windows Phone 7 App. If you’re a previous reader of my blog, you may have noticed that I’m avid XBMC supporter. I run XBMC on several machines in my house including a Linux based HTPC and on Apple TV devices throughout my house. My company recently started piloting Windows Phone 7 devices and I wasn’t happy with the XBMC remotes that were on the market (or their price!) so I decided to write my own.

The first version of XBMC Remote for Windows Phone 7 includes the core capabilities for browsing your library and controlling your XBMC box. If you have any suggestions, comments, or problems using the app, please contact me at [email protected]

Check out XBMC Remote on the Marketplace (the one by BoshDirect). The app includes a fully-functional 15 day trial and is the lowest priced XBMC remote on the market at $1.49

  • View and search movie library and select for playback
  • View and search albums and artists and select songs
  • View and select TV shows, seasons, and episodes
  • Remote control your XBMC
  • View movie, tv, and music artwork (fanart, posters, album covers)

*Sorry for the low-framerate video – I need to re-record it on a better computer.

Note that XBMC Remote is a libary browser and remote control. It does not stream videos or music to your phone.

V1.0 (“Taco”):

  • Requires XBMC Dharma (10.0).
  • Tested with Pre 11 (Eden) — subject to change
  • Movies, Albums, Artists, and TV shows are cached to device for fast viewing and searching
  • Initial movie, music, and TV viewing and remote control functionality
  • Panorama view for movies
  • TV Show Wide Banner view
  • Artwork cached to device for fast viewing (on second load)

Windows Phone 7 Development

My company recently started the process of switching to Exchange and as part of the process I am beta testing Windows Phone 7 devices for them. At the time of writing this article, I’m using the Samsung Focus. The started me off with the LG Quantum which was a pretty abysmal device. It was heavy and the screen quality just wasn’t up to par with even the old BlackBerry Bold 9000 I was using previously. I had heard a lot about the Windows Phone 7 platform through a number of tech websites, but I was really surprised at how great the platform really was once I got my hands on it.

The OS is extremely snappy and the phone is incredibly responsive. At first I thought it was just me, but everytime I showed the phone to my friends and colleagues they would also comment on how quick it was. After browsing through the list of available apps on the Windows Phone marketplace, I started thinking about what apps I wished were available… I realized that there’s already a really great start to some of the more mainstream apps (Netflix, Maps, Facebook, ESPN, Twitter, etc), but there’s definitely some opportunity for improvement.

I decided to look into what it would take to develop and app for Windows phone and realized that it looked pretty easy and the barriers to entry were fairly low. The Windows Phone developer tools are completely free and include everything you need to get up and running. I downloaded the tools  from the App Hub and got started with my first app (Visual Studio, etc). One thing to note is the developer tools allow to fully run and test your application in the Emulator that runs on the computer. If you want to run the application on your phone or deploy it to the App Market (to make money), you’ll have to pony up $100 for a App Hub license. A nice thing about the license is it enables you to unlock up to three devices so you can test on more than just your primary phone.