Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Dates in Consecutive Quarters

Status
Not open for further replies.

fishtek

Technical User
Aug 21, 2002
56
US
I'm trying to figure out a query statement that would identify when 2 dates occur within consecutive quarters (but not the same quarter). I've been messing around with DatePart and DateAdd but not getting very far. Thanks for any help.
 
In Oracle you can do: [tt]SELECT TO_CHAR(SYSDATE, 'Q') FROM dual[/tt]
Access should take [tt]Format(YourDateFieldHere,"q") [/tt]



Have fun.

---- Andy
 
Andy,
I think there is a need to consider the year since Format(#4/1/2012#,"Q) = 2 and Format(#7/1/2013#,"Q") = 3 but clearly these are not consecutive quarters.

Duane
Hook'D on Access
MS Access MVP
 
Thanks dhookum, can I use a variation of DateDiff("Q",date1,date2) = 1 to give me a count of ocurrences in a list of dates. In other words, I have a list of dates on a report where an event occurs and I would like Access to tell me how many times the event occurs in consecutive quarters.
Thanks again for your assistance.
 
I would like Access to tell me how many times the event occurs in consecutive quarters

So if an event occurs in the first two quarters of year one and in the last three quarters of year two, is the answer to your question two (meaning there were two periods in which the event occurred in consecutive quarters) or three (it occurred in quarter one and quarter two in year one, quarter two and quarter three in year two, and quarter three and quarter four in year two)?
 
Thanks guys. See the example below. The result would be a test fail that occurred in three consecutive quarters with a total of 5 fail events. Again, I am looking for Access to look at an array of dates and tell me how may fails occurred in consecutive quarters. In this case 3.

Thanks

2009
1/1/2009 Fail
4/1/2009 Fail
7/22/2009 Pass
12/5/2009 Pass


2010
2/3/2010 Pass
4/30/2010 Fail
8/25/2010 Fail
11/3/2010 Fail
 
There will only be one record per quarter and I am only concerned about the test date.

thanks
 
Given this data:
Code:
ID	testdate	results
1	1/1/2009	Fail
2	4/1/2009	Fail
3	7/22/2009	Pass
4	12/5/2009	Pass
5	2/3/2010	Pass
6	4/30/2010	Fail
7	8/25/2010	Fail
8	11/3/2010	Fail

This query:
Code:
select sum(streak) as con_fails

from
(

SELECT 
iif(a.results=b.results, 1,0) as streak

from
testresults a,
testresults b

where a.results ='fail'
and datediff ("q", b.testdate, a.testdate) =1 
)

returns:
Code:
con_fails
3
 
Thanks BigRed1212:

I'm having trouble making this code work. I tried it in the SQL window of the query but keep getting error messages. Can this code be modified to work in a module then called up from a report?
Thanks Again

Code:
select sum(streak) as con_fails

from
(

SELECT 
iif(a.results=b.results, 1,0) as streak

from
testresults a,
testresults b

where a.results ='fail'
and datediff ("q", b.testdate, a.testdate) =1 
)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top