Click here to monitor SSC
A service from Red Gate

in
Home SQL .NET SysAdmin Opinion Blogs Forums Books
Sign in | Join | Help
Home Blogs Forums
Blogs Home > Lionel Clarke > Sql Puzzle 3

Lionel Clarke

Software Engineer - Red Gate Software

Sql Puzzle 3

Published Tuesday, November 22, 2005 3:50 PM

/*
This puzzle is simple. Given the table below you have to output the words of the famous Christmas carol using a single select statement. Each line must be a separate row of the results set. I am also adding the restriction that you can not use a union statement. This is to stop cheating such as having a select statement for each line. The output should be as follows (A space between each verse would be good).

EDIT Sorry missed the table creation code of the first post DOH!!.

On the first day of Christmas,
my true love sent to me
A partridge in a pear tree.

On the second day of Christmas,
my true love sent to me
Two turtle doves,
And a partridge in a pear tree.

On the third day of Christmas,
my true love sent to me
Three French hens,
Two turtle doves,
And a partridge in a pear tree.

On the fourth day of Christmas,
my true love sent to me
Four calling birds,
Three French hens,
Two turtle doves,
And a partridge in a pear tree.

On the fifth day of Christmas,
my true love sent to me
Five golden rings,
Four calling birds,
Three French hens,
Two turtle doves,
And a partridge in a pear tree.

On the sixth day of Christmas,
my true love sent to me
Six geese a-laying,
Five golden rings,
Four calling birds,
Three French hens,
Two turtle doves,
And a partridge in a pear tree.

On the seventh day of Christmas,
my true love sent to me
Seven swans a-swimming,
Six geese a-laying,
Five golden rings,
Four calling birds,
Three French hens,
Two turtle doves,
And a partridge in a pear tree.

On the eighth day of Christmas,
my true love sent to me
Eight maids a-milking,
Seven swans a-swimming,
Six geese a-laying,
Five golden rings,
Four calling birds,
Three French hens,
Two turtle doves,
And a partridge in a pear tree.

On the ninth day of Christmas,
my true love sent to me
Nine ladies dancing,
Eight maids a-milking,
Seven swans a-swimming,
Six geese a-laying,
Five golden rings,
Four calling birds,
Three French hens,
Two turtle doves,
And a partridge in a pear tree.

On the tenth day of Christmas,
my true love sent to me
Ten lords a-leaping,
Nine ladies dancing,
Eight maids a-milking,
Seven swans a-swimming,
Six geese a-laying,
Five golden rings,
Four calling birds,
Three French hens,
Two turtle doves,
And a partridge in a pear tree.

On the eleventh day of Christmas,
my true love sent to me
Eleven pipers piping,
Ten lords a-leaping,
Nine ladies dancing,
Eight maids a-milking,
Seven swans a-swimming,
Six geese a-laying,
Five golden rings,
Four calling birds,
Three French hens,
Two turtle doves,
And a partridge in a pear tree.

On the twelfth day of Christmas,
my true love sent to me
Twelve drummers drumming,
Eleven pipers piping,
Ten lords a-leaping,
Nine ladies dancing,
Eight maids a-milking,
Seven swans a-swimming,
Six geese a-laying,
Five golden rings,
Four calling birds,
Three French hens,
Two turtle doves,
And a partridge in a pear tree!

(114 row(s) affected)

*/

SET NOCOUNT ON

DECLARE @Presents TABLE
(
    InitialDayRecieved VARCHAR(50),
    Present VARCHAR(50)
)

INSERT INTO @Presents(InitialDayRecieved, Present)
    SELECT 'first', 'A partridge in a pear tree'
INSERT INTO @Presents(InitialDayRecieved, Present)
    SELECT 'second', 'Two turtle doves'
INSERT INTO @Presents(InitialDayRecieved, Present)
    SELECT 'third', 'Three French hens'
INSERT INTO @Presents(InitialDayRecieved, Present)
    SELECT 'fourth', 'Four calling birds'
