Thursday, September 18, 2008

Run aggregate on datatable column

I had one table with columns "Name" and "Department" which i need to display in a multiline textbox. like below
Name~~~~~~~~ Dept
Tarun Ghosh~~~SWD
Diptangshu Das~SSWD
So that the names and Dept are aligned properly. So i needed the Max of the string length of the column "Name" and give a right-padding to name to make all of same length with the maximum lengthed name.
Firstly i went with the primitive looping through each record and calculate the max of the row, if it is greater than the global max, set global max to current max algo...
But i was not satisfied with the approach even though no problem.
Then i got the
public Object Compute(
string expression,
string filter
function of DataTable. Expression par needs to be some aggregate function lik Sum, Avg,Min,Max,Count,Var. with filter expression is same as where clause in dt.Select() function.
But one problemmmmmmmm.... The expression parameter can't work on computed column. Like say you have two column qty and unit price. You need to get the max(qty*unitPrice), then first you have to add a new column to hold the value of the multiplied result and then use the compute function on that column.
So in my case i wanted to get the max of length. So i added a column as

DataColumn col = new DataColumn("Length");
col.Expression = "Len(Name)";
col.DataType = typeof(int);
object obj= dt.Compute("Max(Length)","");

obj is storing the max length value
Some useful link

Wednesday, September 17, 2008

Get the date diference in the form of x Years y months z days

Somethimes we need to get the difference of two dates in SQL server to send the result to front end. The result of DateDiff SQL function will give the difference of the date for a particular part like year or month or day... If you think of just concatinating the results got from three datediff function with parameters for year,month and day, you will get the wrong result. WHY???
DateDiff(Year,'2007/31/12','2008/1/1') is 1 though the difference only 1 day.
Similarly month differnce and day difference will be shown as 1.
So the result age is "1 year 1 month 1 day". But actyually only "1 day".
Here is a function i wrote to get the exact value. This is very useful when you need to get the age on today when you have DOB stored in your database. Here you have to send the DOB as @startdate and GetDate() as @endDate

Create FUNCTION [dbo].[fn_GetDateDifference] (@startDate as DateTime,@endDate DateTime)
RETURNS varchar(50) AS
DECLARE @res varchar(50)
DECLARE @mon int,@year int,@day int
SELECT @year = DateDiff(year,@startDate,@endDate)
--SET @year = @day/365
SET @startDate = DateAdd(Year,@Year,@startDate)
if(@startDate > @endDate) --If the month value of Start Date is more than that of End Date
--@startDate can become more than date 2 if the values are like
--@startDate = '2007/12/15' and @endDate = '2008/02/01',
--The datediff year will be 1 though only 2 month+ difference
SET @year = @year - 1
SET @startDate = DateAdd(Year,-1,@startDate)
SELECT @mon = DateDiff(Month,@startDate,@endDate)
--SET @mon = @day/28
--Select @mon = DateDiff(Month,@startDate,@endDate)
SET @startDate = DateAdd(Month,@mon,@startDate)
if(@startDate > @endDate)
--Same as year,month can show maximum of 1 unit more if the start date "Day" val in more than end date "Day value"
set @startDate = DateAdd(Month,-1,@startDate)
set @mon = @mon - 1
SELECT @day = DateDiff(Day,@startDate,@endDate)
set @res = ''
If(@year > 0)
SELECT @res = Cast(@year as Varchar(10)) + ' Year(s) '
if( @year>0 or @mon>0)
Set @res = @res + Cast(@mon as Varchar(10)) + ' Month(s) '

set @res = @res + Cast(@day as Varchar(10)) + ' Day(s)'
--set @res = @year & ' '
--select @res = @year & ' ' & @mon & ' ' &@day

Note: It is not a good practice to use backend sqlserver for this kind of calculations. It is preferable to return two dates to front end and then do the desired calculations.