Crystal Reports Chart Expert: creating a chart with two date ranges on the X axis

Question

We are currently trying to achieve what I need with the charts in Crystal Reports.

I have parameters for the start and end dates, so the graph will be dynamic in the X axis, I want to be able to specify two dates ... one of them is Received , and the other is Complete .

Thus, we will see in Chart Expert a chart that has

"On Change of" 
    with the Recieved date (for each month) and Completed date (for each month)

"Show Value(s):"
    DistinctCount(ItemsToCount)

The point of this diagram is to show a month-to-month comparison of how many ItemsToCount items Received and Completed in any month.

I think the problem that I am facing is that there are times when the Received Date and the Completed Date are within the same month and can cause problems. Or it is possible that ItemToCount was received in the last month, but has not yet been completed ...

Before asking about this, I created a fairly static diagram in which there was logic,

if the month(received)=1 THEN ItemsToCount ELSE {@Null} 

and I had 12 of these formulas plus another 12 for completed months.

But now the end user is asking for a much longer period of time ... so I don’t think that a static approach will work for this diagram!

. - SQL , , , ... , ;)

Answer 1

- SQL, , . ( / SQL, Crystal Charting). , , SQL , :

select 
  ISNULL(tbl1.MNTH, tbl2.MNTH) as RptMonth, 
  ISNULL(tbl1.CNT,0) as ReceivedCount, 
  ISNULL(tbl2.CNT,0) as CompletedCount 
from 
  (select dateadd(month, datediff(month, 0, R.RCV_DT),0) as MNTH, COUNT(*) as CNT 
     from T_YOUR_SOURCE_TABLE  R
     GROUP BY dateadd(month, datediff(month, 0, R.RCV_DT),0) 
  ) tbl1
  FULL OUTER JOIN 
  (select dateadd(month, datediff(month, 0, R.INV_DT),0) as MNTH, COUNT(*) as CNT
     from T_YOUR_SOURCE_TABLE  R
     GROUP BY dateadd(month, datediff(month, 0, R.INV_DT),0) ) tbl2
  on tbl1.MNTH = tbl2.MNTH
where ISNULL(tbl1.MNTH, tbl2.MNTH) between '{CrystalStartDateParam}' and '{CrystalEndDateParam}'   
order by tbl1.MNTH

, :

RptMonth                ReceivedCount CompletedCount
2005-01-01 00:00:00.000 1465    1269
2005-02-01 00:00:00.000 1264    1163
2005-03-01 00:00:00.000 1466    1561
2005-04-01 00:00:00.000 1505    1504
2005-05-01 00:00:00.000 1329    1416
2005-06-01 00:00:00.000 1540    1529
2005-07-01 00:00:00.000 1263    1216
2005-08-01 00:00:00.000 1249    1316
2005-09-01 00:00:00.000 1520    1460
2005-10-01 00:00:00.000 1448    1240
2005-11-01 00:00:00.000 1321    1239
2005-12-01 00:00:00.000 1421    1302

Crystal , , .