mubangud Absent Member.
Absent Member.
8322 views

Need Help! Setting SQL condition to display Quarterly or 3 month period


I run quarterly reports and would like to set it up so that it always produces the last three months of data when I run the report. Previously I've had to edit the filter to set my time frame but this takes too long with all the reports I have to generate for a quarterly report. Can this be done simply through SQL conditions?

Is there an advanced SQL conditions that will generate a quarterly or previous 3 month report of records in a project? The basic condition would be to display all records that were created in the last previous three months. Generally I would running this report on sometime after the last day of the third month.
0 Likes
9 Replies
Not applicable

Re: Need Help! Setting SQL condition to display Quarterly or 3 month period


if the create date is part of your criteria why not use create date minus 90 keyword. These creates a situation in which the report when run is always NOW() minus 90 days producing a report that should contain all creates in the past 90 days.
0 Likes
Highlighted
mubangud Absent Member.
Absent Member.

Re: Need Help! Setting SQL condition to display Quarterly or 3 month period


Yes I looked into that but I realized that there would be a problem. You could do this if all months were 30 days each. Since some months range from 28 to 31 days then this causes inaccuracy with the reporting. Let's say that 31 issues were generated at the end of the previous quarter, we may be in a situation where those same issues are double counted in the new quarterly report. That is why I am seeking a SQL parameter that is intuitive enough to look 3 months back from the time the report is ran.

In TeamTrack's "Trend report" you can set the Total By: to "Quarters" and I wish this option was also available within the Search Filter part of all the reports.
0 Likes
Not applicable

Re: Need Help! Setting SQL condition to display Quarterly or 3 month period


There are other keywords not sure of all of them but I assume you have been through each one. There's no way I know of to do what you are looking to do with advanced sql
0 Likes
jeff_malin Absent Member.
Absent Member.

Re: Need Help! Setting SQL condition to display Quarterly or 3 month period


Here's an example of doing it by quarter:

http://www.sqlservercurry.com/2009/05.

Using this, the where clause you're looking for to find all tickets submitted since the beginning of the current quarter is:

TS_SUBMITDATE >= DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0)
0 Likes
Not applicable

Re: Need Help! Setting SQL condition to display Quarterly or 3 month period


If he's Oracle he may need the Oracle equivalent this didn't work for me so I assume it's SQL server only
0 Likes
jeff_malin Absent Member.
Absent Member.

Re: Need Help! Setting SQL condition to display Quarterly or 3 month period


Good point - I forgot as I live in MS-SQL-land. I just found this by Google for "SQL start of this quarter" so I'm sure similar examples are available online for Oracle DB if the OP or subsequent viewers of this post need.
0 Likes
mubangud Absent Member.
Absent Member.

Re: Need Help! Setting SQL condition to display Quarterly or 3 month period


This worked just fine...thanks for the help
0 Likes
mobermaier Absent Member.
Absent Member.

Re: Need Help! Setting SQL condition to display Quarterly or 3 month period


For Oracle use: ts_submitdate >= TRUNC(sysdate+1, 'Q'),

to get first and last day of first quarter use

select TRUNC(sysdate+1, 'Q'),TRUNC(ADD_MONTHS(sysdate, +3), 'Q')-1 from dual
0 Likes
mubangud Absent Member.
Absent Member.

Re: Need Help! Setting SQL condition to display Quarterly or 3 month period


When running I wanted to leave a dynamical query that always looks at the previous quarter. Reports are always ran at the beginning of the new quarter rather than the first quarter. I'm sure this could be used when performing a yearly assessment though.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.