December 5, 2013 at 9:54 am
Stealing other's work is not okay.
WITH CTE
AS (SELECT SomeData AS [Data],
Row_number()
OVER (
PARTITION BY [System_Id]
ORDER BY [System_ChangedDate] DESC ) AS [MostRecent]
FROM [dbo].[TableName])
SELECT [Data]
FROM CTE
WHERE MostRecent = 1
ORDER BY [Data]
December 5, 2013 at 9:55 am
SQLTuna (12/5/2013)
I'm glad you named and shamed the culprit!
At the prompting of sqlrnnr, we would like to also maim this person.
Seriously, I've very pleased to see this public apology. This step alone shows why SSC is so great - SSC could have just removed the plagiarized article and left it at that. But to then go ahead and have this contest to give away 10 copies of Itzik's book (the book that was copied from) is, IMHO, going above and beyond. I'm very happy to see SSC do this.
Kudos to you Steve, and all at Red-Gate, for making this happen.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 5, 2013 at 9:57 am
I'm not looking for a free book, but I applaud SQLServerCentral for their prompt and decisive action in both admitting the original plagarism and in seeking to remediate the error. In this age of 'never admit' stonewalling non-response, this is an unfortunately rare breath of very fresh air.
December 5, 2013 at 9:58 am
Plagiarism = bad
Free books = good
/* Display list of names and the count of names on this list that have the same
first name last name combination.
*/
SELECT
COUNT(*) OVER(PARTITION BY FirstName, LastName) AS [NameCount]
,FirstName
,LastName
FROM
Customers
ORDER BY [NameCount] DESC, LastName ASC, FirstName ASC;
December 5, 2013 at 9:58 am
For those of us used to making data-marts
USE [Documents]
GO
/****** Object: StoredProcedure [dbo].[usp_GetTopN_MembershipType] Script Date: 9/10/2013 8:12:48 AM ******/
-- =============================================
-- Name:use_GetTopN_MembershipType
-- Description:Gets some number of top-accessing users grouped by MmebershipType
-- Author:Tom Hamilton
-- Create date: 2013
-- Modified:
-- =============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_GetTopN_MembershipType]
@StartDate DATE= '01/01/2013',
@EndDate DATETIME= '12/31/2099',
@CountPerGroup INT= 5-- Default TOP 5 for each group
AS
SET NOCOUNT ON
DECLARE @CountDays INT
IF @StartDate > @EndDate -- Dates swapped, put them IN RIGHT order
BEGIN
DECLARE @tmpDate DATETIME = @StartDate
SET @StartDate = @EndDate
SET @EndDate = @tmpDate
END
IF @EndDate > GETDATE() OR @EndDate IS NULL SET @EndDate = GETDATE()
-- Check Date range, Fix Date time component (Date only = midnight!)
IF RIGHT(@EndDate, 7) = '12:00AM'SET @EndDate = CONVERT(varchar(10), @EndDate , 101) + ' 23:59:59.99'
SET @CountDays = DATEDIFF(d, @StartDate, @EndDate) + 1-- Does not naturally count today
SELECTCASE WHEN YEAR(@StartDate) = YEAR(@Enddate) AND MONTH(@StartDate) != MONTH(@EndDate)
THEN 'YTD ' + CAST(YEAR(@EndDate) as varchar(4))
ELSE
CASE WHEN YEAR(@StartDate) = YEAR(@Enddate) AND MONTH(@StartDate) = MONTH(@EndDate)
THEN 'MTD ' + ' ' + DateName(YYYY, @EndDate) + RIGHT('00' + CAST(DATEPART(MM, @EndDate) AS varchar(2)), 2)
END
END AS [Level],
'Top ' + CAST(@CountPerGroup AS CHAR) AS [Range],rs.MembershipTypeID, rs.MembershipType, rs.[Rank], rs.UniqueId, rs.[Member Name],
rs.Days, rs.Reads, rs.Docs, @StartDate AS [Start Date], @CountDays AS [Days since start]
FROM
(SELECT u.MembershipTypeID, mt.Description as MembershipType, u.UniqueID, u.LastName + ', ' + u.FirstName AS [Member Name],
COUNT(1) AS Reads,
COUNT (DISTINCT dh.DocumentID) AS Docs,
COUNT (DISTINCT CONVERT(VARCHAR(10), dh.Date, 101)) AS Days,
Rank() OVER (PARTITION BY u.MembershipTypeID ORDER BY COUNT(1) DESC ) AS Rank
FROM dbo.DocumentsHitsAS dhWITH (NOLOCK)
INNER JOIN dbo.UsersAS uWITH (NOLOCK) ON dh.UniqueID = u.UniqueID
INNER JOIN dbo.DocumentsAS dWITH (NOLOCK) ON u.UniqueId = d.DocumentID
INNER JOIN dbo.tblMembershipType AS mt WITH (NOLOCK) ON mt.MemberTypeID = u.MembershipTypeID
WHERE dh.Date > @StartDate
GROUP BY MembershipTypeID, mt.Description, u.UniqueId, u.LastName + ', ' + u.FirstName) AS rs
WHERE RANK <= @CountPerGroup
/* TEST HARNESS
sp_Recompile usp_GetTopN_MembershipType
--USE VINStats ;
EXEC usp_GetTopN_MembershipType '9/1/2012', '9/30/2013', 3-- Top 3 Sept 2013
EXEC usp_GetTopN_MembershipType '8/1/2013', '8/31/2013', 5-- Top 5 8/1/2013 - 8/31/2013
EXEC usp_GetTopN_MembershipType '1/1/2012', '12/31/2013', 3-- Top 3 since 1/1/2011-12/31/2013
DECLARE @Start date = '9/1/2013' ; SELECT DATEDIFF(d, @Start, GetDate())
SELECT * FROM documentsHits WHERE UniqueID = 133890 and date >= '1/1/2013'
*/
Tom in Sacramento - For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 5, 2013 at 9:59 am
I use window functions all the time now when working with inherited databases:
CREATE VIEW vAssisterCert AS
WITH DuplicateACStatus AS (
SELECT RN=Row_number() OVER (Partition BY EnrollmentEntityAssisterId ORDER BY UpdatedDate DESC), EnrollmentEntityAssisterId, CertificationNumber, CertificationStatusId
FROM AssisterCertification
WHERE Active=1
)
December 5, 2013 at 9:59 am
Should not Server Central also take care of this
http://www.sqlservercentral.com/Authors/Articles/Kamil_Moscicki/1531761/
Guest Columnist: Kamil Moscicki
December 5, 2013 at 10:04 am
This is a nice way to make recompense. And fun for us.
Here's a query that I used in production
--the row_number() function returns a bigint data type, which is
--why we need to Convert() it to varchar so that we can concatenate
--with the category type and done/open info.
SELECT followID,
Convert(varchar(2), Row_Number() OVER(ORDER BY fu.followTickleDt DESC))
+ ': ' + fc.folcatgAbbr +
CASE WHEN fu.followDoneDt IS NULL THEN '-Open!' ELSE '(done)' END
AS [DisplayValue]
FROM TadFollowUp fu
JOIN TadFollowCatgTyp fc ON fu.folcatgCd = fc.folcatgCd
WHERE fu.tadID = @i_tadID
ORDER BY fu.followTickleDt DESC
December 5, 2013 at 10:04 am
Hi, i commend you for this. Not long ago i was researching a problem i was having and found a blog where the author copied text directly from another article with no reference whatsoever. "Bad form" says I.
I wrote the code below to solve the following problem: How to simultaneously process a number of transactions in multiple queues. 'Process' here means to analyze each transaction RBAR and take actions depending on configured rules. An important fact is that the source table could have many transactions for a particular reference (Subscription Reference) and that all processing rules only affect those transactions within a reference id. That is all transactions for a particular reference must be processed together - in the same queue. Secondly, since the number of transactions can vary radically by reference, it was helpful to know the number of transactions for each reference and sort accordingly so that the queues are roughly dealing with the same number. So steps are:
1. Populate a list table with all reference id's along with counts
2. As each queue comes online, pick out the nth records from the list to populate the queue's input table
--------------------------------------------------------
--Step 1 - Populate source table for queues to draw from
--
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[WorkBI].[BreakList]') AND type in (N'U'))
DROP TABLE [WorkBI].[BreakList]
GO
CREATE TABLE WorkBI.[BreakList] (RowNum int PRIMARY KEY NOT NULL, BreakValue varchar(20), RecCount int)
INSERT WorkBI.BreakList
SELECT
ROW_NUMBER() OVER (ORDER BY COUNT(*), [Subscription Reference]),
[Subscription Reference], COUNT(*)
FROM LocalBI.[CDSTransactions Stage]
GROUP BY [Subscription Reference]
----------------------------------------
--Step 2 - Populate queue input table(s)
--
BEGIN -- Load BreakTable(queue id)
SET @SQLCommand =
' INSERT WorkBI.' + @BreakTable + ' (RowNum, BreakValue, BreakDone, RecCount)
SELECT RowNum, BreakValue, 0, RecCount
FROM WorkBI.BreakList
WHERE RowNum % ' + cast(@ProcQs as varchar(12)) + ' = ' + cast(@ProcQs - @ActiveQs as varchar(12))
IF @TranRuleLog IN (1,2)
EXEC spLogSeqProc @SQLCommand, 0
BEGIN TRY
EXEC sp_executesql @SQLCommand
END TRY
BEGIN CATCH
-- logging
END CATCH
END -- Load BreakList
December 5, 2013 at 10:07 am
I have a situation where I need to distribute a certain set of rows over a period of time. The number of rows, number of months and base date are all variable. I need to distribute them starting the month before the base date (moving backwards in time) and distribute them to a number of days equal to the number of months. It has to be run periodically.
This assumes we have a Tally1K table and will be processing less than 1,000 rows. This example uses 200 in a sample table, but the real row count is usually far less. It uses NTILE to determine the month to which each row will be distributed.
--define our runtime parameters
DECLARE @dtmBase datetime = '11/01/2013',
@intMonths integer = 4,
@intRows integer;
--create and populate a table of months
IF OBJECT_ID('tempdb.dbo.#tblMonths', 'u') IS NOT NULL DROP TABLE #tblMonths;
CREATE TABLE #tblMonths (
ID Integer not null identity (1, 1),
Start Datetime not null);
INSERT INTO #tblMonths(Start)
SELECT DATEADD(month, -t.N, @dtmBase)
FROM Tally1K t
WHERE t.N <= @intMonths
ORDER BY 1 DESC;
--create a working table to hold the pk from our production table
IF OBJECT_ID('tempdb.dbo.#tblData', 'u') IS NOT NULL DROP TABLE #tblData;
CREATE TABLE #tblData (
ID Integer,
RowDate Datetime,
MonthSet Integer);
--populate with some sample data. in production, it would come from the
--real table instead of a tally table.
INSERT INTO #tblData(ID)
SELECT TOP 200 N
FROM Tally1K t;
--break up our rows into n groups
WITH cte AS (
SELECT ID, NTILE((SELECT COUNT(*) FROM #tblMonths)) OVER(PARTITION BY NULL ORDER BY ID) month_set
FROM #tblData)
UPDATE #tblData
SET #tblData.MonthSet = cte.month_set
FROM cte
WHERE #tblData.ID = cte.ID;
--get the number of rows we're going to distribute
SELECT @intRows = COUNT(*)
FROM #tblData;
--update our working table with the new dates
WITH t AS (
SELECT TOP (@intRows) n
FROM Tally1K
ORDER BY N)
UPDATE #tblData
SET RowDate = DATEADD(day, t.n % @intMonths, m.Start)
FROM #tblData s
INNER JOIN t ON s.ID % @intRows + 1 = t.n
INNER JOIN #tblMonths m ON s.MonthSet = m.ID;
--we're now free to examine the data or update our
--production table with the new dates from #tblData
SELECT * FROM #tblData ORDER BY rowdate
DROP TABLE #tblData;
DROP TABLE #tblMonths;
December 5, 2013 at 10:09 am
I love Itzik Ben-Gan's books, very valuable in practical life.
WITH CTE (Rank_Number,row_date,PBXACD_ID,split,center_id) AS
(SELECT
RANK() OVER ( PARTITION BY row_date, PBXACD_ID, split ORDER BY COUNT(*) DESC) AS Rank_Number,
row_date,
PBXACD_ID,
split,
center_id
FROM
TestDB.dbo.TestTable (NOLOCK)
WHERE
row_date >= CAST(DATEADD(day, -3, GetDATE()) AS DATE) AND row_date <= CAST(DATEADD(day, -1, GetDATE()) AS DATE)
AND center_id > 0
GROUP BY
row_date,
PBXACD_ID,
split,
center_id)
Merge TestDB.dbo.default_values AS T
USING
(SELECT
row_date,PBXACD_ID,split,center_id
FROM
CTE
WHERE
Rank_Number = 1) AS S ON S.row_date = T.row_date
AND S.PBXACD_ID = T.PBXACD_ID
AND S.split = T.split
AND S.center_id = T.center_id
WHEN MATCHED AND T.center_id IS NOT NULL THEN
UPDATE SET
T.center_id = S.center_id
WHEN NOT MATCHED THEN
INSERT(row_date,
PBXACD_ID,
split,
center_id,
client_id)
VALUES
(S.row_date,
S.PBXACD_ID,
S.split,
S.center_id,
NULL);
December 5, 2013 at 10:10 am
zsolt.miskolczi (12/5/2013)
Hi,;WITH e1(n) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1
), -- 10
e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10*10 = 100
e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2 AS c) -- 10*100 = 1.000
SELECT ROW_NUMBER() OVER (ORDER BY n) as mySequence
FROM e3 ORDER BY n ;
Did anyone else notice that the code from the 6th poster, zsolt.miskolczi is Itzik Ben-Gan's recursive counting method? I know that you can't really plagiarize code but I couldn't decide if this was coincidentally funny or ironically disgusting...depends if zsolt.miskolczi is oblivious to this fact I guess. :unsure:
Don't enter me in the drawing...too lazy to post any original code...unless, Itzik's code above is good enough. 😛
December 5, 2013 at 10:11 am
I already bought this book, and love it. Itzik is a terrific writer and thinker, and often asks much of his readers. If you don't win a book in the giveaway, I urge you to buy the book anyway.
Kudos also to SSC for their enlightened response to the plagiarism.
December 5, 2013 at 10:20 am
Plagiarism stinks.
...on that note, here's an ITVF that uses ROW_NUMBER() for calculating the longest common substring.
-- (1) Function to find the longest common substring
CREATE FUNCTION CommonSubstrings(@s1 varchar(8000),@s2 varchar(8000))
RETURNS TABLE
AS
RETURN
WITH
tally (n) AS
( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values),
matrix(loc) AS
( SELECT n
FROM tally
WHERE n<len(@s1)),
sequences(cs,cs_len) AS
( SELECT SUBSTRING(@s1,loc,t.n+1), LEN(SUBSTRING(@s1,loc,t.n+1))
FROM matrix m
CROSS APPLY tally t
WHERE LEN(@s1)-m.loc >= t.n
AND PATINDEX('%'+SUBSTRING(@s1,loc,t.n+1)+'%',@s2)<>0)
SELECT TOP 1
@s1 AS string1,
@s2 AS string2,
cs AS LCS,
cs_len AS LCS_Len
FROM sequences
ORDER BY cs_len DESC;
The function could be used to detect plagiarism. Here's some sample code to demonstrate.
-- (2) test data
IF OBJECT_ID('tempdb..#articles') IS NOT NULL DROP TABLE #articles;
CREATE TABLE #articles (art_nbr int identity primary key, article varchar(8000) NOT NULL);
INSERT #articles (article)
SELECT 'You can use these galleries to insert tables, headers, footers, lists, cover pages, and other document building blocks. In order to understand plagiarism, it helps to understand the process of sharing and creating ideas in the university. All knowledge is built from previous knowledge. As we read, study, perform experiments, and gather perspectives, we are drawing on other people’s ideas. Building on their ideas and experiences, we create our own. When you put your ideas on paper, your instructors want to distinguish between the building block ideas borrowed from other people and your own newly reasoned perspectives or conclusions. You make these distinctions in a written paper by citing the sources for your building block ideas. Providing appropriate citations will also help readers who are interested in your topic find additional, related material to read—in this way, they will be able to build on the work you have done to find sources.'
UNION ALL
SELECT 'On the Insert tab, the galleries include items that are designed to coordinate with the overall look of your document. You can use these galleries to insert tables, headers, footers, lists, cover pages, and other document building blocks. When you create pictures, charts, or diagrams, they also coordinate with your current document look.'
UNION ALL
SELECT 'the galleries include items that are designed to... You can easily change the formatting of selected text in the document text by choosing a look for the selected text from the Quick Styles gallery on the Home tab. You can also format text directly by using the other controls on the Home tab. Most controls offer a choice of using the look from the current theme or using a format that you specify directly.'
and a query that compares a newly submitted article against existing articles stored in a database:
DECLARE
@submittedArticle varchar(1000)='blah, blah, blah... All knowledge is built from previous knowledge. As we read, study, perform experiments';
SELECTl.string1 AS SubmittedArticle,
l.string2 AS ExistingArticle,
lcs_len AS MatchingCharacters,
PATINDEX('%'+lcs+'%',l.string1) AS SubmittedArticlePosition,
PATINDEX('%'+lcs+'%',l.string2) AS NewArticlePosition,
lcs AS PossiblePlagiarism
FROM #articles a
CROSS APPLY CommonSubstrings(@submittedArticle,a.article) l
WHERE lcs_len > 20
P.S. I already have a copy so please exclude me from the contest 😉
Edit: note about contest.
-- Itzik Ben-Gan 2001
December 5, 2013 at 10:43 am
I love his books and most valuable in practical life. Like to have this one.
WITH CTE (Rank_Number,row_date,PBXACD_ID,split,center_id) AS
(SELECT
RANK() OVER ( PARTITION BY row_date, PBXACD_ID, split ORDER BY COUNT(*) DESC) AS Rank_Number,
row_date,
PBXACD_ID,
split,
center_id
FROM
TestDb.dbo.TestTable (NOLOCK)
WHERE
row_date >= CAST(DATEADD(day, -3, GetDATE()) AS DATE) AND row_date <= CAST(DATEADD(day, -1, GetDATE()) AS DATE)
AND center_id > 0
GROUP BY
row_date,
PBXACD_ID,
split,
center_id)
Merge TestDb.dbo.default_info AS T
USING
(SELECT
row_date,PBXACD_ID,split,center_id
FROM
CTE
WHERE
Rank_Number = 1) AS S ON S.row_date = T.row_date
AND S.PBXACD_ID = T.PBXACD_ID
AND S.split = T.split
AND S.center_id = T.center_id
WHEN MATCHED AND T.center_id IS NOT NULL THEN
UPDATE SET
T.center_id = S.center_id
WHEN NOT MATCHED THEN
INSERT(row_date,
PBXACD_ID,
split,
center_id,
client_id)
VALUES
(S.row_date,
S.PBXACD_ID,
S.split,
S.center_id,
NULL);
Viewing 15 posts - 151 through 165 (of 287 total)
You must be logged in to reply to this topic. Login to reply