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?

Nhibernate paging for time consuming operations

Imagine, you have to implement batching for a time consuming operation using NHibernate. I would write it like this:

var items = new List<Item>();
for (var page = 0; page == 0 || items.Any(); page++)
{
    items = GetListOfItems(page);
    foreach (var items in items)

    {
        DoStuff(item);
    }
}

public void GetListOfItems(int page)
{
    return CurrentSession.QueryOver<Item>()
        .Select(Projections.Id())
        .Take(PageSize)
        .Skip(PageSize * page)
        .List();
}

But what I found is that if the DuStuff is time consuming then it is possible to have duplicated items from the query. Thatis because of NHibernate generated query:

SELECT TOP (@p0) y0_
FROM (
  SELECT this_.Id AS y0_
  ,ROW_NUMBER() OVER (
    ORDER BY CURRENT_TIMESTAMP
  ) AS __hibernate_sort_row
  FROM contact.sfContact this_
) AS query
WHERE query.__hibernate_sort_row > @p1
ORDER BY query.__hibernate_sort_row;

That order by CURRENT_TIMESTAMP... If you want consistent results you would have to apply specific ordering:

public void GetListOfItems(int page)
{
    return CurrentSession.QueryOver<Item>()
        .Select(Projections.Id())
        .OrderBy(i => i.Id).Asc
        .Take(PageSize)
        .Skip(PageSize * page)
        .List();
}

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; }
}