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

Change Trigger - Log all data changes in a table

I needed a MSSQL trigger to store alterations to data in a table called tblContacts. All changes (i.e. the before & after values) needed to be stored in a table that logs all changes from various tables.

tblAudit Structure

irpEditRecordID (int)
dwoEditDate (datetime)
swoUser (nvarchar)
cwoAction (char)
swoRecordID (nvarchar)
swoSourceTable (nvarchar)
swoSourceField (nvarchar)
swoBeforeValue (sql_variant)
swoAfterValue (sql_variant)

This is a bit quick and nasty, and probably a bit slow if you've got a table that really gets hammered but it suits my needs. The trigger below will spin through all available fields looking for changes and log them in tblAudit. The payoff is I don't need to remember to update the trigger if I add new fields!

tblContacts Update Trigger

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

-- Change the trigger name below to reflect your table name
CREATE TRIGGER trg_tblContacts_Update

-- Change the table name below to reflect the table you want to audit
ON dbo.tblContacts

FOR UPDATE

AS

DECLARE @sField    VARCHAR(255)
DECLARE @sTable    VARCHAR(255)
DECLARE @sPrimaryKey  VARCHAR(255)
DECLARE @sSQL    VARCHAR(3000)

-- SETTINGS --
-- Change the table name below to reflect the table you want to audit
SET @sTable = 'tblContacts'
-- Change the primary key below to reflect the primary key field for table you want to audit
SET @sPrimaryKey = 'irpContact'

-- INIT --
SET @sSQL = ''

SELECT  * INTO #temp_inserted FROM inserted;
SELECT  * INTO #temp_deleted FROM deleted;


DECLARE fields_cursor CURSOR FOR 
 SELECT column_name FROM information_schema.columns
  WHERE table_name = @sTable
 ORDER BY ordinal_position
OPEN fields_cursor

-- CHECK CHANGES TO DATA IN EACH FIELD --

FETCH NEXT FROM fields_cursor INTO @sField

