December 5, 2013 at 10:49 am
I always forget you use an aggregate window function on an existing aggregate function
USE AdventureWorks
SELECT
SalesPersonID,
YEAR(OrderDate) AS [Yr],
SUM(TotalDue) AS TotalSales,
SUM(TotalDue) / SUM(SUM(TotalDue)) OVER (PARTITION BY YEAR(OrderDate)) AS PctTotal
FROM Sales.SalesOrderHeader soh
GROUP BY SalesPersonID, YEAR(OrderDate)
December 5, 2013 at 10:51 am
There has been quite a bit of very useful scripts being posted. I'd like to share one that utilizes a neat technique that I learned about right here on SSC.
First - I already have the book, so do NOT enter me in the contest. I'm posting this strictly in the interest of sharing.
The goal of this script is to treat multiple, consecutive rows (as determined by an ORDER) the same.
Business case:
I have RFID chips attached to equipment in a hospital, and I have sensors mounted about the hospital to track their location. Each sensor is, by virtue of where it is physical mounted, assigned to a location. However, some locations within the hospital require multiple sensors to cover the entire location (for instance, an emergency room).
I want to track how many trips a piece of equipment makes to each location (whether that sensor reading was trip 1, trip 2, trip 3, etc.). The caveat is that multiple consecutive sensor readings from the same location are treated as the same trip.
So, let's start off with the sample data:
IF OBJECT_ID('tempdb..#HistoryLoc','U') IS NOT NULL DROP TABLE #HistoryLoc;
CREATE TABLE #HistoryLoc (
TagNo INTEGER,
FirstSeen DATETIME,
LocationGroup VARCHAR(50)
);
INSERT INTO #HistoryLoc (TagNo, FirstSeen, LocationGroup)
VALUES (1,'2011-02-24T06:12:55.420','InED'),
(1,'2011-02-24T06:43:49.540','InOther'),
(1,'2011-02-24T07:06:50.053','InED'),
(1,'2011-02-24T07:32:09.593','InOther'),
(1,'2011-02-25T00:01:25.343','InOther'),
(1,'2011-02-25T14:48:01.417','InOther'),
(2,'2011-03-14T16:52:39.623','InOther'),
(2,'2011-03-14T16:53:51.843','InED'),
(2,'2011-03-14T16:55:33.577','InED'),
(2,'2011-03-14T16:58:06.387','InED'),
(2,'2011-03-14T17:00:24.873','InRadiology'),
(2,'2011-03-14T17:02:59.747','InED'),
(2,'2011-03-14T17:04:57.993','InOther'),
(2,'2011-03-14T19:21:09.213','InED'),
(2,'2011-03-14T19:22:28.493','InOther'),
(2,'2011-03-14T19:23:09.117','InOther'),
(2,'2011-04-05T19:34:37.017','InOther');
SELECT *
FROM #HistoryLoc
ORDER BY TagNo, FirstSeen;
The following code using two ROW_NUMBER calculations with a slightly different PARTITION BY clause. When subtracted from each other, they allow the subsequent rows in the same location to be assigned the same value. I created this with a lot of CTEs to make it easy to see what each section is doing. Play with it to see what they are doing. Notes are in the code to explain what is happening.
WITH cte AS
(
/*
First get all of the columns from the table, and add two ROW_NUMBER calculations.
They are both ordered the same. They also start off with the same partitioning,
but the second one adds an additional column to the partitioning. This second
column controls how consecutive rows are treated the same.
*/
SELECT *,
RN1 = ROW_NUMBER()
OVER (PARTITION BY TagNo
ORDER BY FirstSeen),
RN2 = ROW_NUMBER()
OVER (PARTITION BY TagNo, LocationGroup
ORDER BY FirstSeen)
FROM #HistoryLoc
)
, cte2 AS (
/*
Now, subtract the second ROW_NUMBER calculation from the first one.
The result of this is that consecutive rows with the same value in the second
column of the PARTITION BY clause in the second ROW_NUMBER will be assigned
the same value.
Whenever the second column changes, then the subtraction will cause that row to
have a different value (from the preceding row). As long as the value is the
same, both values will be incremented by one, giving consecutive rows the same
value, so that these rows can subsequently be treated the same.
*/
SELECT *, Grp = RN1 - RN2
FROM cte
)
, cte3 AS (
/*
Now apply DENSE_RANK to get the trip number.
(Applying RANK to show how this doesn't quite work - see tag2, the 4th InED row.)
*/
SELECT *,
Ranking = RANK()
OVER (PARTITION BY TagNo, LocationGroup
ORDER BY Grp),
DenseRank = DENSE_RANK()
OVER (PARTITION BY TagNo, LocationGroup
ORDER BY Grp)
FROM cte2
)
, cte4 AS
(
/*
Using the DENSE_RANK, build the string with the location and trip #.
*/
SELECT *, Trip = LocationGroup + '_Trip' + CONVERT(VARCHAR(10), DenseRank)
FROM cte3
)
SELECT *
FROM cte4
ORDER BY TagNo, FirstSeen;
Now, think about how you would have solved this problem without using the windowing functions.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 5, 2013 at 10:59 am
I personally like windows functions and use them often. here is a code snippet from me.
-----------Updates when all same except Region
UPDATE <tbl1>
SET <tbl1>.active_code='N'
FROM
(
SELECT
rank1,member_id,benefit_carrier,
benefit_region,benefit_subprogram,
benefit_group, enrollment_date,row_created max_updated,
exit_date,active_code
FROM (
SELECT RANK() OVER (PARTITION BY member_id,benefit_plan,
benefit_carrier,benefit_product,
enrollment_date order by row) as rank1,
* FROM (
SELECT ROW_NUMBER() OVER (ORDER BY member_id) row ,
* from dba.<tbl2> WHERE
member_id IN (
SELECT
member_id
--, COUNT(*) AS coverage_count
FROM
<tbl2> (NOLOCK)
WHERE
exit_date IS NULL
AND active_code = 'Y'
GROUP BY
member_id
HAVING
COUNT(*) > 1
)
AND exit_date IS NULL
AND active_code = 'Y'
)k
)k1 WHERE rank1>1)CCMS INNER JOIN
<tbl2> CCMSUPDATE
ON CCMS.member_id=CCMSUPDATE.member_id
WHERE max_updated<>CCMSUPDATE.row_created
AND CCMSUPDATE.exit_date IS NULL
December 5, 2013 at 11:01 am
You guys rock.
Here's an example from a table of appointments that calculates the number of days from the previous appointment for the person:
select
Appt.AppointmentId
, Appt.ScheduledStart
, datediff
(dd,
lag(Appt.scheduledstart) over
(
partition by
Appt.PersonId
, Appt.PersonName /*partion by name&id due to inexplicable nulls in id field*/
order by Appt.scheduledstart
)
, Appt.scheduledstart
)DaysSincePrevious
from
Appointment Appt
December 5, 2013 at 11:03 am
SELECT ROW_NUMBER() OVER(ORDER BY (createDate)) AS RowNum, createDate
FROMdbo.Contacts(nolock) con
Where con.orgGroupId = 1
December 5, 2013 at 11:11 am
for some running totals, something i had open today
SELECT PerfCat,
RLA,
M,
org_no,
bldg_no,
ThirtyPercentRead,
ThirtyPercentMath,
SUM(rla) OVER (PARTITION BY org_no, bldg_no ORDER BY PerfCat) AS running_RLA,
SUM(m) OVER (PARTITION BY org_no, bldg_no ORDER BY PerfCat) AS running_M
FROM EDEN.file_c171
December 5, 2013 at 11:23 am
If you only know two names of SQL Server experts, one should be Ben-Gan, and the other should be Delaney.
Itzik Ben-Gan is a true master of the science and art of T-SQL.
December 5, 2013 at 11:26 am
Hope I get opportunity to attend Itzik's seminar one day.
Create table #tbl1( empid varchar(4), name varchar(8))
insert into #tbl1(empid, name)
Select 'abc','Mayank'
union
Select 'xyz','Mayank2'
SELECT empid,name,ROW_NUMBER() OVER(ORDER BY empid DESC) AS Row
FROM #tbl1
December 5, 2013 at 11:30 am
Ethics and responsibility are much less important than they have been in the past. But, to take someone else's work and get Paid for it is worse!
December 5, 2013 at 11:47 am
Chapeau to SQL Server Central!
December 5, 2013 at 11:47 am
SELECT PlayerID
,score
,ROW_NUMBER() OVER(PARTITION BY PlayerID ORDER BY scoredate) as 'rank'
FROM Scores
December 5, 2013 at 11:51 am
Great catch SSC.
SELECT
ProductID,
SUM(LineTotal) OVER (PARTITION BY ProductID) [ProductTotal]
FROM Sales.SalesOrderDetail
WHERE ModifiedDate = '07/31/2008'
December 5, 2013 at 11:51 am
One of my examples that I use to demonstrate various statements.
/* Demonstration of temporary table destruction, creation and population;
* table modification; conditional updating; and windowing functions
*/
IF OBJECT_ID('tempdb.dbo.#ranks') IS NOT NULL
DROP TABLE #ranks;
CREATE TABLE #ranks (
Name varchar(30)
,Birthday datetime
,height decimal(3,2)
);
INSERT INTO #ranks
SELECT 'Alberto','1/11/2013 1:00', .3 UNION ALL
SELECT 'Beryl','3/22/2013 2:00', .4 UNION ALL
SELECT 'Chris','5/2/2013 3:00', .3 UNION ALL
SELECT 'Debby','7/13/2012 4:00', .2 UNION ALL
SELECT 'Ernesto','9/24/2012 23:59:59', .2 UNION ALL
SELECT 'Florence', '11/5/2012 19:37', .5 UNION ALL
SELECT 'Gordon','12/25/2009 2:31', .45 UNION ALL
SELECT 'Helene','5/23/2008 1:15', .52 UNION ALL
SELECT 'Isaac','3/31/2007 18:11', .56 UNION ALL
SELECT 'Joyce','2/28/2007 17:00', .60 UNION ALL
SELECT 'Kirk','1/24/2006 3:45', .71 UNION ALL
SELECT 'Leslie','10/2/2006 2:30', .63 UNION ALL
SELECT 'Michael', '10/2/2004 1:15', .60
;
ALTER TABLE #ranks
ADD
AgeInYear int null
,AgeInMonth int null
;
UPDATE #ranks
SET AgeInMonth = DATEDIFF(mm,Birthday,GETDATE())
WHERE DATEDIFF(mm,Birthday,GETDATE())<=23
;
UPDATE #ranks
SET AgeinYear = DATEDIFF(yy,Birthday,getdate())
Where AgeInMonth IS NULL
;
select * from #ranks;
SELECT Name
,AlphaRowNumber = ROW_NUMBER() OVER(ORDER BY Name)
-- alternate style below
--,ROW_NUMBER() OVER(ORDER BY Name) AS AlphaRowNumber
,Height
,HeightRank = RANK() OVER(ORDER BY Height DESC)
/* order in heights */
,DenseHeight = DENSE_RANK() OVER(Order by Height DESC)
/* dense rank order in heights */
,CAST(Birthday AS DATE) AS 'Birthday'
/* strip out the time value */
,COALESCE( AgeInYear, AgeInMonth, 0 ) AS Age
/* first non-null value is inserted */
,CASE WHEN AgeInYEAR IS NULL THEN 'Month'
ELSE 'Year' END AS [Units]
/* conditional row values */
,Eldest = RANK() OVER(PARTITION BY AgeInYear ORDER BY Birthday)
/* ranks the ages in each age of year */
FROM #ranks;
/* Note that the last OVER's ORDER phrase orders the entire table
unless there is a separate ORDER clause*/
December 5, 2013 at 11:54 am
Free book? Free as in Beer? 😎 OK, here's some code:
SELECT [TABLE_SCHEMA]
,[TABLE_NAME]
,[TABLE_TYPE]
,ROW_NUMBER() OVER ( ORDER BY [TABLE_SCHEMA], [TABLE_NAME]) AS ROW_NUMBER
FROM [INFORMATION_SCHEMA].[TABLES]
ORDER BY 4
December 5, 2013 at 11:55 am
I would really like a copy of the book!
(Select SchoolID, SchoolYR,
ROW_NUMBER() OVER(PARTITION BY SchoolID ORDER BY UPDT_DTTM DESC) AS RowNum
from dbo.SCHOOLYEARDEMOGRAPHICS) AS T
Viewing 15 posts - 166 through 180 (of 287 total)
You must be logged in to reply to this topic. Login to reply