December 5, 2013 at 8:28 am
Good for you for your apology and related actions.
No time for anything more, but keep up the good work.
Thanks.
December 5, 2013 at 8:28 am
Don't plagiarize, internet knows all..
Most recent 10 backups on a server
[font="Courier New"]SELECT database_name [db], name, backup_finish_date, compressed_backup_size FROM
(
SELECT ROW_NUMBER() OVER (partition BY database_name ORDER BY database_name, backup_finish_date desc) [rownum], * FROM msdb.dbo.backupset where type = 'D'
) a
WHERE rownum <= 10
ORDER BY database_name, backup_finish_date desc[/font]
December 5, 2013 at 8:30 am
That looks like a good book for my desk. Here is a window function used with order by for ranking some students in my sister's classes.
SELECT Student_Number, Compare_Group, Subject_Matter, Class_Name, Score,
Rank_Level = RANK() OVER (PARTITION BY Compare_Group, Subject_Matter, Class_Name ORDER BY Score),
Max_Level = COUNT(*) OVER (PARTITION BY Compare_Group, Subject_Matter, Class_Name)
FROM Score_Info
ORDER BY Compare_Group, Subject_Matter, Class_Name, Score
December 5, 2013 at 8:31 am
Itzik Ben-Gan is far beyond awesome and I will get that book! 😎 But I don't want the book through plagiarism...
By the way, this is a classy move SQLServerCentral folks! Stay classy SQLServerCentral!
A very-very simple snippet of tsql that replaced a handful of user defined functions that a developer made years ago:
SELECT EquipmentKey
,RepairKey
,RepairCost
,SUM(RepairCost) OVER (PARTITION BY EquipmentKey) AS "Total Repair Cost"
,AVG(RepairCost) OVER (PARTITION BY EquipmentKey) AS "Avg Repair Cost"
,MIN(RepairCost) OVER (PARTITION BY EquipmentKey) AS "Min Repair Cost"
,MAX(RepairCost) OVER (PARTITION BY EquipmentKey) AS "Max Repair Cost"
,COUNT(RepairCost) OVER (PARTITION BY EquipmentKey) AS "Count of Repairs"
FROM maint.Repairs
WHERE EquipmentKey = 123456789
ORDER BY EquipmentKey
,RepairKey
December 5, 2013 at 8:31 am
WITH
A AS
( SELECT ROW_NUMBER() OVER(ORDER BY TRIP_NO ASC) R
, ROW_NUMBER() OVER(ORDER BY TRIP_NO DESC) RR
, TRIP_NO T FROM TRIP )
, B AS
( SELECT R, RR, T T1
, LAST_VALUE(T) OVER(ORDER BY R ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) T2
, LAST_VALUE(T) OVER(ORDER BY R ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) T3
, LAST_VALUE(T) OVER(ORDER BY RR ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) T4
, LAST_VALUE(T) OVER(ORDER BY RR ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) T5
, LAST_VALUE(T) OVER(ORDER BY RR ROWS BETWEEN CURRENT ROW AND 0 FOLLOWING) T6
FROM A )
SELECT MIN(T1), MIN(T2), MIN(T3)
, MAX(T4), MAX(T5), MAX(T6) FROM B
WHERE R=1 OR RR=1
WITH
A AS
( SELECT
ROW_NUMBER() OVER(ORDER BY TRIP_NO) R
, COUNT(1) OVER() C
, TRIP_NO T FROM TRIP )
SELECT TOP 1 T
, LEAD(T,1) OVER(ORDER BY T)
, LEAD(T,2) OVER(ORDER BY T)
, LEAD(T,C-3) OVER(ORDER BY T)
, LEAD(T,C-2) OVER(ORDER BY T)
, LEAD(T,C-1) OVER(ORDER BY T)
FROM A
ORDER BY R
SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY T) R, T
FROM
(SELECT * FROM
( SELECT TOP 3 TRIP_NO T FROM TRIP ORDER BY TRIP_NO ASC) A
UNION ALL
SELECT * FROM
(SELECT TOP 3 TRIP_NO T FROM TRIP ORDER BY TRIP_NO DESC ) D
) R
) T
PIVOT ( MAX(T) FOR R IN ( [1],[2],[3],[4],[5],[6] )
) P
😀
December 5, 2013 at 8:32 am
I use the windowing functions a lot and still agonise over some of the code I wrote before I knew they existed.
Here's my effort which is in tribute to (not plagiarised from - that would be awful) one of my favourite films.
SELECT
[Over],
[Under],
RANK() OVER (PARTITION BY [Over] ORDER BY [Under]) [Done]
FROM
[NeverGetOver].[MachoGrande]
ORDER BY
[Over],[Under],[Done];
December 5, 2013 at 8:36 am
You didn't say it had to be complicated, so...
SELECT ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) AS RowNum, x, y
FROM tTable
ORDER BY RowNum;
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
December 5, 2013 at 8:38 am
I was experimenting with the RAND() function one day and thought it would be cool to wrap it up with a ROW_NUMBER / PARTITION BY / OVER clause to see just how variable the RAND function was with using the CHECKSUM of a NEWID(). I keep this as a template with comments to remind myself how each piece works. Hope someone finds it as useful as I do.
DECLARE @min-2 int, @max-2 int, @RecCount int; SELECT @min-2 = 1, @max-2 = 15, @RecCount = 50
--Our first CTE creates an 'autonumber' ID field and a random value field. This is used to create a random collection of animals/counts.
;WITH Seed AS (
SELECT 1 [ID], CAST((@Max - @min-2 + 1) * RAND(CHECKSUM(NEWID())) + @min-2 AS int) [Indx]
UNION ALL
SELECT ID + 1, CAST((@Max - @min-2 + 1) * RAND(CHECKSUM(NEWID())) + @min-2 AS int) [Indx]
FROM Seed
WHERE ID < @RecCount
),
--Our second CTE calls the first CTE, then converts our Indx to an Animal value
Animals AS (
SELECT ID, Indx,
CASE Indx
WHEN 0 THEN '****' -- You'll see this if you set your @min-2 variable to zero instead of one
WHEN 1 THEN 'Lion'
WHEN 2 THEN 'Tiger'
WHEN 3 THEN 'Dog'
WHEN 4 THEN 'Cat'
WHEN 5 THEN 'Porpoise'
WHEN 6 THEN 'Whale'
WHEN 7 THEN 'Elephant'
WHEN 8 THEN 'Bear'
WHEN 9 THEN 'Snake'
WHEN 10 THEN 'Giraffe'
WHEN 11 THEN 'Kangaroo'
WHEN 12 THEN 'Wolf'
WHEN 13 THEN 'Deer'
WHEN 14 THEN 'Bird'
WHEN 15 THEN 'Seal'
ELSE '????' -- You'll see this value if your @max-2 variable is set above what we're handling in our CASE
END [Animal]
FROM Seed
)
--Display the results
SELECT *,
--The PARTIION BY (optional) resets the ROW NUMBER when the value of Animal changes.
--The ORDER BY (required) sorts the data before applying our ROW NUMBER
ROW_NUMBER() OVER (PARTITION BY Animal ORDER BY Animal) AS [AnimalCountIndex]
FROM Animals
ORDER BY AnimalCountIndex DESC, Indx
OPTION (MAXRECURSION 0)-- Disables the Maximum Retursions the CTE can have, which is 100 by default
P.S. If I too plagiarized this from someone else (it's very easy to do when you find bits and pieces of great code across the Net that you save for later reference), I humbly apologize. Thanks for keeping SSC as a great forum for us SQL developers!
December 5, 2013 at 8:38 am
I use this many times a day to filter out duplicate data. It was recently tweaked to update the CTE directly after a reading a post by Luis C. on the forums.
;with dedupe (id, statusflag, Rn) as (
select id, statusflag, row_number() over(partition by email order by id) as Rn
from table
)
update dedupe
set statusflag = 10
where rn > 1
and statusflag = 0
December 5, 2013 at 8:39 am
I have Itzik Ben-Gan's T-SQL Fundamentals book and find it quite helpful. I was looking at your giveaway book just the other day, thinking about purchasing it.
Here's a query I did using OVER. Unfortunately it doesn't return what I want, but at least I'm learning more about using it!
SELECT distinct BB6.PREBILL_NUM
,HP4.EMPLOYEE_NAME as 'Billing_Employee'
,HP4.EMPLOYEE_CODE as 'Billing_Employee_Code'
,BB6.TOTAL_BILL_AMT
,BB6.BILL_NUM
,count(BB6.PREBILL_NUM) OVER(PARTITION BY HP4.EMPLOYEE_CODE ) as NumberOfFiles
FROM BLT_BILLM BB1
INNER JOIN HBM_MATTER HM2 ON (BB1.MATTER_UNO = HM2.MATTER_UNO)
LEFT OUTER JOIN HBM_CLIENT HC3 ON (HM2.CLIENT_UNO = HC3.CLIENT_UNO)
LEFT OUTER JOIN HBM_PERSNL HP4 ON (HM2.BILL_EMPL_UNO = HP4.EMPL_UNO)
INNER JOIN BLT_BILL BB6 ON (BB1.BILL_TRAN_UNO = BB6.TRAN_UNO)
WHERE (BB6.PERIOD = 0)
ORDER BY HP4.EMPLOYEE_CODE;
December 5, 2013 at 8:39 am
Thanks for the inspiration - I needed to write this query anyway...but I believe that this is much simpler than what I might have done before researching OVER:
WITH ProductionData as
(
SELECT R.LineNbr, L.PartNbr, L.ShiftDate, L.Shift, SUM(L.Qty) as Quantity
FROM Label L
JOIN RUN R on L.RunNbr = R.RunNbr
WHERE L.Qty <> 0 and L.Shift <> 0
GROUP BY R.LineNbr, L.PartNbr, L.ShiftDate, L.Shift
)
SELECT
LineNbr, PartNbr, ShiftDate, Shift,
CAST(1. * Quantity /SUM(Quantity) OVER(PARTITION BY PartNbr, ShiftDate)
* 100 as DECIMAL(5,2)) AS 'Percent of Part Number for Shift Date',
CAST(1. * Quantity /Sum(Quantity) OVER(PARTITION BY ShiftDate)
* 100 as DECIMAL(5,2)) AS 'Percent of Total for Shift Date'
FROM ProductionData
ORDER BY ShiftDate, Shift, LineNbr, PartNbr
December 5, 2013 at 8:43 am
I can't believe that a supposed professional would lift code from another without at least attributing that code to the owner in some manner.
--This is actual code I wrote for a stored procedure that assigns expenses based on the proportion
-- of a security that may be held by multiple portfolios within the firm on any given date.
--Test Data looks like
DECLARE @CL_GLDate_Holdings TABLE
(GL_Effective_Date date,
Trader varchar(4),
AMH_SEC_ID int,
root_Reference_ID varchar(6),
HoldingTradeType varchar(1),
Held int);
INSERT INTO @CL_GLDate_Holdings
VALUES
('2013-12-02','P001',1234,'000001','P','120000'),
('2013-12-02','P001',1234,'000002','P','100000'),
('2013-12-02','P001',1234,'000003','P','100000'),
('2013-12-02','P002',1234,'000004','S','150000'),
('2013-12-02','P002',1234,'000005','S','200000'),
('2013-12-02','P003',1234,'000006','P','220000'),
('2013-12-02','P003',1234,'000007','P','100000'),
('2013-12-02','P003',1234,'000008','P','100000'),
('2013-12-02','P004',1234,'000009','S','140000'),
('2013-12-02','P005',1234,'000010','P','150000'),
('2013-12-02','P005',1234,'000011','P','120000'),
('2013-12-02','P005',1234,'000012','P','140000'),
('2013-12-02','P005',1234,'000013','P','130000'),
('2013-12-02','P006',1234,'000014','S','190000'),
('2013-12-02','P006',1234,'000015','S','200000')
SELECT cgh.GL_Effective_Date, cgh.Trader, cgh.AMH_SEC_ID, cgh.root_Reference_ID, cgh.HoldingTradeType,
cgh.Held CostLotHeld,
SUM(coalesce(cgh.Held,0.0)) OVER (partition by cgh.GL_Effective_Date, cgh.Trader, cgh.AMH_SEC_ID, cgh.HoldingTradeType) TraderHeld,
SUM(coalesce(cgh.Held,0.0)) OVER (partition by cgh.GL_Effective_Date, cgh.AMH_SEC_ID, cgh.HoldingTradeType) FirmHeld
FROM @CL_GLDate_Holdings cgh
ORDER BY 5,Trader,4
Jim
December 5, 2013 at 8:52 am
Hope that each pro has more principles.
Here's some example to get basic job history information for rolling month. It was passed to me, hope this help!
SELECT
JobName,
ROW_NUMBER() OVER(PARTITION BY JobName ORDER BY runTime) orderR,
RunTime,
Duration,
Status
FROM (
SELECT
JobName = job_name,
RunTime = run_datetime,
Duration = SUBSTRING(run_duration,1,2) + ':' + SUBSTRING(run_duration,3,2) + ':' + SUBSTRING(run_duration,5,2),
Status = run_status
FROM (
SELECT
j.name AS job_name,
run_datetime = CONVERT(DATETIME,RTRIM(run_date)) +
(run_time * 9 + run_time%10000 * 6 +
run_time%100 * 10) / 2160000,
run_duration = RIGHT('000000' +
CONVERT(VARCHAR(6),run_duration), 6),
run_status = CASE run_status
WHEN 0 THEN 'Failure'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'Running'
ELSE 'Other: ' +
Convert(VARCHAR,run_status)
END
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j ON (h.job_id = j.job_id)
WHERE step_id = 0) x
) z
WHERE DATEDIFF(mm,RunTime,getdate()) < 1
December 5, 2013 at 8:52 am
Thanks for the opportunity to win a great reference book!
USE SantaDelivery2013;
GO
SELECT ROW_NUMBER() OVER(PARTITION BY Country ORDER BY GoodDeedsYTD DESC) AS "Row Number",
p.LastName, s.GoodDeedsYTD, a.Country
FROM Children.GoodDeedDoer AS s
INNER JOIN Person.Child AS p
ON s.RandomActID = p.RandomActID
WHERE KindnessID IS NOT NULL
AND GoodDeedsYTD <> 0
ORDER BY Country;
GO
December 5, 2013 at 8:52 am
A example of Row_number()
Suppose that we want to know the number of executions and the duration's average of the procedure's executions of our application in the databases's customers.
We create the table dbo.TRC,
CREATE TABLE dbo.TRC
(
EventSequence bigint NOT NULL,
DatabaseName nchar(16) NOT NULL,
SPID int NULL,
TransactionID bigint NULL,
XactSequence bigint NULL,
TextData nvarchar(max) NULL,
Objectid int NULL,
SchemaP nvarchar(5) NULL, -- is the schema owner of the procedure, information extracted of the column TextData
Objectname nvarchar(128) NULL,
StartTime datetime NULL,
Duration bigint NULL,
CPU int NULL,
EndTime datetime NULL,
KPet int NULL, -- is the datalenght of the value's column TextData
ApplicationName nvarchar(256) NULL
CONSTRAINT [Pk_TRC] PRIMARY KEY CLUSTERED (DatabaseName ASC,EventSequence ASC)
) ON [Primary]
and we fill it with a trace executed in the customers, that has the eventclass 10, RPC completed.
''
declare @z as bigint
declare @sec as decimal(30,8)
select @sec=datediff(s,MIN(starttime),MAX(starttime)) from dbo.TRC
set @z=0;
with a as
(select databasename,SchemaP,objectname,
COUNT(*) as Cexec,@sec/COUNT(*) as IntervalSecondsPerExecution,
sum((case when cpu=0 and transactionId is null then 1 else 0 end)) as CexecC0Tnull,
sum((case when cpu=0 and transactionId is not null then 1 else 0 end)) as CexecC0Tnotnull,
sum((case when cpu<>0 and transactionId is null then 1 else 0 end)) as CexecC1Tnull,
sum((case when cpu<>0 and transactionId is not null then 1 else 0 end)) as CexecC1Tnotnull,
sum((case when cpu=0 and transactionId is null then duration else @z end)) as TimeC0Tnull,
sum((case when cpu=0 and transactionId is not null then duration else @z end)) as TimeC0Tnotnull,
sum((case when cpu<>0 and transactionId is null then duration else @z end)) as TimeC1Tnull,
sum((case when cpu<>0 and transactionId is not null then duration else @z end)) as TimeC1Tnotnull,
AVG(duration) as AvgD,
AVG(cast(KPet as bigint)) AvgKBPet
from dbo.trc
group by databasename,schemaP,objectname
)
select ROW_NUMBER() over(partition by databasename
order by Cexec desc,
CexecC1Tnotnull desc,
CexecC1Tnull desc,
CexecC0Tnotnull desc,
CexecC0Tnull,
schemaP,
objectname) as OrderCexec,
* -- into dbo.Cexec
from a
If we had generated the table dbo.Cexec with the above query, we might execute this query:
select ROW_NUMBER() over(partition by databasename
order by AvgD desc,
TimeC1Tnotnull/(case when CexecC1Tnotnull=0 then 1 else CexecC1Tnotnull end) desc,
TimeC1Tnull/(case when CexecC1Tnull=0 then 1 else CexecC1Tnull end) desc,
TimeC0Tnotnull/(case when CexecC0Tnotnull=0 then 1 else CexecC0Tnotnull end) desc,
TimeC0Tnull/(case when CexecC0Tnull=0 then 1 else CexecC0Tnull end) desc,
schemaP,
objectname) as OrdenAvg,
databasename,schemaP,objectname, AvgD,
TimeC0Tnull/(case when CexecC0Tnull=0 then 1 else CexecC0Tnull end) as AvgC0Tnull,
TimeC0Tnotnull/(case when CexecC0Tnotnull=0 then 1 else CexecC0Tnotnull end) as AvgC0Tnotnull,
TimeC1Tnull/(case when CexecC1Tnull=0 then 1 else CexecC1Tnull end) as AvgC1Tnull,
TimeC1Tnotnull/(case when CexecC1Tnotnull=0 then 1 else CexecC1Tnotnull end) as AvgC1Tnotnull
from dbo.Cexec
Thanks and i'm sorry for my english, it's very very bad.
Bye.
Viewing 15 posts - 106 through 120 (of 287 total)
You must be logged in to reply to this topic. Login to reply