December 6, 2013 at 7:48 am
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)
😀
December 6, 2013 at 7:51 am
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
December 6, 2013 at 8:01 am
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
December 6, 2013 at 8:30 am
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
December 6, 2013 at 8:30 am
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;
December 6, 2013 at 8:38 am
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)
December 6, 2013 at 9:03 am
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)
December 6, 2013 at 9:12 am
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
December 6, 2013 at 9:23 am
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
December 6, 2013 at 9:26 am
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
December 6, 2013 at 10:28 am
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
December 6, 2013 at 11:49 am
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!)
December 6, 2013 at 11:54 am
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".
December 6, 2013 at 12:26 pm
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.
December 6, 2013 at 12:55 pm
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