SQLServerCentral apologizes and you can win a book

  • I appreciate the SQLServerCentral's effort in dealing with this matter !

    select orderid,productID, max(unitprice)

    over ( partition by orderid) as MaxPrice

    from sales.OrderDetails

  • Book looks good. I use OVER to add a sequential number to rows that have duplicate IDs.

    SELECT PID, ROW_NUMBER() OVER(PARTITION BY PID ORDER BY PID) AS LINE_NUM

    FROM MyTable

  • When I read the article from SQL Server Central a lot of it did sound kind of "deja' vu". However, many articles and posts regarding SQL Server functions, processes, best practices, etc. are like that; we all are talking about the same thing ... how to best install, manage, perform SQL Server for goodness sake.

    WITH cteDates(dtDate) AS

    (

    SELECT StartDate = @Date -6

    UNION ALL

    SELECT DATEADD(d,1,dtDate)

    FROM cteDates

    WHERE dtDate < @Date

    )

    ,cteDateDayNum (dtDate,DayNum) AS

    (

    SELECT

    dtDate

    ,DayNum = ROW_NUMBER() OVER(ORDER BY dtDate ASC)

    FROM cteDates

    )

    ,cteEmpData AS

    (

    SELECT

    DDN.DayNum

    ,THD.SSN

    ,THD.Client

    ,THD.GroupCode

    ,THD.ShiftNo

    ,THD.DeptNo

    ,InTime = LEFT(CAST(THD.InTime AS TIME),5)

    ,OutTime = LEFT(CAST(THD.OutTime AS TIME),5)

    ,DailyHours = THD.[Hours]

    ,RowNum = ROW_NUMBER() OVER(PARTITION BY THD.SSN,DDN.DayNum ORDER BY THD.RecordID)

    FROM

    cteDateDayNum DDN

    LEFT JOIN

    xxDBxx.dbo.tblDataDataDetail THD

    ON THD.TransDate = DDN.dtDate

    CROSS APPLY

    xxDBxx.dbo.tvf_GetxxDBxxClusterDefAsFn

    (THD.GroupCode,THD.SiteNo,THD.DeptNo,THD.AgencyNo,THD.SSN,THD.DivisionID,THD.ShiftNo,@ClusterID)

    WHERE

    THD.Client = @Client

    AND THD.GroupCode = @Group

    AND THD.PayrollPeriodEndDate = @Date

    AND xxDBxx.dbo.fn_InCSV(@Sites,thd.SiteNo,1) = 1

    AND xxDBxx.dbo.fn_InCSV(@Dept,thd.DeptNo,1) = 1

    UNION

    SELECT

    DayNum

    ,SSN = NULL

    ,Client = NULL

    ,GroupCode = NULL

    ,ShiftNo = NULL

    ,DeptNo = NULL

    ,InTime = NULL

    ,OutTime = NULL

    ,DailyHours = NULL

    ,RowNum = NULL

    FROM

    cteDateDayNum

    )

    ,cteAllTime AS

    (

    SELECT

    SSN

    ,DeptNo

    ,ShiftNo

    ,DayNum = 'In' + CAST(DayNum AS CHAR(1))

    ,strTime = InTime

    ,RowNum

    FROM cteEmpData

    WHERE InTime IS NOT NULL

    UNION

    SELECT

    SSN

    ,DeptNo

    ,ShiftNo

    ,DayNum = 'Out' + CAST(DayNum AS CHAR(1))

    ,strTime = OutTime

    ,RowNum

    FROM cteEmpData

    WHERE OutTime IS NOT NULL

    UNION

    SELECT

    SSN

    ,DeptNo

    ,ShiftNo

    ,DayNum = 'Hours' + CAST(DayNum AS CHAR(1))

    ,strTime = CAST(DailyHours AS VARCHAR(5))

    ,RowNum

    FROM cteEmpData

    WHERE DailyHours IS NOT NULL

    )

    ,ctePivot AS

    (

    SELECT

    SSN

    ,DeptNo

    ,ShiftNo

    ,In1,Out1,Hours1

    ,In2,Out2,Hours2

    ,In3,Out3,Hours3

    ,In4,Out4,Hours4

    ,In5,Out5,Hours5

    ,In6,Out6,Hours6

    ,In7,Out7,Hours7

    FROM

    (

    SELECT SSN,DeptNo,ShiftNo,RowNum,DayNum,StrTime FROM cteAllTime

    ) CTE

    PIVOT

    (

    MAX(StrTime) FOR DayNum IN

    (

    In1,Out1,Hours1

    ,In2,Out2,Hours2

    ,In3,Out3,Hours3

    ,In4,Out4,Hours4

    ,In5,Out5,Hours5

    ,In6,Out6,Hours6

    ,In7,Out7,Hours7

    )

    ) PVT

    WHERE SSN IS NOT NULL

    )

    SELECT DISTINCT

    EN.SSN

    ,EN.LastName

    ,EN.FirstName

    ,GD.DeptName

    ,ED.ShiftNo

    ,In1 = REPLACE(In1,'00:00',''),Out1 = REPLACE(Out1,'00:00',''),Hours1

    ,In2 = REPLACE(In2,'00:00',''),Out2 = REPLACE(Out2,'00:00',''),Hours2

    ,In3 = REPLACE(In3,'00:00',''),Out3 = REPLACE(Out3,'00:00',''),Hours3

    ,In4 = REPLACE(In4,'00:00',''),Out4 = REPLACE(Out4,'00:00',''),Hours4

    ,In5 = REPLACE(In5,'00:00',''),Out5 = REPLACE(Out5,'00:00',''),Hours5

    ,In6 = REPLACE(In6,'00:00',''),Out6 = REPLACE(Out6,'00:00',''),Hours6

    ,In7 = REPLACE(In7,'00:00',''),Out7 = REPLACE(Out7,'00:00',''),Hours7

    FROM ctePivot CTE

    INNER JOIN

    cteEmpData ED

    ON ED.SSN = CTE.SSN

    AND ED.DeptNo = CTE.DeptNo

    AND ED.ShiftNo = CTE.ShiftNo

    INNER JOIN

    xxDBxx.dbo.tblNames EN

    ON EN.SSN = ED.SSN

    AND EN.Client = ED.Client

    AND EN.GroupCode = ED.GroupCode

    INNER JOIN

    xxDBxx.dbo.tblDepts GD

    ON GD.Client = ED.Client

    AND GD.GroupCode = ED.GroupCode

    AND GD.DeptNo = ED.DeptNo

    ORDER BY

    LastName,FirstName,DeptName,ShiftNo

    ,In1 DESC,In2 DESC,In3 DESC,In4 DESC

    ,In5 DESC ,In6 DESC,In7 DESC;

  • Itzik Ben-Gan is the best. I always know I am getting great information when he is the author! I look for his presentations every time I go to TechEd. It was at TechEd 2011 that I first saw the information on Window Functions (I think it was one of the KeyNote Sessions).

    I had a table that just contained order detail records that I needed to display by individual orders in a consistent order. Unfortunately, PO (purchase order) numbers in our system rollover so they are not unique between stores. I needed to identify unique orders and add a unique order line number for each detail record for each order. Using the OVER clause made this so easy! I have also used the OVER clause to only send a specific set of records back to a web page for paging so the web page is more responsive.

    I commend SQL Server Central for doing the right thing. Like Mr. Ben-Gan, I always know I can find good information on SQL Server Central.

    SELECT

    ORDER_NUM = CONVERT(VARCHAR(3), StoreNo) + '-'

    + CONVERT(VARCHAR(10), PONo) + '-'

    + CONVERT(VARCHAR(8), PODate, 112)

    ,ORDER_LINENO = ROW_NUMBER() OVER (PARTITION BY PODate, StoreNo, PONo ORDER BY ProductCode)

    ,StoreNo

    ,PONo

    ,PODate

    ,ProductCode

    ,Quantity

    FROM ORDERS

  • I had to fumble through windowing functions when the need arose. After Watching Christina Leo's PASS2013 session, I let her know that she made sense of what I had hacked together(below).

    This is what I managed to cobble together. I use it in an SSRS report for finding the total hours worked for some of my users

    SELECT UL.[Date_Time]

    ,CAST(UL.[Date_Time] - LAG(UL.[Date_Time],1) OVER (PARTITION BY CAST(UL.[Date_TIME] AS DATE) ORDER BY UL.[Date_TIME]) AS TIME) AS 'Time On'

    FROM [SERVER].[dbo].[TABLENAME] AS UL WITH (NOLOCK) /**Pay no attention to the NOLOCK, no I can't change that **/

    WHERE UL.[Department] = 'DEPARTMENT'

    AND ((UL.[Action] = 'On' OR UL.[Action] = 'Login') OR UL.[Action] = 'Off')

    AND (UL.[Name] LIKE '%' + @Name + '%') /** Really, there isn't a better way, no I can't change that **/

    AND (

    (CAST(UL.[Date_TIME] AS DATE) >= @Start_Date AND @End_Date IS NULL)

    OR (CAST(UL.[Date_TIME] AS DATE) <= @End_Date AND @Start_Date IS NULL)

    OR (CAST(UL.[Date_TIME] AS DATE) >= @Start_Date AND CAST(UL.[Date_TIME] AS DATE) <= @End_Date)

    )

  • Sincere, accurate apologies raise credibility with me. Well done, SQLServerCentral.

    Here's a query I modified from one I saw somewhere (in BOL, I think) while studying windowing functions. It shows an example of the Lag and Lead functions.

    use AdventureWorks2012

    go

    -- LAG, LEAD

    -- Retrieves a row at a given physical offset that comes before (LAG) or after (LEAD) the current row.

    select Department, Lastname, Rate

    ,lag(Rate, 2, -9) over (partition by Department order by Rate desc) as [lag(Rate, 2, -9)]

    ,lead(Rate, 2, -8) over (partition by Department order by Rate desc) as [lead(Rate, 2, -8)]

    from HumanResources.vEmployeeDepartmentHistory as EDH

    inner join HumanResources.EmployeePayHistory as E

    on E.BusinessEntityID=EDH.BusinessEntityID

    where Department in ('Information Services', 'Document Control')

    order by Department, Rate desc

  • j.zinn (12/5/2013)


    I'm just really impressed by the reaction that this site had to content that was lifted without attribution. A quick, scholarly reaction. I hope that Mr. Ben-Gan appreciated the response, and that this community knows that sharing knowledge is lofty, but giving credit is vital. Without it, it's theft, not sharing.

    As I mentioned in my post, it's still theft even when you give credit for the lifted content. Only when a person has permission from the author copyright holder is it not theft.

    EDIT: Corrected wrong word choice.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • SELECT

    [PlagiarizedArticle] AS oversite

    ,rank() OVER (partition by [Ben-Gan].public.Apology order by dateofApology)

    FROM SQLServerCentral

    Result:

    ||Hope all is forgiven...here's your free book by author.||

  • jsimancas (12/5/2013)


    SELECT

    [PlagiarizedArticle] AS oversite

    ,rank() OVER (partition by [Ben-Gan].public.Apology)

    FROM SQLServerCentral

    Result:

    ||Hope all is forgiven...here's your free book by author.||

    HA! I like this one.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • yeah, I did it so quick I forgot the order by clause.. ooops:-D

  • I think you are doing the right thing to admit a mistake and provide some benefit to the plagerized. I did not write following from scratch but modified from http://www.sqlservercentral.com/Forums/Topic675554-8-46.aspx:

    ;WITH AdjCategoryTree

    AS

    (

    SELECT A.UN_standard_product_code, A.UN_standard_product_name,

    CONVERT(VARCHAR(MAX), UN_standard_product_name) AS UN_standard_product_namePath,

    A.UN_standard_parent_product_code, 0 AS Depth

    FROM dbo.UN_STANDARD_PRODUCT A WITH (NOLOCK)

    WHERE UN_standard_parent_product_code IS NULL

    UNION ALL

    SELECT A.UN_standard_product_code, A.UN_standard_product_name,

    CONVERT(VARCHAR(MAX), B.UN_standard_product_namePath + '|' + A.UN_standard_product_name) AS UN_standard_product_namePath,

    A.UN_standard_parent_product_code, B.Depth + 1

    FROM dbo.UN_STANDARD_PRODUCT A WITH (NOLOCK)

    JOIN AdjCategoryTree B

    ON A.UN_standard_parent_product_code = B.UN_standard_product_code

    )

    SELECT TOP 100 PERCENT UN_standard_product_code,

    REPLICATE(' ', Depth) + UN_standard_product_name AS DisplayLabel,

    UN_standard_product_name

    , ROW_NUMBER() OVER (ORDER BY UN_standard_product_namePath) AS [Order], Depth

    FROM AdjCategoryTree

    ORDER BY UN_standard_product_namePath;

  • A very good book! If you do not have a copy, then get one!

  • Good to read that the offender has been identified and SQL ServerCentral is taking the right action. Here is a way to get the sales order counts by year and month name:

    WITH SourceData

    AS

    (SELECT DISTINCT

    Year(OrderDate) AS OrderYear,

    Month(OrderDate) AS OrderMonth,

    DateName(mm, OrderDate) AS OrderMonthName,

    Count(*) OVER (PARTITION BY Year(OrderDate), Month(OrderDate)) AS OrderCount

    FROM Sales.SalesOrderHeader

    )

    SELECTOrderYear,

    OrderMonthName,

    OrderCount

    FROMSourceData

    ORDER BY OrderYear DESC, OrderMonth DESC;

  • Was checking out this book and it looks perfect for me! I've added it to my Amazon wish list just in case I'm not one of the ten lucky winners.

  • One of my favorite uses for window functions is for removing duplicates. This is pretty well documented, so I wanted to show how I use the new (2012) SUM() with window function for running totals or balances:

    --Create database

    CREATE DATABASE RunningTotal

    GO

    --Switch database context

    USE RunningTotal

    GO

    --Create a table for our ledger

    CREATE TABLE Ledger(

    ledgerID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    userID INT NOT NULL,

    credit DECIMAL(18,2),

    debit DECIMAL(18,2)

    )

    GO

    --Insert values into Ledger table

    INSERT INTO Ledger (userID, credit, debit)

    SELECT 1, 200.00, 0

    UNION ALL

    SELECT 1, 300.00, 0

    UNION ALL

    SELECT 1, 0, 150.00

    UNION ALL

    SELECT 1, 25.00, 0

    UNION ALL

    SELECT 1, 650.00, 0

    UNION ALL

    SELECT 1, 0, 1000.00

    UNION ALL

    SELECT 1, 950.00, 0

    UNION ALL

    SELECT 1, 0, 10.00

    UNION ALL

    SELECT 2, 1000.00, 0

    UNION ALL

    SELECT 2, 25.00, 0

    UNION ALL

    SELECT 2, 0, 15.00

    UNION ALL

    SELECT 2, 32.00, 0

    UNION ALL

    SELECT 2, 15.00, 0

    GO

    --Verify data

    SELECT *

    FROM Ledger

    ORDER BY ledgerID

    GO

    --Calculate running toal for balance

    SELECT

    ledgerID

    ,userID

    , credit

    , debit

    , SUM(credit - debit) OVER(PARTITION BY userID ORDER BY ledgerid) AS balance

    FROM Ledger

    --Clean up

    USE master

    DROP DATABASE RunningTotal

    GO

    Jared
    CE - Microsoft

Viewing 15 posts - 46 through 60 (of 287 total)

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