December 5, 2013 at 11:55 am
Had the pleasure of a training course with Mr. Ben-Gan. It was the best training course I ever took.
This gives both file size and total database size in the same query. Since we discussing credit for work. This is derivative of a post by Pinal Dave. ( http://blog.sqlauthority.com/2010/02/08/sql-server-find-the-size-of-database-file-find-the-size-of-log-file/ )
SELECT
DB_NAME(database_id) AS Database_Name
, Name AS Logical_Name
, Physical_Name
, (size*8)/1024 SizeMB
, sum (size*8/1024) OVER (PARTITION BY database_id) TotalDBSizeMB
FROM sys.master_files
ORDER BY database_name
December 5, 2013 at 12:00 pm
select candidate_key,
CASE WHEN ROW_NUMBER() OVER ( PARTITION BY candidate_key, MAX(date_test_taken), LEFT(REPLACE(NAME,'260913','100613'), LEN(ssl.name)- 1) ORDER BY value DESC, REPLACE(ssl.NAME, '260913','100613') DESC ) = 1
THEN RIGHT(ssl.NAME, 1)
ELSE 0
END AS SomeValue
from Table1
December 5, 2013 at 12:01 pm
Here's something I call OVERkill:
with cte_ftSales(SalesPersonNm, SalesQty, SalesAmt)
AS(
SELECT 'Rick', 3, 15.00
UNION ALL
SELECT 'Joe', 12, 3.50
UNION ALL
SELECT 'Tommy', 9, 47.53
UNION ALL
SELECT 'Betty', 3, 7.63
UNION ALL
SELECT 'David', 18, 3.50
)
SELECT
SalesPersonNm,
SalesQty,
SalesAmt,
RANK() OVER(ORDER BY SalesQty) [SalesQtyRank],
DENSE_RANK() OVER(ORDER BY SalesQty) [SalesQtyDRank],
ROW_NUMBER() OVER(ORDER BY SalesAmt) [SalesAmtRowNum],
ROW_NUMBER() OVER(PARTITION BY SalesAmt ORDER BY SalesQty DESC) [SalesAmtRowNumBySalesQty],
SUM(SalesQty) OVER() [TotalSalesQty],
SUM(SalesAmt) OVER() [TotalSalesAmt],
SUM(SalesQty) OVER(PARTITION BY SalesAmt) [TotalSalesQtySharingSalesAmt],
SUM(SalesAmt) OVER(ORDER BY SalesAmt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) [RollingSalesAmt]
FROM cte_ftSales
ORDER BY 11
December 5, 2013 at 12:04 pm
I would love a copy of the book as well.
There are lots of queries we can do with OVER clause. The most common, I think, is to get top N rows with some extra related information from a table. Say, we need to get latest order info form the Orders table for each customer:
;with cte as (select *, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) as Rn from dbo.Orders)
select * from cte WHERE Rn = 1 -- latest order's info for each customer
----------------------------------------
I reference Itzik's article on this topic in this TechNet Wiki article
http://social.technet.microsoft.com/wiki/contents/articles/19670.t-sql-useful-links.aspx
I encourage you all to check that article and add good links if some are missing.
December 5, 2013 at 12:14 pm
-- Will SQL 4 Food - FTW!
declare @t table (name varchar(50), original_content_count int)
insert into @t (name, original_content_count)
values ('Kamil Moscicki', 0), ('Itzik Ben-Gan', 107), ('Kalen Delaney', 98), ('Steve Jones', 587)
selectname, original_content_count, rank() over (order by original_content_count desc) champion_content_creator
from@t
But boss, why must the urgent always take precedence over the important?
December 5, 2013 at 12:37 pm
Hi,
This is my humble post, we shall respect all laws
I hope I could win one copy
Thanks
CREATE TABLE #Mytable22
(
gcounter int not null,
codesud varchar(10),
sequence int,
forms char(1),
caccount varchar(10),
account numeric(18,2),
covarname varchar(10),
covaryear varchar(10)
)
ALTER TABLE [dbo].[#Mytable22] ADD CONSTRAINT [PK_T_PAG2] PRIMARY KEY CLUSTERED
(
[gcounter] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE TABLE #Mytable33
(
gcounter int not null,
codesud varchar(5),
sequence int,
forms char(1),
caccount varchar(10),
account numeric(18,2),
covarname varchar(2),
covaryear varchar(4)
)
ALTER TABLE [dbo].[#Mytable33] ADD CONSTRAINT [PK_T_PAG3] PRIMARY KEY CLUSTERED
(
[gcounter] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Insert into #Mytable33
(gcounter,codesud,sequence,forms,caccount,account,covarname,covaryear)
SELECT 76,'05961',cast('1' as int),'A','1200000000',cast('9000000.00' as numeric(18,2)),'03','2011'
Insert into #Mytable33
(gcounter,codesud,sequence,forms,caccount,account,covarname,covaryear)
SELECT 77,'05961',1,'A','1200000000',9000000.00,'03','2011'
Insert into #Mytable33
(gcounter,codesud,sequence,forms,caccount,account,covarname,covaryear)
SELECT 1,'05961',3,'F','5120000000',-246395.83,'03','2011' UNION ALL
SELECT 2,'03421',32,'F','4319910000',5987405.04,'03','2011' UNION ALL
SELECT 3,'03621',3,'F','5120000000',-8710527.17,'03','2011' UNION ALL
SELECT 4,'04721',3,'F','5120000000',-16825100.70,'03','2011' UNION ALL
SELECT 44,'04721',3,'F','5120000000',-16825100.70,'03','2011' UNION ALL
SELECT 45,'04721',3,'F','5120000000',-16825100.70,'03','2011' UNION ALL
SELECT 5,'04722',45,'F','4480910600',928.15,'03','2011' UNION ALL
SELECT 52,'04722',45,'F','4480910600',928.15,'03','2011' UNION ALL
SELECT 53,'04722',45,'F','4480910600',928.15,'03','2011' UNION ALL
SELECT 6,'04723',45,'F','4480910100',66064.50,'03','2011'
Insert into #Mytable22
(gcounter,codesud,sequence,forms,caccount,account,covarname,covaryear)
SELECT 1,'05961',3,'F','5120000000',-246395.83,'03','2011' UNION ALL
SELECT 2,'03421',32,'F','4319910000',5987405.04,'03','2011' UNION ALL
SELECT 3,'03621',3,'F','5120000000',-8710527.17,'03','2011' UNION ALL
SELECT 6,'04723',45,'F','4480910100',66064.50,'03','2011'
SELECT ROW_NUMBER() OVER (order by b.gcounter) as 'ROWNUMBER',b.codesud as 'ceda2', b.sequence, b.forms
FROM #Mytable33 AS b
WHERE (b.gcounter IN (SELECT d.gcounter FROM #Mytable22 AS d))
December 5, 2013 at 12:44 pm
After looking this over, you'll see why I need this book! On the flip side, this article made me go research OVER and I learned something today.
select b.bookedby, B.bus_id, b.AGR_ROOMS, sum(B.blk_rooms) OVER(PARTITION BY B.bus_id) as TotalBlkRooms
,CAST(1. * B.blk_rooms / SUM(b.AGR_ROOMS) OVER(PARTITION BY B.bus_id) * 100 AS DECIMAL(5,2))AS "Percent of Total Rooms"
from business b (nolock)
where b.ARRIVEDATE between '1/1/2013' and '12/1/2013' and b.bookedby is not NULL and b.BUSSTSYNAB = 'D'
and b.BLK_ROOMS > 0 and b.AGR_ROOMS > 0 and b.BLK_ROOMS <> b.AGR_ROOMS and b.blk_rooms < b.agr_rooms
Result Set:
bookedby bus_idAGR_ROOMSTotalBlkRoomsPercent of Total Rooms
16378 8523 10 8 80.00
16339 8879 100 10 10.00
16365 8890 41414 23 0.06
December 5, 2013 at 12:46 pm
Here is my post for this:
USE TSQLFundamentals2008;
SELECT ord.orderid, ord.custid, ord.shipcity
,SUM(ord.freight) OVER(PARTITION BY ord.shipcity) AS TotalFreight
FROM Sales.Orders AS ord
ORDER BY ord.shipcity;
Spencer Moeller sjmoeller1264@gmail.com
December 5, 2013 at 12:54 pm
I had to modify one of those feared and much-maligned triggers in order to let an insert trigger assemble a dynamic SQL statement that only updates (actually modified) columns from a table:
select ROW_NUMBER() OVER(ORDER BY colorder ASC) AS Row, name, colorder
into #tmpTrigger
from syscolumns
where id = (select id from sysobjects where name='SCD_Project')
Larry
December 5, 2013 at 12:54 pm
Sorry to hear about plagiarizing. Yes, it should not be happened again.
SELECT
ROW_NUMBER() OVER (ORDER BY crdate DESC)
,name,id,xtype,crdate
FROM sysobjects
ORDER BY crdate DESC
December 5, 2013 at 1:08 pm
The following code selects the largest order (or, in the case of a tie, orders) from each customer.
WITH
Step1 as (
SELECT
Customer,
Order_Number,
Order_Date,
Order_Dollars,
Max_Order_Dollars = MAX(Order_Dollars) OVER (PARTITION BY Customer)
FROM Sales
)
SELECT
Customer,
Order_Number,
Order_Date,
Order_Dollars
FROM Step1
WHERE Order_Dollars = Max_Order_Dollars
;
December 5, 2013 at 1:08 pm
Great post! Sure would like to have the book! 🙂
select p.ID, p.PC
from (
select
m.ID, a10.PC
, ROW_NUMBER() over (partition by m.AN, m.ID order by case when a10.ED is null then 1 else 0 end asc, a10.ED asc) as rownum
from
TableM m
inner join Tablea10 a10 on a10.AN = m.AN
inner join Tablel01c l01c on l01c.PC = a10.PC and l01c.CT = 'P' and l01c.CV = 'S'
where
a10.PS = 'A'
and m.BPD >= a10.SD
and (a10.ED is null or m.BPD <= a10.ED)
) p where p.rownum = 1
;
December 5, 2013 at 1:31 pm
December 5, 2013 at 1:38 pm
I use OVER all of the time in order to find duplicate rows based on columns partitioned and ordered using OVER(). In fact, I learned this technique from Itzik En-Gan's superb "Microsoft SQL Server 2008 T-SQL Fundamentals."
;WITH CTE as
(
SELECT
ROW_NUMBER() OVER(PARTITION BY CompanySSN ORDER BY CompanySSN) as RowID
,EmpNo
,CompanySSN
,CompanyCode
,SSN
,EMPEMPLOYEE
FROM vw_UltiPro_EmpComp
)
,CTE2 as
(
SELECT
RowID
,CompanySSN
FROM CTE
WHERE RowID > 1
)
SELECT
CTE.RowID
,CTE.CompanySSN
,CTE.EmpNo
,e.LAST_NAME AS NameLast
,e.FIRST_NAME AS NameFirst
,CTE.CompanyCode
,CTE.SSN
from CTE
join lawprod.EMPLOYEE e on CTE.EMPEMPLOYEE = e.EMPLOYEE
where CTE.CompanySSN in (select CompanySSN from CTE2)
order by CTE.CompanySSN, CTE.RowID
Dan
December 5, 2013 at 1:43 pm
It's unethical to copy someone's work - I'll not do it. It's illegal as well as shameful. Here is my query:
with MyTesttable AS
(
select partyID, BillNo, TotalRollNo, OwnerName, TotalTax, SchoolDistrict, ClassCode, ROW_NUMBER() over (order by
partyID, BillNo, TotalRollNo, OwnerName, TotalTax, SchoolDistrict, ClassCode ASC) as Row_ID from TaxBillLogTest3
)
delete from MyTestTable where Row_ID not in (select MIN(Row_ID) from MyTestTable Group By BillNo)
Golam
Viewing 15 posts - 181 through 195 (of 287 total)
You must be logged in to reply to this topic. Login to reply