Previous(fld), is there another way? 2

[DeviousDi]

Hi,

I'm currently using the following formula to work out the number of days between query status changes, here's the formula:

datediff('d', previous({query_status_changes.date_changed}), {query_status_changes.date_changed})

The problem being is that this is apparently a summarized field, and I can't get totals based on my various groups from this.

I've tried using a cross tab, which will add the totals together, but not how I want them. I've also tried using tonumber which doesn't appear to have any effect. Is there another way of doing it?

I'm using SAP CR XI.

Thanks

Di

 

[hilfy]

Have you found a solution for this? As far as I know, there is no way to get around this using just Crystal. However, I can think of a way to get all of the data for the report using a Command (SQL Select statement) that would provide the previous date in the same rows as the date you're trying to calculate from so you wouldn't have to use the Previous() function. In fact, you could actually calculate the number of days in the Command and not have to do the calculation in Crystal at all.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[SkipVought]

hi,

If your database is Oracle, you can use analytcal functions. To get some value from a previous row, would be a LAG() or next row, LEAD().

My select statement looks something like this

select
  order_nbr
, operation_nbr
, lag(operation_nbr) over(partition by order_nbr order by order_nbr, operation_nbr) as Prev_Oper
, lead(operation_nbr) over(partition by order_nbr order by order_nbr, operation_nbr) as Next_Oper

where I have an order_nbr that contains a series of sequential operation_nbrs

the result might look like this for 3 operations...
[pre]
order_num operation_num Prev_Oper Next_Oper
111111111 0010 0020
111111111 0020 0010 0030
111111111 0030 0020
[/pre]

Caveat: you must query ALL applicable rows in your primary query and then pare it down to the specific rows of interest in a subsequent query. In my case, I query all open orders in my primary query and then in a subsequent query, filter out operations that I don't need.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DeviousDi]

Hi to all!

No, haven't found another way yet, and my database is SQL.

Any help would be really appreciated, its driving me potty! I'm so far with the report and just can't finish it!!

Thank you!

Di

 

[DeviousDi]

Hilfy

The code would be bloomin fantastic, if you don't mind?

The field I basically need the 'days' for is "query_status_changes"."date_changed", to show how long its take to move from one "query_status_changes"."old_standard_query_status" to another.

Here's the code if it helps!!

SELECT "query_status_changes"."query_id",
"query_status_changes"."id",
"query_status_changes"."old_standard_query_status",
"query_status_changes"."date_changed",
"debtors"."debtor_number",
"query_status"."fixed",
"standard_queries"."code"
FROM ((("OnGuard"."dbo"."query_status_changes" "query_status_changes"
INNER JOIN "OnGuard"."dbo"."queries" "queries" ON "query_status_changes"."query_id"="queries"."id")
INNER JOIN "OnGuard"."dbo"."debtors" "debtors" ON "queries"."debtor_id"="debtors"."id")
INNER JOIN "OnGuard"."dbo"."query_status" "query_status" ON "queries"."query_status_id"="query_status"."id")
INNER JOIN "OnGuard"."dbo"."standard_queries" "standard_queries" ON "queries"."standard_query_id"="standard_queries"."id"

Thank you!!!

Di

 

[hilfy]

You'll have to re-write your report to use a command. If you have any parameters that you're using in the Select Expert to filter data, you'll have to delete them from the Select Expert and delete the parameters from the report then recreate them in the Command Editor and use them in a "Where" clause in the command.

I believe this query should work for you as a command:

SELECT qsc.query_id,
qsc.id,
qsc.old_standard_query_status,
qsc.date_changed,
d.debtor_number,
qs.fixed,
sq.code,
case
when qsc1.query_id is null then 0
else datediff(day, qsc.date_changed, max(qsc1.date_changed))
end as days_between
FROM query_status_changes qsc
INNER JOIN queries q ON qsc.query_id=q.id
INNER JOIN debtors d ON q.debtor_id=d.id
INNER JOIN query_status qs ON q.query_status_id=qs.id
INNER JOIN standard_queries sq ON q.standard_query_id=sq.id
LEFT JOIN query_status_changes qsc1
ON qsc.query_id = qsc1.query_id
AND qsc.date_changed > qsc1.date_changed
AND qsc.old_standard_query_status <> qsc1.old_standard_query_status
<WHERE (Optional - use this if you are filtering your data - all conditions should go under the where clause)>
GROUP BY
qsc.id,
qsc.old_standard_query_status,
qsc.date_changed,
d.debtor_number,
qs.fixed,
sq.code

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[SkipVought]

to show how long its take to move from one "query_status_changes"."old_standard_query_status" to another.

