Archive

Archive for October, 2014

String type to ENum (Generic)

October 21, 2014 Leave a comment

///

/// Allows the discovery of an enumeration value based on the EnumTextValueAttribute
///

/// The type of the enum to get the value for.
/// The text of the EnumTextValueAttribute.
/// boxed representation of the enum value

public static T GetEnumValue(string text)
{
Type enumType = typeof(T);

MemberInfo[] members = enumType.GetMembers();
foreach (MemberInfo mi in members)
{
object[] attrs = mi.GetCustomAttributes(typeof(EnumTextValueAttribute), false);
if (attrs.Length == 1)
{
if (((EnumTextValueAttribute)attrs[0]).Text == text)
return (T) Enum.Parse(enumType, mi.Name);
}
}
throw new ArgumentOutOfRangeException(“text”, text, “The text passed does not correspond to an attributed enum value”);
}

Categories: C#

Ageing procedure

October 9, 2014 Leave a comment

This procedure will return data in age range format.

DECLARE @StartDate DATETIME =’2009-1-1′
DECLARE @EndDate DATETIME=’2010-12-31′
DECLARE @Table TABLE([MONTH] Varchar(20),
[18-24] money,
[25-34] money,
[35-44] money,
[45-54] money,
[55-64] money,
[65+] MONEY,
[Total] money)
INSERT INTO @Table
SELECT CONVERT(VARCHAR(4),YEAR(o.dtApplication)) + ‘/’ + CONVERT(VARCHAR(2),MONTH(o.dtApplication)) AS [MONTH],
sum(case when DATEDIFF(yy, o.DOB, GETDATE()) BETWEEN 18 and 24 then 1 else 0 end) as ’18-24′,
sum(case when DATEDIFF(yy, o.DOB, GETDATE()) BETWEEN 25 and 34 then 1 else 0 end) as ’25-34′,
sum(case when DATEDIFF(yy, o.DOB, GETDATE()) BETWEEN 35 and 44 then 1 else 0 end) as ’35-44′,
sum(case when DATEDIFF(yy, o.DOB, GETDATE()) BETWEEN 45 and 54 then 1 else 0 end) as ’45-54′,
sum(case when DATEDIFF(yy, o.DOB, GETDATE()) BETWEEN 55 and 64 then 1 else 0 end) as ’55-64′,
sum(case when DATEDIFF(yy, o.DOB, GETDATE()) > 64 then 1 else 0 end) as ’65+’,
0 AS [Total]
FROM table o WITH(NOLOCK) where o.dtApplication between @StartDate and @EndDate
GROUP BY CONVERT(VARCHAR(4),YEAR(o.dtApplication)) + ‘/’ + CONVERT(VARCHAR(2),MONTH(o.dtApplication))
— Update total
UPDATE @Table SET [Total] = [18-24] + [25-34] + [35-44] + [45-54] + [55-64] + [65+]
–SELECT * from @Table
— Update average
UPDATE @Table
SET [18-24] =[18-24]/(Total/100),
[25-34] =[25-34]/(Total/100),
[35-44] =[35-44]/(Total/100),
[45-54] =[45-54]/(Total/100),
[55-64] =[55-64]/(Total/100),
[65+] =[65+]/(Total/100)
–Add Bancorp average
INSERT INTO @Table
VALUES ( ‘Average’,
(select AVG([18-24]) FROM @Table),
(select AVG([25-34]) FROM @Table),
(select AVG([35-44]) FROM @Table),
(select AVG([45-54]) FROM @Table),
(select AVG([55-64]) FROM @Table),
(select AVG([65+]) FROM @Table),
(select AVG([Total]) FROM @Table)
)
— Select all
SELECT * from @Table

Categories: SQL