Our training courses

Other training resources

Our training venues

Why we are different

Details for ShukiMolk

ShukiMolk has participated in the following threads:

Added by ShukiMolk on 04 May 2020 at 17:43

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.

Added by ShukiMolk on 23 Apr 2020 at 02:04

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

Added by ShukiMolk on 12 Apr 2020 at 23:38

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

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

Added by ShukiMolk on 11 Apr 2020 at 22:07

Here's my solution:

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

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

Added by ShukiMolk on 11 Apr 2020 at 02:53

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]
[EventDetails] LIKE 'Water falling from the sky'
[EventDetails] LIKE 'There is no more water'

Added by ShukiMolk on 10 Apr 2020 at 02:12

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

Added by linjom on 23 Apr 2018 at 07:33

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.


Head office

Kingsmoor House

Railway Street


SK13 2AA


Landmark Offices

99 Bishopsgate




Holiday Inn

25 Aytoun Street


M1 3AE

© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.

End of small page here
Please be aware that our website uses cookies!
I'm OK with this Tell me more ...