December 5, 2013 at 6:45 am
60 posts? ARf I can't win.
This a code I wrote some time. My first OVER.
It's working but i think it 's not a good example 😛
SELECT
OrderBy
,Measure
,label
,groupby
,ordredate
,
CASE
WHEN OrderBy = 303 AND ordredate = @DernierMois THEN concat(RANK() OVER(PARTITION BY OrderBy,groupby ORDER BY Measure DESC ),label)
ELSE 'zzzz'
END AS RANK1
,CASE
WHEN OrderBy = 303 AND ordredate = @DernierMois THEN RANK() OVER(PARTITION BY OrderBy,groupby ORDER BY Measure DESC )
ELSE 999
END AS RANK_VALUE
FROM #temp
where OrderBy = 303
OR (OrderBy = 1 AND ordredate = @DernierMois)
OR (OrderBy = 307 AND ordredate = @DernierMois)
OR (OrderBy = 2 AND ordredate = @DernierMois)
OR (OrderBy = 7 AND ordredate = @DernierMois)
OR (OrderBy = 8 AND ordredate = @DernierMois)
December 5, 2013 at 6:45 am
Classy way to handle an unfortunate occurrence. Kudos to SQLServerCentral for that. Also, SQL window functions are awesome. I recently discovered the unbounded preceding and preceding constraints, which make the creation of a running total field a breeze!
select
q.source_client_id
,q.source_employee_isgi_id
,q.first_product
,q.YEAR_MONTH
,q.service_category_name
,q.OuvertureMois
,q.FermetureMois
,isnull(sum(q.OuvertureMois) over (partition by q.source_client_id order by q.YEAR_MONTH rows between unbounded preceding and 1 preceding), 0.00) as OuvertureMois_RT
,isnull(sum(q.FermetureMois) over (partition by q.source_client_id order by q.YEAR_MONTH rows between unbounded preceding and 1 preceding), 0.00) as FermetureMois_RT
,isnull(sum(q.OuvertureMois) over (partition by q.source_client_id order by q.YEAR_MONTH rows between unbounded preceding and 1 preceding), 0.00) +
isnull(sum(q.FermetureMois) over (partition by q.source_client_id order by q.YEAR_MONTH rows between unbounded preceding and 1 preceding), 0.00) as Net_RR_Month_Begin
,isnull(sum(q.OuvertureMois) over (partition by q.source_client_id order by q.YEAR_MONTH rows between unbounded preceding and 1 preceding), 0.00) +
isnull(sum(q.FermetureMois) over (partition by q.source_client_id order by q.YEAR_MONTH rows between unbounded preceding and 1 preceding), 0.00) +
q.OuvertureMois + q.FermetureMois as Net_RR_Month_End
into #tbt_agg_data
December 5, 2013 at 6:46 am
Brandie Tarvin (12/5/2013)
One thing most people don't realize is that quoting an author's work, and attributing it properly, might not be plagiarism but it is still copyright infringement. Microsoft gives a certain amount of latitidue in allowing us to quote BOL to each other to help the community solve problems
There is a "fair use" aspect to copyright law which allows for properly attributed quotes and snippets.
Under the fair use doctrine of the U.S. copyright statute, it is permissible to use limited portions of a work including quotes, for purposes such as commentary, criticism, news reporting, and scholarly reports.
http://www.copyright.gov/help/faq/faq-fairuse.html
examples of activities that courts have regarded as fair use: “quotation of excerpts in a review or criticism for purposes of illustration or comment; quotation of short passages in a scholarly or technical work, for illustration or clarification of the author’s observations; use in a parody of some of the content of the work parodied; summary of an address or article, with brief quotations, in a news report; reproduction by a library of a portion of a work to replace part of a damaged copy; reproduction by a teacher or student of a small part of a work to illustrate a lesson; reproduction of a work in legislative or judicial proceedings or reports; incidental and fortuitous reproduction, in a newsreel or broadcast, of a work located in the scene of an event being reported.”
December 5, 2013 at 6:52 am
Not interested in the contest but I wanted to thank you for the prominent way you apologized for the use of the material of others. Most sites would have buried this somewhere where it wouldn't be noticed. It is a sign of integrity.
December 5, 2013 at 6:56 am
Yes I agree that Plagiarism is not good,but I also appreciate the fact that you have instantly come up with an apology.
Below is a script to find duplicates in a table using the function
UPDATE X
SET X.RN = Y.RN
--select *
FROM dbo.Customer X
JOIN (
SELECT PIN,
ROW_NUMBER()
OVER(PARTITION BY [Phone] ORDER BY [Phone]) AS RN
FROM dbo.Customer) as Y
ON X.Customer_ID = Y.Customer_ID
delete
--select *
FROM dbo.Customer
WHERE X.RN > 1
December 5, 2013 at 7:00 am
Samuel Vella (12/5/2013)
Brandie Tarvin (12/5/2013)
One thing most people don't realize is that quoting an author's work, and attributing it properly, might not be plagiarism but it is still copyright infringement. Microsoft gives a certain amount of latitidue in allowing us to quote BOL to each other to help the community solve problems
There is a "fair use" aspect to copyright law which allows for properly attributed quotes and snippets.
Under the fair use doctrine of the U.S. copyright statute, it is permissible to use limited portions of a work including quotes, for purposes such as commentary, criticism, news reporting, and scholarly reports.
http://www.copyright.gov/help/faq/faq-fairuse.html
examples of activities that courts have regarded as fair use: “quotation of excerpts in a review or criticism for purposes of illustration or comment; quotation of short passages in a scholarly or technical work, for illustration or clarification of the author’s observations; use in a parody of some of the content of the work parodied; summary of an address or article, with brief quotations, in a news report; reproduction by a library of a portion of a work to replace part of a damaged copy; reproduction by a teacher or student of a small part of a work to illustrate a lesson; reproduction of a work in legislative or judicial proceedings or reports; incidental and fortuitous reproduction, in a newsreel or broadcast, of a work located in the scene of an event being reported.”
http://www.copyright.gov/fls/fl102.html%5B/quote%5D
Fair Use doesn't mean what most people think it means. If you quote a single line from a song or poem without permission, for instance, you can still get in copyright infringement trouble. Fair use tends to mostly apply to academic and journalism environments. And quoting an entire article or whole pages doesn't count under Fair Use.
I learned this all from a corporate lawyer who was well versed in copyright law. And since this is a hot button issue for me (as both a corporate employee and a professional author), I'm going to drop the subject here before I get into rant mode.
December 5, 2013 at 7:00 am
Glad to see you're doing your best to make things right.
SELECTRANK () OVER (PARTITION BY br2.brand ORDER BY br2.forecast_percent DESC) rnk,
br2.brand, br2.forecast_percent, br2.territory
FROM brand_ranking br2
December 5, 2013 at 7:02 am
Here's one I've used to remove duplicate values in multiple rows in test:
begin tran
;with educ_dupes
as (
select BegDate,StudentID,EndDate,GPA,IsGraduate,Level,
EfeLocation,EfeMajor,EfeNumberOfYears,EfeRank,EfeSchool,
row_number() OVER (
PARTITION BY BegDate,EEID,EndDate,GPA,IsGraduate,Level,Location,Major,NumberOfYears,Rank,School
ORDER BY studentID) as dupe
from Educ)
delete educ_dupes
where dupe =2
December 5, 2013 at 7:02 am
Here's one I've used to remove duplicate values in multiple rows in test:
begin tran
;with educ_dupes
as (
select BegDate,StudentID,EndDate,GPA,IsGraduate,Level,
EfeLocation,EfeMajor,EfeNumberOfYears,EfeRank,EfeSchool,
row_number() OVER (
PARTITION BY BegDate,EEID,EndDate,GPA,IsGraduate,Level,Location,Major,NumberOfYears,Rank,School
ORDER BY studentID) as dupe
from Educ)
delete educ_dupes
where dupe =2
December 5, 2013 at 7:03 am
In ETL processing, I commonly use windowing functions to remove duplicates as some others have said. By identifying the best record of the duplicates, I can get all the connected values properly by ranking the preferred record.
Also, I like how with the addition of some of the windowing features that have been available in Oracle already and also CLR functions how you can do a lot more now than you used to be able to do.
I hope I win the book. I don't already have a copy 🙂
December 5, 2013 at 7:03 am
Plagarisrism is just plain wrong and it was good of you to post this apology.
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS ROW_NUM, name, object_id, create_date, type
FROM sys.objects
where type = 'U'
December 5, 2013 at 7:07 am
I'm glad they spotted this and outed the plagiarizer.
CREATE TABLE #temp(SalesPersonID INT, Sales NUMERIC(10,2))
INSERT INTO #temp
(SalesPersonID,Sales)
SELECT 1000,2080.25
UNION
SELECT 1001,10083.35
UNION
SELECT 1002,2900.78
SELECT SalesPersonID,Sales,ROW_NUMBER() OVER(ORDER BY Sales DESC)
FROM #temp
December 5, 2013 at 7:07 am
Thank you!
;WITH cte AS
(
SELECT ABS(CHECKSUM(NEWID())) random_number
UNION ALL
SELECT ABS(CHECKSUM(NEWID())) random_number
)
SELECT
random_number
,RANK() OVER(ORDER BY random_number) as rnk
FROM
cte
December 5, 2013 at 7:07 am
A copy of the book would be nice 😀
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN
FROM MASTER..spt_values
December 5, 2013 at 7:08 am
Sad thing that people feel the need to plagiarize. But, thanks for turning this into something positive.
Whipped this up just to try to get a book. Used AdventureWorks database in case people that haven't used OVER want to play with it.
-- Find the top 5 selling products for each salesperson
-- Uses AdventureWorks2008 database
-- Written: 12/05/2013By: Tom Abraham
WITH Top5ProductsPerSalesperson AS
(
SELECT
SalesPersonID,
ProductID,
ProductTotalSales,
ROW_NUMBER() OVER(PARTITION BY SalesPersonID
ORDER BY ProductTotalSales DESC) AS SalesRank
FROM (SELECT Sales.SalesOrderHeader.SalesPersonID,
Sales.SalesOrderDetail.ProductID,
SUM(Sales.SalesOrderDetail.LineTotal) AS ProductTotalSales
FROM Sales.SalesOrderHeader INNER JOIN
Sales.SalesOrderDetail ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID
GROUP BY Sales.SalesOrderHeader.SalesPersonID, Sales.SalesOrderDetail.ProductID) AS SalesTotals
)
SELECT
SalesPersonID,
ProductID,
ProductTotalSales,
SalesRank
FROM Top5ProductsPerSalesperson
WHERE SalesRank < 6
ORDER BY SalesPersonID,
SalesRank;
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
Viewing 15 posts - 61 through 75 (of 287 total)
You must be logged in to reply to this topic. Login to reply