HOWTO get business days between two dates using TSQL

Ok, so I looked around and found varying formulas for calculating the number of business days between two dates and all of them had shortcomings that made them unreliable so I went about making my own.  Now I do borrow code from http://www.kodyaz.com/articles/sql-server-dates-table-using-tsql-cte-calendar-table.aspx  to create my query.  Here is the result:

 DECLARE @TotalWorkingDays INT

 DECLARE @StartDate  DATETIME
 DECLARE @EndDate  DATETIME
 DECLARE @CurrentDate DATETIME
...

SET DATEFIRST 7;

WITH CTE_DatesTable
 AS
 (
   SELECT DATEPART (dw,@StartDate) as [dn], @StartDate AS [date]
   UNION ALL
   SELECT DATEPART (dw,DATEADD(dd, 1, [date])) as [dn], DATEADD(dd, 1, [date])
   FROM CTE_DatesTable
   WHERE DATEADD(dd, 1, [date]) <= @EndDate
 )
 SELECT @TotalWorkingDays =  COUNT([dn]) FROM CTE_DatesTable WHERE [dn] NOT IN (7,1)
 OPTION (MAXRECURSION 0);

 SELECT @TotalWorkingDays


What happens with this query is that it counts the days between the start and end dates excluding Saturday and Sunday (7 and 1 using DATEPART with the dw argument). 

Note: this query does NOT take in to consideration any holidays.

HOWTO get TiVO daily call and updates working under Solaris 11

I still use my Gen 1 TiVO because I bought it when you could still buy a lifetime subscription. The problem that I ran into when I switched to Magic Jack for phone service was that it was no longer possible to obtain updates because the dial up connection would timeout probably due to a slow data connection. So I setup an old laptop that had a bad HDD and a dead battery to run a live distro of FC which let me use the instructions found everywhere for connecting a gen 1 TiVO to your network using a serial cable (with a null modem) and Linux. Today I ran out of luck as that old laptop finally croaked and that had me wondering if I could have my Ultra 11 step up to the plate and work. Surprisingly, I was able to get everything going after a few hours of "why doesn't this work?" 

Step 1: Get a USB->Serial adapter. 

 I actually had the least amount of problems with this step. I went to Best Buy and, by some miracle, they had an open box/returned Dynex USB->Serial (DX-UDBB9). I came home wondering if I was going to have to go scouring the Internet for obscure drivers to work under Solaris, but, to my complete surprise, it worked as soon as I plugged it in without even needing to reboot. So I thought the next part would be the easiest, but it took some head scratching only because I wasn't familiar with enabling forwarding on the Solaris TCP/IP stack. 

Step 2: Enable setting up pppd on Solaris 

There are two parts to this: 

 a) Enabling forwarding of TCP/IP packets. 

 This part gave me headaches because under Linux it's: echo 1 > /proc/sys/net/ipv4/ip_forward 

Under Solaris, after much searching, I found the obscure equivalent: routeadm -e ipv4-routing -u 

 If you're running a firewall or firewall rules on your Solaris box then you may have to adjust some/all of your rule sets to accommodate this setting. I leave this to you because I don't run any firewall rules on my box since I'm behind a firewall appliance. 

 b) The actual pppd command. 

I haven't created an elaborate SMF service or anything else (yet) so I open a terminal and then execute the following (as root): pppd cua/0 115200 noauth proxyarp nocrtscts nobsdcomp nodeflate persist local lcp-max-configure 28800 asyncmap 0 passive netmask 255.255.255.0 192.168.a.x:192.168.a.y 

Where a is your local number (e.g. 0 for 192.168.0.x) 

x is an unused number on your network (e.g. 253) 

y is an unused number on your network (e.g. 254) 

cua/0 is the /dev on my Solaris box for the USB->Serial adapter. 

If you're in doubt you can launch the Device Driver Utility (located under Applications->System Tools) and look at the devfs path for your serial adapter then see what file under /dev is linked to it. 

Anyone looking for the modifications on the TiVO end of things can look here