WHILE @@FETCH_STATUS = 0
BEGIN

 -- Find and log changes in tblAudit
 SET @sSQL = ' INSERT INTO tblAudit  (dwoEditDate,swouser,cwoAction,swoRecordID,swoSourceTable,swoSourceField, swoBeforeValue,swoAfterValue) ( select GETUTCDATE() as calc_datestamp, system_user as calc_username, ''A'' as calc_status, #temp_inserted.' + @sPrimaryKey + ', ''' + @sPrimaryKey + ''' as calc_TableName, ''' + @sField + ''' as calc_fieldname,  #temp_deleted.' + @sField + ', #temp_inserted.' + @sField + ' from #temp_inserted , #temp_deleted  where (#temp_deleted.' + @sPrimaryKey + ' = #temp_inserted.' + @sPrimaryKey + ') AND (#temp_inserted.' + @sField + ' != #temp_deleted.' + @sField + ' ) ) '
 
--PRINT @sSQL
 EXEC(@sSQL)

 FETCH NEXT FROM fields_cursor INTO @sField
END

CLOSE fields_cursor
DEALLOCATE fields_cursor



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

MSSQL Calls through ODBC in MS Access

As MS Access no longer supports .adp projects any Access databases created have to link to MS-SQL back ends through ODBC.
However, sometimes you still need to call Stored Procedures, Views or create dynamic query statements to be passed directly to MS-SQL.

You can still achieve this by creating a passthru query in Access.

Below is a little bit of code I put together to allow you to run SQL statements using a passthru query called "SQLCall" or choosing one of your own.

To set things up, you'll need to create a passthru query in Access (see http://support.microsoft.com/kb/303968 for how to do this) called SQLCall and then drop this code into a module:

Public Sub SQLExec(ByVal sSQL As String _
 , Optional bRetRecords As Boolean = False _
 , Optional bExecute As Boolean = True _
 , Optional sQueryName As String = "SQLCall" _
 , Optional vConnect As Variant)
 
' vConnect   : Optional connection string
' bRetRecords   : Optional set return records on / off. Default: on
' sQueryName : Optional set alternate location for query storage

On Error GoTo ErrorHandler

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim sConnect As String

 Set db = CurrentDb
 Set qdf = db.QueryDefs(sQueryName)
 If IsMissing(vConnect) Then
  sConnect = qdf.Connect
 Else
  sConnect = CStr(vConnect)
 End If
 
 qdf.SQL = sSQL
 qdf.Connect = sConnect
 qdf.ReturnsRecords = bRetRecords
 If bExecute Then qdf.Execute

 GoTo ErrorExit
ErrorHandler:
 Select Case Err.Number
 Case Else
  MsgBox cErrorIntro & vbCrLf _
  & vbCrLf & Err.Number _
  & vbCrLf & Err.Description _
  & vbCrLf & Err.Source _
  , vbCritical + vbOKOnly, "Error"
 End Select
 Resume Next
ErrorExit:
End Sub

You can use this Sub to do thinks like:

Call the untag all contacts stored procedure

SQLExec "EXEC spUntagAllContacts;", False, True

Delete Contact #5

SQLExec "DELETE FROM tblContacts Where ContactID = 5;"

Call the SP to tag a contact based on ContactID

SQLExec "EXEC spTagContact " & Nz(ContactID, 0) & ";"

Do a select query and make the results accessible from the SQLContactList passthru

SQLExec "SELECT * from tblContacts WHERE ContactID < 100;", True, False, "SQLContactList"

Call the stored procedure to get the current users security groups and make the results accessible from the SQLSecurityGroupList passthru

SQLExec "EXEC spListMySecurityGroups '" & UserName & "';", True, False, "SQLSecurityGroupList"

Viewing a Nested Set Model tree as an Adjacency List

Recently, I needed to view a Nested Set Model tree as an Adjacency list so I could use it with VB's TreeCtrl.

Here's the MS-SQL view I came up with:

SELECT   T1.irpCategory, T1.iwmleft, T1.iwmright, T1.swmCategoryName, T1.ircDepth, T2.ircParentID
FROM     (SELECT   TOP 100 PERCENT node.irpCategory, node.iwmleft, node.iwmright, node.swmCategoryName, (COUNT(parent.swmCategoryName) - 1) 
                       AS ircDepth
            FROM     dbo.tblCategories AS node, dbo.tblCategories AS parent
            WHERE   node.iwmleft BETWEEN parent.iwmleft AND parent.iwmright
            GROUP BY node.irpCategory, node.swmCategoryName, node.iwmleft, node.iwmright
            ORDER BY node.iwmleft) T1 LEFT OUTER JOIN
             (SELECT   TOP 100 PERCENT irpCategory,
                            (SELECT   TOP 1 irpCategory
                             FROM     dbo.tblCategories t2
                             WHERE   t2.iwmleft < t1.iwmleft AND t2.iwmright > t1.iwmright
                             ORDER BY t2.iwmright - t1.iwmright ASC) AS ircParentID
              FROM     dbo.tblCategories t1
              ORDER BY iwmright - iwmleft DESC) T2 ON T1.irpCategory = T2.irpCategory

And the VB for the TreeCtrl object called tvCategories:

Private Sub Refresh_tvCategories()
On Error GoTo Err_Handler
Dim vNodeSet As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim bFirstItem As Boolean
Dim sFirstKey As String
    Set db = CurrentDb
    'SQLCategoriesList is a odbc passthru query to the view on the MSSQL server
    Set rs = db.OpenRecordset("SELECT * FROM SQLCategoriesList")
    bFirstItem = True
    
    
    If Not rs.EOF Then
        rs.MoveFirst
        While Not rs.EOF
                        

            If IsNull(rs.Fields("ircParentID")) Then
                Set vNodeSet = tvCategories.Nodes.Add()
                vNodeSet.Key = "K" & rs.Fields("irpCategory")
                vNodeSet.Expanded = False
                vNodeSet.Text = rs.Fields("swmCategoryName")
            Else
                'Tree Controls need text for their IDs so I'm prefixing the ID numbers with the letter K
                Set vNodeSet = tvCategories.Nodes.Add("K" & rs.Fields("ircParentID"), tvwChild)
                vNodeSet.Key = "K" & rs.Fields("irpCategory")
                vNodeSet.Expanded = False
                vNodeSet.Text = rs.Fields("swmCategoryName")
            End If
            If bFirstItem Then
                sFirstKey = vNodeSet.Key
                bFirstItem = False
            End If
            rs.MoveNext
        Wend
    End If
    
    'for some reason the form doesnt work properly unless an item has been selected at some point
    tvCategories.Nodes(sFirstKey).Selected = True
    'it can be unslected straight away
    
    
    
Exit_Err_Handler:
    Exit Sub
    
Err_Handler:
    Select Case Err.Number
       Case vbObjectError + 1
          Resume Next
       Case Else
          msgbox Err.Number & Err.Description
          Resume Exit_Err_Handler
    End Select

End Sub

MSSQL Calcluate age from Date of Birth

This excerpt will convert a date of birth into an age. It returns NULL when no DoB is present.

CASE WHEN T.dwoDOB IS NULL THEN NULL 
 ELSE 
 CASE WHEN DATEADD(YEAR, DATEDIFF (YEAR, T.dwoDOB, CURRENT_TIMESTAMP), T.dwoDOB) > CURRENT_TIMESTAMP 
 THEN DATEDIFF(YEAR, T.dwoDOB, CURRENT_TIMESTAMP) - 1 
 ELSE DATEDIFF(YEAR, T.dwoDOB, CURRENT_TIMESTAMP)
 END 
END

Example

SELECT 
T.swmFirstName, T.swmLastName,
ircAge = 
   CASE
      WHEN 
         T.dwoDOB IS NULL 
      THEN
         NULL 
      ELSE
         CASE 
            WHEN
               DATEADD(YEAR, DATEDIFF (YEAR, T.dwoDOB, CURRENT_TIMESTAMP), T.dwoDOB) > CURRENT_TIMESTAMP 
            THEN
               DATEDIFF(YEAR, T.dwoDOB, CURRENT_TIMESTAMP) - 1 
            ELSE 
               DATEDIFF(YEAR, T.dwoDOB, CURRENT_TIMESTAMP) 
         END
   END
FROM T as tblContacts