Avatar of Marcos27
Marcos27
Flag for United States of America asked on

Chart by Month and show month even if there are no records

Hello Experts -

I'm using Crystal 10 and I want to chart records by Date_Opened. The problem is that I want to chart the number of records per month and still show the months that return no records at all. Right now, my chart just skips those months within my defined date range. Help!

Thanks,
Marcos
Crystal ReportsDB Reporting ToolsMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
Koen Van Wielink

Can you provide your database query? You will have to insert records into the data for the missing months. Not sure how to do that without seeing your query.
vasto

You need to include a table with a record for each month and left join this table with your data
PortletPaul

vasto has put it nicely, you must provide all the months as the FROM table and then left join your existing data to this.

A small recursive CTE is often a solution for ensuring all the time units appear and then this used as the from table. e.g.
declare @BeginsAt as datetime
set @BeginsAt = '2012-01-01'

;with CTE as (
             select 1 as id, @BeginsAt as Mnth
             union all
             select (id + 1) , dateadd(month,id,@BeginsAt)
             from CTE
             where (id + 1) <= 12
            )
select
*
from CTE
left join ( -- your existing query ) etc.

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Marcos27

ASKER
Hi All -

Sorry fo the late reply on this! I understand the concept, but I'm having a hard time fitting my query into this - and how I should join the two tables. Here's my simple query:

SELECT Incident.Category, Incident.IncidentID, Incident.Text051, Incident.Date001, Incident.Problem, CorrectiveAction.Action_Description

FROM Incident LEFT OUTER JOIN CorrectiveAction
ON Incident.CorrectiveActionID=CorrectiveAction.ActionID

WHERE  Incident.Category='Change Request' AND (Incident.Text051='System Error - Isolated' OR Incident.Text051='System Failure' OR Incident.Text051='Validation Error Message Occurred')

** I want to show IncidentIDs per Month (Date001) and show months even where there aren't any records, so I want to join Paul's CTE above, but not sure how. Thanks.

-Marcos
PortletPaul

You state that Date001 is Month (I think), so assuming Date001 IS a match to the CTE dates, then:
declare @BeginsAt as datetime
set @BeginsAt = '2012-01-01'

;with CTE as (
             select 1 as id, @BeginsAt as Mnth
             union all
             select (id + 1) , dateadd(month,id,@BeginsAt)
             from CTE
             where (id + 1) <= 12
            )

SELECT
      Incident.Category
    , Incident.IncidentID
    , Incident.Text051
    , CTE.Mnth
    , Incident.Problem
    , CorrectiveAction.Action_Description
FROM CTE
LEFT JOIN Incident on CTE.Mnth = Incident.Date001
LEFT JOIN CorrectiveAction ON Incident.CorrectiveActionID = CorrectiveAction.ActionID
WHERE Incident.Category = 'Change Request'
    AND (
        Incident.Text051 = 'System Error - Isolated'
        OR Incident.Text051 = 'System Failure'
        OR Incident.Text051 = 'Validation Error Message Occurred'
        )
 

Open in new window

If those dates don't align then this isn't going to work. In other words Date001 should be the 1st of the month at 00:00:00
Marcos27

ASKER
Those dates do not align. Those are record creation dates - so I could have 5 in jan, 3 in Feb, and none in March. What I'm trying to do is graph that - including March where there are none. The CTE would work, but I don't know who to join the CTE with my data. CTE.Id=IncidentID doesn't work either, because my data set doesn't have IncidentID's of 1, 2, 3, 4, 5, etc . . . they don't have a pattern - so they are 7760, 8924, 5893 etc.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PortletPaul

a record per day isn't required for charting by month, the exact same effect is achieved by dropping incident dates to first of month (& you still have to truncate the incident time to date from datetime for the daily records, so there is not saving in calculation effort)

my guess however is that the query needs much more summarization to be suitable for monthly charting anyway, e.g. to get counts of incidents by category, by month, in which case you are joining way fewer records (making a per day calendar much less necessary again)
vasto

Full date is the right way to join the data. Once it is joined you can get the month from the result.

Summary might be prepared in a pivot grid or crosstab which will handle monthly grouping.
PortletPaul

>>Those are record creation dates referring to [Date001]

When you construct the date range by day (via function or cte) the dates will typically be at 00:00:00; but the creation date/time is not, so to make the match you still require some date function to remove "time". e.g

Dates d Left Join Incident I on d.Date=dateadd(dd, datediff(dd,0, i.Date001), 0)
--or--
Dates d Left Join Incident I on d.Date=cast(i.Date001 as date)
Hence it does not matter in query efficiency terms if you join by day or by month.

