Solved

# sum datediff in crystal reports pervasive sql

Posted on 2006-04-14
1,520 Views
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
Question by:KeithMcElroy

LVL 77

Expert Comment

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

Author Comment

Error on my part.
Here is the corrected...
Sum(DateDiff("m","CL_MAST"."Client_Date", "CL_MAST"."Delete_Date" )
0

LVL 100

Expert Comment

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

Author Comment

I get the errr message
Failed to open rowset
Details:  42000 Pervasive ODBC Client Interface LNA Invalid Scalar Function:Date Diff
0

LVL 100

Expert Comment

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

Author Comment

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

LVL 77

Assisted Solution

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

LVL 100

Expert Comment

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

Author Comment

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

LVL 100

Expert Comment

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

Author Comment

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

LVL 100

Accepted Solution

WHere are the customers?

mlmcc
0

Author Comment

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

Author Comment

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
how to add IIS SMTP to handle application/Scanner relays into office 365.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…