December 5, 2013 at 6:07 am
I appreciate the SQLServerCentral's effort in dealing with this matter !
select orderid,productID, max(unitprice)
over ( partition by orderid) as MaxPrice
from sales.OrderDetails
December 5, 2013 at 6:07 am
Book looks good. I use OVER to add a sequential number to rows that have duplicate IDs.
SELECT PID, ROW_NUMBER() OVER(PARTITION BY PID ORDER BY PID) AS LINE_NUM
FROM MyTable
December 5, 2013 at 6:08 am
When I read the article from SQL Server Central a lot of it did sound kind of "deja' vu". However, many articles and posts regarding SQL Server functions, processes, best practices, etc. are like that; we all are talking about the same thing ... how to best install, manage, perform SQL Server for goodness sake.
WITH cteDates(dtDate) AS
(
SELECT StartDate = @Date -6
UNION ALL
SELECT DATEADD(d,1,dtDate)
FROM cteDates
WHERE dtDate < @Date
)
,cteDateDayNum (dtDate,DayNum) AS
(
SELECT
dtDate
,DayNum = ROW_NUMBER() OVER(ORDER BY dtDate ASC)
FROM cteDates
)
,cteEmpData AS
(
SELECT
DDN.DayNum
,THD.SSN
,THD.Client
,THD.GroupCode
,THD.ShiftNo
,THD.DeptNo
,InTime = LEFT(CAST(THD.InTime AS TIME),5)
,OutTime = LEFT(CAST(THD.OutTime AS TIME),5)
,DailyHours = THD.[Hours]
,RowNum = ROW_NUMBER() OVER(PARTITION BY THD.SSN,DDN.DayNum ORDER BY THD.RecordID)
FROM
cteDateDayNum DDN
LEFT JOIN
xxDBxx.dbo.tblDataDataDetail THD
ON THD.TransDate = DDN.dtDate
CROSS APPLY
xxDBxx.dbo.tvf_GetxxDBxxClusterDefAsFn
(THD.GroupCode,THD.SiteNo,THD.DeptNo,THD.AgencyNo,THD.SSN,THD.DivisionID,THD.ShiftNo,@ClusterID)
WHERE
THD.Client = @Client
AND THD.GroupCode = @Group
AND THD.PayrollPeriodEndDate = @Date
AND xxDBxx.dbo.fn_InCSV(@Sites,thd.SiteNo,1) = 1
AND xxDBxx.dbo.fn_InCSV(@Dept,thd.DeptNo,1) = 1
UNION
SELECT
DayNum
,SSN = NULL
,Client = NULL
,GroupCode = NULL
,ShiftNo = NULL
,DeptNo = NULL
,InTime = NULL
,OutTime = NULL
,DailyHours = NULL
,RowNum = NULL
FROM
cteDateDayNum
)
,cteAllTime AS
(
SELECT
SSN
,DeptNo
,ShiftNo
,DayNum = 'In' + CAST(DayNum AS CHAR(1))
,strTime = InTime
,RowNum
FROM cteEmpData
WHERE InTime IS NOT NULL
UNION
SELECT
SSN
,DeptNo
,ShiftNo
,DayNum = 'Out' + CAST(DayNum AS CHAR(1))
,strTime = OutTime
,RowNum
FROM cteEmpData
WHERE OutTime IS NOT NULL
UNION
SELECT
SSN
,DeptNo
,ShiftNo
,DayNum = 'Hours' + CAST(DayNum AS CHAR(1))
,strTime = CAST(DailyHours AS VARCHAR(5))
,RowNum
FROM cteEmpData
WHERE DailyHours IS NOT NULL
)
,ctePivot AS
(
SELECT
SSN
,DeptNo
,ShiftNo
,In1,Out1,Hours1
,In2,Out2,Hours2
,In3,Out3,Hours3
,In4,Out4,Hours4
,In5,Out5,Hours5
,In6,Out6,Hours6
,In7,Out7,Hours7
FROM
(
SELECT SSN,DeptNo,ShiftNo,RowNum,DayNum,StrTime FROM cteAllTime
) CTE
PIVOT
(
MAX(StrTime) FOR DayNum IN
(
In1,Out1,Hours1
,In2,Out2,Hours2
,In3,Out3,Hours3
,In4,Out4,Hours4
,In5,Out5,Hours5
,In6,Out6,Hours6
,In7,Out7,Hours7
)
) PVT
WHERE SSN IS NOT NULL
)
SELECT DISTINCT
EN.SSN
,EN.LastName
,EN.FirstName
,GD.DeptName
,ED.ShiftNo
,In1 = REPLACE(In1,'00:00',''),Out1 = REPLACE(Out1,'00:00',''),Hours1
,In2 = REPLACE(In2,'00:00',''),Out2 = REPLACE(Out2,'00:00',''),Hours2
,In3 = REPLACE(In3,'00:00',''),Out3 = REPLACE(Out3,'00:00',''),Hours3
,In4 = REPLACE(In4,'00:00',''),Out4 = REPLACE(Out4,'00:00',''),Hours4
,In5 = REPLACE(In5,'00:00',''),Out5 = REPLACE(Out5,'00:00',''),Hours5
,In6 = REPLACE(In6,'00:00',''),Out6 = REPLACE(Out6,'00:00',''),Hours6
,In7 = REPLACE(In7,'00:00',''),Out7 = REPLACE(Out7,'00:00',''),Hours7
FROM ctePivot CTE
INNER JOIN
cteEmpData ED
ON ED.SSN = CTE.SSN
AND ED.DeptNo = CTE.DeptNo
AND ED.ShiftNo = CTE.ShiftNo
INNER JOIN
xxDBxx.dbo.tblNames EN
ON EN.SSN = ED.SSN
AND EN.Client = ED.Client
AND EN.GroupCode = ED.GroupCode
INNER JOIN
xxDBxx.dbo.tblDepts GD
ON GD.Client = ED.Client
AND GD.GroupCode = ED.GroupCode
AND GD.DeptNo = ED.DeptNo
ORDER BY
LastName,FirstName,DeptName,ShiftNo
,In1 DESC,In2 DESC,In3 DESC,In4 DESC
,In5 DESC ,In6 DESC,In7 DESC;
December 5, 2013 at 6:09 am
Itzik Ben-Gan is the best. I always know I am getting great information when he is the author! I look for his presentations every time I go to TechEd. It was at TechEd 2011 that I first saw the information on Window Functions (I think it was one of the KeyNote Sessions).
I had a table that just contained order detail records that I needed to display by individual orders in a consistent order. Unfortunately, PO (purchase order) numbers in our system rollover so they are not unique between stores. I needed to identify unique orders and add a unique order line number for each detail record for each order. Using the OVER clause made this so easy! I have also used the OVER clause to only send a specific set of records back to a web page for paging so the web page is more responsive.
I commend SQL Server Central for doing the right thing. Like Mr. Ben-Gan, I always know I can find good information on SQL Server Central.
SELECT
ORDER_NUM = CONVERT(VARCHAR(3), StoreNo) + '-'
+ CONVERT(VARCHAR(10), PONo) + '-'
+ CONVERT(VARCHAR(8), PODate, 112)
,ORDER_LINENO = ROW_NUMBER() OVER (PARTITION BY PODate, StoreNo, PONo ORDER BY ProductCode)
,StoreNo
,PONo
,PODate
,ProductCode
,Quantity
FROM ORDERS
December 5, 2013 at 6:14 am
I had to fumble through windowing functions when the need arose. After Watching Christina Leo's PASS2013 session, I let her know that she made sense of what I had hacked together(below).
This is what I managed to cobble together. I use it in an SSRS report for finding the total hours worked for some of my users
SELECT UL.[Date_Time]
,CAST(UL.[Date_Time] - LAG(UL.[Date_Time],1) OVER (PARTITION BY CAST(UL.[Date_TIME] AS DATE) ORDER BY UL.[Date_TIME]) AS TIME) AS 'Time On'
FROM [SERVER].[dbo].[TABLENAME] AS UL WITH (NOLOCK) /**Pay no attention to the NOLOCK, no I can't change that **/
WHERE UL.[Department] = 'DEPARTMENT'
AND ((UL.[Action] = 'On' OR UL.[Action] = 'Login') OR UL.[Action] = 'Off')
AND (UL.[Name] LIKE '%' + @Name + '%') /** Really, there isn't a better way, no I can't change that **/
AND (
(CAST(UL.[Date_TIME] AS DATE) >= @Start_Date AND @End_Date IS NULL)
OR (CAST(UL.[Date_TIME] AS DATE) <= @End_Date AND @Start_Date IS NULL)
OR (CAST(UL.[Date_TIME] AS DATE) >= @Start_Date AND CAST(UL.[Date_TIME] AS DATE) <= @End_Date)
)
December 5, 2013 at 6:18 am
Sincere, accurate apologies raise credibility with me. Well done, SQLServerCentral.
Here's a query I modified from one I saw somewhere (in BOL, I think) while studying windowing functions. It shows an example of the Lag and Lead functions.
use AdventureWorks2012
go
-- LAG, LEAD
-- Retrieves a row at a given physical offset that comes before (LAG) or after (LEAD) the current row.
select Department, Lastname, Rate
,lag(Rate, 2, -9) over (partition by Department order by Rate desc) as [lag(Rate, 2, -9)]
,lead(Rate, 2, -8) over (partition by Department order by Rate desc) as [lead(Rate, 2, -8)]
from HumanResources.vEmployeeDepartmentHistory as EDH
inner join HumanResources.EmployeePayHistory as E
on E.BusinessEntityID=EDH.BusinessEntityID
where Department in ('Information Services', 'Document Control')
order by Department, Rate desc
December 5, 2013 at 6:20 am
j.zinn (12/5/2013)
I'm just really impressed by the reaction that this site had to content that was lifted without attribution. A quick, scholarly reaction. I hope that Mr. Ben-Gan appreciated the response, and that this community knows that sharing knowledge is lofty, but giving credit is vital. Without it, it's theft, not sharing.
As I mentioned in my post, it's still theft even when you give credit for the lifted content. Only when a person has permission from the author copyright holder is it not theft.
EDIT: Corrected wrong word choice.
December 5, 2013 at 6:21 am
SELECT
[PlagiarizedArticle] AS oversite
,rank() OVER (partition by [Ben-Gan].public.Apology order by dateofApology)
FROM SQLServerCentral
Result:
||Hope all is forgiven...here's your free book by author.||
December 5, 2013 at 6:22 am
jsimancas (12/5/2013)
SELECT[PlagiarizedArticle] AS oversite
,rank() OVER (partition by [Ben-Gan].public.Apology)
FROM SQLServerCentral
Result:
||Hope all is forgiven...here's your free book by author.||
HA! I like this one.
December 5, 2013 at 6:23 am
yeah, I did it so quick I forgot the order by clause.. ooops:-D
December 5, 2013 at 6:30 am
I think you are doing the right thing to admit a mistake and provide some benefit to the plagerized. I did not write following from scratch but modified from http://qa.sqlservercentral.com/Forums/Topic675554-8-46.aspx:
;WITH AdjCategoryTree
AS
(
SELECT A.UN_standard_product_code, A.UN_standard_product_name,
CONVERT(VARCHAR(MAX), UN_standard_product_name) AS UN_standard_product_namePath,
A.UN_standard_parent_product_code, 0 AS Depth
FROM dbo.UN_STANDARD_PRODUCT A WITH (NOLOCK)
WHERE UN_standard_parent_product_code IS NULL
UNION ALL
SELECT A.UN_standard_product_code, A.UN_standard_product_name,
CONVERT(VARCHAR(MAX), B.UN_standard_product_namePath + '|' + A.UN_standard_product_name) AS UN_standard_product_namePath,
A.UN_standard_parent_product_code, B.Depth + 1
FROM dbo.UN_STANDARD_PRODUCT A WITH (NOLOCK)
JOIN AdjCategoryTree B
ON A.UN_standard_parent_product_code = B.UN_standard_product_code
)
SELECT TOP 100 PERCENT UN_standard_product_code,
REPLICATE(' ', Depth) + UN_standard_product_name AS DisplayLabel,
UN_standard_product_name
, ROW_NUMBER() OVER (ORDER BY UN_standard_product_namePath) AS [Order], Depth
FROM AdjCategoryTree
ORDER BY UN_standard_product_namePath;
December 5, 2013 at 6:33 am
A very good book! If you do not have a copy, then get one!
December 5, 2013 at 6:37 am
Good to read that the offender has been identified and SQL ServerCentral is taking the right action. Here is a way to get the sales order counts by year and month name:
WITH SourceData
AS
(SELECT DISTINCT
Year(OrderDate) AS OrderYear,
Month(OrderDate) AS OrderMonth,
DateName(mm, OrderDate) AS OrderMonthName,
Count(*) OVER (PARTITION BY Year(OrderDate), Month(OrderDate)) AS OrderCount
FROM Sales.SalesOrderHeader
)
SELECTOrderYear,
OrderMonthName,
OrderCount
FROMSourceData
ORDER BY OrderYear DESC, OrderMonth DESC;
December 5, 2013 at 6:37 am
Was checking out this book and it looks perfect for me! I've added it to my Amazon wish list just in case I'm not one of the ten lucky winners.
December 5, 2013 at 6:41 am
One of my favorite uses for window functions is for removing duplicates. This is pretty well documented, so I wanted to show how I use the new (2012) SUM() with window function for running totals or balances:
--Create database
CREATE DATABASE RunningTotal
GO
--Switch database context
USE RunningTotal
GO
--Create a table for our ledger
CREATE TABLE Ledger(
ledgerID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
userID INT NOT NULL,
credit DECIMAL(18,2),
debit DECIMAL(18,2)
)
GO
--Insert values into Ledger table
INSERT INTO Ledger (userID, credit, debit)
SELECT 1, 200.00, 0
UNION ALL
SELECT 1, 300.00, 0
UNION ALL
SELECT 1, 0, 150.00
UNION ALL
SELECT 1, 25.00, 0
UNION ALL
SELECT 1, 650.00, 0
UNION ALL
SELECT 1, 0, 1000.00
UNION ALL
SELECT 1, 950.00, 0
UNION ALL
SELECT 1, 0, 10.00
UNION ALL
SELECT 2, 1000.00, 0
UNION ALL
SELECT 2, 25.00, 0
UNION ALL
SELECT 2, 0, 15.00
UNION ALL
SELECT 2, 32.00, 0
UNION ALL
SELECT 2, 15.00, 0
GO
--Verify data
SELECT *
FROM Ledger
ORDER BY ledgerID
GO
--Calculate running toal for balance
SELECT
ledgerID
,userID
, credit
, debit
, SUM(credit - debit) OVER(PARTITION BY userID ORDER BY ledgerid) AS balance
FROM Ledger
--Clean up
USE master
DROP DATABASE RunningTotal
GO
Jared
CE - Microsoft
Viewing 15 posts - 46 through 60 (of 287 total)
You must be logged in to reply to this topic. Login to reply