| Home > Synergy > Hints and tips > Reports |
 |
-7D is a relative date, and is a single day. Use “between -7D and TODAY” to get last 7 days (8 in fact, counting inclusively) but this is not the same as “last week”.
“Last week” is a date range, inherently two dates, neither of which is ever today by definition.
You need to use “IN” with date ranges eg
…start_date IN LW
or
…start_date IN LY
Which is 1/1/01 to 31/12/01 NOT 11/4/01 to 11/4/02
See help file F1, Advanced Functions, MRL Reference, Misc. Elements for full explanation (copied below)
Dates
Absolute or relative dates may be used:
report for each patient where patient.date_of_birth < 23/7/1940 or patient.date_of_birth > -10Y ;
finds patients born before 23/7/1940 or born more recently than ten years before the date the query is run. The allowable letters in relative dates are Y for years, Q for quarters, M for months, W for weeks and D for days. Relative dates may end with B for beginning or E for end of a week, month, quarter or year. For example:
report for each note where note.start_date > -2QB;
Finds all the notes starting since the beginning of the last quarter but one.
Date ranges
Date ranges may be specified in queries:
report for each patient with note start_date in L5W ;
finds all patients with a note starting in the last five weeks. The allowable letters are L for last, T for this, N for next, and Y, Q, M, W, D as above.
Date expression elements (see chart below)
Element Meaning Example
- – Ago or past -1Y (one week ago)
- + Hence or in the future +1Y (one week hence)
- D Day -5D (5 days ago)
- W Weeks -5W (5 weeks ago)
- M Months -5M (5 months ago)
- Q Quarters -1Q (1 quarter ago)
- Y Years -1Y (1 year ago)
- T This TW (this week)
- L Last LW (last week)
- N Next NW (next week)
- E End -1QE (last quarter end)
- B Beginning -1QB (last quarter beginning)
Further examples of date expressions
+1W would indicate 1 week in the future.
Broken up into its constituents, 1W = “One Week”. This alone is not of much use to the system. You need to specify one week ago or one week hence. In MRL + indicates future (hence) and – indicates past (ago).
There are two types of date expression:
Relative dates (e.g. +3W) are single dates relative to today are
expressed:
+/- followed by a number, followed by D/W/M/Q/Y.
Therefore an expression like +3W indicates the date 3 weeks from today. The date implied by a relative date thus changes every day.
Date ranges (e.g. in N3Y) are two dates, neither of which are today and are expressed:
L (last)/N (next), followed by a number, followed by D/W/M/Q/Y.
These are used with the element IN rather than = or < etc. and work on whole units.
The following expression IN L3W means in the last 3 weeks, i.e. from 3 weeks last Monday to last Sunday (and not including today or any of the current week).
The date range stays the same throughout the week, only switching dates on Mondays. IN LY (with no number) is valid too, and means last year in the normal sense i.e. Jan to Dec, not in the last 365 days, which would be IN L365D and (wouldn’t include today). Alternatively, you could say > -365D to express in the last 365 days.