Prev: Setting of @@FETCH_STATUS
Next: SQL Server Profiler Determine Number Times User Account Used
From: Michael Coles on 3 Dec 2009 00:53
Sorry, had Tiger on the brain :) Maybe Plamen wishes to apologize to his
fans? :)

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"TheSQLGuru" <kgboles(a)earthlink.net> wrote in message
news:lNidneQvEJBp14rWnZ2dnUVZ_jadnZ2d(a)earthlink.com...
> Would that be before or after his wife beat him up with a golf club?? :-))
>
>
> oooopsie! was that my outloud voice?!?! LOL
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
>
> "Michael Coles" <admin(a)geocodenet.com> wrote in message
> news:423E25C9-27BA-4068-877C-B65528D96122(a)microsoft.com...
>> LOL that's like asking Tiger Woods to play in the local weekend
>> tournament at the public course.
>>
>> --
>> Thanks
>>
>> Michael Coles
>> SQL Server MVP
>> Author, "Expert SQL Server 2008 Encryption"
>> (http://www.apress.com/book/view/1430224649)
>> ----------------
>>
>> "Justin Blanding" <JustinBlanding(a)discussions.microsoft.com> wrote in
>> message news:1CB44830-DF6E-45AC-A228-C02F56B3BB01(a)microsoft.com...
>>> Agreed, just thought his first post made it seem like it was easy as
>>> cake and
>>> should be spit out in about 10 seconds. Thought the challenge deserved
>>> more
>>> respect than that... He should compete, and hammer the competition.
>>>
>>> "TheSQLGuru" wrote:
>>>
>>>> Plamen does indeed know how to craft a fine query!!
>>>>
>>>> --
>>>> Kevin G. Boles
>>>> Indicium Resources, Inc.
>>>> SQL Server MVP
>>>> kgboles a earthlink dt net
>>>>
>>>>
>>>> "Brad Schulz" <BradSchulz(a)discussions.microsoft.com> wrote in message
>>>> news:548AB3BA-28AD-4263-9D3D-587B95711271(a)microsoft.com...
>>>> > Jeff and Justin...
>>>> >
>>>> > I have no doubt at all that Plamen could solve the challenge
>>>> > perfectly,
>>>> > with
>>>> > his eyes blindfolded and his hands tied behind his back. He has
>>>> > better
>>>> > T-SQL
>>>> > skills than anybody I know.
>>>> >
>>>> > Thank you to Plamen, though, for not taking the bait and posting more
>>>> > code
>>>> > in response. I appreciate that.
>>>> >
>>>> > Good luck to the 3 J's in the challenge (Jay, Jeff, Justin).
>>>> >
>>>> > --Brad
>>>> >
>>>> >
>>>>
>>>>
>>>> .
>>>>
>>
>
>

From: Michael Coles on 3 Dec 2009 00:54
Lebron James and Kobe Bryant taking on the J-V team at the local high school
:)

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"TheSQLGuru" <kgboles(a)earthlink.net> wrote in message
news:dN6dncITYP-R1orWnZ2dnUVZ_hmdnZ2d(a)earthlink.com...
> Those guys are just hacks ... :D
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
>
> "Abba" <sql_help(a)aaa.com> wrote in message
> news:e3hon68cKHA.4708(a)TK2MSFTNGP02.phx.gbl...
>>>>He has better T-SQL
>>>> skills than anybody I know.
>> Not into comparison, however...how about Itzik Ben-gan, Gert-Jan Strik?
>>
>> "Brad Schulz" <BradSchulz(a)discussions.microsoft.com> wrote in message
>> news:548AB3BA-28AD-4263-9D3D-587B95711271(a)microsoft.com...
>>> Jeff and Justin...
>>>
>>> I have no doubt at all that Plamen could solve the challenge perfectly,
>>> with
>>> his eyes blindfolded and his hands tied behind his back. He has better
>>> T-SQL
>>> skills than anybody I know.
>>>
>>> Thank you to Plamen, though, for not taking the bait and posting more
>>> code
>>> in response. I appreciate that.
>>>
>>> Good luck to the 3 J's in the challenge (Jay, Jeff, Justin).
>>>
>>> --Brad
>>>
>>>
>>
>>
>
>

From: Abba on 3 Dec 2009 01:09
Thanks. There is another reason why I asked this..(sorry for hijacking the
thread, but this topic is not that important so as to start a new thread)

