Skip to Content
author's profile photo Former Member
Former Member

How can I create an arbitrary date group?

Post Author: whimbrel

CA Forum: Crystal Reports

For a support desk we want a Crystal Report to restrospectively count and graph the number of calls that were still open at the end of each month for each month over a period of say 13 months.

So for example, given an input via parameters of month: May year: 2006 , how many calls were still open at the end of May 2006, how many at the end of June 2006 etc up to the end of May 2007.

The relevant database items are call.date_logged, call.date_closed and call.status e.g. open, closed.

The problem I have is to create an arbitrary date group to represent each month given that I can't target the database date fields.

For example a call might have been logged in a month before the target month and if closed the closed date would be in a month after the target month e.g for target month of May, the call might have been logged in April then closed in June so would be still open at the end of May but the date logged would be in April and the date closed in June and neither of these dates are in May.

Hope I've explained this sufficiently clearly. Any help would be gratefully appreciated.

Thanks,whimbrel.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Nov 20, 2007 at 07:01 PM

    Post Author: Ranjit

    CA Forum: Crystal Reports

    See, below query will give you counts of open calls per month.

    select distinct(count(*) over (partition by To_char(call.date_logged, 'MM/YYYY'))) cnt, to_char(call.date_logged,'MM/YYYY') dt from Call a

    where Upper(call.status)<>'CLOSE'

    Regards

    Ranjit

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 21, 2007 at 09:10 AM

    Post Author: whimbrel

    CA Forum: Crystal Reports

    Thanks for your reply, Ranjit.

    Unfortunately the query would not work as, if you read my post, the aim was to count the calls that were still open at the end of each month, regardless of their present status. So a call which could now have a status of 'CLOSED' but was still open at the end of a particular month would need to be counted for that month.

    Regards,whimbrel.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 21, 2007 at 05:56 PM

    Post Author: yangster

    CA Forum: Crystal Reports

    essentially what you need to do is isolate each month and determine if the close date occurred within the same month you are dealing withso for example lets say a call was opened in Jan and the closed date occurred feb 2your formula would go something like thisif call_logged <= date(2007, 01, 31) and closed_date >= date(2007, 02, 01) then 1 else 0so as long as the call was created on or before the end of the month and the close date occurred after the end of the month before then it was still open at the end of the monthyou'll have to do some tweaking based on what exact dates you are trying to count but this should give you the general direction of what you need to do

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 14, 2007 at 02:24 PM

    Post Author: whimbrel

    CA Forum: Crystal Reports

    Yangster, thanks for the reply.

    I don't know if I'm missing something here.

    The formula you describe would be appropriate to the calls still open at the end of January 2007.However I want to be able to graph the situation for a year in monthly divisions.You seem to be saying that I need to create a formula for each month? if so how would I group theseso as to be able to bring them together to show in a single graph? Is it not possible to derive a more general formula?

    My original thoughts were that I'd need to create a formula to specify the required date rangeindependent of the dates the calls were logged or closed, (such as some SQL Server database system date) then somehow relate the logged or closed dates to this date range as appropriate.

    A related report that I have to produce has a similar problem where I want to display boththe count of calls logged and calls closed in each month over a particular year in the same graph. Once again the date closed could be in the same month as the date logged or a different month. As far as I can see if I group on the date logged the count of calls closed wouldn't be right and vice versa.

    So I would need an arbitrary date to group on?

    Any further advice would be appreciated.

    Regards,whimbrel.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.