Removing duplicates from SQL Server 2005

I am currently working on project that is required to remove duplicate entries from Facility table, duplicates are based on address and city being the same. The duplicates would be recorded into an excel file for further examination and later expiry. I was looking for way to rank the entries and ran into this article Removing Duplicate Records using SQL Server 2005 that helped me write my query as follows.


CREATE TABLE #orgexpire

(

organizationuid BIGINT,

expirationrule VARCHAR (100)

);

WITH cte(labid, labname, cliacode, address, state, city)

AS (SELECT o.organizationuid,

o.organizationnm,

o.cliacode,

streetaddr = CASE

WHEN Len(Isnull(pl.streetaddr1, )) > 0 THEN

pl.streetaddr1

ELSE pl.streetaddr2

END,

pl.statecd,

pl.citydesc

FROM dbo.organization o

INNER JOIN dbo.entity e

ON o.organizationuid = e.entityuid

LEFT OUTER JOIN dbo.entitylocatorparticipation elp

ON elp.entityuid = e.entityuid

LEFT OUTER JOIN dbo.postallocator pl

ON elp.locatoruid = pl.postallocatoruid

WHERE o.cd = ‘LAB’ — facility type is lab

AND ( o.totime IS NULL

OR o.totime > Getdate() ) — Lab is not already expired

AND ( o.cliacode IS NULL

OR Len(o.cliacode) = 0 ) — There is no Clia code

AND o.addtime <= ’02/20/2009′

— Lab was not added after STD migration on 2/20/2009

),

dupcte(labid, address, city, ranking)

AS (SELECT labid,

address,

city,

ranking = Dense_rank() OVER (PARTITION BY address, city ORDER BY

(

SELECT

COUNT(p.actuid)

FROM dbo.participation p WHERE p.subjectentityuid =

labid)

DESC)

FROM cte

WHERE address IS NOT NULL

AND city IS NOT NULL)

INSERT INTO #orgexpire

SELECT labid,

‘6121-1 F002’

FROM dupcte

WHERE ranking > 1

SELECT * FROM #orgexpire

Advertisements
Posted in Development | Tagged | Leave a comment

SQL Server 2005 Query XML data stored in ntext field

We moved from SQL 2000 to SQL 2005 so we are still storing the XML data in ntext field. Finally I need to mine the XML content and I thought cool let me write some XPath queries and be done with it. As is the case with everything new there was a slight learning curve and I am trying to document the process.

First thing I need to do was cast my ntext data into xml. Simple!

DECLARE @xmlData AS xml
SELECT @xmlData = CAST(messagexml AS XML) FROM dbo.H2O_Message WHERE MessageID = 80467

The query editor responded with the following SQL error message
SQL Server Database Error: XML parsing: line 1, character 38, unable to switch the encoding

After some googling it turns out that SQL server works with UTF-16 and has issues with UTF-8 XML document. No problem, lets switch XML header tag. Well, the field is ntext so I need to cast the field to nvarchar(max) then replace the utf-8 to utf-16

DECLARE @xmlData AS xml
SELECT @xmlData = CAST( REPLACE(CAST(messagexml AS nvarchar(MAX)),'utf-8', 'utf-16') AS XML) FROM dbo.H2O_Message WHERE MessageID = 80467

Wonderful all I had to do then was query the Xpath I needed. There are various examples on the web on to do this so  I would not go into details here.

Here is the final SQL statement to get MessageControlID element text from my xml document stored as ntext field in SQL server 2005 Database.

DECLARE @xmlData AS xml
SELECT @xmlData = CAST( REPLACE(CAST(messagexml AS nvarchar(MAX)),'utf-8', 'utf-16') AS XML) FROM dbo.H2O_Message WHERE MessageID = 80467

SELECT msg.query('data(/ObservationMessage/MessageControlID)') FROM (SELECT @xmlData msg) tbl

Posted in Development | Tagged | 1 Comment

C# Delegate Covariance and Contra-variance

Covariance allows us to have a more derived type as return type
Contra-variance allows us to have less derived type as parameter type than what is specified in the delegate.
Example:

using System;

using System.Collections.Generic;

public class MyBase

{

public virtual void MyName()

{

Console.WriteLine(“from base”);

}

}

public class Derived : MyBase

{

public override void MyName()

{

Console.WriteLine(“from derived”);

}

}

public class MyClass

