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_Da te"), Sum(DateDiff("m","CL_MAST" ."Delete_D ate", "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_Da te")
SELECT Month("CL_MAST"."Delete_Da
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">={
AND "CL_MAST"."Delete_Date"<={
GROUP BY
Month("CL_MAST"."Delete_Da
ASKER
Error on my part.
Here is the corrected...
Sum(DateDiff("m","CL_MAST" ."Client_D ate", "CL_MAST"."Delete_Date" )
Here is the corrected...
Sum(DateDiff("m","CL_MAST"
YOu need to assign names to the calculated fields
Try this
SELECT Month("CL_MAST"."Delete_Da te") AS DeleteMonth, Sum(DateDiff("m","CL_MAST" ."Delete_D ate", "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
Try this
SELECT Month("CL_MAST"."Delete_Da
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">={
AND "CL_MAST"."Delete_Date"<={
GROUP BY
DeleteMonth
mlmcc
ASKER
I get the errr message
Failed to open rowset
Details: 42000 Pervasive ODBC Client Interface LNA Invalid Scalar Function:Date Diff
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_Da te") 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
SELECT Month("CL_MAST"."Delete_Da
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">={
AND "CL_MAST"."Delete_Date"<={
mlmcc
ASKER
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.
I need a way to aquire the DateDiff, then Sum it.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Why don't you think it will work in Crystal?
mlmcc
ASKER
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
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
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
...
Each branch has a range of loc nos that represent customers in their branch
Ex 1000-2000 SF
2000-3000 LA
...
ASKER
I located a solution that works. Here it is
SELECT Month("CL_MAST"."Client_Da te"),
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_Da te")
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
SELECT Month("CL_MAST"."Client_Da
sum(TIMESTAMPDIFF ( SQL_TSI_MONTH, "CL_MAST"."Client_Date", "CL_MAST"."Delete_Date") )
FROM "CL_MAST" "CL_MAST"
WHERE ("CL_MAST"."Delete_Date">=
GROUP BY Month("CL_MAST"."Client_Da
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
Sum(DateDiff("m","CL_MAST"
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