Getting around DatePart

Posts   
 
    
hlesesne avatar
hlesesne
User
Posts: 47
Joined: 22-Jul-2004
# Posted on: 04-Jan-2006 17:27:08   

Hello Everyone, Happy New Year!

I just wondered if anyone could offer any opinions on the best way to get around the lack of being able to execute DatePart() functions through predicates. I absolutely love LLBL and it is perfect for all of my CRUD operations, but I am running into this limitation a lot when reporting. I find myself running 24 different queries to extract hourly statistics or 30 to run daily statistics. This is fine in our current development stage, but I can see it becoming problematic as we scale up.

I am considering breaking down my date fields as follows

Currently ContactAddDate (datetime)

Chaged to: --> ContactAddDate (date) --> ContactAddHour (int) --> ContactAddMinute (int) --> ContactAddSecond (int)

But I am going to have to rework a good deal of code to make this change and wanted to find out if anyone has found a better solution that allows me to keep my date together.

Any other potential problems with this layout? Indexing? Size? Join complexity?

Thanks for your feed back.

Best regards,

Hal

Posts: 65
Joined: 07-Dec-2005
# Posted on: 04-Jan-2006 17:31:40   

What are you trying to accomplish by performing DatePart()? I can understand that you'd like access to it, but most of the time I've been able to replace it by using a between predicate.

hlesesne avatar
hlesesne
User
Posts: 47
Joined: 22-Jul-2004
# Posted on: 04-Jan-2006 17:38:39   

AdamRobinson wrote:

What are you trying to accomplish by performing DatePart()? I can understand that you'd like access to it, but most of the time I've been able to replace it by using a between predicate.

I am trying to extract something like the following:

select DatePart(Hour,ContactAddDate), count(1) as [New Contacts] from tblContact
    where ContactAddDate between '1/4/2006' and '1/4/2006 12:00:00 PM'
    group by DatePart(hour,ContactAddDate)
    order by DatePart(hour, ContactAddDate)

if I broke out my DateTime into the new fields, I could do something like this:

select Hour, Count(1) as [New Contacts] from tblContact
    where ContactAddDate = '1/4/2006'
    and hour Between 0 and 12
    group by Hour
    order by Hour

which is more "predicable" smile than the first example if I am not mistaken.

Thanks,

Hal

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 04-Jan-2006 17:40:46   
Frans Bouma | Lead developer LLBLGen Pro
hlesesne avatar
hlesesne
User
Posts: 47
Joined: 22-Jul-2004
# Posted on: 04-Jan-2006 18:01:50   

Otis wrote:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829

I noticed that, but could not figure out a way to use it in an aggregate/groupby. Is that possible?

Looked at it a little closer and think I figured it out. Sorry, I'm a doofus.

Thanks - I am always so impressed by the responsiveness of this forum.

Hal

hlesesne avatar
hlesesne
User
Posts: 47
Joined: 22-Jul-2004
# Posted on: 04-Jan-2006 20:15:11   

Just for reference sake...

Here are the three expressions that I created (vb.net).


Imports SD.LLBLGen.Pro.ORMSupportClasses

The first one allows you to specify any function with a single parameter. This is just a cut of your code Frans at the thread mentioned above in vb.net.


<Serializable()> _
Public Class FunctionExpression
    Inherits Expression
    Private _FunctionToApply As String = String.Empty

    Public Sub New(ByVal Field As IEntityField, ByVal FunctionToApply As String)
        MyBase.New(Field)
        _FunctionToApply = FunctionToApply
    End Sub
    Public Sub New(ByVal Field As IEntityField2, ByVal FunctionToApply As String)
        MyBase.New(Field)
        _FunctionToApply = FunctionToApply
    End Sub

    Public Shadows Function ToQueryText(ByRef uniqueMarker As Integer, ByVal inHavingClause As Boolean) As String
        If _FunctionToApply.Length > 0 Then
            Return String.Format("{0}({1})", _FunctionToApply, MyBase.ToQueryText(uniqueMarker, inHavingClause))
        Else
            Return MyBase.ToQueryText(uniqueMarker, inHavingClause)
        End If
    End Function

    Public Property FunctionToApply() As String
        Get
            Return _FunctionToApply
        End Get
        Set(ByVal Value As String)
            _FunctionToApply = Value
        End Set
    End Property
End Class

The second one returns whole date parts without the time. Great except that it returns as a varchar(11) so it is not really sortable by date - better when sorting is not necessary.


<Serializable()> _
Public Class FunctionExpressionToShortDate
    Inherits Expression

    Public Sub New(ByVal Field As IEntityField)
        MyBase.New(Field)
    End Sub
    Public Sub New(ByVal Field As IEntityField2)
        MyBase.New(Field)
    End Sub

    Public Shadows Function ToQueryText(ByRef uniqueMarker As Integer, ByVal inHavingClause As Boolean) As String
        Return String.Format("{0}(varchar(11),{1},101)", "Convert", MyBase.ToQueryText(uniqueMarker, inHavingClause))
    End Function

End Class

The third one converts to the varchar short date, then back to a datetime to allow for sorting based on date. Less efficient, but easy to sort.


<Serializable()> _
Public Class FunctionExpressionToShortDateSortable
    Inherits Expression

    Public Sub New(ByVal Field As IEntityField)
        MyBase.New(Field)
    End Sub
    Public Sub New(ByVal Field As IEntityField2)
        MyBase.New(Field)
    End Sub

    Public Shadows Function ToQueryText(ByRef uniqueMarker As Integer, ByVal inHavingClause As Boolean) As String
        Return String.Format("{0}(datetime,{0}(varchar(11),{1},101))", "Convert", MyBase.ToQueryText(uniqueMarker, inHavingClause))
    End Function

End Class

Thanks again for all the help on this. I can't really think of ANYTHING I can't do with LLBL anymore. sunglasses !

Hal