• Jeff Moden (1/16/2011)


    Sachin Nandanwar (1/16/2011)


    Well I just stumbled upon this article.I tried to do it using quirky update method and seems to be working but haven't tested it on a huge no of rows though.

    --=============================================================================

    -- Create the test data. This is NOT a part of the solution.

    -- This is virually instantaneous.

    --=============================================================================

    --===== Conditionally drop the test table to make reruns easier.

    IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL

    DROP TABLE #MyHead

    ;

    GO

    --===== Create the test table

    CREATE TABLE #MyHead

    (SomeDate DATETIME, id int DEFAULT(0))

    ;

    --===== Populate the test table with test data

    INSERT INTO #MyHead

    (SomeDate)

    SELECT '2010-01-01' UNION ALL --1st "Group" of dates (StartDate and EndDate)

    SELECT '2010-01-01' UNION ALL --Duplicate date

    SELECT '2010-01-03' UNION ALL --2nd "Group" of dates (StartDate and EndDate)

    SELECT '2010-01-05' UNION ALL --3rd "Group" of dates (StartDate)

    SELECT '2010-01-06' UNION ALL --3rd "Group" of dates (EndDate)

    SELECT '2010-01-10' UNION ALL --4th "Group" of dates (StartDate)

    SELECT '2010-01-10' UNION ALL --Duplicate date

    SELECT '2010-01-11' UNION ALL --4th "Group" of dates

    SELECT '2010-01-11' UNION ALL --Duplicate date

    SELECT '2010-01-11' UNION ALL --Duplicate date

    SELECT '2010-01-12' --4th "Group" of dates (EndDate)

    ;

    declare @ordse int=0

    declare @somedate datetime=''

    update #MyHead set @ordse=ID=case when somedate=@somedate+1 or @somedate=somedate then @ordse+1 else @ordse-1 end,@somedate=somedate

    select min(somedate)min,max(somedate)max,DATEDIFF(dd,min(SomeDate)-1,max(SomeDate))Diff from

    (

    select *,id-ROW_NUMBER()over(order by (select 1))id1 from #MyHead

    )t group by id1 order by min(SomeDate)

    drop table #MyHead

    I realize the intentions are good here and thank you for that but there are a couple of problems with the code there. For one, it breaks several of the rules for doing a Quirky Update. It's tough enough for me to defend the use of the Quirky Update as it is. If you're going to use it and post such solutions, please follow the rules for its use. Thanks.

    Second, although the Quirky Update does the job, isn't a panacea and there's simply no need no need for it here. It requires the use of an extra column and would necessarily require the copying of data from a permanent table to a Temp Table if the column couldn't be added to the permanent table.

    Last but not least, since you still do a SELECT with aggregates, I believe you'll find that the Quirky Update method is actually a bit slower than conventional methods, in this case.

    I can confirm the last statement, namely that QU performs slower than Jeff's method.

    Because I was so conceptually challenged to understand what Jeff had done at first, I tried to see if I could apply QU to this case. While I did get it to work (QU I understand, including the rules :-)), it was definitely slower.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St