I got some comments about the Last_Day-funktion. Mainly "Why all this stringconverts" and so on. My only answer for this is "Why not?" and the main reason for the post was to describe how easy it is creating functions, working with different datatypes and manipulate strings. Well, I guess it also showed how easy it is to get something wrong. There are many ways of doing this and I have a bunch of them. I'm sorry for publish "bad" code... We all know that if we read it on the net it's true... *smiles

Anyway, to reach the last date in current month you can do this:

SELECT getdate(), DATEADD(day, 0 - DATEPART(dd, DATEADD(month, 1, getdate())), DATEADD(month, 1, getdate()))

It's the bold part that's interesting. Lets break it up!

DATEADD(month, 1, getdate()) is just one month ahead. Let's call it {today_add_one_month}

DATEPART(dd, DATEADD(month, 1,  {today_add_one_month})) gives us the actual daynumber of next month. Could be called {daynumber_next_month}

Using 0 - {daynumber_next_month} as an offset, {date_offset}

DATEADD(day, {date_offset}, {today_add_one_month})

So, there you have it. I guess this is the fastest way of doing it. But with functioncalls you'll never know.

I'd like to share some samples from the class "T618 Informationsbehandling med SQL". This is the design we discussed the last day.

For all you others, this is an example on how to do it based on the techDays-session. There are some minor adjustments in the script to be fixed, but it works  good enough. For example the fillfactor settings need to be calculated based on actual load and so on.

 Anyway, enjoy!

OrdersDB_PART1_V1.sql (9,20 kb)

To extract hierarcies based on parent-child relationships you can try with Common Table Expressions. This is a new feature from MS SQL Server 2005 and it can do some magic. And for retrieving hierarchies it's by far more effective than using a CURSOR.

WITH cte (LVL, EMPID, MGRID )
AS (
    SELECT
        1 AS LVL , EmployeeID AS EMPID, ManagerID AS MGRID
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT
        cte.LVL + 1, emp.EmployeeID, emp.ManagerID
    FROM HumanResources.Employee emp
    INNER JOIN cte ON emp.ManagerID = cte.EMPID
)
SELECT * FROM cte
ORDER BY LVL ASC

To explain how this works I'll simplify the CTE as a named and defined resultset. By naming it, it'll become addressable and this makes the magic. Now you can address previously retrieved rows. If we break down the query in the main parts it'll be easier to explain.

First the declarative part, WITH cte(LVL, EMPID, MGRID). This names the resultset to cte with the columns. Secondly we have the AS(...) that is the logic and will do the retrieval of rows. Third and finally the extraction of the data, SELECT * FROM cte ORDER BY LVL. This is the easy part.

If we look at the AS(...)-part. It consists of two SELECT-statements combined with a UNION ALL. The first SELECT-statement retrieves the top level of the hierarchy by retrieving the members that do not have a ManagerID, the 1 AS LVL symbolizes the top level. The second SELECT-statement joins the table against the resultset of the CTE based on cte.EMPID = emp.ManagerID, and in the same time we add 1 to cte.LVL to symbolize the level of the current member. The UNION ALL adds the rows retrieved in the second SELECT-statement with the resultset of the CTE. The second SELECT-statement is then repeated until there are no further rows to retrieve or if the MAXRECURSION-level is reached, by default 100. 
 
This second CTE retrieves the full hierarchy down to the level where a certain member resides.

WITH cte (LVL, EMPID, MGRID )
AS (
    SELECT
        1 AS LVL , EmployeeID AS EMPID, ManagerID AS MGRID
        FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT
        cte.LVL + 1, emp.EmployeeID, emp.ManagerID
    FROM HumanResources.Employee emp
    INNER JOIN cte ON emp.ManagerID = cte.EMPID
)
SELECT * FROM cte
WHERE LVL <= ( SELECT LVL FROM cte WHERE EMPID = 279 )
ORDER BY LVL ASC

The third example retrieves the direct chain of parents for a certain member. A higher LVL means in this example higher up in the hierarchy.

WITH cte (LVL, EMPID, MGRID )
AS (
    SELECT
        1 AS LVL, EmployeeID AS EMPID, ManagerID AS MGRID
    FROM HumanResources.Employee
    WHERE EmployeeID = 279
    UNION ALL
    SELECT
        cte.LVL + 1, emp.EmployeeID, emp.ManagerID
    FROM HumanResources.Employee emp
    INNER JOIN cte ON cte.MGRID = emp.EmployeeID
)
SELECT * FROM cte
ORDER BY LVL DESC