• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1548
  • Last Modified:

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")
0
KeithMcElroy
Asked:
KeithMcElroy
  • 7
  • 5
  • 2
2 Solutions
 
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
0
 
KeithMcElroyAuthor Commented:
Error on my part.
Here is the corrected...
 Sum(DateDiff("m","CL_MAST"."Client_Date", "CL_MAST"."Delete_Date" )
0
 
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
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
KeithMcElroyAuthor Commented:
I get the errr message
Failed to open rowset
Details:  42000 Pervasive ODBC Client Interface LNA Invalid Scalar Function:Date Diff
0
 
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
0
 
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.
0
 
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
0
 
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
0
 
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.  
0
 
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
0
 
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
0
 
mlmccCommented:
WHere are the customers?

mlmcc
0
 
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
      ...
0
 
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
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 7
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now