INSERT INTO @Presents(InitialDayRecieved, Present)
    SELECT 'fifth', 'Five golden rings'
INSERT INTO @Presents(InitialDayRecieved, Present)
    SELECT 'sixth', 'Six geese a-laying'
INSERT INTO @Presents(InitialDayRecieved, Present)
    SELECT 'seventh', 'Seven swans a-swimming'
INSERT INTO @Presents(InitialDayRecieved, Present)
    SELECT 'eighth', 'Eight maids a-milking'
INSERT INTO @Presents(InitialDayRecieved, Present)
    SELECT 'ninth', 'Nine ladies dancing'
INSERT INTO @Presents(InitialDayRecieved, Present)
    SELECT 'tenth', 'Ten lords a-leaping'
INSERT INTO @Presents(InitialDayRecieved, Present)
    SELECT 'eleventh', 'Eleven pipers piping'
INSERT INTO @Presents(InitialDayRecieved, Present)
    SELECT 'twelfth', 'Twelve drummers drumming'

SET NOCOUNT OFF
by Lionel

Comments

 

Phil Factor said:

--I claim your splendid cash prize

Declare @present varchar(8000)
Declare @carol varchar(8000)

Select
@Present=present+case when @present is null then '' else ',
'+case when len(@present) < 30 then replace(@Present,'A partridge','and a partridge')else @present end end,
@carol=coalesce(@carol,'')+'On the '+InitialDayRecieved+' day of christmas
my true love sent to me
'+ @present+'.

'
from @presents

Select @carol
November 23, 2005 12:03 PM
 

Phil Factor said:

/*
Lionel says
>The Carol has to have to have each line as a
> separate line of a results set. So you can
> use that trick. That is what makes it harder.

> Lionel

No it doesn't. You can just create a work table with the layout of the carol and do a simple inner join with a correlated subquery.

*/

Declare @ii int
Declare @jj int
Declare @Join varchar(30)

DECLARE @work TABLE
(
verse int, --which verse are we on
present_ID int,--which present to say
line int, --the line within the verse
thejoin varchar(50)--the ordinal day of christmas
)
Select @ii=1
while @ii<=12
begin
Select @jj=@ii, @Join=case @ii
when 1 then 'first'
when 2 then 'second'
when 3 then 'third'
when 4 then 'fourth'
when 5 then 'fifth'
when 6 then 'sixth'
when 7 then 'seventh'
when 8 then 'eighth'
when 9 then 'ninth'
when 10 then 'tenth'
when 11 then 'eleventh'
when 12 then 'twelfth'
else 'eeek!' end
insert into @work (verse,present_ID ,line, thejoin)
select @ii,@ii,1,@join
insert into @work (verse,present_ID ,line, thejoin)
select @ii,@ii,2,@join
while @jj>0
begin
insert into @work (verse,present_ID ,line, thejoin)
select @ii,@jj,3,@join
Select @jj=@jj-1
end
insert into @work (verse,present_ID ,line, thejoin)
select @ii,@ii,4,@join
Select @ii=@ii+1
end

Select
case line --for the sake of simplicity, we treat the list of presents as line 3
when 1 then 'On the ' +InitialDayRecieved+' day of christmas'
when 2 then 'my true love sent to me'
when 3 then
replace(
(--get the right present
select min(p.present) from @work w inner join @Presents p
on theJoin=InitialDayRecieved
where w.verse = work.present_ID
),
'A partridge',
case when verse>1 then 'And a partridge' else 'A partridge' end
)
+ case when present_ID>1 then ',' else '.' end--and the punctuation
when 4 then ''end

from @work work
inner join @Presents
on theJoin=InitialDayRecieved
order by verse, line, present_ID desc

--Phil Factor



November 23, 2005 3:37 PM
 

Lionel said:

You should only use one select statement at the top level so thouse soultions arn't correct I am afraid. To clairy no DDL and no varables.

