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

 

[DeviousDi]

Dell,

Can I also ask why you have said:

when qsc1.query_id is null then 0

query.id will never be a null. The only null that could be possible is the first date difference between the 'old' query status and the 'new' query status.

Thanks, I'm just trying to understand

Di

 

[hilfy]

Qsc1.query_ID could be null if the record you're looking at in t query_status_changes is the first record for that query - that's why there's a Left join instead of an Inner Join.

Let's try something else.... Replace this:

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

with this:

max(qsc1.date_changed)

You can then do the datediff in the report.

-Dell


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

http://www.decisionfirst.com

 

[DeviousDi]

Hi Del,

Did the changes, and used this code:

SELECT 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,
max(qsc1.date_changed) 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.new_standard_query_status,
query_status_changes.date_changed,
debtors.debtor_number,
query_status.fixed,
standard_queries.code

But the data returned isn't right:

debtor_number query_id id old_standard_query_status new_standard_query_status date_changed days_between
001-822306
8,414
001-822306 8,414 16,628 8 13/06/2013
001-822306 8,414 17,349 8 2 13/06/2013
15,133
001-822306 15,133 40,345 8 22/07/2013
001-822306 15,133 41,640 8 5 23/07/2013
001-822306 15,133 44,443 5 2 25/07/2013 23/07/2013
001-822318
9,645
001-822318 9,645 19,779 9 19/06/2013
001-822318 9,645 20,807 9 5 20/06/2013
001-822318 9,645 26,228 5 2 28/06/2013 20/06/2013
001-822738
8,259
001-822738 8,259 16,233 8 12/06/2013
001-822738 8,259 18,229 8 9 14/06/2013
001-822738 8,259 19,831 9 5 19/06/2013 14/06/2013
001-822738 8,259 25,673 5 2 28/06/2013 19/06/2013
17,445
001-822738 17,445 48,769 8 31/07/2013
001-822738 17,445 50,527 8 9 02/08/2013
001-822738 17,445 54,109 9 5 07/08/2013 02/08/2013
001-822738 17,445 55,901 5 2 09/08/2013 07/08/2013
001-823385
9,506
001-823385 9,506 19,515 8 18/06/2013
001-823385 9,506 20,767 8 15 20/06/2013
001-823385 9,506 20,780 15 5 20/06/2013 20/06/2013
001-823385 9,506 26,805 5 2 28/06/2013 20/06/2013
11,701
001-823385 11,701 27,903 8 02/07/2013
001-823385 11,701 29,140 8 9 03/07/2013
001-823385 11,701 29,336 9 5 03/07/2013 03/07/2013

I need the number of days between each change, not just the first date to the last, thats why I was trying to use 'previous' in my original report, but couldn't as its a summarized field.

Thanks again

Di

 

[hilfy]

At this point you need to do the days between calculation in Crystal itself. It will look something like this:

If IsNull({command.Days_Between}) then 0
else DateDiff('d', {command.Date_Changed}, {command.Days_Between})

To prevent confusion, you could also change "as Days_Between" in the query to "as Prior_Date", because that's what the field really contains.

-Dell

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

http://www.decisionfirst.com

 

[DeviousDi]

Hi Dell,

I've done the calculation, but the field that's been created doesn't hold all the data, its missing dates.

If you look at the example above, all the records are missing the first two dates - the new field, and in the first case, none at all! How can I get these to populate?

Thanks

Di

 

[pmax9999]

Hi Di

I know I am a bit late to this party, but can you explain about the report structure and grouping. I might be missing something here but I if you are trying to get the difference between dates on consecutive Detail section lines it should be possible to use the Previous or Next function.

If the two dates are summarised in Group Headers/Footers I would think it is still possible, however it would require the use of variables.

One thing you might not be aware of is if you want the difference between two dates in days it is not necessary to use the DateDiff function. You can simply subtract the earlier date from the later one.



Cheers
Pete

 

[DeviousDi]

Hi Pete,

I know I can use the previous function in the detail section, the problem being is that its a summarized field, and so won't allow me to sum it, average it or anything else, and that's what I am desperately trying to do!

This is the SQL code:

SELECT "query_status_changes"."query_id", "query_status_changes"."id", "query_status_changes"."date_changed", "debtors"."debtor_number", "query_status"."fixed", "standard_queries"."code", "queries"."query_date", "query_status"."id", "query_status_changes"."old_standard_query_status", "query_status_changes"."new_standard_query_status"
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"

I'm grouping on five fields:

Group 5: query_status_changes.id
Group 4: debtors.debtor_number
Group 3: standard_ queries.code
Group 2: query_status.fixed
Group 1: company - formula field

I'm trying to find the difference in days between the old_stand_query_status and the new_standard_query_status. The date_changed field is the one that holds all the dates I need, and causing the problem!

I basically need to see how long each query is staying in each status, and can therefore work out the average, length of time etc.

Is this helpful or as clear as mud?!

Thanks

Di

 

[pmax9999]

So, can I assume that the dates you need to calculate the difference between are in consecutive Group 5 (query_status_changes.id) Footers? And what Summary are you performing?



Pete

 

[DeviousDi]

Hello All!

Thank you so much for all your help!

I've finally managed to sort it out using various bits and bobs from this post.

I'd buy you all a drink, but I can't, so stars and virtual drinks for all!!

Thanks again

Di