SmartGroup Help? Published This Year + Added in Last 30 Days

[austinkocher]

Greeting,

I'm writing to ask how to write a SQL/REGEX query for a Smart Group that will return all citations published this year and added in the last 30 days.

The rationale is that I already have two separate Smart Groups that I want to effectively combine. I have a Smart Group for recently added citations and a Smart Group for citations published this year. But a Smart Group that includes anything published this year AND added in the past 30 days would quickly surface the most relevant citations for my purposes.

Note that the regular Static Group options don't seem to include this kind of formulation: added in the past 30 days.

Thanks for any help you can provide!

Austin Kocher

[Jon]

It sounds like you should be able to just OR the two searches. You can do that now without coding by selecting both at once (and setting the joining of multiple selections with and OR by right-clicking in the groups pane).

If want this done in a single group and don't know how, post both SQL commands and I'll do it for you.

Jon
Sonny Software

[austinkocher]

Thanks, Jon.

I'm sorry I can't provide you with a more competent starting point, but here's the best I can do.

As I said, I'm looking for a group of all citations that: (1) were published in the past year and (2) added to the database in the past 30 days.

In the Smart Group interface that doesn't require SQL, I don't see any option for selecting the "Date Added" to the database as an option, so I'm assuming I need to use the more advanced SQL interface. If that's wrong and I can do it in the simple smart group interface without SQL, that would be great, but I don't see that option – so I would need some more direction. If it does require SQL, I don't even know where to start and would still require direction.

I tried asking ChatGPT for help, but I don't think I know enough to even ask the right questions. Sorry!

Austin

[Jon]

You say you have the smart groups already -- they must be SQL smart groups. To see their content, double click on them and they will open for editing. Copy the searches in each (they are plain text) and post them here.

Or did you mean you "want to have"?

This is the search you would enter for entered in the last 30 days

dateAdded > datediff( now(), '01/01/1904 00:00:00', 'second' ) - (30 * 24 * 60 * 60)


This is the search for publications in 2023

thedate REGEX '2023'

You can combine them in one search:

(dateAdded > datediff( now(), '01/01/1904 00:00:00', 'second' ) - (30 * 24 * 60 * 60)) OR (thedate REGEX '2023')

You can refer to the User Guide to see the UI for creating a smart SQL group.

Jon
Sonny Software

[austinkocher]

Thanks so much! This was very helpful. I just replaced OR with AND and got exactly what I needed!

Austin

[Jon]

Are you sure you want that. Using AND, and checking on January 10, 2024, a paper published January 1, 2024 would not be found, event though it had been published in the last 30 days.

Jon
Sonny Software