SQLServerCentral apologizes and you can win a book

  • We use this in an update trigger to perform additional processing on affected child keys:

    insert into @IntermediateData

    select ROW_NUMBER () OVER (order by D.FOLDER_KEY),

    D.FOLDER_KEY, D.CHILD_KEY, D.CHILD_TYPE,

    I.CHILD_KEY, I.CHILD_TYPE

    from DELETED D

    join INSERTED I on D.CHILD_KEY = I.CHILD_KEY

    and D.CHILD_TYPE = I.CHILD_TYPE

    where D.CHILD_TYPE in (2,3)

  • Most SQL people I've come across are more than happy to share their knowledge and more often than not their code but be courteous and ask first and ALWAYS give credit where it's due.

    DECLARE @tmp_Strat TABLE (StateCode CHAR(2), SomeValue INT);

    INSERT INTO @tmp_Strat (StateCode, SomeValue) VALUES

    -- Site Your Source: http://en.wikipedia.org/wiki/List_of_U.S._states_and_territories_by_population

    ('CA', 38041430), ('TX', 26059203), ('NY', 19570261), ('FL', 19317568), ('IL', 12875255);

    SELECTStateCode

    , SomeValue

    , PopulationRank = RANK() OVER (ORDER BY SomeValue DESC)

    FROM@tmp_Strat;

    _____________________________________________________________________
    - Nate

    @nate_hughes
  • I think sqlservercentral did the right thing. The author of the article should have given due credit to Itzik Ben-Gan.

    -------------------------------

    /* Find True Duplicates from a table and delete them */

    ;WITH MyDuplicateCTE

    AS

    (

    SELECT *, ROW_NUMBER() OVER (PARTITION BY [Code],[Description]

    ORDER BY [Code],[Description]) Ps

    FROM [dbo].[Table1]

    )

    DELETE FROM MyDuplicateCTE WHERE Ps > 1;

    GO

    -------------------------------

  • These two lines in the SELECT statement allowed me to add calls per shift and calls per unit to each line of detail. Without them, I would have had to calculate the group totals in separate queries, then join back to the dataset with the detail:

    , count(*) over (partition by vw_CODS_DW.Business_Unit, vw_CODS_DW.Unit_Number, ActualStart) as 'CallsPerShift'

    , count(*) over (partition by vw_CODS_DW.Business_Unit, vw_CODS_DW.Unit_Number) as 'CallsPerUnit'

  • jim-1022345 (12/4/2013)


    Window Functions are a great addition to TSQL. I use them all the time now.

    WITH RandomData AS

    (

    SELECT 1 AS Number

    UNION ALL

    SELECT Number + 1

    FROM RandomData

    WHERE Number < 200

    )

    SELECT

    Number,

    ROW_NUMBER() OVER(PARTITION BY Number%10 ORDER BY Number) AS AlternateNTile

    FROM RandomData

    ORDER BY 1

    OPTION (MAXRECURSION 200)

    You might want to take a look at the following article for why you shouldn't use recursive CTEs that count. 😉

    http://www.sqlservercentral.com/articles/T-SQL/74118/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • smutallib (12/4/2013)


    -- The following script will generate time between given start time and end time

    -- Row_number() window function at the end is used to generate serial no.

    DECLARE @T_AVAILABLE_TIME TABLE(T_RUNTIME TIME,POST_DATE DATETIME)

    DECLARE@START_TIME TIME,

    @END_TIME TIME,

    @Inc INT;

    SET @Inc = 10; --Incrementing by 10 minutes

    SET @START_TIME='09:00:00'

    SET @END_TIME='11:00:00';

    WITH Vals AS (

    SELECT @START_TIME RunTime

    UNION ALL

    SELECT DATEADD(mi,@Inc,RunTime)

    FROM Vals

    WHERE DATEADD(mi,@Inc,RunTime) < @END_TIME

    )

    INSERT INTO @T_AVAILABLE_TIME(T_RUNTIME,POST_DATE)

    SELECT LEFT(RunTime,8) AS RUNTIME,GETDATE()

    FROm Vals

    OPTION (MAXRECURSION 0)

    SELECTROW_NUMBER() OVER(ORDER BY POST_DATE) AS SERIAL,

    LEFT(T_RUNTIME,8) AS FROM_TIME,

    LEFT(DATEADD(MI,10,T_RUNTIME),8) AS TO_TIME

    FROM @T_AVAILABLE_TIME A

    The code above is another example of a recursive CTE that counts. Please see the following aritcle for why you should avoid such things.

    http://www.sqlservercentral.com/articles/T-SQL/74118/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • This example uses T-SQL windowing to calculate the median price for a bundle of services in one particular service line. I use windowing functions all the time to calculate the median value of a collection.

    Thanks,

    Andre Ranieri

    USE TempDB

    GO

    IF OBJECT_ID('dbo.SalesData', 'U') IS NOT NULL

    DROP TABLE dbo.SalesData

    GO

    CREATE TABLE dbo.SalesData

    (

    Counter int NOT NULL IDENTITY (1,1),

    AccountNum int,

    ServiceLine varchar(20),

    SaleAmount decimal (19,4)

    CONSTRAINT PK_sample_table PRIMARY KEY (Counter)

    )

    -- Populate table with sample sales data for two customers and multiple service lines.

    INSERT INTO dbo.SalesData (AccountNum, ServiceLine, SaleAmount)

    Values(1000, 'Lawn Care', 39.95), (1000, 'Lawn Care', 39.95), (1000, 'Lawn Care', 39.95), (1000, 'Lawn Care', 39.95),(1000, 'Lawn Care', 39.95),(1000, 'Lawn Care', 42.95),(1000, 'Lawn Care', 42.95), (1000, 'Lawn Care', 42.95)

    , (2000, 'Tree Care', 45.95), (2000, 'Tree Care', 49.95), (2000, 'Tree Care', 49.95), (2000, 'Tree Care', 49.95),(2000, 'Tree Care', 49.95),(2000, 'Tree Care', 62.95),(2000, 'Tree Care', 55.95), (2000, 'Tree Care', 52.95)

    , (2000, 'Pest Control', 89.95), (2000, 'Pest Control', 89.95), (2000, 'Pest Control', 109.95);

    -- Find the median sales amount for each customer and service line

    WITH Median AS

    (

    SELECT AccountNum, ServiceLine, SaleAmount,

    ROW_NUMBER() OVER(PARTITION BY AccountNum, ServiceLine ORDER BY SaleAmount) AS RowNum,

    COUNT(*) OVER(PARTITION BY AccountNum, ServiceLine) AS Cnt

    FROM dbo.SalesData

    )

    SELECT AccountNum, ServiceLine, MAX(SaleAmount) AS MedianPrice

    FROM Median

    WHERE RowNum IN((Cnt + 1) / 2, (Cnt + 2) / 2)

    GROUP BY AccountNum, ServiceLine

  • 1) Bummer, I was just gonna use the stuff... Need a book!!

    2) SELECTBusinesID,

    AreaID,

    DATEPART(yy, GetDate()) AS [Year],

    SalesYTD,

    AVG(SalesYTD) OVER (PARTITION BY AreaID ORDER BY DATEPART(yy, GetDate())) AS MovingAvgYTD,

    SUM(SalesYTD) OVER (PARTITION BY AreaID ORDER BY DATEPART(yy, GetDate())) AS TotalYTD

    FROMMyTable

    WHEREAreaID < 0

  • Excellent reference here now in how the OVER can be used. This was a great idea!

    M.

    Not all gray hairs are Dinosaurs!

  • I hope more books gets plagiarized so we can get to win more books!

    Kidding.

    Here's my take on a query with an OVER statement:

    DROP TABLE #HEY

    CREATE TABLE #HEY (Region CHAR(10),Age INT, CustomerName VARCHAR(50))

    INSERT INTO #HEY

    VALUES ('A',29,'Morgan'),('A',15,'Marley'),('A',21,'Ashley'),('B',29,'Roger'),('B',20,'Claire')

    -- Create a rank by Region of all customers by Age in descending order

    SELECT ROW_NUMBER() OVER (PARTITION BY Region

    ORDER BY Age DESC) AS Rank

    ,*

    FROM #HEY

  • Deliver('Book') = Good * 4

    And now for Matrix Multiplication with bonus Window Functions. 😀

    if object_id('tempdb..#a') is not null begin drop table tempdb..#a end ;

    create table #a (

    row_num int,

    col_num int,

    value int,

    primary key ( row_num, col_num )

    );

    insert into #a ([row_num], [col_num], [value])

    select 0, 3, 55 union all select 0, 4, 78 union all

    select 1, 0, 19 union all select 1, 2, 21 union all

    select 1, 3, 3 union all select 1, 4, 81 union all

    select 2, 1, 48 union all select 2, 2, 50 union all

    select 2, 3, 1 union all select 3, 2, 33;

    if object_id('tempdb..#b') is not null begin drop table tempdb..#b end ;

    create table #b (

    row_num int,

    col_num int,

    value int,

    primary key ( row_num, col_num )

    );

    insert into #b ([row_num], [col_num], [value])

    select 0, 1, 73 union all select 0, 4, 42 union all

    select 1, 2, 82 union all select 2, 0, 83 union all

    select 2, 1, 13 union all select 2, 3, 57 union all

    select 3, 0, 48 union all select 3, 1, 85 union all

    select 3, 2, 18 union all select 3, 3, 24;

    With BadongleBangle as (

    select MatrixA.row_num, MatrixB.col_num, sum(MatrixA.value*MatrixB.value) as Val

    from (select 'Matrix1' as Matrix, * from #a) as MatrixA

    join (select 'Matrix2' as Matrix, * from #b) as MatrixB

    on MatrixA.col_num = MatrixB.row_num

    group by MatrixA.row_num, MatrixB.col_num

    )

    , FlibbertyJibby AS (

    SELECT [Matrix] = p.row_num

    , [0] = IsNull(p.[0],0)

    , [1] = IsNull(p.[1],0)

    , [2] = IsNull(p.[2],0)

    , [3] = IsNull(p.[3],0)

    FROM BadongleBangle

    pivot (

    max(val)

    for [col_num] in ([0], [1], [2], [3])

    ) p

    )

    select Matrix

    , [0], [1], [2], [3]

    , [0_%] = [0] * 100 / sum([0]) over ()

    , [1_%] = [1] * 100 / sum([1]) over ()

    , [2_%] = [2] * 100 / sum([2]) over ()

    , [3_%] = [3] * 100 / sum([3]) over ()

    , [0_zscore] = ([0] - avg([0]) over ()) / stdev([0]) over()

    , [1_zscore] = ([1] - avg([1]) over ()) / stdev([1]) over()

    , [2_zscore] = ([2] - avg([2]) over ()) / stdev([2]) over()

    , [3_zscore] = ([3] - avg([3]) over ()) / stdev([3]) over()

    from FlibbertyJibby

    ;

  • I've never posted before, so I hope this works, but this here is a small part of a stored proc that drives a report I have. The report shows 10 records per page because it looks like a dashboard and lets users lick on the dates and drill down to the detail reports for those dates.

    -- this is a part of a stored procedure and the @GetPageNumber is passed in. This report displays this data 10 per page

    DECLARE @GetPageNumber INT = 1;

    DECLARE @PageNumber INT,

    @Result INT,

    @Outcome VARCHAR(MAX),

    @MaxDateKey BIGINT;

    SELECT @PageNumber = ((COUNT(DISTINCT CAST(CAST(DateKey AS CHAR(8)) AS DATE)))/10) + 1 FROM dbo.anchor_values (NOLOCK);

    WITH AV AS(

    SELECT DISTINCT CAST(CAST(DateKey AS CHAR(8)) AS DATE) AS AnchorValuesDates

    ,COUNT(*) AS RecordCount

    ,MAX(DateProcessed) AS DateProcessed

    ,SUM(CASE WHEN AnchorValue IS NULL THEN 1 ELSE 0 END) AS NumberBlankAnchorValues

    ,ROW_NUMBER() OVER (ORDER BY CAST(CAST(DateKey AS CHAR(8)) AS DATE) DESC) AS Position

    ,NTILE(@PageNumber) OVER (ORDER BY CAST(CAST(DateKey AS CHAR(8)) AS DATE) DESC) AS PageNumber

    FROM dbo.anchor_values (NOLOCK)

    GROUP BY CAST(CAST(DateKey AS CHAR(8)) AS DATE))

    SELECT AnchorValuesDates

    ,RecordCount

    ,DateProcessed

    ,Position

    ,PageNumber

    ,@PageNumber AS PageCount

    ,NumberBlankAnchorValues

    ,TrendixValuesDates

    FROM AV

    LEFT OUTER JOIN (SELECT DISTINCT CAST(CAST(DateKey AS CHAR(8)) AS DATE) AS TrendixValuesDates

    FROM dbo.trendix_values) TV

    ON AV.AnchorValuesDates = TV.TrendixValuesDates

    WHERE PageNumber = @GetPageNumber

    ORDER BY PageNumber, Position

  • Great to see SSC call out and apologize for the plagiarism, but how about the culprit (Kamil Moscicki) stand up and do the right thing and apologize not only to the community but to Ben-Gan.

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • /*top 10 queries by avg cpu time*/

    SELECT top 10

    rowid=row_number() over(order by total_worker_time/execution_count desc)

    ,last_execution_time

    , 'last_elapsed_time(ms)'= last_elapsed_time/1000

    , 'avg_elapsed_time(ms)'= total_elapsed_time/execution_count/1000

    , 'last_cpu_time(ms)'=last_worker_time/1000

    , 'avg_cpu_time(ms)'=total_worker_time/execution_count/1000

    , total_logical_reads, total_logical_writes

    , execution_count

    , (SELECT SUBSTRING(text, statement_start_offset/2 + 1,

    (CASE WHEN statement_end_offset = -1

    THEN datalength(text)

    ELSE statement_end_offset END - statement_start_offset)/2 + 1)

    FROM sys.dm_exec_sql_text(sql_handle)) AS query_text

    FROM sys.dm_exec_query_stats

  • Plagiarism of any kind is stealing - and it is rare that you see it so publicly acknowledged and with an apology!

    I already own a copy of the book - it is an excellent resource as are the other two Ben-Gan SQL books that I own!. Windowed functions coupled with CTEs make for some powerful and efficient T-SQL code.

    Fred M3

Viewing 15 posts - 196 through 210 (of 287 total)

You must be logged in to reply to this topic. Login to reply