How to compare approximate dates with SQL

Let say you have to write a code for the periodic payment application, and in your database you store the payment start date. After that you want to check if the next payment is within +/- 1 day of the current date - do something. And obviously, you font want to write a lot of code to do that :)

The first idea I had was to create a whole bunch of unions for each day and then check if current date is one of those, but later I shrinked that to 3 conditions connected with 'or' (here is NHibernate function for that)

var settlementDateDifferenceFromNow = Projections.SqlFunction(
 new SQLFunctionTemplate(
  NHibernateUtil.Int32,
  @"
  CASE WHEN
  ABS(DATEDIFF(DAY, GETDATE(), DATEADD(MONTH, DATEDIFF(MONTH, ?1, GETDATE()) - 1, ?1))) <= 1 OR
  ABS(DATEDIFF(DAY, GETDATE(), DATEADD(MONTH, DATEDIFF(MONTH, ?1, GETDATE()), ?1))) <= 1 OR
  ABS(DATEDIFF(DAY, GETDATE(), DATEADD(MONTH, DATEDIFF(MONTH, ?1, GETDATE()) + 1, ?1))) <= 1
  THEN 1
  ELSE 0
  END
  "),
 NHibernateUtil.Int32,
 Projections.Property<Contract>(x => x.SettlementDate_Actual)
);

I am not sure about 'or' conjunction tho... would it cause performance problems?

Calculating Median value with NHibernate

NHibernate provides a lot of useful projections to make your life easier in case of statistics queries. But it does not provide you with median value, for example and this is what was needed for me recently. (Just in case here is a link for the definition of median)

To simplify the example, lets imagine that I`m querying over one table 'Sales' that have a reference to 'Employee' object (the person who made that sale) and a 'Volume' column.

The requirement is to produce a report with a total number of sales, average, total and median volumes for each employee.

As always, here is a code:

var sql = @"(SELECT AVG([Volume])
FROM (
  SELECT
    [Volume],
    ROW_NUMBER() OVER (ORDER BY [Volume] ASC, Id ASC) AS RowAsc,
    ROW_NUMBER() OVER (ORDER BY [Volume] DESC, Id DESC) AS RowDesc
  FROM Sales
  WHERE [Volume] > 0 and [EmployeeId] = ?1
  ) ra
WHERE RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
)";

var medianFunc = new SQLFunctionTemplate(NHibernateUtil.Double, sql);

var querry = Session.QueryOver(); //and join needed aliases

var resultList = querry
                  .SelectList(builder => builder
                                .Select(Projections.Group(s => s.Employee)).WithAlias(() => result.Employee)
                                .Select(Projections.Count(s => s.Id)).WithAlias(() => result.TotalSales)
                                .Select(Projections.Sum(s => s.Volume)).WithAlias(() => result.TotalVolume)
                                .Select(Projections.Avg(s => s.Volume)).WithAlias(() => result.AverageVolume)
                                .Select(Projections.SqlFunction(medianFunc, NHibernateUtil.Double, Projections.Property("Employee"))
                                     .WithAlias(() => result.MedianVolume))
                              )
                  .TransformUsing(Transformers.AliasToBean())
                  .List();

protected class Result
{
  public int TotalSales{ get; set; }

  public Money TotalVolume { get; set; }

  public double AverageVolume { get; set; }

  public double MedianVolume { get; set; }

  public Employee Employee { get; set; }
}

Nullable operations

Today I faced a funny and unexpected thing. What do you think is the result of this:

int? x, y, z;
z = 7;
x = y + z;

No, it`s not 7, as I expected, but x == null! So just be careful when working with nullable types!
And just as another reminder:

default(int?) == null;
default(int) == 0;

Probably this behavior comes from the SQL and its three-valued logic (here is a great article about it SQL and the Snare of Three-Valued Logic).