Handy SQL Functions: Number List

Sometimes, in SQL group queries, you need totals over a series(e.g. Month numbers 1 to 12). What do you do if some parts of that range have no data but you still want the number in the series displayed?

For instance in our example you have sales data for Month 1,2,3,5 & 6 but there isn't anything for month number 4. You still want Month 4 displayed as a row, just with a null value.

The easiest way to do this is have a small table with numbers 1 to 12 that you can LEFT JOIN on to.

This function will output a table just like that.

CREATE FUNCTION dbo.fnNumberList
(
 @iStart int,
 @iEnd int,
 @iStep int = 1
)  
RETURNS @RtnValue table 
(
 ircNum int
) 

/*
 Returns a table with column name "ircNum" numbers from iStart to iEnd incrementing/decrementing by iStep
 Example:
 select * from dbo.NumberList(4,10,2);
 
 ircNum
 ------
 4
 6
 8
 10
*/

AS  
BEGIN 
 Declare @Cnt int
 Set @Cnt = @iStart

 if @iStep>0 
 While (@Cnt<=@iEnd)
 Begin
  Insert Into @RtnValue (ircNum) VALUES (@Cnt)
  Set @Cnt = @Cnt + @iStep
 End

 if @iStep<0 
 While (@Cnt>=@iEnd)
 Begin
  Insert Into @RtnValue (ircNum) VALUES (@Cnt)
  Set @Cnt = @Cnt + @iStep
 End
 
 Return
END

Handy SQL Functions: Reverse Week Number

I thought it was about time I posted some of the random little functions I've created in SQL that I use now and then.

So here's the first. It's very easy in SQL to get a week number (1..53) from a date - just use "datepart". However doing the reverse isn't. Say, for instance you wanted to know what the date was at the start of week 27 in 2012. How would you go about working that out? Here's my solution.

Just supply the function with the week number and year and out pops the date.

CREATE FUNCTION dbo.fnGetDateFromWeekNo
(@weekNo int , @yearNo  int)
RETURNS smalldatetime
AS
BEGIN 

DECLARE @tmpDate smalldatetime

IF @weekNo<1 set @weekNo=1

set @tmpdate= cast(cast (@yearNo as varchar) + '-01-01' as smalldatetime)
-- jump forward x-1 weeks to save counting through the whole year 
set @tmpdate=dateadd(wk,@weekno-1,@tmpdate)

-- make sure weekno is not out of range
if @WeekNo <= datepart(wk,cast(cast (@yearNo as varchar) + '-12-31' as smalldatetime))
BEGIN
 WHILE (datepart(wk,@tmpdate)<@WeekNo)
 BEGIN
  set @tmpdate=dateadd(dd,1,@tmpdate)
 END
END
ELSE
BEGIN
 -- invalid weeknumber given
 set @tmpdate=null
END


RETURN @tmpDate

END

Way too addictive - Circuit Coder

Ok, let's get one thing straight. I don't do games. I spend enough time at a computer that I'd rather go out in the fresh air after hours. Hacking zombies, casting spells and boosting cars just ain't my thing.
However I do like a puzzle; Something aching to be solved. I just can't stop picking at a problem until I've understood it and solved it.

Unfortunately for me, I stumbled across Circuit Coder. Ooooh it's addictive. I've only had it on the iPad for an evening and I've already chomped through a fair chunk of the levels.

The idea of the game (and bear with me on this) is to build a circuit to solve the challenge set.
Like most of these style of puzzles, you have blocks that perform different functions that you can connect together to achieve your goal. However, unlike most, these blocks aren't paint pots, cogs or springs, but electrical circuit diagrams. Dispensing with the usual visual analogies and going straight for logical diagrams makes for a refreshing change.

The interface is clean and easy to understand, making game-play easy even if some of the challenges are deceptively difficult.
It's definitely not for everyone. If I hadn't done some electronics at uni, I'm not sure if I would have understood some of the logic gate terminology straight away. But perhaps I'm being unfair here. It's got a concise help and beginner/tutorial levels that get you into the swing of things.

If you like a challenge and don't mind looking like a geek - give it a go!

MS Access Hebrew Dates Class (revisited)

This article has been updated and is now available here.

I've just finished the new version of the Hebrew Dates code.
All thanks to Moishy at Access World Forums for some really useful feedback on the original.
The new version can be downloaded for free via:

• 4Shared
• Access World Forums

Here's a description of the functionality

