sjschmalfeld
sjschmalfeld
Reputation Top 1%
sjschmalfeld
10 Snippets  (86th place)
Published
1 Channel
Created
14 Channels
Following
Dec 31, 2018
Last Visit
Aug 4, 2014
Registered
3697 points  (27th place)
Reputation
Junior Code Generator
Serious Code Generator
Senior Code Generator
Junior Publisher
Serious Publisher
Junior Trend Maker
Junior Influencer
Junior Popular Coder
Serious Popular Coder
Junior Autobiographer
Serious Autobiographer
Junior Famous Coder
Junior Wise Coder

Recent Snippets See all snippets by sjschmalfeld

public by sjschmalfeld created Jun 2, 2016  506  3  4  0

SQL Duplicates

WITH cte AS
(SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2, col3, col4 ORDER BY (SELECT 0)) AS DuplicateRowNumber
    FROM table
)
SELECT FROM cte WHERE DuplicateRowNumber > 1


or----

WITH cte as
(SELECT PEOPLE.*, Loan.LoanID, ROW_NUMBER() OVER (PARTITION BY SocialSecurityNo ORDER BY SocialSecurityNo) AS DuplicateRowNumber
FROM people INNER JOIN LOAN ON FK_PeopleID=PeopleID WHERE ISNULL(SocialSecurityNo,'') <> '' AND SocialSecurityNo <> '--' AND SocialSecurityNo <> '000-00-0000' AND loan.Status='active' )

SELECT * INTO #TEMP99 FROM CTE WHERE cte.DuplicateRowNumber > 1

SELECT *,b.SocialSecurityNo FROM dbo.Loan 
	INNER JOIN (SELECT PeopleID,SocialSecurityNo FROM PEOPLE WHERE SocialSecurityNo	 
							IN (SELECT SocialSecurityNo FROM #temp99)) b 
	ON B.PeopleID=LOAN.FK_PeopleID
	WHERE LOAN.Status='ACTIVE'

DROP TABLE #TEMP99  


-- Another way
	--GET RID OF #NewAddressCandidates ADDESSES ALEADY IN THE ADDRESS TABLE
	DELETE FROM #NewAddressCandidates WHERE EXISTS (
	SELECT t.[Address1] 
				,t.Address2
				,t.City
				,t.[State]
				,SUBSTRING(t.Zip,1,5) AS ZIP, 
				SUBSTRING(t.Zip,7,4) AS ZIP4 
		FROM #NewAddressCandidates T 
			INTERSECT  
		SELECT [Address1] ,[Address2],[City],[State],[Zip],ZIP4 FROM dbo.[Address])				
		
		
-- Another way
	WITH NODUP AS 
		(SELECT  A.ADDRESS1,A.ADDRESS2,A.CITY,A.STATE,A.ZIP,A.ZIP4
			  , row_number() OVER ( PARTITION BY  A.ADDRESS1,A.ADDRESS2,A.CITY,A.STATE,A.ZIP,A.ZIP4 ORDER BY  A.ADDRESS1,A.ADDRESS2,A.CITY,A.STATE,A.ZIP,A.ZIP4 ) AS nr
		FROM    #NewAddressCandidates A)
	DELETE FROM NODUP WHERE NR > 1;	
	
	SELECT * INTO  #UniqueNewAddresses  FROM #NewAddressCandidates WHERE EXISTS (
	SELECT t.[Address1] 
				,t.Address2
				,t.City
				,t.[State]
				,SUBSTRING(t.Zip,1,5) AS ZIP, 
				SUBSTRING(t.Zip,7,4) AS ZIP4 			
		FROM #NewAddressCandidates T 
			EXCEPT 
		SELECT [Address1] ,[Address2],[City],[State],[Zip],ZIP4 FROM dbo.[Address])
	
	// Now to remove the duplicate rows, use the same table and use EXCEPT operator with an 
	// empty result set returned by the same table	
	// USING except operator	
	SELECT col1,col2
  FROM DuplicateRcordTable
  EXCEPT
  SELECT col1,col2
  FROM DuplicateRcordTable WHERE 1=0
								
;

public by sjschmalfeld created May 21, 2015  273  0  4  0

Search TList with LINQ

TList<Global_AppValueList> TLMaritalCodes = new TList<Global_AppValueList>();
TLMaritalCodes = DataRepository.Global_AppValueListProvider.GetByListName("Marital");

// Check to see if this is a valid Marital Code because orig system had some oddities in the data
var MaritalCodeMatched =
         from mc in TLMaritalCodes
         where mc.ListValue.Contains(pP.MaritalStatus.ToString())
         select mc;

TList<Global_AppValueList> TLFilteredList = new TList<Global_AppValueList>((IList)MaritalCodeMatched.ToList<Global_AppValueList>());
						
;

public by sjschmalfeld created Dec 8, 2015  260  1  4  0

Getting Min or Max value without aggregates

SQL Server Transact SQL
// Enter here the actual content of the snippet.
// Select max value without using MAX()

//Version 1
SELECT *
  FROM SO.dbo.MaxNoAgg mna1
LEFT JOIN SO.dbo.MaxNoAgg mna2 ON (mna2.salary > mna1.salary)
WHERE mna2.mna_id IS NULL;

//version 2
SELECT age
FROM Owner o1
WHERE NOT EXISTS (SELECT 1 FROM Owner o2 WHERE o2.age > o1.age)            						
;

public by sjschmalfeld created Nov 6, 2017  274  1  4  0

Null parameters in Stored Procedure

Do not use coalesce methodology, causes table scan
// Dont do this: LastName = COALESCE (@LastName, LastName) 


WHERE (LastName = @LastName OR @LastName IS NULL) 						
;

public by sjschmalfeld created Oct 30, 2017  353  1  4  0

Datatable to XML

Converting a datatable (or dataset) to XML
//To go to a string
string result;
using (StringWriter sw = new StringWriter()) {
dataTable.WriteXml(sw);
result = sw.ToString();
}	

//To go to  memory stream and XPath document
XPathDocument result;
using (MemoryStream ms = new MemoryStream()) {
dataTable.WriteXml(ms);
ms.Position = 0;
result = new XPathDocument(ms);
}
;