SQLServerCentral apologizes and you can win a book

  • I used ROW_NUMBER() OVER to speed up a query that returned the latest status for an "Activity" entity that is linked to a user-defined set of interface tabs. The code used to depend on a GROUP BY and HAVING that was quite slow in comparison.

    WITH StatusHistory (ActivityID, TabID, StatusReportID, StatusOrder) AS (

    SELECT

    ASR.ActivityID

    , StatusVar.TabID

    , ASR.StatusReportID

    , ROW_NUMBER () OVER (PARTITION BY ASR.ActivityID, ActStat.ActivityStatusTypeID ORDER BY ASR.ActivityID, ActStat.ActivityStatusTypeID, ASR.StatusReportDate DESC) [StatusOrder]

    FROM dbo.ActivityStatusReport [ASR]

    INNER JOIN dbo.ActivityStatus [ActStat] ON ActStat.ActivityStatusID = ASR.ActivityStatusID

    INNER JOIN dbo.ActivityStatusType [StatusVar] ON StatusVar.ActivityStatusTypeID = ActStat.ActivityStatusTypeID

    And StatusVar.ActivityStatusTypeEnabled = 1

    )

    SELECT

    UsedTabs.ActivityID

    , AllTabs.TabID [TabID]

    , SH.StatusReportID

    FROM dbo.AppTab [AllTabs]

    INNER JOIN dbo.AppActivityTab [UsedTabs] ON UsedTabs.TabID = AllTabs.TabID

    INNER JOIN StatusHistory [SH] ON SH.ActivityID = UsedTabs.ActivityID

    And SH.TabID = UsedTabs.TabID

    And SH.StatusOrder = 1

    WHERE AllTabs.CMSTabEnabled = 1

    And UsedTabs.[Enabled] = 1

    I have a couple of Itzik's books, but not this one.

  • SELECT AccountActivityID, FK_EntityID, ActivityTypeID, ActivityDate, DebitAmount,

    FK_CourtID, PaidTo, AccountActivity.NoticeID, AttRefNum, SR.Plaintiff, T.DefList,

    (ROW_NUMBER() OVER(Order by AccountActivity.NoticeID) + (@StartCheck-1)) AS CheckNumber

    FROM AccountActivity

    inner join ServiceRequest SR on SR.NoticeID = AccountActivity.NoticeID

    left join @TableOut T on T.NoticeID = SR.NoticeID

    WHERE (ActivityTypeID = 2)

    AND ( (@ActivityID IS NULL AND PrintDate IS NULL )

    OR (AccountActivityID = @ActivityID AND PrintDate IS NOT NULL))

    ORDER BY NoticeID

  • Yello! Pick me! 🙂

    select UserID, sum(ServerInstanceCount) OVER (Partition by UserID) as UserServerCount from User u join ServerInstance si on u.Id = si.UserId;

  • Unfortunately, any forum that alllows community posts is going to run into plagarism. Nobody reviewing the incoming posts is going to be familiar with every other published article on the Internet. It's all in how you handle the situation when it is discovered. And what a great way. Who wouldn't want free stuff from Itzik?!

    Putting a little Christmas spirit into the mix!

    DECLARE @Products TABLE

    (

    ProductId INTEGER,

    ProductName NVARCHAR(25)

    )

    DECLARE @Orders TABLE

    (

    OrderId INTEGER,

    DeliveryDate DATE,

    ProductID INTEGER,

    Quantity INTEGER

    )

    INSERT INTO @Products (ProductId, ProductName)

    VALUES (12, 'Drummers Drumming'),

    (11, 'Pipers Piping'),

    (10, 'Lords a Leaping'),

    (9, 'Ladies Dancing'),

    (8, 'Maids a Milking'),

    (7, 'Swans a Swimming'),

    (6, 'Geese a Laying'),

    (5, 'Golden Rings'),

    (4, 'Calling Birds'),

    (3, 'French Hens'),

    (2, 'Pipers Piping'),

    (1, 'Partridge in a Pear Tree')

    -- Fill the order table

    ;WITH CTEProducts (ProductID, ProductName, DayOfChristmas)

    AS

    (

    SELECT

    ProductId,

    ProductName,

    12 AS DayOfChristmas

    FROM @Products

    WHERE ProductId <= 12

    UNION ALL

    SELECT

    P.ProductId,

    P.ProductName,

    DayOfChristmas - 1

    FROM @Products P

    JOIN CTEProducts CTE ON P.ProductId = CTE.ProductID

    WHERE P.ProductId <= DayOfChristmas - 1 AND DayOfChristmas > 1

    )

    INSERT INTO @Orders (OrderId, DeliveryDate, ProductID, Quantity)

    SELECT 1000+DayOfChristmas, DATEADD(DAY, DayOfChristmas-1, '2013-12-25'), ProductID, ProductID FROM CTEProducts

    -- Determine Total Sales Quantity by Product

    SELECT DISTINCT P.ProductName, ProductQuantityTotal FROM @Products P

    JOIN

    (

    SELECT ProductID, DeliveryDate, Quantity AS OrderQuantity, SUM(Quantity) over (partition by ProductID) AS ProductQuantityTotal from @Orders

    ) D ON P.ProductId = D.ProductID

    ORDER BY P.ProductName, ProductQuantityTotal

    -- Determine Delivery Requirements

    SELECT P.ProductName, DeliveryDate, OrderQuantity, ProductQuantityTotal FROM @Products P

    JOIN

    (

    SELECT ProductID, DeliveryDate, Quantity AS OrderQuantity, SUM(Quantity) over (partition by ProductID) AS ProductQuantityTotal from @Orders

    ) D ON P.ProductId = D.ProductID

    ORDER BY DeliveryDate, P.ProductID

  • Thank you for apologizing so publically. It's good to see SQL Server Central cares about honesty and acts appropriately.

    SELECT DISTINCT OrganizationLevel

    ,COUNT(NationalIDNumber) OVER (PARTITION BY OrganizationLevel) AS LevelCount

    FROM [HumanResources].[Employee]


    Russ

  • Love Mr. Ben-Gan's books!! One of the authors I look for when purchasing a new SQL Server book.

    select ROW_NUMBER() over (partition by TABLE_SCHEMA, TABLE_NAME

    order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE),* from INFORMATION_SCHEMA.TABLE_CONSTRAINTS

  • Nice one!

    This is an example of the use of the "OVER" clause (cited from MS Exam 70-461 Training Kit - Microsoft Press, Authors: Itzik Ben-Gan, Dejan Sarka, Ron Talmage - which I can highly recommend. I am using it to prepare for 70-457 at present):

    SELECT custid, orderid, val,

    SUM (val) OVER(PARTITION BY custid) AS custtotal,

    SUM (val) OVER() AS grandtotal

    FROM Sales.OrderValues;

  • It is pretty disconcerting that somebody is trying to make money by taking some body elses work without attribution. It is one thing to organize coursewhere by surveying multiple topical sources and including those in your own work, but you must reference the original source.

    Below is a simple CTE to create a line_number for a detail table insert.

    with titles (id, report_name, r_l1, r_l2, r_l3, title, line_no)

    as

    (

    select *, ROW_NUMBER() over (partition by report_id order by report_id, level_1, level_3, level_4) from

    (select distinct s.REPORT_ID, s.report_name, level_1, LEVEL_3, LEVEL_4, TITLE_1

    from ESGACTTR t inner join ESGGARSH s

    on t.LEVEL_3 = SUBSTRING(s.report_name,2,4)

    and t.LEVEL_4 = SUBSTRING(s.report_name,7,2)

    and TITLE_ID = -1

    and ACCT_TYPE = 'E'

    and ACCT_YEAR = 2014

    and s.REPORT_GROUP = 'expbud') as v

    )

    insert into ESGGARSD

    (REPORT_ID, LINE_NO, LEVEL_1, LEVEL_2, LEVEL_3, LINE_DESCR, REPORT_YEAR)

    select id, line_no, r_l1, r_l2, r_l3, upper(title), 2014

    from titles

  • I am very tempted to just quote someone else's query with OVER 😀

  • Yes, but this one is mine. I just wrote it yesterday. I did see a few that looked like they were straight out of BOL 😉

  • SELECT U.UserId, U.FirstName, EU.EventId, E.EventName, 'Yes' as RSVP, EU.Attended,

    COUNT(EU.UserId) OVER (PARTITION BY U.UserId ) AS UserTotalRSVPs,

    SUM(EU.Attended) OVER (PARTITION BY U.UserId ) AS UserTotalAttended

    FROM EventUser EU

    INNER JOIN Users U ON U.UserID = EU.UserID

    INNER JOIN Events E ON EU.EventID = E.EventId

  • Too bad this happens but glad it was corrected. Itzik Ben-Gan is one of the folks I follow in the SQL Server world.

  • I teach online at Kaplan University. There are serious disciplinary actions taken if students plagiarize.

    Here is some code I wrote to illustrate GROUP BY. First, the table is defined and rows inserted:

    -- create table vehicles

    create table dbo.vehicles

    ( Year int null

    ,Makevarchar(15)null

    ,Modelvarchar(15)null

    ,BodyStylevarchar(30)null

    ,Priceintnull

    );

    go

    insert vehicles values

    (2000, 'Ford', 'Mustang', '2-Door Coupe', 8000)

    ,(1999, 'Honda', 'Civic', '2-Door Coupe', 5000)

    ,(2000, 'Chevrolet', 'Cavalier', '2-Door Coupe', 5000)

    ,(1999, 'Ford', 'Taurus', '4-Door Sedan', 7000)

    ,(1999, 'Toyota', 'Camry', '4-Door Sedan', 9000)

    ,(2000, 'Toyota', 'Camry', '4-Door Sedan', 10000)

    ,(2002, 'Toyota', 'Prius', 'Hybrid', 16000)

    ,(2001, 'Toyota', 'Prius', 'Hybrid', 14000)

    ,(2003, 'Toyota', 'Prius', 'Hybrid', 18000)

    ,(2003, 'Honda', 'Civic', 'Hybrid', 17000)

    ,(2003, 'Ford', 'Mustang', 'Convertible', 17000)

    ,(1999, 'Ford', 'Mustang', 'Convertible', 9000)

    ,(1998, 'Ford', 'Windstar', 'Cargo Van', 5000)

    ,(2000, 'Ford', 'Windstar', 'Cargo Van', 9000)

    ,(2000, 'Chevrolet', 'Silverado', 'Pickup Truck', 15000)

    ,(2001, 'Chevrolet', 'Silverado', 'Pickup Truck', 17000)

    ;

    Next, here are two queries against the vehicles table that illustrate the use of OVER:

    -- Query 1

    SELECT *

    , AVG(Price) OVER(PARTITION BY BodyStyle) as AvgPriceByBodyStyle

    FROM dbo.vehicles;

    -- Query 2

    SELECT *

    , AVG(Price) OVER(PARTITION BY BodyStyle) as AvgPriceByBodyStyle

    , Price - AVG(Price) OVER(PARTITION BY BodyStyle) as CompareToAvg

    FROM dbo.vehicles;

  • -- Create sample data

    -- Each Poster gets their name (I'm pretending their name is unique),

    -- a posting ID, a flag to confirm their post has an OVER clause,

    -- and a random number.

    [font="Courier New"]WITH Sample AS (

    SELECT 'James' AS Poster, 50 AS PostingID, 1 AS HasOVER

    , CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 AS SemiRandom UNION ALL

    SELECT 'John' , 51, 0, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Robert' , 52, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'James' , 53, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Mary' , 54, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Linda' , 55, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Michael', 56, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Maria' , 57, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Susan' , 58, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Paul' , 59, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Mark' , 60, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Lisa' , 61, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Mary' , 62, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Brian' , 63, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Ian' , 64, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Chris' , 65, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Jose' , 66, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Anton' , 67, 1, CAST(RAND(CHECKSUM(NEWID())[/font]) * 100 AS INT) + 1

    )[/font]

    -- In the spirt of fairness, multiple postings by the same "poster" don't

    -- increase the odds of winning this great prize - it actually

    -- decreases the odds 🙂 (MAX function)

    -- Only postings that have an OVER example are considered (WHERE clause)

    , Winners AS (

    [font="Courier New"]SELECT Poster

    ,MAX(SemiRandom) OVER (PARTITION BY Poster) AS SemiRandomWorst

    FROM Sample

    WHERE HasOVER = 1

    )[/font]

    -- The next step is to pick the top ten, but I'll leave that for someone else

    -- to use for their OVER post 🙂

  • Malcolm Wynden (12/7/2013)


    -- Create sample data

    -- Each Poster gets their name (I'm pretending their name is unique),

    -- a posting ID, a flag to confirm their post has an OVER clause,

    -- and a random number.

    [font="Courier New"]WITH Sample AS (

    SELECT 'James' AS Poster, 50 AS PostingID, 1 AS HasOVER

    , CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 AS SemiRandom UNION ALL

    SELECT 'John' , 51, 0, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Robert' , 52, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'James' , 53, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Mary' , 54, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Linda' , 55, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Michael', 56, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Maria' , 57, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Susan' , 58, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Paul' , 59, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Mark' , 60, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Lisa' , 61, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Mary' , 62, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Brian' , 63, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Ian' , 64, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Chris' , 65, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Jose' , 66, 1, CAST(RAND(CHECKSUM(NEWID())) * 100 AS INT) + 1 UNION ALL

    SELECT 'Anton' , 67, 1, CAST(RAND(CHECKSUM(NEWID())[/font]) * 100 AS INT) + 1

    )[/font]

    -- In the spirt of fairness, multiple postings by the same "poster" don't

    -- increase the odds of winning this great prize - it actually

    -- decreases the odds 🙂 (MAX function)

    -- Only postings that have an OVER example are considered (WHERE clause)

    , Winners AS (

    [font="Courier New"]SELECT Poster

    ,MAX(SemiRandom) OVER (PARTITION BY Poster) AS SemiRandomWorst

    FROM Sample

    WHERE HasOVER = 1

    )[/font]

    -- The next step is to pick the top ten, but I'll leave that for someone else

    -- to use for their OVER post 🙂

    You can keep it very simple using the one of the very rules for the contest. As you say, have the server extract a list of people's handles (their names aren't required for registration) and their email addresses from the participants of this thread according too the rules of participation. Of course, this list would be filtered by DISTINCT. Then, the selection is easy by using OVER with no chance of a tied random number by using OVER and NEWID(). Here's an example using Adventure works, which is also my submittal for this contest.

    SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY NEWID())

    ,Handle = ContactID --Simulating the "handle" here

    ,EmailAddress

    INTO dbo.WinnerOrder

    FROM AdventureWorks.Person.Contact

    ;

    The reason for not limiting it to the TOP 10 is because some good folks may turn the book down because they already have it or because they cannot be contacted due to a defunct email address or they simply don't respond.

    --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)

Viewing 15 posts - 241 through 255 (of 287 total)

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