Lionel
November 23, 2005 5:46 PM
 

Phil Factor said:

--Lionel says
> You can’t create any extra table or temporary
> table but you can do a derived table within
> the select.

--OK then here is the third and completely
--different solution to the problem. If only
--you'd put in an identity field the whole
--problem would have been a lot easier
--I didn't bother with the derived table, I
--just added three extra rows to the original
--as you have a ban on unions


INSERT INTO @Presents(InitialDayRecieved, Present)
SELECT 'thirteenth', ''
INSERT INTO @Presents(InitialDayRecieved, Present)
SELECT 'fourteenth', ''
INSERT INTO @Presents(InitialDayRecieved, Present)
SELECT 'fifteenth', ''


Select

case f.[index]-g.[index]
when -3 then ''
when -2 then 'On the '+f.InitialDayRecieved+' day of christmas'
when -1 then 'My true love sent to me'
else case when f.[index]=1 then g.present else replace (g.present,'A partridge','and a partridge') end
+case when g.[index]=1 then '.' else ',' end
end

from
(
select
[index]=case InitialDayRecieved
when 'First' then 1
when 'second' then 2
when 'third' then 3
when 'fourth' then 4
when 'fifth' then 5
when 'sixth' then 6
when 'seventh' then 7
when 'eighth' then 8
when 'ninth' then 9
when 'tenth' then 10
when 'eleventh' then 11
when 'twelfth' then 12
when 'thirteenth' then 13
when 'fourteenth' then 14
when 'fifteenth' then 15
else 0 end,
present,
InitialDayRecieved
from @presents
)f
inner join
(
select
[index]=case InitialDayRecieved
when 'First' then 1
when 'second' then 2
when 'third' then 3
when 'fourth' then 4
when 'fifth' then 5
when 'sixth' then 6
when 'seventh' then 7
when 'eighth' then 8
when 'ninth' then 9
when 'tenth' then 10
when 'eleventh' then 11
when 'twelfth' then 12
when 'thirteenth' then 13
when 'fourteenth' then 14
when 'fifteenth' then 15
else 0 end,
present,
InitialDayRecieved
from @presents
)g
on g.[index] between 1 and f.[index]+3
where f.[index] <13
order by f.[index],g.[index] desc
November 24, 2005 9:01 AM
 

James said:

A bit of a crazy answer which can probably be improved a lot.. I've used another table variable rather than use case statements everywhere:

SET NOCOUNT ON

DECLARE @TOrder TABLE
(
Position INT,
InitialDayRecieved VARCHAR(50)

)

INSERT INTO @TOrder(Position, InitialDayRecieved)
SELECT 1, 'first'
INSERT INTO @TOrder(Position, InitialDayRecieved)
SELECT 2, 'second'
INSERT INTO @TOrder(Position, InitialDayRecieved)
SELECT 3, 'third'
INSERT INTO @TOrder(Position, InitialDayRecieved)
SELECT 4, 'fourth'
INSERT INTO @TOrder(Position, InitialDayRecieved)
SELECT 5, 'fifth'
INSERT INTO @TOrder(Position, InitialDayRecieved)
SELECT 6, 'sixth'
INSERT INTO @TOrder(Position, InitialDayRecieved)
SELECT 7, 'seventh'
INSERT INTO @TOrder(Position, InitialDayRecieved)
SELECT 8, 'eighth'
INSERT INTO @TOrder(Position, InitialDayRecieved)
SELECT 9, 'ninth'
INSERT INTO @TOrder(Position, InitialDayRecieved)
SELECT 10, 'tenth'
INSERT INTO @TOrder(Position, InitialDayRecieved)
SELECT 11, 'eleventh'
INSERT INTO @TOrder(Position, InitialDayRecieved)
SELECT 12, 'twelfth'




SET NOCOUNT OFF

