Details for ShukiMolk

As far as I'm concerned, these word-plays are VARyCHARming and I consider them a pundamental part of your site.
I wouldn't have it any odder way.

Holy ****!
That was awesome!

It took me waaaaay more time than I care to admit, but I got it.

I had to exclude 2 and 3 because of the use of FLOOR(SQRT(@P)) though. 

There it is:

            -------------------------------------------------
            -- A little script to see how long it takes: 
            DECLARE @StartTime DATETIME
            DECLARE @EndTime DATETIME
            -- This is the start point. At the end we'll set the end point and calculate it
            SET @StartTime = CURRENT_TIMESTAMP
            -------------------------------------------------

/* Printing all prime numbers from 1 to 1,000 */

DECLARE @P INT                            --A variable to hold the number in check
DECLARE @counter INT                    -- A variable to hold the running numbers from 1 to @SQP
DECLARE @SQP INT                        -- the square root of @P (without the leftover)

SET @P = 1
SET @counter = 1

WHILE @P <= 1000                        --As long as @P <= 1000, do the following:
    BEGIN
    /* 1 */ SET @SQP = FLOOR(SQRT(@P))
    /* 2 */ IF @P = 1
                BEGIN
                    PRINT '1 is a special number. it''s neither Prime nor Composite Number'
                END
    /* 3 */ IF @P IN (2,3)
                BEGIN
                    PRINT @P
                END    
            ELSE           -- Now we're REALLY starting to check the number in @P
                BEGIN
                    WHILE @counter <= @SQP
                        BEGIN
                        /* 1 */ IF @P % @counter = 0
                                    BEGIN
                                    /*    PRINT 'Not Prime' */
                                        BREAK -- We can stop checking
                                    END
                                ELSE
                                    BEGIN
                                        IF @counter = @SQP --That means it's a Prime
                                            BEGIN
                                                PRINT @P
                                                BREAK -- We can stop checking
                                            END
                                    END
                        /* 2 */ IF @counter = @SQP
                                    BEGIN
                                        PRINT @P
                                        BREAK -- We can stop checking
                                    END
                                ELSE
                                    BEGIN
                                        SET @counter = @counter+1
                                    END
                        END
                END
    /* 4 */BEGIN
                SET @P = @P+1
                SET @counter = 2
            END 
    END
    ----------------------------------------
    -- Calculating how long it took:
    SET @EndTime = CURRENT_TIMESTAMP
    PRINT ''
    PRINT 'This process took '+ CAST(DateDiff(ms, @StartTime, @EndTime) AS VARCHAR) + ' milliseconds'
    ----------------------------------------

Couldn't do it without using HAVING in the subquery :-(
Anyone has any ideas?

DISTINCT cntr.CountryName
FROM
    tblEvent evnt
    INNER JOIN tblCountry cntr
    ON evnt.CountryID = cntr.CountryID
WHERE evnt.CountryID IN
--countryID for countries that has more than 8 events
        (
        SELECT
        evnt.CountryID
        --,count(evnt.EventID) AS [Number of events]
        FROM
            tblEvent evnt
            INNER JOIN tblCountry cntr
            ON evnt.CountryID = cntr.CountryID
        GROUP BY
        evnt.CountryID,
        cntr.CountryName
        Having
        count(evnt.EventID) > 8
        )

Here's my solution:

SELECT
    lv3.FamilyName,
    (ISNULL(lv1.FamilyName + ' -> ', '') +  ISNULL(lv2.FamilyName + ' -> ' , '') + lv3.FamilyName)
    AS 'Family Path'
FROM
    tblFamily lv3
    LEFT JOIN tblFamily lv2
    ON lv3.ParentFamilyId = lv2.FamilyID
        LEFT JOIN tblFamily lv1
        ON lv2.ParentFamilyId = lv1.FamilyID 
ORDER BY
lv1.familyid, lv2.familyid, lv3.familyid

But yeah, I also used LEFT JOIN instead of FULL OUTER JOIN

Actually, there is a small bug with previous answers.  When using this criterion:

(EventDetails) like '% water %

we get indeed events that have the word 'water' in them (with space before and after the word 'water'), but we leave out events that begins or ends with the word 'water'.  True, there are none in the database, but if we add them, we could see that they are left out.

Try adding these events:

INSERT INTO [tblEvent]
VALUES    ('Event that BEGINS with water', 'Water falling from the sky', GETDATE(), 1, 1),
        ('Event that ENDS with water', 'There is no more water', GETDATE(), 1, 1)

There should be 7 rows now, shouldn't there?

In order to "capture" also events that start or end with the word 'water' we should add these conditions:

OR    [EventDetails] LIKE 'water %'
OR    [EventDetails] LIKE '% water'

Hope this helps.

P.S. In order to remove these 2 events that we added, execute the following script:

DELETE FROM [tblEvent]
WHERE
[EventDetails] LIKE 'Water falling from the sky'
OR
[EventDetails] LIKE 'There is no more water'

Why do we need to use the CASE function?
What's wrong with this:

SELECT EventName, EventDate, ( (DATENAME(Weekday, EventDate)) + ' ' + (DATENAME(day, EventDate)) + 'th ' + (DATENAME(month, EventDate)) + ' ' + (DATENAME(year, EventDate)) ) AS 'Full Date'
FROM [tblEvent]
ORDER BY EventDate

I am getting different result.. 

NULL    459
18th Century    4
19th Century    14
20th Century    396
21st Century    45

for both rollup and cube.. is this right? Final total same as site.