sum datediff in crystal reports pervasive sql

I am trying to get the sum total of a series of datediff values as follows and getting the error Invalid Scalar Function

SELECT Month("CL_MAST"."Delete_Date"), Sum(DateDiff("m","CL_MAST"."Delete_Date", "CL_MAST"."Delete_Date" ))
 FROM
"CL_MAST"
WHERE
"CL_MAST"."Loc_No" >= 1000
AND
"CL_MAST"."Loc_No" < 2000
AND
"CL_MAST"."Loc_No" <>  1009
AND
"CL_MAST"."Delete_Date">={d '2005-01-01'}
AND "CL_MAST"."Delete_Date"<={d '2005-12-31'}

GROUP BY
Month("CL_MAST"."Delete_Date")
KeithMcElroyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
Hi KeithMcElroy,
 Sum(DateDiff("m","CL_MAST"."Delete_Date", "CL_MAST"."Delete_Date" )

In the SQL dialects I am familiar with this would always produce 0, since it is using the same date value for the 'from' and 'to' dates.
Does it do something different for yours?


Pete
KeithMcElroyAuthor Commented:
Error on my part.
Here is the corrected...
 Sum(DateDiff("m","CL_MAST"."Client_Date", "CL_MAST"."Delete_Date" )
mlmccCommented:
YOu need to assign names to the calculated fields
Try this

SELECT Month("CL_MAST"."Delete_Date") AS DeleteMonth, Sum(DateDiff("m","CL_MAST"."Delete_Date", "CL_MAST"."Delete_Date" )) AS MonthsOpen
 FROM
"CL_MAST"
WHERE
"CL_MAST"."Loc_No" >= 1000
AND
"CL_MAST"."Loc_No" < 2000
AND
"CL_MAST"."Loc_No" <>  1009
AND
"CL_MAST"."Delete_Date">={d '2005-01-01'}
AND "CL_MAST"."Delete_Date"<={d '2005-12-31'}

GROUP BY
DeleteMonth

mlmcc
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

KeithMcElroyAuthor Commented:
I get the errr message
Failed to open rowset
Details:  42000 Pervasive ODBC Client Interface LNA Invalid Scalar Function:Date Diff
mlmccCommented:
Try it this way and do the subtraction in Crystal
SELECT Month("CL_MAST"."Delete_Date") AS DeleteMonth, "CL_MAST"."Client_Date", "CL_MAST"."Delete_Date"
 FROM
"CL_MAST"
WHERE
"CL_MAST"."Loc_No" >= 1000
AND
"CL_MAST"."Loc_No" < 2000
AND
"CL_MAST"."Loc_No" <>  1009
AND
"CL_MAST"."Delete_Date">={d '2005-01-01'}
AND "CL_MAST"."Delete_Date"<={d '2005-12-31'}

mlmcc
KeithMcElroyAuthor Commented:
I have to aggregate this in the query so I do not think that will work.
I need a way to aquire the DateDiff, then Sum it.
peter57rCommented:
Why do have to aggregate in the query?
CR would do the grouping and create the totals.  You can just do the record selection in the data source query.

Pete
mlmccCommented:
If the ODBC driver doesn't recognize DateDiff then you need to find another function or do it in Crystal.

Why don't you think it will work in Crystal?

mlmcc
KeithMcElroyAuthor Commented:
I am creating a report based on monthly figures.  Jan - Dec.  I created a table with 1 - 12 for Jan to Dec.  Each column has its own criteria and all my initial development has led me to needing to create a sql command for each column.  
mlmccCommented:
Still not really clear what the problem is.  You created a table with 12 columns 1-12?

Can you show the tables involved and some sample (even made up) data and what you need to do?  Crystal is very powerful and can do an lot with formulas to manipulate the data.

mlmcc
KeithMcElroyAuthor Commented:
I am creating a report of counts and totals of sales and customers.  Each column represents a separate office

Month   SF   LA  SD  Sac  Total
Jan      
Feb
Mar
Apr
May
...

I created a months table it layout as follows
1, Jan
2, Feb
...

This table links to each of the count and totals recordsets.  The months table was necessary so that the month would show up even if totals were 0

All works good except for the last column which is the number of months each customer has been with our company.
That lead me to the question, which is I need a way to sum up the aggregate of datediff() as shown above.

Thanks so much for any help.  The project is completed except this one item.

Keith
mlmccCommented:
WHere are the customers?

mlmcc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KeithMcElroyAuthor Commented:
CL_MAST table.  The field is Loc_No
Each branch has a range of loc nos that represent customers in their branch
Ex  1000-2000 SF
      2000-3000 LA
      ...
KeithMcElroyAuthor Commented:
I located a solution that works.  Here it is
SELECT Month("CL_MAST"."Client_Date"),
sum(TIMESTAMPDIFF ( SQL_TSI_MONTH, "CL_MAST"."Client_Date", "CL_MAST"."Delete_Date") )
 FROM   "CL_MAST" "CL_MAST"
 WHERE  ("CL_MAST"."Delete_Date">={d '2005-01-01'} AND "CL_MAST"."Delete_Date"<={d '2005-12-31'})
GROUP BY Month("CL_MAST"."Client_Date")

Can someone coach me on how I should award points and hopefully this will help someone else out.
Many thanks for everyone's help.  I really appreciate it.

Keith
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.