SELECT 'Text' = CASE
WHEN ordering.Position = 1 AND P2.Position != 1 THEN 'and ' + P0.Present
WHEN P3.Position = 4 THEN ''
WHEN P3.Position = 3 THEN 'On the ' + P2.InitialDayRecieved + ' day of christmas'
WHEN P3.Position = 2 THEN 'My true love sent to me'
WHEN P3.Position = 1 THEN P0.Present
ELSE ' ' END
FROM @Presents as P0
JOIN @TOrder as ordering
ON P0.InitialDayRecieved = ordering.InitialDayRecieved
CROSS JOIN
(SELECT TOP 4 PIn.Present, PIn.InitialDayRecieved, TIn.Position
FROM @Presents as PIn JOIN @TOrder as TIn ON PIn.InitialDayRecieved = TIn.InitialDayRecieved ) P3
CROSS JOIN
(SELECT PInner.Present, PInner.InitialDayRecieved, TInner.Position
FROM @Presents as PInner JOIN @TOrder as TInner ON PInner.InitialDayRecieved = TInner.InitialDayRecieved) P2
WHERE ((P0.InitialDayRecieved = P2.InitialDayRecieved OR P3.InitialDayRecieved = 'first') AND P3.Position <= (2 + ordering.Position) ) AND
(P2.Position) >= ordering.Position ORDER BY P2.Position ASC, ordering.Position DESC, P3.Position Desc
November 24, 2005 10:03 AM
 

Phil Factor said:

--OK. If it is really only one select statement
--then this will do the trick

--I still wish you'd popped in an identity field
--as most of the code is making up for that

Select

case [verse line]
when 1 then ''
when 2 then 'On the '+f.InitialDayRecieved+' day of christmas'
when 3 then 'My true love sent to me'
else case when f.[index]=1 then [what she gets] else replace ([what she gets],'A partridge','and a partridge') end
+case when [the order she gets them]=1 then '.' else ',' end
end

from
(
select
[index]=case InitialDayRecieved
when 'First' then 1
when 'second' then 2
when 'third' then 3
when 'fourth' then 4
when 'fifth' then 5
when 'sixth' then 6
when 'seventh' then 7
when 'eighth' then 8
when 'ninth' then 9
when 'tenth' then 10
when 'eleventh' then 11
when 'twelfth' then 12
when 'thirteenth' then 13
when 'fourteenth' then 14
when 'fifteenth' then 15
else 0 end,
InitialDayRecieved
from @presents
)f
cross join
(select top 4
[verse line]=case InitialDayRecieved
when 'First' then 1
when 'second' then 2
when 'third' then 3
when 'fourth' then 4
else 0 end
from @presents
where InitialDayRecieved in ('first','Second','Third','Fourth'))h
left outer join
(
select
[the order she gets them]=case InitialDayRecieved
when 'First' then 1
when 'second' then 2
when 'third' then 3
when 'fourth' then 4
when 'fifth' then 5
when 'sixth' then 6
when 'seventh' then 7
when 'eighth' then 8
when 'ninth' then 9
when 'tenth' then 10
when 'eleventh' then 11
when 'twelfth' then 12
when 'thirteenth' then 13
when 'fourteenth' then 14
when 'fifteenth' then 15
else 0 end,
[what she gets]=present
from @presents
)g
on [the order she gets them] between 1 and f.[index] and h.[verse line]=4


order by f.[index],[verse line],[the order she gets them] desc
November 24, 2005 11:16 AM
 

Philip said:

Another solution, slightly mad but it works. Plus mine only inserts a blank line 'between the verses', :p