clsHebrewDate Class
  PROPERTIES
    .HDay
        Read/Write (long)
        stores the value of the current Hebrew day
        When set, the Gregorian Date will automatically be updated.
    .HMonth
        Read/Write (long)
        stores the value of the current Hebrew month
        When set, the Gregorian Date will automatically be updated.
    .HYear
        Read/Write (long)
        stores the value of the current Hebrew year
        When set, the Gregorian Date will automatically be updated.
    .HDaysInMonth
        Read Only (integer)
        returns the number of days in current Hebrew month.
    .GDate
        Read/Write (date)
        stores the value of the current Gregorian Date
        When set, the Hebrew Date will automatically be updated.
    .GAfterSunset
        Read/Write (boolean)
        used to mark the Gregorian date as occuring after sunset.
        NB: Gregorian days start at midnight but Hebrew days start
        after sunset
        TRUE = After sunset
        FALSE = Before sunset
        When set, the Hebrew Date will automatically be updated.
    .IsHLeapyear
        Read Only (boolean)
        Returns TRUE if the current Hebrew Year is a Hebrew leapyear
        NB: Hebrew leap years include a whole extra month (Adar II)
        and occur in a pattern that follows a 19 year cycle (see below)
    .IsValid
        Read Only (boolean)
        Returns TRUE if the current Hebrew Date is valid
  METHODS
    .Copy()
        Function returns clsHebrewDate type
        Creates and returns a new copy of the current instance
        of the clsHebrewDate class.
    .SetHebrewDate()
        Sets the Hebrew date and automatically updates the Gregorian
        date.
    .YahrzeitStep([,])
        Alters the current Hebrew Date moving it forward or backward
        x years according to the value of YearStep in accordance with
        the selected algorithm selected by CalcStyle.
        NB: You should always calculate additional Yahrzeit dates from
        the orginal date of the aneversary. DO NOT be tempted to calc
        a Yahrzeit from a previous Yahrzeit! This will lead to errors!
        YearStep: integer value can be positive or negative
        CalcStyle: (OPTIONAL)
                    1: Traditional (DEFAULT)
                    2: Reform
                    3: CatchAll

Module modHebrewDate
Function's
 GetHebrewMonthsList(bLeapYear As Boolean) As Variant
  returns a list of month numbers and their names delimited with semicolons
  this is particularly useful for generating combobox dropdown lists
  NB: Hebrew leap years have a extra month, hence the need for bLeapYear 
  to specify if the list should generate months for a leap year

 FormatHDate(myDate As clsHebrewDate, myFormat As Variant) As Variant
  Format hebrew date and output as a variant string.
  Variant string is needed so unicode letters can be used i.e. for Hebrew text
  the format parameters are based on the ones uses in PHP. See http://php.net/manual/en/function.date.php
  Format codes:
   Misc
    \ escape character
   Day
                d Date, 2 digits with leading zeros (00..30)
                f Textual representation of date as stored in the language table (HDate01..HDate30)
                j Date as number without leading zeros (1..30)
                l NB: this is a lowercase letter L
     Textual representation of the day of the week as stored in the language table (HDay1..HDay7)
                S English ordinal suffix for the day of the month, 2 characters (st, nd, rd, th)
                w returns 1..7 where 1=Sunday (Yom Rishon)
   Month
                F Textual representation of a month as stored in the language table
     (HMonth01..05, HMonth07..13, HMonth06Leap, HMonth07Leap)
                m Numeric representation of a month, with leading zeros (01..13)
                n Numeric representation of a month, without leading zeros (1..13)
                t Number of days in the given month (29..30)
   Year
                L Whether it's a leap year (1=yes 0=no)
                Y A full numeric representation of a year, all digits (eg 5758)
                y A two digit representation of a year (eg 58)
   Time
                a Textual representation of time of day in the Gregorian Date part as stored in Language Table (GTimeNight, GTimeDay)
Sub's
 SetHebrewDateFromGUI(ByRef MyHebrewDate As clsHebrewDate)
  Shows a hebrew date picker dialog box and returns any selected date back to the carable passed to MyHebrewDate
  NB: if you prefer the dialog box to display in Right to Left mode and have days that change according to the hebrew
  rather than the gregorian calendar (i.e. at sunset rather than midnight) edit the dlgHebDatePick13/14 form and set
  Const CDisplayRTL = True
  
  
  

Internationalisation in Access

Working with Joomla, I'm used to messing around with language files for the user interface.
Each language file supplies text that should be displayed (as part of the interface) to the user depending on their language.
So today, I decided it was high time I built something similar in Access. Below is the (surprisingly easy) result, using Kernel32 to identify the current language of the user...

First of all you'll need a table to store your text. Access text fields store text as unicode so you can even include text from right-to-left languages like Arabic or Hebrew.

The table will need 3 fields

1. swmVar to store the variable name
   Type: Text
   Size: 25
   Required: Yes
   Input Mask:  >Aaaaaaaaaaaaaaaaaaaaaaaaa
2. lwmLanguageID to store the ID of the Language the text is for
   Type: Long (Long Integer)
   Required: Yes
   Default Val: 0
3. swoData to store the text itself
    Type: Text
   Size: 255

I can't think of a reason why table would need a primary key but it does need to make sure that you don't store duplicate values of swmVar within the same Language ID so for ease I've set swmVar + lwmLanguageID as a composite primary key. However, you could create a separate primary key and just have these to fields as a composite unique index.

NB: I'm using my standard naming convention here where the first letter of the field represents the field type, the 2nd notes if the field is editable (w=write, r=read-only) and the 3rd shows if the field is optional (o) or mandatory (m).

Next you'll need the module
Module: ModLanguage