{

public delegate MyBase Test(Derived d);

public static void RunSnippet()

{

//C# Delegate Covariance and Contra-variance

//Covariance allows us to have a more derived type as return type

//Contra-variance allows us to have less derived type as parameter type than what is specified in the delegate.

Test t = new Test(TestFunction);

MyFunction(t);

RL();

}

public static void MyFunction(Test t)

{

//WL(t.GetType().ToString());

WL(t.GetInvocationList()[0].Method.Name);

}

public static Derived TestFunction(MyBase d)

{

d.MyName();

Derived d2 = new Derived();

return d2;

}

#region Helper methods

public static void Main()

{

try

{

RunSnippet();

}

catch (Exception e)

{

string error = string.Format(“—\nThe following error occurred while executing the snippet:\n{0}\n—“, e.ToString());

Console.WriteLine(error);

}

finally

{

Console.Write(“Press any key to continue…”);

Console.ReadKey();

}

}

private static void WL(object text, params object[] args)

{

Console.WriteLine(text.ToString(), args);

}

private static void RL()

{

Console.ReadLine();

}

private static void Break()

{

System.Diagnostics.Debugger.Break();

}

#endregion

}

Posted in Development | Leave a comment

C# Anonymous Methods and Lambda Expressions

In the following example talks about anonymous methods, method group conversions and finally lambda expressions.

using System;

using System.Collections.Generic;

public class CarEventArgs : EventArgs

{

public string Msg { get; set; }

public CarEventArgs(string msg)

{

Msg = msg;

}

}

public class Car

{

private int _speed = 0;

public event EventHandler<CarEventArgs> MyEvent;

public void Acccelarate(int speed)

{

_speed += speed;

if (MyEvent != null)

{

MyEvent(this, new CarEventArgs(string.Format(“Current speed {0}”, _speed)));

}

}

}

public class MyClass

{

public static void RunSnippet()

{

Car c = new Car();

//traditional event method call.

c.MyEvent += new EventHandler<CarEventArgs>(TestMethod);

//c# method group conversion, let the compiler figure out the delegate type from method signature.

c.MyEvent += TestMethod;

//c# anonymous method — associate delegate directly to block of code statements at the time of event regristration.

c.MyEvent += delegate(object sender, CarEventArgs args) { WL(“from Anonymous method.” + args.Msg); };

//Lamda expression: more consise way of authoring anonymous methods.

//lamda expression syntax: ArgumentsToProcess => statementsToProcessThem;

c.MyEvent += (s, e) => { WL(“lamda “ + ((CarEventArgs)e).Msg); };

c.MyEvent += (object s, CarEventArgs e) => { WL(“lamda with variables declared:” + e.Msg); };

c.Acccelarate(10);

RL();

}

public static void TestMethod(object sender, CarEventArgs args)

{

WL(args.Msg);

}

#region Helper methods

public static void Main()

{

try

{

RunSnippet();

}

catch (Exception e)

{

string error = string.Format(“—\nThe following error occurred while executing the snippet:\n{0}\n—“, e.ToString());

Console.WriteLine(error);

}

finally

{

Console.Write(“Press any key to continue…”);

Console.ReadKey();

}

}

private static void WL(object text, params object[] args)

{

Console.WriteLine(text.ToString(), args);

}

private static void RL()

{

Console.ReadLine();

}

private static void Break()

{

System.Diagnostics.Debugger.Break();

}

#endregion

}

Posted in Development, Uncategorized | Tagged | Leave a comment

Using the SQL 2005 Pivot Clause

Let’s say you are querying interview table for the interview id 488



Lets say we are only interested in these 3 disease specific fields



Lets get the interview values for the above fields along with the values from the interview table



The problem as you can see is that the interview data first 3 columns get repeated. Let’s use pivot clause to get the information in a single row.

Let’s create a Common Table Expression query to get all the data for the fields we are interested in from Interview Value table


Now lets Pivot Common Table expression table so that we can get an aggregate value like Count, sum, min, max etc., of field value for given field id.



The use of the square brackets [ ] around the column names in both the SELECT and the IN in the pivot clause is required.

These are keys, because the pivot operation is treating the values in these columns as column names.

Effectively you are grouping field values by two columns InterviewID and FieldID, then pivoting field id ie., you are treating the row values of field id as column names.

All we need to do now is to join our pivot to the interview table to get the required result.



