locked
Query based on number of days: How to filter based on last year, or last 2 years? RRS feed

  • Question

  • I have a computed property on an employee table, which returns the number of days since an employee has last completed a yearly certification requirement. I'd like to be able to, in another search screen, is have a query (through the use of a picklist) return either ONLY those employees that have completed the certification within the last year (365 days) OR the last 2 years (730 days) based on current date (the date the query is executed). 

    There are many examples of querying by date ranges, either since a certain day, or between two dates, but I'm struggling with both how to bind that to "within the last 365 days". 

    Thanks for any pointers to a good example on how to accomplish this! 

    Saturday, March 8, 2014 6:29 PM

Answers

  •         partial void EmployeeByRank_PreprocessQuery(ref IQueryable<Employee> query)
            {
                var lastYear = DateTime.Now.AddYears(-1);
                query = from q in query
                        where q.Modified > lastYear
                        select q;
            }
    

    That is a sample to check for a 'Modified' date in the last year added to a custom query's PreProcess event. There are other options too.


    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

    • Marked as answer by tpcolson Sunday, March 9, 2014 4:07 PM
    Saturday, March 8, 2014 7:00 PM

All replies

  •         partial void EmployeeByRank_PreprocessQuery(ref IQueryable<Employee> query)
            {
                var lastYear = DateTime.Now.AddYears(-1);
                query = from q in query
                        where q.Modified > lastYear
                        select q;
            }
    

    That is a sample to check for a 'Modified' date in the last year added to a custom query's PreProcess event. There are other options too.


    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

    • Marked as answer by tpcolson Sunday, March 9, 2014 4:07 PM
    Saturday, March 8, 2014 7:00 PM
  • I hope I'm on the right track here with the following, where "Datefit" is the column containing the date the certification was completed. 

            Private Sub QRYDateFit_PreprocessQuery(ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Employee))
                Dim lastYear = DateAndTime.Now.AddYears(-1)
                query = From q In query
                        Where q.DateFit > lastYear
                 Select q
            End Sub

    Forgive what seems to be a dumb question, I'm at the beginning of the LS learning curve here, but would I add this query as a "Query" to a search screen, then add a query parameter "lasyYear", which would then be bound to this query? 

    Thanks!

    Saturday, March 8, 2014 8:38 PM
  • So here is the solution based on Xpert360's answer:

         Private Sub QRYDateFit_PreprocessQuery(Year As System.Nullable(Of Integer), ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Employee))
                'if a user-supplied year range is selected, use that value in the query. 
                'values are "1","2", and "10" (integers)
                If Year.HasValue Then
                    Dim lastYear = DateAndTime.Now.AddYears(-Year.ToString)
                    query = From q In query
                            Where q.DateFit > lastYear
                     Select q
                    'if the user does nothing, the search 
                    'defaults to certifications valid within the last year
                Else
                    Dim lastYear = DateAndTime.Now.AddYears(-1)
                    query = From q In query
                            Where q.DateFit > lastYear
                     Select q
                End If
            End Sub

    "Year" is an optional integer parameter that is added to "QRYDateFit" on the employee table. 

    A search screen was created using "QRYDateFit"  and an integer local property called "Year" was added to the screen.

    The query parameter "Year" was bound to the "Year" local property. 

    The local property "Year" was populated with a choice list of 1, 2, or 10. 

    At run time, the screen defaults to returning employees only whom have taken a test within the last year, or the user can select within the last 2 or 10 years. 

    Not elegant at all, but gets the job done. Thanks for your answer!


    • Edited by tpcolson Sunday, March 9, 2014 4:37 PM
    Sunday, March 9, 2014 4:06 PM