Option Compare Database
' Module to assist internationalisation of database interface
' written by Marc Ozin
' version: rc2012.02.02
' Website: http://marcozin.blogspot.com


' this module requires a lanaguage table like this:

' Table Name: tblLanguage
' Fields:
'   swmVar
'       Type:        Text
'       Size:        25
'       Required:    Yes
'       Input Mask:  >Aaaaaaaaaaaaaaaaaaaaaaaaa
'   lwmLanguageID
'       Type:        Long Integer
'       Required:    Yes
'       Default Val: 0
'   swoData
'       Type:        Text
'       Size:        255
'
' Primary Key: Composite of swmVar & lwmLanguageID
'
' this table will store language varables & text for each lanaguage code
' plus language varables & text to default to if language code not found
' signifed using lwmLanguageID of zero (0)

' Notes:
' Table of Locale ID's can be found on these pages:
' http://msdn.microsoft.com/en-us/goglobal/bb895996
' http://msdn.microsoft.com/en-us/goglobal/bb964664

' Examples:
' to set the caption for the button called ButtonCancel in a form when it opens
' edit the OnLoad event for the form and insert the following line:
'   ButtonCancel.Caption = GetLangTxt("CANCEL")
' Then in the language table create a row with these values:
'   swmVar:         CANCEL
'   lwmLanguageID:  0
'   swoData:        &Cancel
' "&Cancel" will be the default text when a particular user's language cannot be found
' to have different text for German, add the following row:
'   swmVar:         CANCEL
'   lwmLanguageID:  1031
'   swoData:        &Kündigen




'Get the language ID for the current user's locale from kernel32
'returns Long number representing the Language ID
Public Declare Function GetUserDefaultLCID% Lib "kernel32" ()


Public Function GetLangTxt(sLangVar As String, Optional ByVal lLanguageID As Long = -1) As String
' returns language text from specified variable stored in the Language Table
' function will try and return text for specified varable for current language (or language specified by lLanguageID)
' if no text found, then it will try and return text for varable with a language id of zero
' if still no text found, then it will return the name of the varable incased in square brackets

Const cLanguageTable = "tblLanguage"
Const cLanguageDataField = "[swoData]"
Const cLanguageVarableNameField = "[swmVar]"
Const cLanguageIDField = "[lwmLanguageID]"

Dim vTemp As Variant
    
    If lLanguageID = -1 Then
        lLanguageID = GetUserDefaultLCID()
    End If

    'try for a specific language id
    vTemp = DLookup(cLanguageDataField, cLanguageTable _
    , cLanguageVarableNameField & " like '" & UCase(Trim(sLangVar)) & "' and " & cLanguageIDField & "=" & CStr(lLanguageID))
    
    ' if nothing found
    If IsNull(vTemp) Then
        'try for language zero
        vTemp = DLookup(cLanguageDataField, cLanguageTable _
        , cLanguageVarableNameField & " like '" & UCase(Trim(sLangVar)) & "' and " & cLanguageIDField & "=0")
    End If
    GetLangTxt = Nz(vTemp, "[" & sLangVar & "]")
End Function

Here's an example of how to set the caption for the button called ButtonCancel in a form when it opens.

1. edit the OnLoad event for the form and insert the following line:

   ButtonCancel.Caption = GetLangTxt("CANCEL")

2. Then to set "&Cancel" as the default text when a particular user's language cannot be found, in the language table create a row with these values:
   
   • swmVar: CANCEL
   • lwmLanguageID: 0
   • swoData: &Cancel
3. to have different text for German, add the following row:
   
   • swmVar: CANCEL
   • lwmLanguageID: 1031
   • swoData: &Kündigen

Google Groups Invites not being received

At work we've been getting a lot of people reporting that they've been invited to a private google group but haven't received the invite. The Group moderator says they sent the invite but the recipient never received it.

It took a while but I think I have an answer. At least, I have a fix if you are using Trend Micro IMSS to check your mail before it is delivered to your internal mail servers.

It might be to do with the length of the email address that google's server uses to send the invite.
As soon as IMSS started to receive the invite email, it would drop it as invalid.

The invite email header's MAIL-FROM address was: "grbounce-v69lOAUEAEBnNWTkA5m_texcgVeUF-2Q=xxxxxxx=xxxxxxxxx.org.uk@googlegroups.com"
(I've replaced my details with x's above)

According to the official RFC (#3696) the part of the address before the @ sign must be no longer than 64 characters long.
The Google Groups invite email was 66 characters long, so our mail server was rejecting them as invalid.

“In addition to restrictions on syntax, there is a length limit on
email addresses. That limit is a maximum of 64 characters (octets)
in the "local part" (before the "@") and a maximum of 255 characters
(octets) in the domain part (after the "@") for a total length of 320
characters. Systems that handle email should be prepared to process
addresses which are that long, even though they are rarely
encountered.”

Extract from page 6 of RFC3696: http://tools.ietf.org/html/rfc3696


To stop IMSS checking the length of the MAIL-FROM address we added the following line in tsmtpd.ini in the [SmtpServer] section:

CheckSenderLength=0

Hope this helps someone!