working on it ...

Filters

snippets
6
followers
1
Published by sjschmalfeld

Cool stuff by sjschmalfeld

This channel collects the brain compositions of this guy
Sort by

Found 6 snippets

    public by sjschmalfeld  582  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  315  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  291  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  305  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  386  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);
    }

    public by sjschmalfeld  11542  7  4  0

    Join Two Datatables

    also deals with duplicate field names
    DataTable targetTable = dataTable1.Clone();
    var dt2Columns = dataTable2.Columns.OfType<DataColumn>().Select(dc => 
    new DataColumn(dc.ColumnName, dc.DataType, dc.Expression, dc.ColumnMapping));
    var dt2FinalColumns=from dc in dt2Columns.AsEnumerable()
                        where targetTable.Columns.Contains(dc.ColumnName) == false
                        select dc;
    targetTable.Columns.AddRange(dt2FinalColumns.ToArray());
    var rowData =from row1 in dataTable1.AsEnumerable()
                 join row2 in dataTable2.AsEnumerable()
                 on row1.Field<int>("ID") equals row2.Field<int>("ID")
                 select row1.ItemArray.Concat(row2.ItemArray.Where(r2=> row1.ItemArray.Contains(r2)==false)).ToArray();
    foreach (object[] values in rowData)
    targetTable.Rows.Add(values);                                                

    List Complete ( 6 snippets total )

    • Public Snippets
    • Channels Snippets