select case
when show = 0 and left(ploc.linemembers,2) = 12 then p.present
when show = 0 and left(ploc.linemembers,2) <> 12 then replace(p.present, 'A partridge', 'And a partridge')
when show = 1 and p.initialdayrecieved = 'first' and left(ploc.linemembers,2) <> '01' then ''
when show = -1 and p.initialdayrecieved = 'first' then 'my true love sent to me'
when show = -2 and p.initialdayrecieved = 'first' then 'On the ' + ploc.initialdayrecieved + ' day of Christmas,'
end
from
@presents p
inner join (
select
case InitialDayRecieved
when 'first' then '12;first'
when 'second' then '11;first;second'
when 'third' then '10;first;second;third'
when 'fourth' then '09;first;second;third;fourth'
when 'fifth' then '08;first;second;third;fourth;fifth'
when 'sixth' then '07;first;second;third;fourth;fifth;sixth'
when 'seventh' then '06;first;second;third;fourth;fifth;sixth;seventh'
when 'eighth' then '05;first;second;third;fourth;fifth;sixth;seventh;eighth'
when 'ninth' then '04;first;second;third;fourth;fifth;sixth;seventh;eighth;ninth'
when 'tenth' then '03;first;second;third;fourth;fifth;sixth;seventh;eighth;ninth;tenth'
when 'eleventh' then '02;first;second;third;fourth;fifth;sixth;seventh;eighth;ninth;tenth;eleventh'
when 'twelfth' then '01;first;second;third;fourth;fifth;sixth;seventh;eighth;ninth;tenth;eleventh;twelfth'
end linemembers,
initialdayrecieved
from
@presents
) ploc on ploc.linemembers like '%' + p.initialdayrecieved + '%'
cross join
(
select
case initialdayrecieved
when 'first' then 0
when 'second' then 1
when 'third' then -1
when 'fourth' then -2
end show,
initialdayrecieved
from
@presents where initialdayrecieved in ('first', 'second', 'third', 'fourth')
) blanks
where
1 = case
when show = -2 and p.initialdayrecieved = 'first' then 1
when show = -1 and p.initialdayrecieved = 'first' then 1
when show = 0 then 1
when show = 1 and p.initialdayrecieved = 'first' and left(ploc.linemembers,2) <> '01' then 1
end
order by left(ploc.linemembers,2) desc,
show,
case p.InitialDayRecieved
when 'first' then '12;first'
when 'second' then '11;first;second'
when 'third' then '10;first;second;third'
when 'fourth' then '09;first;second;third;fourth'
when 'fifth' then '08;first;second;third;fourth;fifth'
when 'sixth' then '07;first;second;third;fourth;fifth;sixth'
when 'seventh' then '06;first;second;third;fourth;fifth;sixth;seventh'
when 'eighth' then '05;first;second;third;fourth;fifth;sixth;seventh;eighth'
when 'ninth' then '04;first;second;third;fourth;fifth;sixth;seventh;eighth;ninth'
when 'tenth' then '03;first;second;third;fourth;fifth;sixth;seventh;eighth;ninth;tenth'
when 'eleventh' then '02;first;second;third;fourth;fifth;sixth;seventh;eighth;ninth;tenth;eleventh'
when 'twelfth' then '01;first;second;third;fourth;fifth;sixth;seventh;eighth;ninth;tenth;eleventh;twelfth'
end
December 2, 2005 3:45 PM
 

Greg said:

