We help IT Professionals succeed at work.

Quarterly Report Query Filtering

GeneBat
GeneBat asked
on
Hi Experts –

I need some help with the following query. It’s to show all Quarters but the one you’re in and I don’t know how to set that up correctly.

For example:
1. This Quarterly report needs to pull all Claims that are NOT (NCCI or IN).
2. Where there is NO CLOSED Date.
3. And the Date Referred (DateOpened) is prior to the Quarter we are pulling data on. This is where I’m having a problem fixing this query.

My Quarters in the year run as follows:
1st Q: Jan, Feb, Mar; 2nd Q Apr, May, Jun; 3rd Q Jul, Aug, Sep; 4th Q Oct, Nov, Dec
I put this here If you need to know that information.

As always any help is appreciated.

***** see example database *****  


Thanks in advance,

GeneBat

quarterlyreport.mdb
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016
Commented:
try this query

SELECT ClaimMasterTable.Insured AS [Insured Name], ClaimMasterTable.CLUAnalyst AS Analyst, ClaimMasterTable.TrvAtty AS [Attorney Firm], ClaimMasterTable.ClaimNbr AS [Claim Number], ClaimMasterTable.DateOpened AS [Date Referred], ClaimMasterTable.Class AS [Business Unit], ClaimMasterTable.DateClosed AS [Date Closed], DatePart("q",[DateOpened]) AS Q
FROM ClaimMasterTable
WHERE (((ClaimMasterTable.Class) Not In ("NCCI","IN")) AND ((ClaimMasterTable.DateClosed) Is Null) AND ((DatePart("q",[DateOpened]))<DatePart("q",Date())));
CERTIFIED EXPERT
Top Expert 2016

Commented:

to get just the previous quarter

SELECT ClaimMasterTable.Insured AS [Insured Name], ClaimMasterTable.CLUAnalyst AS Analyst, ClaimMasterTable.TrvAtty AS [Attorney Firm], ClaimMasterTable.ClaimNbr AS [Claim Number], ClaimMasterTable.DateOpened AS [Date Referred], ClaimMasterTable.Class AS [Business Unit], ClaimMasterTable.DateClosed AS [Date Closed], DatePart("q",[DateOpened]) AS Q
FROM ClaimMasterTable
WHERE (((ClaimMasterTable.Class) Not In ("NCCI","IN")) AND ((ClaimMasterTable.DateClosed) Is Null) AND ((DatePart("q",[DateOpened]))=DatePart("q",Date())-1));

Author

Commented:
Works as expected! Thanks Cap1 for clearing that up for me and showing me something new.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.