Facebook Sign in | Join

			5 MINUTES to source control your database
Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

Aurelien and Matthieu have fun with TSQL Challenges

Any challenge can be source of fun and teasing between people. TSQL Challenges obviously do! Aurelien Verla had a lot of fun solving TSQL Challenge 6 along with his workmate Matthieu Hodin last week. Aurelien and Matthieu are workmates at Wygwam.

Aurelien narrated the story as follows:

"Sometimes, there is a real story behind the scene. For TSQL Challenge 6, the story involved my colleague Matthieu Hodin and myself as key actors !
Here’s a little resume of that story"

Day 1 : TSQL Challenges #6 is online and my first attempt was made with the “UNION” trick.
Aurelien: "Matthieu, look at my query, I solved the TSQL Challenges 6 !"
Matthieu: "HAHAHA ! What a noob, you didn’t used the GROUP BY WITH ROLLUP trick !"

Day 2 : Disappointed by Matthieu, I worked on a GROUP BY WITH ROLLUP version of my query.
Aurelien: "Matthieu, look at my query, I solved the TSQL Challenges 6 with the f@!!#? GROUP BY WITH ROLLUP trick"
Matthieu: "HAHAHA ! What a noob, the query uses DATENAME() to establish the MON/TUE…, this will never work on my FRENCH SQL SERVER INSTANCE !"
Aurelien To himself: "I HATE YOU MATTHIEU ! J"

"At this point, the race began with Matthieu, my goal was to bust him with the “perfect” query, less characters than him, more beautiful tricks and so on.
This is where the SUBSTRING() helped me to take a real advantage. Working a lot with Javascript, this trick come from my attempts of creating a nice localized client side Calendar."

When we received Matthieu Hodin’s solution, we were a bit confused. It was a very valuable solution but difficult to read.
At this time, we didnt know the story behind the scenes :-)

Here is Matthieu’s solution:

SELECT  
    a+ISNULL(s,'')+ISNULL(LEFT(MIN(L)+j,1),'T') PERIOD_ID  
    ,grouping(j)+grouping(s)LEVEL  
    ,COALESCE(  
        SUBSTRING('SATSUNMONTUEWEDTHUFRI',1+((MIN(L)+@@datefirst)%7)*3,3)  
        +' '+J ,'TOTAL WEEK '+s ,'TOTAL YEAR '+a  
        )PERIOD  
    ,SUM((P-P%72*2)/72*V)HOME  
    ,SUM((P-P%67*2)/67*V)CONTACT  
    ,SUM(P/80*V)PRODUCTS  
FROM(  
    SELECT  
        *  
        ,DATENAME(dd,d)+'/'+CAST(DATEPART(mm,d)AS CHAR)j  
        ,DATENAME(yy,d)A ,CAST(DATEPART(dw,D)AS VARCHAR)L   
        ,DATENAME(ww,d)s  
    FROM(  
        SELECT  
            VisitDate D,  
            ASCII(page)P  
            ,NbVisitors V  
        FROM @t  
    )t  
)t  
GROUP BY A,s,J  
WITH rollup  
HAVING grouping(a)=0  
ORDER BY PERIOD_ID

Aurelien’s solution and comments is here.

We see this story as really encouraging, and it should also be for all SQL Server developers in keeping looking for better ways of writing common TSQL Code.

Thanks to Aurelien and Matthieu !


Share

Comments

# re: Aurelien and Matthieu have fun with TSQL Challenges

Sunday, June 14, 2009 2:24 PM by Pinal Dave

Nice I love it!


Copyright © Rivera Informatic Private Ltd.