Try this:
select case days.InitialDayRecieved
when gifts.InitialDayRecieved
then case days.InitialDayRecieved
when 'first'
then 'On the '
else char(13) + 'On the ' end +
days.InitialDayRecieved + ' day of Christmas,' + char(13) +
'my true love sent to me' + char(13) + gifts.present +
case days.InitialDayRecieved
when 'first' then '.'
else ',' end
else case gifts.InitialDayRecieved
when 'first' then REPLACE(gifts.present, 'A part',
case days.InitialDayRecieved
when 'first' then 'A part'
else 'And a part' end) +
case days.InitialDayRecieved
when 'twelfth' then '!'
else '.' end
else gifts.present + ','
end
end
from Presents days
LEFT OUTER JOIN Presents gifts
ON case days.InitialDayRecieved
when 'first' then 1
when 'second' then 2
when 'third' then 3
when 'fourth' then 4
when 'fifth' then 5
when 'sixth' then 6
when 'seventh' then 7
when 'eighth' then 8
when 'ninth' then 9
when 'tenth' then 10
when 'eleventh' then 11
when 'twelfth' then 12
else 99 end
>= case gifts.InitialDayRecieved
when 'first' then 1
when 'second' then 2
when 'third' then 3
when 'fourth' then 4
when 'fifth' then 5
when 'sixth' then 6
when 'seventh' then 7
when 'eighth' then 8
when 'ninth' then 9
when 'tenth' then 10
when 'eleventh' then 11
when 'twelfth' then 12
else 99 end
ORDER BY
case days.InitialDayRecieved
when 'first' then 1
when 'second' then 2
when 'third' then 3
when 'fourth' then 4
when 'fifth' then 5
when 'sixth' then 6
when 'seventh' then 7
when 'eighth' then 8
when 'ninth' then 9
when 'tenth' then 10
when 'eleventh' then 11
when 'twelfth' then 12
else 99 end ASC,
case gifts.InitialDayRecieved
when 'first' then 1
when 'second' then 2
when 'third' then 3
when 'fourth' then 4
when 'fifth' then 5
when 'sixth' then 6
when 'seventh' then 7
when 'eighth' then 8
when 'ninth' then 9
when 'tenth' then 10
when 'eleventh' then 11
when 'twelfth' then 12
else 99 end DESC
December 5, 2005 5:17 AM
 

Lionel said:

Nice answers guys and good to see somebody put the last exclamation mark on. Now time to think up a new puzzle.

Lionel

My solution is :

SELECT (CASE WHEN Pos=1 THEN ''
WHEN Pos=2 THEN 'On the ' + days.DayName + ' day of Christmas,'
WHEN Pos=3 THEN 'my true love sent to me'
WHEN Pos=4 AND presents.DayNo <> 1 THEN presents.Present + ','
WHEN Pos=4 AND days.DayNo = 1 THEN presents.Present + '.'
WHEN Pos=4 AND days.DayNo = 12 THEN 'And a' + RIGHT(presents.Present, LEN(presents.Present) - 1) + N'!'
ELSE 'And a' + RIGHT(presents.Present, LEN(presents.Present) - 1) + N'.'
END)
FROM (SELECT (CASE InitialDayRecieved
WHEN 'first' THEN 1
WHEN 'second' THEN 2
WHEN 'third' THEN 3
WHEN 'fourth' THEN 4
WHEN 'fifth' THEN 5
WHEN 'sixth' THEN 6
WHEN 'seventh' THEN 7
WHEN 'eighth' THEN 8
WHEN 'ninth' THEN 9
WHEN 'tenth' THEN 10
WHEN 'eleventh' THEN 11
WHEN 'twelfth' THEN 12
END) AS DayNo,
InitialDayRecieved AS DayName,
Present
FROM @Presents) days
CROSS JOIN (SELECT (CASE InitialDayRecieved
WHEN 'first' THEN 1
WHEN 'second' THEN 2
WHEN 'third' THEN 3
WHEN 'fourth' THEN 4
WHEN 'fifth' THEN 5
WHEN 'sixth' THEN 6
WHEN 'seventh' THEN 7
WHEN 'eighth' THEN 8
WHEN 'ninth' THEN 9
WHEN 'tenth' THEN 10
WHEN 'eleventh' THEN 11
WHEN 'twelfth' THEN 12
END) AS Pos
FROM @Presents
WHERE (CASE InitialDayRecieved
WHEN 'first' THEN 1
WHEN 'second' THEN 2
WHEN 'third' THEN 3
WHEN 'fourth' THEN 4
WHEN 'fifth' THEN 5
WHEN 'sixth' THEN 6
WHEN 'seventh' THEN 7
WHEN 'eighth' THEN 8
WHEN 'ninth' THEN 9
WHEN 'tenth' THEN 10
WHEN 'eleventh' THEN 11
WHEN 'twelfth' THEN 12
END) <= 4) Position
LEFT OUTER JOIN (SELECT (CASE InitialDayRecieved
WHEN 'first' THEN 1
WHEN 'second' THEN 2
WHEN 'third' THEN 3
WHEN 'fourth' THEN 4
WHEN 'fifth' THEN 5
WHEN 'sixth' THEN 6
WHEN 'seventh' THEN 7
WHEN 'eighth' THEN 8
WHEN 'ninth' THEN 9
WHEN 'tenth' THEN 10
WHEN 'eleventh' THEN 11
WHEN 'twelfth' THEN 12
END) AS DayNo,
InitialDayRecieved AS DayName,
Present
FROM @Presents) presents
ON presents.DayNo <= days.DayNo
AND Position.Pos = 4
WHERE Position.Pos > 1 OR days.DayNO > 1
ORDER BY days.DayNo, Position.Pos, presents.DayNo DESC
December 5, 2005 5:06 PM
 