...over what partition of data element(s) in the resultset, sequenced by what collation of data element(s) in the resultset?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DeviousDi]

HI,

Have tried the above, but removed the 'where' line, and keep getting the error:


failed to retrieve data from the database
details 420000
query_status_changes.query_id is invalid in the select list because it is not contain in either an aggregate function or the GROUP BY clause
database vendor code:8120

My SQL is a good as a chocolate fireguard!

Thanks

Di

 

[hilfy]

It's possible that you accidentally removed the "GROUP BY" when you removed the "WHERE". Try this:

SELECT query_status_changes.query_id,
query_status_changes.id,
query_status_changes.old_standard_query_status,
query_status_changes.date_changed,
debtors.debtor_number,
query_status.fixed,
standart_queries.code,
case
when qsc1.query_id is null then 0
else datediff(day, query_status_changes.date_changed, max(qsc1.date_changed))
end as days_between
FROM query_status_changes qsc
INNER JOIN queries q ON query_status_changes.query_id=q.id
INNER JOIN debtors d ON q.debtor_id=debtors.id
INNER JOIN query_status qs ON q.query_status_id=query_status.id
INNER JOIN standard_queries sq ON q.standard_query_id=standart_queries.id
LEFT JOIN query_status_changes qsc1
ON query_status_changes.query_id = qsc1.query_id
AND query_status_changes.date_changed > qsc1.date_changed
AND query_status_changes.old_standard_query_status <> qsc1.old_standard_query_status
GROUP BY
query_status_changes.id,
query_status_changes.old_standard_query_status,
query_status_changes.date_changed,
debtors.debtor_number,
query_status.fixed,
standart_queries.code

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[DeviousDi]

Hi,

Thank you all so far.

I've tried the last post, now its coming back with this error:

database connector error 420000
the multi-part identifier query_status_changes.query_id could not be bound
database vendor code:4104

Its Halloween, maybe I should be expecting all this?! I am dressed as a bat after all!!!

Thanks again everyone

Di

 

[hilfy]

Ok, I see what I missed in my last post. Try this on:

SELECT query_status_changes.query_id,
query_status_changes.id,
query_status_changes.old_standard_query_status,
query_status_changes.date_changed,
debtors.debtor_number,
query_status.fixed,
standart_queries.code,
case
when qsc1.query_id is null then 0
else datediff(day, query_status_changes.date_changed, max(qsc1.date_changed))
end as days_between
FROM query_status_changes
INNER JOIN queries ON query_status_changes.query_id=q.id
INNER JOIN debtors ON q.debtor_id=debtors.id
INNER JOIN query_status ON q.query_status_id=query_status.id
INNER JOIN standard_queries ON q.standard_query_id=standart_queries.id
LEFT JOIN query_status_changes as qsc1
ON query_status_changes.query_id = qsc1.query_id
AND query_status_changes.date_changed > qsc1.date_changed
AND query_status_changes.old_standard_query_status <> qsc1.old_standard_query_status
GROUP BY
query_status_changes.id,
query_status_changes.old_standard_query_status,
query_status_changes.date_changed,
debtors.debtor_number,
query_status.fixed,
standart_queries.code

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[DeviousDi]

Hi Hilfy,

Another day, minus the bat costume and its still not having it!

Here's the error message:

database connector error 420000
the multi-part identifier q.id could not be bound
database vendor code:4104

and the same for q.debtor_id, q.query_status.id, q.standard_query.id, standart_queries.id, standart_queries.code

Thank you all so far. I really need to get my T SQL back up to scratch, I didn't know you could do all of this!

Di

 

[hilfy]

Replace the following:

"q." with "Queries."
"standart_queries" with "standard_queries"

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[DeviousDi]

Hi Hilfy,

Thank you so much! Got it to work with the following code:

SELECT "query_status_changes"."query_id",
"query_status_changes"."id",
"query_status_changes"."old_standard_query_status",
"query_status_changes"."date_changed",
"debtors"."debtor_number",
"query_status"."fixed",
"standard_queries"."code",
CASE
when "query_status_changes"."id" is null then 0
else datediff(day, "query_status_changes"."date_changed", max("query_status_changes"."date_changed")) end as days_between


FROM ((("OnGuard"."dbo"."query_status_changes" "query_status_changes"
INNER JOIN "OnGuard"."dbo"."queries" "queries" ON "query_status_changes"."query_id"="queries"."id")
INNER JOIN "OnGuard"."dbo"."debtors" "debtors" ON "queries"."debtor_id"="debtors"."id")
INNER JOIN "OnGuard"."dbo"."query_status" "query_status" ON "queries"."query_status_id"="query_status"."id")
INNER JOIN "OnGuard"."dbo"."standard_queries" "standard_queries" ON "queries"."standard_query_id"="standard_queries"."id"

