December 5, 2013 at 7:08 am
Love those windowing functions! Here's a silly example:
select one,two, ROW_NUMBER() over(order by two desc) as rn
from (values (1,2),(1,4),(1,6)) vals(one,two)
December 5, 2013 at 7:10 am
I am often amazed by the number of people who will plagiarize another's work merely to create a blog post or comment. I find this most often when someone copies an entry direct from BOL, posts to their blog, and does not attribute the content to BOL. Along the same lines are forum posters who feel the need to repeat exactly what a prior entry contained. If you can't post an original thought then just post the link to the relevant original content.
Now, at the risk of appearing hypocritical, below is a query I use to monitor database growth. The bones of the query I copied from a long forgotten web page (apologies to the original author) but have subsequently modified for my particular use.
;
WITH BackupSize ( DBName, BkupSize, BkupDate, ROW, DAY, WEEK, MONTH )
AS ( SELECT database_name
, backup_size
, backup_start_date
, ROW_NUMBER() OVER ( PARTITION BY database_name ORDER BY backup_start_date ) ROW
, DATEADD(DAY,
DATEDIFF(DAY, 0,
backup_start_date),
0) DAY
, DATEADD(WEEK,
DATEDIFF(WEEK, 0,
backup_start_date),
0) WEEK
, DATEADD(MONTH,
DATEDIFF(MONTH, 0,
backup_start_date),
0) MONTH
FROM backupset
) ,
BackupGrowth ( DBName, BkupSize, bkupDate, Growth, DAY, WEEK, MONTH )
AS ( SELECT S.DBName
, S.BkupSize
, S.BkupDate
, ISNULL(S.BkupSize
- X.BkupSize, 0) Growth
, S.Day
, S.Week
, S.Month
FROM BackupSize S
LEFT JOIN BackupSize X
ON S.bkupDate = X.bkupDate
AND S.Row = X.Row + 1
)
SELECT DBName
, BkupSize
, bkupDate
, Growth
, AVG(BkupSize) OVER ( PARTITION BY DBName,
Day ) AvgDailySize
, SUM(Growth) OVER ( PARTITION BY DBName,
Day ) DailyGrowth
, AVG(BkupSize) OVER ( PARTITION BY DBName,
Week ) AvgWeeklySize
, SUM(Growth) OVER ( PARTITION BY DBName,
Week ) WeeklyGrowth
, AVG(BkupSize) OVER ( PARTITION BY DBName,
Month ) AvgMonthlySize
, SUM(Growth) OVER ( PARTITION BY DBName,
Month ) MonthlyGrowth
FROM BackupGrowth
ORDER BY DBName
, bkupDate
Gordon Pollokoff
Wile E. is my reality, Bugs Bunny is my goal - Chuck Jones
Walking on water and developing software from a specification are easy if both are frozen. - E. Berard
Doing more things faster is no substitute for doing the right things. - S. R. Covey
Any sufficiently advanced bug is indistinguishable from a feature.- R. Kulawiec
December 5, 2013 at 7:17 am
Like to have a book! 😀
SELECT FirstName,LastName,
ROW_NUMBER() over (order by FirstName) as 'Row per FirstName',
RANK() over (order by FirstName) as 'Rank per FirstName'
from Person.Person
December 5, 2013 at 7:19 am
Like to have a book! 😀
SELECT FirstName,LastName,
ROW_NUMBER() over (order by FirstName) as 'Row per FirstName',
RANK() over (order by FirstName) as 'Rank per FirstName'
from Person.Person
December 5, 2013 at 7:19 am
I have the book (so choose someone else) but am posting as a shout out to Mr. Ben-Gan and to second it’s a very good book. (I’m a why person and always wondered – why “OVER”? After reading this book I now get what they were thinking.)
I primarily do reporting and we do a ton of top or bottom X reporting. We also use the row_number for all sorts of things.
SELECT SalesPerson,
Region,
Ranking = RANK () OVER (PARTITION BY Region ORDER BY SUM(Sales) DESC)
FROM Sales
GROUP BY SalesPerson,
Region
December 5, 2013 at 7:23 am
I use ranking functions to find and delete dups:
SELECT * FROM
(
SELECT [pat_info_handle]
,[pat_handle]
,[type]
,[item]
,RANK() OVER(PARTITION BY[pat_handle] + '' + [type] + '' + [item] ORDER BY [pat_info_handle]) RANK
FROM [PatientInfo]
) SQL1 WHERE RANK > 1
I use a composite key in the Partition clause. I then use [pat_info_handle] as an identifier to delete the dups.
December 5, 2013 at 7:23 am
I have great respect for the site for owning the mistake and giving credit where it is properly due.
/* Find the first and last dates for each object */
;WITH MeterVolume AS (
SELECT T.[Date], T.Meter, T.Volume
, ROW_NUMBER() OVER(PARTITION BY Meter ORDER BY [Date]) AS Date_ASC
, ROW_NUMBER() OVER(PARTITION BY Meter ORDER BY [Date] DESC) AS Date_DESC
)
SELECT [Date], Meter, Volume FROM MeterVolume
WHERE (Date_ASC = 1 OR Date_DESC = 1)
ORDER BY Meter, [Date]
December 5, 2013 at 7:31 am
Useful tsql found in 70-431 documentation and modified to meet my needs.
SELECT
OrderId
, SiteId
, ClientId
, DocumentDate
, TotalAmount
, SUM(TotalAmount) OVER(PARTITION BY ClientId
ORDER BY DocumentDate, OrderId, SiteId
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS RunningTotalValue
FROM dbo.CustomerOrder;
December 5, 2013 at 7:34 am
Great announcement from SQLServerCentral.com!
Below is my own written script.
CREATE TABLE #EMPLOYEES
(
ID INT IDENTITY(1,1) NOT NULL,
NAME NVARCHAR(50) NULL,
SALARY DECIMAL(18,2) NULL
)
INSERT INTO #EMPLOYEES VALUES ('Joe',2000)
INSERT INTO #EMPLOYEES VALUES ('Michael',1800)
INSERT INTO #EMPLOYEES VALUES ('Peter',2200)
INSERT INTO #EMPLOYEES VALUES ('Paul',2500)
INSERT INTO #EMPLOYEES VALUES ('David',2700)
INSERT INTO #EMPLOYEES VALUES ('Jomy',1300)
INSERT INTO #EMPLOYEES VALUES ('Jayne',1200)
INSERT INTO #EMPLOYEES VALUES ('Mary',1900)
SELECT * FROM #EMPLOYEES
SELECT
*
FROM
(
SELECT
NAME,
SALARY,
ROW_NUMBER() OVER (ORDER BY SALARY DESC) SALARY_RANK
FROM
#EMPLOYEES
) AS A
WHERE A.SALARY_RANK = 3 /*change value here to find nth highest salary*/
DROP TABLE #EMPLOYEES
December 5, 2013 at 7:35 am
Plagarism.... no good. But kudos to you for doing the "next right thing".
Here's my query:
[font="Courier New"]DECLARE @BillingMonth int = 201312
with cteInvoices as (
SELECT hdr.InvoiceHeaderId
, smry.BillingMonthIndex
, CompanyId = zeezor.dbo.getNodeId(hdr.BillingAccountId, 'Company')
, hdr.TotalAmount
, smry.StatusId
, smry.BillingDate
, nbr = ROW_NUMBER() OVER (partition by dbo.getNodeId(hdr.BillingAccountId, 'Company'), BillingMonthIndex order by BillingDate)
FROM [dbo].[InvoiceHeader] hdr
JOIN dbo.InvoiceSummary smry
ON smry.InvoiceSummaryId = hdr.InvoiceSummaryId
WHERE BillingMonthIndex = @BillingMonth
)
update dbo.InvoiceHeader
set InvoiceNumber = RIGHT('00' + CONVERT(nvarchar,CompanyId),3) + '-' + CONVERT(nvarchar,BillingMonthIndex) + '-' + RIGHT('0' + CONVERT(nvarchar,nbr),2)
from dbo.InvoiceHeader hdr
JOIN cteInvoices inv
ON inv.InvoiceHeaderId = hdr.InvoiceHeaderId
where InvoiceNumber is null[/font]
Have a blessed day,
December 5, 2013 at 7:44 am
SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeGroup ORDER BY EmployeeName), * FROM Employee
December 5, 2013 at 7:45 am
Plagiarism cannot be tolerated but we all get ideas from examples. The idea of the query below came from the OTN Forum SQL and PL/SQL by Chris227. The example was used to de-duplicate a join. Does that make this plagiarism? My example query is:
WITH sample_data
AS (SELECT 1 id, SYSDATE - 10 start_date FROM DUAL
UNION
SELECT 1 id, SYSDATE - 5 start_date FROM DUAL
UNION
SELECT 1 id, SYSDATE - 2 start_date FROM DUAL
UNION
SELECT 1 id, SYSDATE - 1 start_date FROM DUAL
UNION
SELECT 1 id, SYSDATE start_date FROM DUAL
UNION
SELECT 2 id, SYSDATE - 20 start_date FROM DUAL
UNION
SELECT 2 id, SYSDATE - 10 start_date FROM DUAL
UNION
SELECT 3 id, SYSDATE start_date FROM DUAL
UNION
SELECT 3 id, SYSDATE - 10 start_date FROM DUAL
UNION
SELECT 3 id, SYSDATE - 1 start_date FROM DUAL
UNION
SELECT 3 id, SYSDATE - 3 start_date FROM DUAL
UNION
SELECT 4 id, SYSDATE - 1 start_date FROM DUAL
UNION
SELECT 4 id, SYSDATE - 12 start_date FROM DUAL
UNION
SELECT 4 id, SYSDATE - 3 start_date FROM DUAL
UNION
SELECT 4 id, SYSDATE - 40 start_date FROM DUAL)
SELECT *
FROM (SELECT sd.id,
sd.start_date,
RANK ( ) OVER (PARTITION BY id ORDER BY id, start_date DESC) rnk
FROM sample_data sd) data
WHERE data.rnk = 1
ORDER BY data.id;
His original query was
with data as (
select
r.REQUEST_ID
--,r.BOX_no
,r.FILE_NO
,r.CUSTOMER_NO
,r.DISTRICT_NO
,r.DEPARTMENT_NO
,r.ORDER_ID
,a.REGION_CD
,rank() over (partition by r.request_id order by
decode(a.district_no, r.district_no, 0, null, 1, 2)
,decode(a.department_no ,r.department_no, 0, null, 1, 2)
) rnk
from
REQUESTS r
,ADDRESS a
where
r.customer_no = a.customer_no
)
select
*
from data
where
rnk = 1
order by request_id;
December 5, 2013 at 7:46 am
Would love a copy of his book.
The following gives me the annual sales for customers in a given year.
Thanks!
SELECT c.CustomerId [Customer ID]
, DATEPART(yyyy, t.TranDate) [Bill Year]
, SUM(t.SalesAmount) OVER(PARTITION BY c.CustomerId) AS [Yearly Customer Sales]
FROM SALES..Customers c WITH (NOLOCK)
JOIN SALES..Transactions t WITH (NOLOCK) ON t.CustomerId = c.CustomerId
JOIN SALES.DBO.Calendar_Select(DATEPART(yyyy,GETUTCDATE())) cs ON cs.Year = t.TranYear
December 5, 2013 at 7:48 am
I'd love to win a copy of this book too. 🙂
WITH cte AS (
SELECT 1 AS UserID, 1 AS GroupID, 10 As Rating
UNION ALL SELECT 1 AS UserID, 1 AS GroupID, 8 As Rating
UNION ALL SELECT 2 AS UserID, 3 AS GroupID, 9 As Rating
UNION ALL SELECT 3 AS UserID, 1 AS GroupID, 10 As Rating
UNION ALL SELECT 4 AS UserID, 2 AS GroupID, 8 As Rating
UNION ALL SELECT 5 AS UserID, 2 AS GroupID, 1 As Rating
UNION ALL SELECT 6 AS UserID, 3 AS GroupID, 4 As Rating
UNION ALL SELECT 7 AS UserID, 2 AS GroupID, 4 As Rating
UNION ALL SELECT 8 AS UserID, 1 AS GroupID, 6 As Rating
UNION ALL SELECT 9 AS UserID, 1 AS GroupID, 7 As Rating
UNION ALL SELECT 10 AS UserID, 2 AS GroupID, 7 As Rating
UNION ALL SELECT 11 AS UserID, 2 AS GroupID, 8 As Rating
UNION ALL SELECT 12 AS UserID, 3 AS GroupID, 8 As Rating
UNION ALL SELECT 13 AS UserID, 2 AS GroupID, 10 As Rating
UNION ALL SELECT 14 AS UserID, 3 AS GroupID, 8 As Rating
UNION ALL SELECT 15 AS UserID, 2 AS GroupID, 6 As Rating
UNION ALL SELECT 16 AS UserID, 3 AS GroupID, 6 As Rating
UNION ALL SELECT 17 AS UserID, 2 AS GroupID, 1 As Rating
UNION ALL SELECT 18 AS UserID, 3 AS GroupID, 1 As Rating
UNION ALL SELECT 19 AS UserID, 2 AS GroupID, 5 As Rating
UNION ALL SELECT 20 AS UserID, 2 AS GroupID, 5 As Rating
)
SELECT
GroupID, UserID, Rating, RANK() OVER (PARTITION BY GroupID ORDER BY Rating) AS RankWithinGroup, DENSE_RANK() OVER (PARTITION BY GroupID ORDER BY Rating) AS DenseRankWithinGroup
FROM cte
ORDER BY GroupID, Rating, UserID;
December 5, 2013 at 7:50 am
With the number of blog sites I see that say the same as the next, verbatim, it's no surprise to me that work gets lifted and posted under somebody else's name. I admit I borrow ideas and code from blog sites to accomplish my day-to-day work, but I'm not a researcher, which is why I don't blog. The idea of even lifting somebody else's blog and posting it as my own makes me cringe. But, to take a published body of work and post articles on SSC and sell the work as your own......WOW! Mr. Kamil Moscicki should be deeply ashamed!
update dbo.Equity set
emp_rec_rank = ct.rec_rank
--select *
from dbo.Equity eq
inner join (select *, DENSE_RANK() over (partition by empl_id order by file_date desc) as rec_rank from dbo.Equity) ct
on eq.empl_id = ct.empl_id and eq.file_date = ct.file_date
Viewing 15 posts - 76 through 90 (of 287 total)
You must be logged in to reply to this topic. Login to reply