Phil Factor said:

Come on Lionel, What about another puzzle.
December 9, 2005 3:22 PM
 

AlexM said:

--Here's my answer using CTE:
-- (don't forget to switch to "results to text")

;WITH Presents (DayNum, DayName, Present) AS
(SELECT ROW_NUMBER()
OVER (ORDER BY (CASE P.InitialDayRecieved
WHEN 'first' THEN 1
WHEN 'second' THEN 2
WHEN 'third' THEN 3
WHEN 'fourth' THEN 4
WHEN 'fifth' THEN 5
WHEN 'sixth' THEN 6
WHEN 'seventh' THEN 7
WHEN 'eighth' THEN 8
WHEN 'ninth' THEN 9
WHEN 'tenth' THEN 10
WHEN 'eleventh' THEN 11
WHEN 'twelfth' THEN 12
END)), InitialDayRecieved, Present
FROM @Presents P)
SELECT CASE WHEN P1.DayNum = P2.DayNum THEN 'On the ' + P1.DayName + ' day of Christmas,
my true love sent to me
' ELSE '' END
+ CASE WHEN P2.DayNum = 1 AND P1.DayNum <> 1 THEN 'And ' ELSE '' END
+ CASE WHEN P2.DayNum = 1 AND P1.DayNum > 1 THEN LOWER(P2.Present) ELSE P2.Present END
+ CASE WHEN P2.DayNum = 1 THEN
CASE WHEN P1.DayNum = (SELECT MAX(DayNum) FROM Presents) THEN '!' ELSE '.' END
ELSE ',' END
+ CASE WHEN P2.DayNum = 1 THEN '

' ELSE '' END
March 24, 2008 4:59 PM
 

AlexM said:

-- this comment should be concatenated to my previous comment :)
FROM Presents P1
INNER JOIN Presents P2 ON P1.DayNum >= P2.DayNum
ORDER BY P1.DayNum, P2.DayNum DESC
March 24, 2008 5:14 PM
 

Fun with SQL (games, painting, puzzles) « 1st blog, pre-beta version… said:

PingBack from http://rolldice.wordpress.com/2010/01/24/fun-with-sql-games-painting-puzzles/
January 24, 2010 2:03 AM
You need to sign in to comment on this blog
<November 2005>
SuMoTuWeThFrSa
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910
How to Kill a Company in One Step or Save it in Three
 The majority of companies that suffer a major data loss subsequently go out of business. David Wesley... Read more...

Migrating from OCS 2007 R2 to Lync: Part 4
 Having migrated the rest of our users and legacy resources across and started getting ready to... Read more...

Automated Script-generation with Powershell and SMO
 In the first of a series of articles on automating the process of building, modifying and copying SQL... Read more...

Seth Godin: Big in the IT Business
 Seth Godin has transformed our understanding of marketing in IT. He invented the concept of 'permission... Read more...

Using SQL Test Database Unit Testing with TeamCity Continuous Integration
 With database applications, the process of test and integration can be frustratingly slow because so... Read more...