Since Iam not that great in T-SQL, Iam always in awe about people who write
great t-sql code. In the intial phase, I was a fan of those who wrote code
that would make things work; later with experience, it was for those who
would make things work right. Now, with more experience, I have realised
that it should be those people who code that would make things work right
and fast. In SQL terms, it would mean about better reads/writes, better plan
etc etc.. Among the MSDN regulars, I have placed Itzik Ben-gan, Gert-Jan
Strik, Plamen,Dan Guzman, Tony Rogerson in that group.

I agree 100% that Plamen is excellent in T-SQL; I like Plamen's approach as
there is no boasting about the approach as the 'best' one; Plamen always
mentions that "here is one way of doing it". That not only shows the
humility but the inclination to learn.

"Brad Schulz" <BradSchulz(a)discussions.microsoft.com> wrote in message
news:302DC0EB-93EA-4C5B-AED4-64E2F43F5224(a)microsoft.com...
> Well, I "know" Plamen because I've interacted with him at the MSDN T-SQL
> Forum and other corresponence.
>
> Unfortunately I've never had the pleasure of "know"ing Itzik or Gert-Jan.
>
> However, I'm glad you mentioned it, because I do have to say that it was
> Itzik's books that inspired me the most in entering the SQL Server world.
> They are my Bibles (with apologies to Paul Nielsen).
>
> --Brad
>
>
> "Abba" wrote:
>
>> >>He has better T-SQL
>> >> skills than anybody I know.
>> Not into comparison, however...how about Itzik Ben-gan, Gert-Jan Strik?
>>
>


From: Dave Ballantyne on 4 Dec 2009 03:47
If you need to sharpen your skills, the same team are (or at least were)
doing a series of beginners challenges

http://beyondrelational.com/blogs/tcb/

Also ,for those that are interested, the next Phil Factor challenge is
live. This time the prize has been increased to $100.

http://ask.sqlservercentral.com/questions/2295/phil-factor-speed-phreak-challenge-3


Dave Ballantyne
http://sqlblogcasts.com/blogs/sqlandthelike/

Abba wrote:
> Thanks. There is another reason why I asked this..(sorry for hijacking the
> thread, but this topic is not that important so as to start a new thread)
>
> Since Iam not that great in T-SQL, Iam always in awe about people who write
> great t-sql code. In the intial phase, I was a fan of those who wrote code
> that would make things work; later with experience, it was for those who
> would make things work right. Now, with more experience, I have realised
> that it should be those people who code that would make things work right
> and fast. In SQL terms, it would mean about better reads/writes, better plan
> etc etc.. Among the MSDN regulars, I have placed Itzik Ben-gan, Gert-Jan
> Strik, Plamen,Dan Guzman, Tony Rogerson in that group.
>
> I agree 100% that Plamen is excellent in T-SQL; I like Plamen's approach as
> there is no boasting about the approach as the 'best' one; Plamen always
> mentions that "here is one way of doing it". That not only shows the
> humility but the inclination to learn.
>
> "Brad Schulz" <BradSchulz(a)discussions.microsoft.com> wrote in message
> news:302DC0EB-93EA-4C5B-AED4-64E2F43F5224(a)microsoft.com...
>> Well, I "know" Plamen because I've interacted with him at the MSDN T-SQL
>> Forum and other corresponence.
>>
>> Unfortunately I've never had the pleasure of "know"ing Itzik or Gert-Jan.
>>
>> However, I'm glad you mentioned it, because I do have to say that it was
>> Itzik's books that inspired me the most in entering the SQL Server world.
>> They are my Bibles (with apologies to Paul Nielsen).
>>
>> --Brad
>>
>>
>> "Abba" wrote:
>>
>>>>> He has better T-SQL
>>>>> skills than anybody I know.
>>> Not into comparison, however...how about Itzik Ben-gan, Gert-Jan Strik?
>>>
>
>
From: PaulGordonKiwi on 9 Dec 2009 16:15
I have no idea how to do the formatting od the text so I can not submit my
code for the challenge. I also have taken a completely different approach
which does not utilise cte's as I believe they do not perform well compared
to traditional ways of doing things. So I am posting what I have done so far
here for other people to look at and comment on.

Its a bit rough as I have just thrown it together and given it a quick test,
could be optimised a bit more.