GROUP BY
"query_status_changes"."query_id",
"query_status_changes"."id",
"query_status_changes"."old_standard_query_status",
"query_status_changes"."date_changed",
"debtors"."debtor_number",
"query_status"."fixed",
"standard_queries"."code"


I do sort of have an extension to this though.

Here's my formula in crystal:

if {query_status_changes.old_standard_query_status} = 12 THEN days_between (this being the number of days from the above SQL code) ELSE 0

I thought I could just slide it under the last 'else' statement, but it doesn't seem to like it.

Thank you for all your help so far. I'll let Santa know you've been good this year!

Di

 

[DeviousDi]

eeeekkkkk!!!

I've just had a real look at the data, and all my 'days_between' are coming back as 0!!!

That's not right! So much for getting all the pesky gremlins out of the system!

Di

 

[hilfy]

This is because you need the second query to get the max date that is prior to the date in the current record. That's what the sub-query was doing in the SQL I gave you.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[DeviousDi]

HI Del,

I used your code, and the days_between comes through as all 0's. I haven't changed anything yet.

Sorry for being a pain, I am trying, honest!

Di

 

[DeviousDi]

Oh dear,

I owe you a HUGE apology! I'm missing a field. Its in my crystal report, but for some reason its not in the SQL code.

This is the proper code

SELECT "query_status_changes"."query_id",
"query_status_changes"."id",
"query_status_changes"."new_standard_query_status",
"query_status_changes"."old_standard_query_status",
"query_status_changes"."date_changed",
"debtors"."debtor_number",
"query_status"."fixed",
"standard_queries"."code",
CASE
when "query_status_changes"."old_standard_query_status" is null then 0
else datediff(day, "query_status_changes"."date_changed", max("query_status_changes"."date_changed")) end as days_between


FROM ((("OnGuard"."dbo"."query_status_changes" "query_status_changes"
INNER JOIN "OnGuard"."dbo"."queries" "queries" ON "query_status_changes"."query_id"="queries"."id")
INNER JOIN "OnGuard"."dbo"."debtors" "debtors" ON "queries"."debtor_id"="debtors"."id")
INNER JOIN "OnGuard"."dbo"."query_status" "query_status" ON "queries"."query_status_id"="query_status"."id")
INNER JOIN "OnGuard"."dbo"."standard_queries" "standard_queries" ON "queries"."standard_query_id"="standard_queries"."id"

GROUP BY
"query_status_changes"."query_id",
"query_status_changes"."id",
"query_status_changes"."old_standard_query_status",
"query_status_changes"."new_standard_query_status",
"query_status_changes"."date_changed",
"debtors"."debtor_number",
"query_status"."fixed",
"standard_queries"."code"

I've adjusted where needed, but need to find the number of days between each change in old query to new query, this being the days_between, which is still showing up as 0.

Thank you, thank you, thank you for all your help.

Di

 

[hilfy]

This is NOT the query I gave you. You have a single link to query_status_changes where you need it in the query twice to get this to work.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[DeviousDi]

Dell,

Sorry about the code, but when I changed the query you gave me, with the changes to told me to make:

SELECT query_status_changes.query_id,
query_status_changes.id,
query_status_changes.old_standard_query_status,
query_status_changes.date_changed,
debtors.debtor_number,
query_status.fixed,
standard_queries.code,
case
when qsc1.query_id is null then 0
else datediff(day, query_status_changes.date_changed, max(qsc1.date_changed))
end as days_between
FROM query_status_changes
INNER JOIN queries ON query_status_changes.query_id=queries.id
INNER JOIN debtors ON queries.debtor_id=debtors.id
INNER JOIN query_status ON queries.query_status_id=query_status.id
INNER JOIN standard_queries ON queries.standard_query_id=standard_queries.id
LEFT JOIN query_status_changes as qsc1
ON query_status_changes.query_id = qsc1.query_id
AND query_status_changes.date_changed > qsc1.date_changed
AND query_status_changes.old_standard_query_status <> qsc1.old_standard_query_status
GROUP BY
query_status_changes.query_id,
query_status_changes.id,
query_status_changes.old_standard_query_status,
query_status_changes.date_changed,
debtors.debtor_number,
query_status.fixed,
standard_queries.code

It still comes back with an error:

Failed to retrieve data from the database error 42000
Column 'query_status_changes.query_id is invalid in the select list because it is not contained in either an aggregate function or the group by clause
Database vendor code: 8120

I don't understand why that error occurred as it is contained in the group by clause.

Thank you Dell, I know you don't have to help

Di