SQLServerCentral apologizes and you can win a book

  • When we upgrade to 2012, I will not need this statement anymore:

    select * from (select *,row_number() over (order by value) row from dailydata where tagname = '<TagName>' and datepart(year,datetime) = 2012) D where

    row = (select round(.05 * count(*) + .5,0) Pct from (select value,row_number() over (order by value) row from dailydata where tagname = '<TagName>' and datepart(year,datetime) = 2012)f)

    😀

  • Here's a script that will give you the top 10 wait stats over the last 15 seconds along with what percent of the overall waits they were on the server at that time.

    IF OBJECT_ID('tempdb..#WaitBefore') IS NOT NULL BEGIN

    DROP TABLE #WaitBefore

    END

    SELECT wait_type

    , wait_time_ms

    INTO #WaitBefore

    FROM sys.dm_os_wait_stats

    WAITFOR DELAY '00:00:15'

    SELECT TOP 10 A.wait_type

    , wait_time_ms = A.wait_time_ms - B.wait_time_ms

    , percent_of_waits = Cast((A.wait_time_ms - B.wait_time_ms)*100.0 / SUM(A.wait_time_ms - B.wait_time_ms) OVER () as DEC(4,1))

    FROM #WaitBefore B

    INNER JOIN sys.dm_os_wait_stats A ON B.wait_type = A.wait_type

    ORDER BY 2 DESC

  • Show's the run status of each sql agent job's last 5 executions..

    USE MSDB

    DECLARE @PrevRuns TINYINT

    SET @PrevRuns = 5

    SELECT

    Name

    , Description

    , message

    , run_status

    , RunDateTime

    FROM (

    SELECT

    j.Name

    , j.Description

    , h.message

    , h.run_status

    , msdb.dbo.agent_datetime(run_date, run_time) as RunDateTime

    , RANK() OVER(PARTITION BY j.Name ORDER BY msdb.dbo.agent_datetime(run_date, run_time) DESC) as rnk

    FROM sysjobs j

    INNER JOIN sysjobhistory h

    ON h.job_id = j.job_id

    WHERE

    step_id = 0

    AND j.category_id = 3

    ) jhist

    WHERE jhist.Rnk <= @PrevRuns

    ORDER BY Name, RunDateTime DESC

  • Last 3 dates for each month in a calendar table

    WITH dates as (

    select

    dateId

    , calendarMonthName

    , ROW_NUMBER() over (partition by calendarMonthName order by dateId desc) as id

    from DimDate

    where calendarYear = 2013

    )

    SELECT * from dates where id <=3

  • It's nice to see that you aren't accepting plagiarism, it must be very difficult to monitor such things within the blogs, posts etc.

    Here is a bit of code I received from this site along with some of my own tweaks, I use it to help me determine rate of change in disk space usage.

    ALTER PROCEDURE [dbo].[rpt_Server_FreeSpace_RateOfChange]

    @ServerName varchar(128) = '', @StartDate datetime, @EndDate datetime, @DiskLetter varchar(2) = ''

    AS

    with diskspace

    as(

    select freespace as diskfree, size as disksize, [server] as servername, snapshotdate as logdate, deviceID

    from [vw_ReportDiskSpaceTracking]

    where (@ServerName = '' OR [server] like @ServerName) AND

    (@DiskLetter = '' OR [deviceID] like @DiskLetter) AND

    (snapshotdate between @StartDate and @EndDate) AND (isnumeric(convert(float, size))) = 1

    ),

    orderdrows

    as

    (

    select freespace, size, [server], snapshotdate, deviceID as diskletter,

    ROW_NUMBER() OVER (order by [server], snapshotdate) as rn

    from vw_ReportDiskSpaceTracking where

    (@ServerName = '' OR [server] like @ServerName) AND (@DiskLetter = '' OR [deviceID] like @DiskLetter) and snapshotdate between @StartDate and @EndDate AND isnumeric(convert(float, size)) = 1

    )

    Select Min(convert(float, freespace)*1) as MinFreespace,

    Max(convert(float, freespace)*1) as MaxFreespace,

    DATEDIFF(d, min(snapshotdate), max(snapshotdate)) as NumDays,

    avg(convert(float, size)) as avgdisksize,

    diskletter,

    [server],

    (Max(convert(float, freespace)*1) - Min(convert(float, freespace)*1)) / ((Min(convert(float, freespace)*1)*100)/DATEDIFF(d, min(snapshotdate), max(snapshotdate))) as RateOfChange

    From orderdrows

    Cross Join diskspace

    group by [server], diskletter

    having ((Min(convert(float, freespace)*1)*100)/DATEDIFF(d, min(snapshotdate), max(snapshotdate))) >0;

  • Would love a copy of the book.

    Here is a rather kludgy way of getting backup counts for today, using OVER

    select

    database_name,

    server_name,

    CASE type

    WHEN 'D' THEN 'Full'

    WHEN 'I' THEN 'Differential'

    WHEN 'L' THEN 'Log'

    ELSE 'Other'

    END as Backup_Type, CONVERT(date, backup_finish_date), COUNT(CASE type

    WHEN 'D' THEN 'Full'

    WHEN 'I' THEN 'Differential'

    WHEN 'L' THEN 'Log'

    ELSE 'Other'

    END) OVER(Partition BY CASE type

    WHEN 'D' THEN 'Full'

    WHEN 'I' THEN 'Differential'

    WHEN 'L' THEN 'Log'

    ELSE 'Other'

    END) as 'BackupsCompleted'

    from msdb.dbo.backupset

    WHERE backup_finish_date = convert(date, getdate())

    GROUP BY database_name, server_name, CASE type

    WHEN 'D' THEN 'Full'

    WHEN 'I' THEN 'Differential'

    WHEN 'L' THEN 'Log'

    ELSE 'Other'

    END , CONVERT(date, backup_finish_date)

  • Haven't gotten too far beyond using these for RowNumber(), but I look forward to using some of the 2012 functionality soon.

    --Query to display sets of daily recurring schedules with different patterns

    -- , start, and end dates

    SELECT DISTINCT TOP 100 PERCENT

    Schedule_ID

    , Customer_ID

    , Schedule_Name

    , Schedule_Recurrence_ID

    , FullDate + CONVERT(VARCHAR(10), Schedule_Start_Time, 114) AS StartTime

    , FullDate + CONVERT(VARCHAR(10), Schedule_End_Time, 114) AS EndTime

    FROM (

    SELECT s.Schedule_ID

    , s.Customer_ID

    , s.Schedule_Name

    , s.Schedule_Start_Time

    , s.Schedule_End_Time

    , sr.Schedule_Recurrence_ID

    , End_After_Occurrences

    , Recurrence_Pattern

    , Every_Number_Days

    , c.FullDate

    , c.DayOfWeek

    , c.DayOfMonth

    , c.MonthOfYear

    , c.YearNumber

    , c.DayOccurrenceNumber

    , c.IsLast

    , ROW_NUMBER() OVER(PARTITION BY s.Schedule_ID, sr.Schedule_Recurrence_ID ORDER BY s.Schedule_ID, c.FullDate) AS RowID

    FROM dbo.Schedule AS s

    JOIN dbo.Schedule_Recurrence AS sr

    ON s.Schedule_ID = sr.Schedule_ID

    JOIN dbo.Calendar AS c

    ON c.FullDate BETWEEN sr.Start_Date AND COALESCE(sr.End_Date, DATEADD(mm, 12, GETDATE()) )

    WHERE sr.Recurrence_Pattern = 1 --Daily

    AND s.Customer_ID = @CustomerID

    AND (s.Schedule_ID = @ScheduleID OR @ScheduleID IS NULL)

    AND (sr.Schedule_Recurrence_ID = @ActivityRecurrenceID OR @ActivityRecurrenceID IS NULL)

    ) AS t1

    WHERE RowID % CASE WHEN Every_Number_Days < 1 THEN 1 ELSE t1.Every_Number_Days END = 1

    AND (t1.End_After_Occurrences = 0

    OR RowID <= t1.End_After_Occurrences * t1.Every_Number_Days )

    ORDER BY t1.Schedule_ID, FullDate + CONVERT(VARCHAR(10), Schedule_Start_Time, 114)

  • I would love a copy of the book because I just can't get my head around the windowing or the over clause. Also, it's 4pm on a friday of own-development time, and I need to go home and help with homework.

    Afraid my example is from the TechNet page because I really don't get it:

    USE AdventureWorks2012;

    GO

    SELECT ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number",

    p.LastName, s.SalesYTD, a.PostalCode

    FROM Sales.SalesPerson AS s

    INNER JOIN Person.Person AS p

    ON s.BusinessEntityID = p.BusinessEntityID

    INNER JOIN Person.Address AS a

    ON a.AddressID = p.BusinessEntityID

    WHERE TerritoryID IS NOT NULL

    AND SalesYTD <> 0

    ORDER BY PostalCode;

    GO

  • Given a table that has the history of changes to some value, and the changedate for the new value :

    CREATE TABLE [dbo].[AnleggMeasurementTypeHistory](

    [AnleggsId] [decimal](18, 0) NOT NULL,

    [FromDate] [date] NOT NULL,

    [MeasurementType] [int] NOT NULL,

    PRIMARY KEY CLUSTERED

    (

    [AnleggsId] ASC,

    [FromDate] ASC

    )

    ) ON [PRIMARY]

    One could use the following query to create a view that returns the period, fromdate - todate, for each value:

    ;WITH CTE_AnleggMeasurementTypeHistoryOrdered

    AS

    (

    SELECT mt.Anleggsid, mt.FromDate , mt.MeasurementType, ROW_NUMBER() OVER(PARTITION BY mt.Anleggsid ORDER BY mt.FromDate) AS RowNo

    FROM dbo.AnleggMeasurementTypeHistory mt

    INNER JOIN dbo.Anlegg an ON mt.anleggsid = an.anleggsid

    )

    SELECT c1.Anleggsid, c1.Fromdate, COALESCE(c2.FromDate, CAST(DATEADD(day,1,a.StoppDato) as date),'40000101') AS ToDate, c1.MeasurementType

    FROM CTE_AnleggMeasurementTypeHistoryOrdered c1

    INNER JOIN dbo.Anlegg a ON c1.Anleggsid = a.Anleggsid

    LEFT JOIN CTE_AnleggMeasurementTypeHistoryOrdered c2 ON c1.Anleggsid = c2.Anleggsid

    AND c1.RowNo = c2.RowNo-1

  • Sorry, no useful code here...just whimsy:

    select What, sum([where]) over() 'The Rainbow'

    from (

    select 'Blue' as What, 1 as [where] UNION

    select 'Birds' as What, 1 as [where] UNION

    select 'Flyyyyyyy' as What, 1 as [where]

    ) k

  • I would love to own this book.

    Copying without permission is not good.

    create table #tmp_test1

    (col1 int);

    insert #tmp_test1

    SELECT 1

    UNION ALL

    SELECT 1

    UNION ALL

    SELECT 1

    select *,ROW_NUMBER() OVER (ORDER BY col1) as RowNum from #tmp_test1

  • I am soooo tempted to just copy and paste somebody else's entry...

    😀

    (Kudos for fessing up to the mistake and doing something about it!)

  • I wrote this so I could learn how to use it. It was to get the number of labels by item and the particular # you were one: 1 of 4, 2 of 4 etc. I ended up putting something very similar into production (I don't remember but I am pretty sure I stole/borrowed portions of this):

    DECLARE @_Detail TABLE (

    RowKey INT NOT NULL IDENTITY(1,1),

    PackageKeyINT,

    Itemkey INT);

    DECLARE @_Header TABLE (

    RowKey INT NOT NULL IDENTITY(1,1),

    PackageKey INT,

    ShipKeyINT,

    ItemCnt INT,

    ItemTotal INT);

    DECLARE @_Work TABLE (

    PackageKeyINT,

    ShipKeyINT);

    INSERT INTO @_Detail (PackageKey, ItemKey)

    VALUES (1,1), (2,1), (3,2), (4,1), (5,2), (6,3);

    INSERT INTO @_Header (PackageKey, ShipKey)

    VALUES (1,1), (2,1), (3,1), (4,1), (5,1), (6,1);

    INSERT INTO @_Work (PackageKey, ShipKey)

    VALUES (1, 1);

    SELECT * FROM @_Detail;

    SELECT * FROM @_Header;

    SELECT * FROM @_Work;

    WITH ItemCount (ShipKey, ItemKey, ItemCount) AS

    (

    SELECT c.ShipKey, a.Itemkey, COUNT(*)

    FROM @_Detail a

    INNER JOIN @_Header b

    ON a.PackageKey = b.PackageKey

    INNER JOIN @_Work c

    ON b.ShipKey = c.ShipKey

    GROUP BY c.ShipKey , a.Itemkey

    )

    UPDATE a

    SET ItemTotal = d.ItemCount

    FROM @_Header a

    INNER JOIN ItemCount d

    ON a.ShipKey = d.ShipKey

    INNER JOIN @_Detail b

    ON a.PackageKey = b.PackageKey AND d.ItemKey = b.Itemkey;

    WITH ItemRow (PackageKey, ItemKey, ItemCnt) AS

    (

    SELECT a.PackageKey, b.Itemkey,

    ROW_NUMBER() OVER(PARTITION by b.ItemKey ORDER BY a.PackageKey) AS 'ItemCnt'

    FROM @_Header a

    INNER JOIN @_Detail b

    ON a.PackageKey = b.PackageKey

    )

    UPDATE a

    SET ItemCnt = b.ItemCnt

    FROM @_Header a

    INNER JOIN ItemRow b

    ON a.PackageKey = b.PackageKey

    SELECT * FROM @_Header

    Steve - as others have said I am very glad you are going for "integrity".

  • Maybe this will be the time I win something useful...

    With Reasons (Respondent, Reason) AS

    (SELECT 'John', 'just because' UNION ALL

    SELECT 'Joe', 'why not?' UNION ALL

    SELECT 'Jane','need comode reading material' UNION ALL

    SELECT 'Judy','its free' UNION ALL

    SELECT 'Jimmy','why not?' UNION ALL

    SELECT 'Jenny','its free' UNION ALL

    SELECT 'Joyce','it''ll be good' UNION ALL

    SELECT 'Jason','its free')

    SELECT TOP 1

    'Number one reason people want something for free = ' + Reason

    FROM Reasons

    ORDER BY COUNT(REASON) OVER (PARTITION BY REASON) DESC

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • Here we go:

    SELECT

    O.orderID

    ROW_NUMBER() OVER(PARTITION BY O.orderID) AS line_item_no,

    OL.productID

    FROM Orders O

    This can be used to set up line numbers for your product orders using a simple join syntax

Viewing 15 posts - 226 through 240 (of 287 total)

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