how to pul record for 1 month using database view

[PatriciaA987250]

Hi,

I've a database view to pull records from three tables. The report generation and distribution will be automated and i want to pull records for every 1 month. e.g. In the month of  July, i want to pull records from 1st  June to 30th June. I've tried something like this at the time_card_daily where clause:-

timecard_sys_id=timecarddaily_time_card && timecarddaily_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) but it doesnt work. I've attached database view screenshot for your reference. Kindly advise on the solution.

[Mark Manders · Accepted solution]

Why go through the DB view to filter records? Why not just create a report with 'created on last month' that you schedule on every first of the month? Your DB view will contain all records and your report will only collect the ones from last month, while your scheduled report distributes it to all recipients.

[Yashsvi]

Hi @PatriciaA987250,

please check below link:

https://www.servicenow.com/community/developer-forum/database-view-date-parameter/m-p/1417336

Thank you, please make helpful if you accept the solution.

[Mark Manders · Accepted solution]

Why go through the DB view to filter records? Why not just create a report with 'created on last month' that you schedule on every first of the month? Your DB view will contain all records and your report will only collect the ones from last month, while your scheduled report distributes it to all recipients.

[AndersBGS · Accepted solution]

HI @PatriciaA987250 ,

What timestamp are you trying to filter on when creating a database view on the tree tables? From my point of view, I would create a report based on the database view where I would set the conditions accordingly on all three tables. This can further more be a scheduled report, so you will receive it monthly according to your need.

If my answer has helped with your question, please mark my answer as accepted solution and give a thumb up.

Best regards

Anders

[PatriciaA987250]

Sorry for the late reply. I wanted to filter based on date field from timecard daily table