Link to home
Start Free TrialLog in
Avatar of KeithMcElroy
KeithMcElroy

asked on

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")
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of KeithMcElroy
KeithMcElroy

ASKER

Error on my part.
Here is the corrected...
 Sum(DateDiff("m","CL_MAST"."Client_Date", "CL_MAST"."Delete_Date" )
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
I get the errr message
Failed to open rowset
Details:  42000 Pervasive ODBC Client Interface LNA Invalid Scalar Function:Date Diff
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
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.
SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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.  
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
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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
      ...
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