That’s all folks!

Posted in Development, Uncategorized | Tagged , | Leave a comment

using Enums as data source for a drop down list box in asp.net

We can discuss the merits of using enums as data sources at a different time. But I needed to use an enum as datasource and wanted to have a space in one of the enum values. The article by Luke explains how we can go about it. I updated code just a bit to stream line adding the enum as datasource to drop down list control.

 #region Enum Description

 

        /// <summary>

        /// Gets the enum list.

        /// </summary>

        /// <typeparam name=”T”></typeparam>

        /// <returns></returns>

        public static List<string> GetEnumList<T>()

        {

            List<string> enumValues = new List<string>();

            foreach (T enumVal in EnumToList<T>())

            {

                enumValues.Add(GetEnumDescription(enumVal));

            }

            return enumValues;

        }

 

        /// <summary>

        /// Enums to list.

        /// </summary>

        /// <typeparam name=”T”></typeparam>

        /// <returns></returns>

        public static List<T> EnumToList<T>()

        {

            Type enumType = typeof(T);

 

            // Can’t use generic type constraints on value types,

            // so have to do check like this

            if (enumType.BaseType != typeof(Enum))

                throw new ArgumentException(“T must be of type System.Enum”);

 

            Array enumValArray = Enum.GetValues(enumType);

            List<T> enumValList = new List<T>(enumValArray.Length);

 

            foreach (int val in enumValArray)

            {

                enumValList.Add((T)Enum.Parse(enumType, val.ToString()));

            }

 

            return enumValList;

        }

 

 

        /// <summary>

        /// Gets the enum description.

        /// </summary>

        /// <typeparam name=”T”></typeparam>

        /// <param name=”value”>The value.</param>

        /// <returns></returns>

        public static string GetEnumDescription<T>(T value)

        {

 

            Type enumType = typeof(T);

 

            // Can’t use generic type constraints on value types,

            // so have to do check like this

            if (enumType.BaseType != typeof(Enum))

                throw new ArgumentException(“T must be of type System.Enum”);

 

            FieldInfo fi = value.GetType().GetField(value.ToString());

 

            DescriptionAttribute[] attributes =

                (DescriptionAttribute[])fi.GetCustomAttributes(

                    typeof(DescriptionAttribute),

                    false);

 

            if (attributes != null && attributes.Length > 0)

                return attributes[0].Description;

            else

                return value.ToString();

        }

 

        /// <summary>

        /// Gets the eum from description.

        /// </summary>

        /// <typeparam name=”T”></typeparam>

        /// <param name=”value”>The value.</param>

        /// <returns></returns>

        public static T GetEumFromDescription<T>(string value)

        {

            Type enumType = typeof(T);

            if (enumType.BaseType != typeof(Enum))

                throw new ArgumentException(“T must be of type System.Enum”);

 

            T returnValue = default(T);

            foreach (T enumVal in EnumToList<T>())

            {

                if (GetEnumDescription(enumVal) == value)

                {

                    returnValue = enumVal;

                    break;

                }

            }

            return returnValue;

        }

        #endregion

Sample Enum

using System.ComponentModel;

namespace TestEnumNameSpace

{

   public enum AgentDrugResult

    {

        Intermediate,

        [Description(“Not Done”)]

        NotDone,

        Resistant,

        Susceptible,

        Unknown,

    }

}

Call to attach the sample enum as datasource

 ddl.DataSource = GetEnumList<AgentDrugResult>();

Posted in Development | Tagged | 1 Comment

Add Column with a check on acceptable range of values

Here is the code to add a new column to an existing table with a check on range. The following column only allows values of 0,1 or 2.


IF NOT EXISTS (SELECT c.name FROM dbo.sysobjects s
INNER JOIN dbo.syscolumns c ON s.id = c.id
WHERE s.id = object_id(N'[dbo].[H2O_ResultMap]') AND c.name = 'ResultTypeFlag')
BEGIN
ALTER TABLE dbo.H2O_ResultMap ADD ResultTypeFlag INT NOT NULL
, CONSTRAINT df_ResultMap_ResultTypeFlag DEFAULT 0 FOR ResultTypeFlag
, CONSTRAINT chk_ResultMap_ResultTypeRange CHECK (ResultTypeFlag >=0 AND ResultTypeFlag <=2)
END
GO

Posted in Development | Tagged | Leave a comment