Joining by day is a good way, and a function would potentially give rise to further use, Marcos27 should take note of the information you have provided for building the date range.

this is also a handy reference in using CTEs to generate sequences (including dates). If you were to build a table of dates consider using the last CTE of that page for ideas.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
PortletPaul

doh! >>good catch on the where clause killing the left join
PortletPaul

anyway, regarding charting, the existing query is way too detailed for charting and it contains  too much text probably. Something like this (and it is an example only) is the direction I would follow:
declare @BeginsAt as datetime
set @BeginsAt = '2012-01-01'

;with CTE as (
             select 1 as id, @BeginsAt as Mnth
             union all
             select (id + 1) , dateadd(month,id,@BeginsAt)
             from CTE
             where (id + 1) <= 12
            )
SELECT
      CTE.Mnth
    , Incident.Category
    , count(Incident.Category) as cat_count
FROM CTE
LEFT JOIN Incident ON CTE.Mnth = DATEADD(dd, - (DAY(Incident.Date001) - 1), dateadd(dd, datediff(dd,0, Incident.Date001), 0))
        AND Incident.Date001 > = @BeginsAt AND Incident.Date001 < dateadd(month,12,@BeginsAt)
        AND Incident.Category = 'Change Request'
        AND (
            Incident.Text051 = 'System Error - Isolated'
            OR Incident.Text051 = 'System Failure'
            OR Incident.Text051 = 'Validation Error Message Occurred'
            )
GROUP BY
      CTE.Mnth
    , Incident.Category

Open in new window

PortletPaul

>> I want to chart the number of records per month and still show the months that return no records at all.
declare @BeginsAt as datetime
set @BeginsAt = '2012-01-01'

;with CTE as (
             select 1 as id, @BeginsAt as Mnth
             union all
             select (id + 1) , dateadd(month,id,@BeginsAt)
             from CTE
             where (id + 1) <= 12
            )
SELECT
      CTE.Mnth
    , count(Incident.Date001) as cat_count
FROM CTE
LEFT JOIN Incident ON CTE.Mnth = DATEADD(dd, - (DAY(Incident.Date001) - 1), dateadd(dd, datediff(dd,0, Incident.Date001), 0))
        AND Incident.Date001 > = @BeginsAt AND Incident.Date001 < dateadd(month,12,@BeginsAt)
GROUP BY
      CTE.Mnth

Open in new window

This structure should meet your original request, then you can add further filters if neeed as conditions to the left join
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Marcos27

ASKER
** SOLVED **

I used advice from both of you to develop the query below, which I used in a SQL command to base my Crystal chart on. This seems to work great. I joined my data set to a CTE – which is used to construct of all my months from January of 2010 until the present day, and I left joined that CTE to my data set on the date fields (on the month/year of my record dates) , so 18-Oct-2012 would be joined the 01-Oct-2012 value in my CTE . . . and rather than use a WHERE clause, I defined my filters in the join itself, which preserves the Left Outer Join.

declare @BeginsAt as datetime
set @BeginsAt = '2010-01-01'

;with CTE as (
             select 1 as id, @BeginsAt as Mnth
             union all
             select (id + 1) , dateadd(month,id,@BeginsAt)
             from CTE
             where dateadd(month,id,@BeginsAt) < GETDATE()
            )

SELECT
      Incident.Category
    , Incident.IncidentID
    , Incident.Text051
    , Incident.Date001
    , CTE.Mnth
    , Incident.Problem
    , CorrectiveAction.Action_Description
FROM CTE
LEFT JOIN Incident on CTE.Mnth = DATEADD(dd, - (DAY(Incident.Date001) - 1), dateadd(dd, datediff(dd,0, Incident.Date001), 0)) AND Incident.Category = 'Change Request'
    AND (
        Incident.Text051 = 'System Error - Isolated'
        OR Incident.Text051 = 'System Failure'
        OR Incident.Text051 = 'Validation Error Message Occurred'
        )
    AND Void=0
LEFT JOIN CorrectiveAction ON Incident.CorrectiveActionID = CorrectiveAction.ActionID
Order By CTE.Mnth

Let me know if you see any flaws in my logic, but like I said, it seems to work great, and is something I can apply to other projects. Thanks to both of you!

- Marcos
PortletPaul

logic look fine to me, a small note in your code to explain what this is wouldn't hurt :)
 DATEADD(dd, - (DAY(Incident.Date001) - 1), dateadd(dd, datediff(dd,0, Incident.Date001), 0))

I'm not sure how you translate this detail into a chart but that's for you to figure out I guess.

All the best.

vasto's advice throughout was rock solid IMHO