select
'| ' +
isnull(cast([Sunday] as CHAR(2)),' ') + ' ' +
isnull(cast([Monday] as CHAR(2)),' ') + ' ' +
isnull(cast([Tuesday] as CHAR(2)),' ') + ' ' +
isnull(cast([Wednesday] as CHAR(2)),' ') + ' ' +
isnull(cast([Thursday] as CHAR(2)),' ') + ' ' +
isnull(cast([Friday] as CHAR(2)),' ') + ' ' +
isnull(cast([Saturday] as CHAR(2)),' ') +
' |'
from
(
select
fulldate,
((DATEPART(WEEKDAY, cast(Yr as varchar) + right('0' + cast(Mth as
varchar),2) + '01') - 1) + (value-1)) / 7 weekno,
DATENAME(WEEKDAY, cast(Yr as varchar) + right('0' + cast(Mth as
varchar),2) + right('0' + cast(value as varchar),2)) dayof,
value
from
(
select Yr,Mth, cast(Yr as varchar) + right('0' + cast(Mth as varchar),2)
+ '01' fulldate, day(DATEADD(dd,-1,cast(Yr as varchar) + right('0' +
cast(Mth+1 as varchar),2) + '01')) as numdays from @t
) tt
join
(
select
value
from
(
select Number as value from master..spt_values where Type='P' and
Number<=31 and number > 0
) maxdays
) alldays on alldays.Value <= tt.numdays
where
value <= numdays
) allrecs
PIVOT
(
SUM(value)
FOR dayof IN ([Sunday], [Monday], [Tuesday], [Wednesday], [Thursday],
[Friday], [Saturday])
) AS ProductSalesPivot
order by
fulldate,weekno

This query will produce this result when output to text.

-------------------------------
| 1 2 3 |
| 4 5 6 7 8 9 10 |
| 11 12 13 14 15 16 17 |
| 18 19 20 21 22 23 24 |
| 25 26 27 28 |
| 1 2 3 |
| 4 5 6 7 8 9 10 |
| 11 12 13 14 15 16 17 |
| 18 19 20 21 22 23 24 |
| 25 26 27 28 29 30 31 |
| 1 |
| 2 3 4 5 6 7 8 |
| 9 10 11 12 13 14 15 |
| 16 17 18 19 20 21 22 |
| 23 24 25 26 27 28 29 |
| 30 31 |



"Plamen Ratchev" wrote:

> And you can do very similar with pivoting... :)
>
> ;WITH
> N0 AS (SELECT 1 AS n UNION ALL SELECT 1),
> N1 AS (SELECT 1 AS n FROM N0 AS A, N0 AS B),
> N2 AS (SELECT 1 AS n FROM N1 AS A, N1 AS B),
> N3 AS (SELECT 1 AS n FROM N2 AS A, N2 AS B),
> Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY n) AS n FROM N3),
> Dates AS (
> SELECT CAST(CAST(yr * 10000 + mth * 100 + 1 AS CHAR(8)) AS DATETIME) AS start_date,
> CAST(CAST(yr * 10000 + (mth + 1) * 100 + 1 AS CHAR(8)) AS DATETIME) AS end_date
> FROM @t),
> Calendar AS (
> SELECT DATEPART(WEEKDAY, DATEADD(DAY, n - 1, start_date)) AS wk_day,
> DATEDIFF(DAY, '18991231', DATEADD(DAY, n - 1, start_date)) / 7 AS grp,
> DAY(DATEADD(DAY, n - 1, start_date)) AS dt_day
> FROM Nums
> JOIN Dates AS D
> ON DATEADD(DAY, n - 1, start_date) >= start_date
> AND DATEADD(DAY, n - 1, start_date) < end_date)
> SELECT RIGHT(' ' + COALESCE(LTRIM([1]), ' '), 2) + ' ' +
> RIGHT(' ' + COALESCE(LTRIM([2]), ' '), 2) + ' ' +
> RIGHT(' ' + COALESCE(LTRIM([3]), ' '), 2) + ' ' +
> RIGHT(' ' + COALESCE(LTRIM([4]), ' '), 2) + ' ' +
> RIGHT(' ' + COALESCE(LTRIM([5]), ' '), 2) + ' ' +
> RIGHT(' ' + COALESCE(LTRIM([6]), ' '), 2) + ' ' +
> RIGHT(' ' + COALESCE(LTRIM([7]), ' '), 2)
> FROM Calendar AS C
> PIVOT
> (MAX(dt_day) FOR wk_day IN ([1], [2], [3], [4], [5], [6], [7])) AS P;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
> .
>
First  |  Prev  | 
Pages: 1 2 3 4 5
Prev: Setting of @@FETCH_STATUS
Next: SQL Server Profiler Determine Number Times User Account Used