December 5, 2013 at 8:56 am
There are numerous ways to generate a quick numbers table. I use this one.
--Numbers table 1M rows.
;with seed(col) as
(
select top 1000 1 [x] from sys.objects t1
cross join (select top 1000 1 [x] from sys.objects) t2
)
,Numbers(col) as (select 1 from seed cross join seed [x])
select top 10000 row_number() over (order by col) [rownumber]
from Numbers
December 5, 2013 at 8:57 am
This isn't the first time on SSC that I've seen someone write an article that other readers quickly noted contained plagerized work (or lacked references crediting the original author). Fortunately, this kind of thing is a rare occurrence on SSC.
As for Itzik Ben-Gan -- he is the master! I attended one of his pre-conference workshops back in 2008, which was all about the power of windows functions, even in its limited implementation back then -- Amazing.
Below is a goofy-random script that ranks students scores on assignments, wherein it's assumed that a teacher lets students repeat assignments, and ultimately take the best score for the assignments completed. (Kind of nutty, but the sql works).
;
WITH cte
AS ( SELECT TOP 100
Student = ABS(CHECKSUM(NEWID())) % 10 + 1
,Score = ABS(CHECKSUM(NEWID())) % 100 + 1
,Assignment = ABS(CHECKSUM(NEWID())) % 6 + 1
FROM syscolumns x
,syscolumns y
),
cte_StudentScoresRanked
as ( SELECT student
,Assignment
,Score
,ScoreRank = ROW_NUMBER() OVER ( partition by student order by Score desc )
from cte
)
Select Student
,Assignment
,Score
from cte_StudentScoresRanked
where ScoreRank = 1
December 5, 2013 at 8:59 am
Very useful for finding the most recent transaction per Account (or customer, facility, etc., etc...)
;WITH [transxRows] AS (
SELECT *,
ROW_NUMBER() OVER(
PARTITION BY [AccountID]
ORDER BY [TransxTimestamp] DESC ) AS [RowRank]
FROM [Transx]
)
SELECT *
FROM [transxRows]
WHERE [RowRank] = 1;
December 5, 2013 at 9:02 am
Plagarism on the internet, shocking. No, it's not OK to do it, but it makes you wonder how much info actually remains in the hands of the author. It seems like SQL Server Central has little to apologize for, since it was someone else at fault, but it is great that you are acknowledging the true author in this fashion.
I used this query to import a list of companies and employees from an Excel spreadsheet (in an SSIS Dataflow Source):
SELECT'Y' as Active
, 64 as CREATED_BY_USER
, GetDate() as CREATED_DATE
, 'CMSSTAPV' as S_CMSSTATUS
, DENSE_RANK() Over( order by CompanyName)
+ (Select SerialNo - 1
From Temp.SERIALNO
Where SFNAME = 'CMS_CID') as CID
, 'CMAPRCOM' as CompanyCMSType
, CompanyName
, CompanyShortName
, ROW_NUMBER() Over(Order by CompanyName,FullName)
+ (Select SerialNo - 1
From Temp.SERIALNO
Where SFNAME = 'CMS_CID')
+ (Select COUNT(Distinct CompanyName)
FROM [Temp].[AppraisalCompanyInfo]) as CID2
, 'CMAPPRSR' as AppraiserCMSType
, FullName
, ApprShortName
, LicenseState
, 'CMSTLAPP' as CMSTitle
, Certified
, Notes
, ROW_NUMBER() Over(Order by CompanyName,FullName)
+ (Select SerialNo - 1
From Temp.SERIALNO
Where SFNAME = 'CMS_PHN') as SERIALNOPhone
, 'CMSPTWRK' as PhoneType
, Phone
, ROW_NUMBER() Over(Order by CompanyName,FullName)
+ (Select SerialNo - 1
From Temp.SERIALNO
Where SFNAME = 'CMS_EML') as SERIALNOEmail
, 'CMSETWRK' as EmailAddressType
, EMailAddress
, DENSE_RANK() Over( order by Address)
+ (Select SerialNo - 1
From Temp.SERIALNO
Where SFNAME = 'CMS_ADR') as ROWSERIALNOAddr
, 'CMSATWRK' as AddressType
, Address1
, Address2
, City
, [State]
, ZipCode
FROM Temp.AppraisalCompanyInfo
order by Address
December 5, 2013 at 9:11 am
Once upon a time, in the dark ages of printed textbooks and such, plagarism was not only frowned upon, it was cause for immediate dismissal or expulsion from university.
To claim the work of another as your own is at best fraud and at worst theft.
Always cite your sources but use them as sources of information, not the sources of your content!
December 5, 2013 at 9:11 am
Here is some code I use to locate and deal with duplicates in a table.
WITH cte
AS (SELECTtwd.idtblWithDups
,ft.idfkTbl
,ROW_NUMBER() OVER (PARTITION BY ft.idfkTbl ORDER BY twd.idtblWithDups) 'RowRank'
FROMdbo.tblWithDups twd
JOIN dbo.associativeTbl at ON at.idtblWithDups = twd.idtblWithDups
JOIN dbo.fkTbl ft ON ft.idfkTbl = at.idfkTbl
WHEREft.createdByAppName = 'I2E'
)
SELECT *
INTO ##tblWithDups
FROM cte
WHERE RowRank > 1
December 5, 2013 at 9:14 am
SELECT
RANK() OVER(PARTITION BY [Name] ORDER BY [DateTime]) As [EntryOrder]
,[Name]
,[DateTime]
,[EntryPoint]
FROM
(
SELECT 'Billy' As [Name], '2/8/2002 6:00' As [DateTime], 'Back Door' As [EntryPoint]
UNION ALL
SELECT 'Bob' As [Name], '2/8/2002 6:00' As [DateTime], 'Back Door' As [EntryPoint]
UNION ALL
SELECT 'Bob' As [Name], '2/8/2002 6:01' As [DateTime], 'Back Door' As [EntryPoint]
UNION ALL
SELECT 'Bug R. D.' As [Name], '2/8/2002 6:00' As [DateTime], 'Back Door' As [EntryPoint]
UNION ALL
SELECT 'Bug R. D.' As [Name], '2/8/2002 6:01' As [DateTime], 'Back Door' As [EntryPoint]
UNION ALL
SELECT 'Bug R. D.' As [Name], '2/8/2002 6:02' As [DateTime], 'Back Door' As [EntryPoint]
UNION ALL
SELECT 'H. Berry' As [Name], '2/8/2002 9:00' As [DateTime], 'Ball Room' As [EntryPoint]
UNION ALL
SELECT 'H. Berry' As [Name], '2/8/2002 9:01' As [DateTime], 'Ball Room' As [EntryPoint]
UNION ALL
SELECT 'H. Berry' As [Name], '2/8/2002 9:02' As [DateTime], 'Ball Room' As [EntryPoint]
UNION ALL
SELECT 'H. Berry' As [Name], '2/8/2002 9:03' As [DateTime], 'Ball Room' As [EntryPoint]
) As Entries
December 5, 2013 at 9:14 am
The downside is that he was plagiarized. The upside is that SQL Server Central has integrity and all of these folks know about his book now. I looked at the book's preview on Amazon and it looks like it's packed with useful information on the mysterious OVER clause.
Here's what I learned from my perusal of the book:
with myproducts as (
select row_number() over(order by productid) as RowNum , * from product
)
select * from myproducts where RowNum between 5 and 10
December 5, 2013 at 9:15 am
Windowing functions are incredibly helpful and time-saving. Without the new functions, I would have had to use temporary tables or possibly cursor in some situations, but some things are so easy with the new functions.
Below, I've used them to determine when stock will be available, based on beginning position, sales orders, and purchase orders.
SELECT
Section
, StockCode
, OrderNumber
, DateOrderBy
, AllocatedOrSORunningTotal = SUM(AllocPlusSORTNumber) OVER (PARTITION BY StockCode ORDER BY DateOrderBy, Hierarchy, OrderNumber)
, RunningTotal = SUM(ThisChange) OVER (PARTITION BY StockCode ORDER BY DateOrderBy, Hierarchy, OrderNumber)
+ COALESCE((select SUM(S2.QtyAllocated + S2.ThisChange ) from Stock_Changes_By_Date S2 WHERE S2.StockCode = S1.StockCode AND S2.DateOrderBy >= S1.DateOrderBy AND Section = 'S' ) , 0)
, AllocatedRunningTotal = SUM(AllocRTNumber) OVER (PARTITION BY StockCode ORDER BY DateOrderBy, OrderNumber)
, PhysicalRunningTotal = SUM(ThisChange) OVER (PARTITION BY StockCode ORDER BY DateOrderBy, OrderNumber)
FROM Stock_Changes_By_Date S1
WHERE StockCode = 'x'
I'd love to win that book. 🙂
December 5, 2013 at 9:18 am
I use the Over clause in this statememt to calculate tiered incentives (the more an employee sells, the more they earn)
DECLARE @TellerID as smallint
DECLARE @StartDate as smalldatetime
DECLARE @EndDate as smalldatetime
SET @TellerID = '2061'
SET @StartDate = '11/1/2013'
SET @EndDate = '11/30/2013'
DECLARE @Start as smalldatetime
SET @Start = CAST(month(@endDate) as varchar(2)) + '/01/' + CAST(YEAR(@endDate) as varchar(4))
SET @EndDate = DATEADD(m, 1, @Start)
SET @EndDate = DATEADD(d, -1, @EndDate)
SELECTti.TieredProductName
,tl.levelNumber
,cast(tl.startQuantity as varchar(3)) + ' - ' + isnull(cast(tl.endQuantity as varchar(15)),'or more') as levelRange
,tl.incentiveamount
,count(ss.Incentive) as Quantity
,isnull(sum(ss.Incentive),0) as Payout
FROM SymWarehouse.sales.tieredlevel as tl inner join
SymWarehouse.Sales.TieredIncentive as ti on tl.TieredProductID = ti.TieredProductID left join
(
SELECT
ROW_NUMBER() OVER(PARTITION BY [TellerID], tp.TieredProductID ORDER BY SalesDate, [ParentAccount], [Ordinal], [FieldNbr] DESC) as rowNumber
,[TellerID]
,[TellerName]
,tp.TieredProductID
,[ParentAccount]
,[Ordinal]
,[FieldNbr]
,[SalesType]
,[Incentive]
,SalesDate
FROM [SymWarehouse].Sales.[SalesSummary] as ss inner join
SymWarehouse.sales.TieredProduct as tp on ss.SalesType = tp.productid inner join
SymWarehouse.Sales.TieredIncentive as ti on tp.TieredProductID = ti.TieredProductID
WHERE SalesDate between @Start and @endDate and TellerID = @tellerID
and ti.StartDate <= SalesDate and (ti.EndDate is null or ti.EndDate >= SalesDate)
) as SS on ss.TieredProductID = tl.TieredProductID and ss.rowNumber >= tl.startquantity and ss.rowNumber <= ISNULL(tl.endquantity, 999)
WHERE ti.StartDate <= @Start and (ti.EndDate is null or ti.EndDate >= @endDate)
GROUP BY ti.TieredProductName, tl.levelnumber ,tl.startQuantity, tl.endQuantity, tl.incentiveamount
ORDER BY ti.TieredProductName, tl.levelnumber
December 5, 2013 at 9:20 am
Looking to see if a tenant has a rent increase, only bringing back the first one...
WITH PREP AS(
select
ROW_NUMBER() OVER(partition by BU.bu_id, vUnits.UnitNumber order by RS.IncreaseStartDate) as 'RowNumber',
UnitNumber,UnitSF, Lease, Tenant,Property, IncreaseStartDate, IncreasePSF
from vUnits
join PROPERTIES BU on vUnits.bu_id = bu.bu_id
join dbo.RENT_BUMPS RS on vUnits.lea_num = RS.rs_lea_num
)
SELECT * FROM PREP where RowNumber = 1
bigcraiginjax
December 5, 2013 at 9:20 am
Here is my bit. I did not go through the whole list of responses, so this might be duplicated. This is the most useful part of windowing I have found thus far - deleting duplicate rows where there is no unique identifier.
;With List as
(Select [UserID], [AppID], ROW_NUMBER() Over (Partition by [UserID], [AppID] Order By [UserID]) as Row
FROM [AppUser])
Delete From List
where Row > 1
Yes, it really is that simple. I am looking forward to understanding and using Windowing more.
December 5, 2013 at 9:23 am
I would highly recommend this book to anyone learning TSQL Windowing. I share an Amazon link to Itzak's books in all my classes.
SELECT e.LastName + ', '+ e.FirstName AS EmpName, p.Name AS Product, s.Quantity, s.ListPrice,
RANK() Over(Partition By p.Name, Order By s.ListPrice) as ProductSold
FROM HumanResources.Employee as e
JOIN Accounting.Sales as s
ON e.empid = s.empid
JOIN Production.Product as p
ON p.prodid = s.prodid
December 5, 2013 at 9:25 am
Keep up the good work!
SELECT
Id
,[1] As Note1
,[2] As Note2
,[3]As Note3
FROM
(
SELECT a.Id, a.Note
, RANK() OVER (PARTITION BY a.Id ORDER BY a.CreatedDate DESC, a.Id DESC) AS RankNum
FROM (SELECT Id, Note, CreatedDate FROM MyTable (NOLOCK)) a
) p
PIVOT
(
MAX(Note) FOR RankNum IN ([1], [2], [3])
) AS pvt
December 5, 2013 at 9:25 am
I SUM() INTO the apologizes to Ben-Gan && I would love to keep improving my knowledge with this book!
Enjoy the function below!
GO
DECLARE @N BIGINT;
SET @N = 100;
WITH
[DATA0] AS (SELECT 1 'Col' UNION ALL SELECT 1),
[DATA1] AS (SELECT 1 'Col' FROM [DATA0] AS [01] CROSS JOIN [DATA0] AS [02]),
[DATA2] AS (SELECT 1 'Col' FROM [DATA1] AS [11] CROSS JOIN [DATA1] AS [12]),
[DATA3] AS (SELECT 1 'Col' FROM [DATA2] AS [21] CROSS JOIN [DATA2] AS [22]),
[DATA4] AS (SELECT 1 'Col' FROM [DATA3] AS [31] CROSS JOIN [DATA3] AS [32]),
[RESULT] AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) 'N' FROM [DATA4])
SELECT TOP (@N) [N] FROM [RESULT] ORDER BY [N];
GO
Viewing 15 posts - 121 through 135 (of 287 total)
You must be logged in to reply to this topic. Login to reply