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:
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'
----------------------------------------

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
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
)

Added by ShukiMolk on 11 Apr 2020 at 22:07

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

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]
WHERE
[EventDetails] LIKE 'Water falling from the sky'
OR
[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.

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE