Like it

Showing posts with label SQL tips. Show all posts
Showing posts with label SQL tips. Show all posts

Sunday, July 17, 2011

T-SQL function to Get Maximum of values from the same row

--SELECT dbo.GetMax(23, 45, 64, 22, 18, 224, 74, 138, 1, 98, 11, 86, 198)
--Naturally adjust data type to match what you actually need for your specific values
I've created a set of functions (e.g. GetMaxOfDates3 , GetMaxOfDates13 )to find max of up to 13 Date values.
CREATE FUNCTION GetMaxOfDates13 (
@value01 DateTime = NULL,  
@value02 DateTime = NULL,
@value03 DateTime = NULL,
@value04 DateTime = NULL,
@value05 DateTime = NULL,
@value06 DateTime = NULL,
@value07 DateTime = NULL,
@value08 DateTime = NULL,
@value09 DateTime = NULL,
@value10 DateTime = NULL,
@value11 DateTime = NULL,
@value12 DateTime = NULL,
@value13 DateTime = NULL
)
RETURNS DateTime
AS
BEGIN
RETURN (
SELECT TOP 1 value
FROM (
SELECT @value01 AS value UNION ALL
SELECT @value02 UNION ALL
SELECT @value03 UNION ALL
SELECT @value04 UNION ALL
SELECT @value05 UNION ALL
SELECT @value06 UNION ALL
SELECT @value07 UNION ALL
SELECT @value08 UNION ALL
SELECT @value09 UNION ALL
SELECT @value10 UNION ALL
SELECT @value11 UNION ALL
SELECT @value12 UNION ALL
SELECT @value13
) AS [values]
ORDER BY value DESC    
)
END --FUNCTION
GO
CREATE FUNCTION GetMaxOfDates3 (
@value01 DateTime = NULL,  
@value02 DateTime = NULL,
@value03 DateTime = NULL
)
RETURNS DateTime
AS
BEGIN
RETURN dbo.GetMaxOfDates13(@value01,@value02,@value03,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
END --FUNCTION
 I haven't considered UNPIVOT solution at the time of writing these functions, but it probably will be better.

Sunday, July 10, 2011

Queue in C# / Add and Remove items from Queue in C# (.NET)

Queues are common in real world - Flight checkins to School. Here is the way you can use it in C#

private static void CustomerServiceQueue() 
{ 
Queue CSQ = new Queue(); 
CSQ.Enqueue("Sid"); 
CSQ.Enqueue("Nad"); 
CSQ.Enqueue("Am"); 
CSQ.Enqueue("Sam"); 
Console.WriteLine("Peeking Queue: {0}", CSQ.Peek()); 
Console.WriteLine("Check if CSQ Contains Sid: {0}", CSQ.Contains("Sid"));
while (CSQ.Count > 0) 
{ 
Console.WriteLine(CSQ.Dequeue()); 
} 
}
Contains checks for presence of the given object in the queue. Peek returns the first element from the queue

Dequeuue does the same as Peek, however, it also removes the object


System.Xml.Serialization.XmlSerializer.XmlSerializer() is inaccessible due to its protection level

One reason for this error is if you haven't mentioned the type when you initialize the XmlSerializer object

XmlSerializer Xser = new XmlSerializer()  

can be replaced with

XmlSerializer Xser = new XmlSerializer(this.GetType() );