Sort report by two columns

[RepRider]

CRXI and SQL

I have a report that shows various providers and the services they perform.
It is currently sorted ascending by the average # of days to service documentation 0 days to 14 days. there are additional columns of data one which shows the total number of services during the same time period.

I would like to sort all the O days providers by the total number of services descending and the same for each number of days.

It should look something like this:
name days total services
Prov1 0 500
prov2 0 362
prov3 0 127
prov4 1 480
prov5 1 376
prov6 2 700
prov7 2 432

the data columns are formulas.
What do I need to do to make this happen?

Thanks in advance.

 

[lbass]

Try avg instead of average. I only changed it originally because you said it didn't work, but you were using it in the wrong place.

-LB

 

[RepRider]

Still getting the error.
See latest screenshot below.

 

[lbass]

I'm not sure if the message is because of the datetimes or because of the syntax. Do you have a number field you could test instead by replacing:

"update_date" - "begin_date"

...with that number field? Then we can eliminate one of the potential issues.

-LB

 

[RepRider]

I am sorry I am totally lost. I did insert the actual field names for the avg formula which may be wrong but when I tried other number fields (without the brackets) they were not recognized.

Here is the screen shot of what I did and the message.

 

[lbass]

No, you cannot reference the actual field names. I am asking you to pick a number field from your database, e.g., is elapsed time a number? Then enter:

round((
select avg("elapsed_time")
from "Service_Time_DMC" A, "Progress_Note_View_dmc" B,
"Progress_Note" C
where A."consumer_service_ID" = B."consumer_service_ID" and
B."progress_note_ID" = C."progress_note_ID" and
A."last_name" = "Service_Time_DMC"."last_name"
),0)

This is JUST for test purposes.

 

[RepRider]

Ok, thank you for bearing with me.

I put in that exactly.... NO ERRORS FOUND!

So that test worked. I await further help.

 

[lbass]

Good. Then try this:

round((
select avg(trunc("update_date") - trunc("begin_date"))
from "Service_Time_DMC" A, "Progress_Note_View_dmc" B,
"Progress_Note" C
where A."consumer_service_ID" = B."consumer_service_ID" and
B."progress_note_ID" = C."progress_note_ID" and
A."last_name" = "Service_Time_DMC"."last_name"
),0)

Let me know if this works--exactly as written.

-LB

 

[RepRider]

It gave an error message of ("trunc' is not a recognized function) so I changed them to truncate.

round((
select avg(truncate("update_date") - truncate("begin_date"))
from "Service_Time_DMC" A, "Progress_Note_View_dmc" B,
"Progress_Note" C
where A."consumer_service_ID" = B."consumer_service_ID" and
B."progress_note_ID" = C."progress_note_ID" and
A."last_name" = "Service_Time_DMC"."last_name"
),0)


Now the error says "Incorrect syntax near the word truncate

 

[lbass]

Truncate is not the correct usage. Try this:

round((
select avg(cdate("update_date") - cdate("begin_date"))
from "Service_Time_DMC" A, "Progress_Note_View_dmc" B,
"Progress_Note" C
where A."consumer_service_ID" = B."consumer_service_ID" and
B."progress_note_ID" = C."progress_note_ID" and
A."last_name" = "Service_Time_DMC"."last_name"
),0)

If it doesn't accept the cdate, try opening a new report and adding the above to a command that looks like this:

Select
round((
select avg(cdate("update_date") - cdate("begin_date"))
from "Service_Time_DMC" A, "Progress_Note_View_dmc" B,
"Progress_Note" C
where A."consumer_service_ID" = B."consumer_service_ID" and
B."progress_note_ID" = C."progress_note_ID" and
A."last_name" = "Service_Time_DMC"."last_name"
),0) "Ave", "Service_Time_DMC"."last_name"

From (("SC_Production"."dbo"."Service_Time_DMC" "Service_Time_DMC" INNER JOIN
"SC_Production"."dbo"."Consumer_Service_View" "Consumer_Service_View" ON "Service_Time_DMC"."consumer_service_ID"="Consumer_Service_View"."consumer_service_ID") LEFT OUTER JOIN
"SC_Production"."dbo"."Progress_Note_View_dmc" "Progress_Note_View_dmc" ON "Service_Time_DMC"."consumer_service_ID"="Progress_Note_View_dmc"."consumer_service_ID") LEFT OUTER JOIN
"SC_Production"."dbo"."Progress_Note" "Progress_Note" ON "Progress_Note_View_dmc"."progress_note_ID"="Progress_Note"."progress_note_ID"
WHERE
NOT ("Service_Time_DMC"."service_code"='120' OR "Service_Time_DMC"."service_code"='170' OR "Service_Time_DMC"."service_code"='202' OR "Service_Time_DMC"."service_code"='226' OR "Service_Time_DMC"."service_code"='238' OR "Service_Time_DMC"."service_code"='239' OR "Service_Time_DMC"."service_code"='240' OR "Service_Time_DMC"."service_code"='420' OR "Service_Time_DMC"."service_code"='475' OR "Service_Time_DMC"."service_code"='530' OR "Service_Time_DMC"."service_code"='620' OR "Service_Time_DMC"."service_code"='649' OR "Service_Time_DMC"."service_code"='651' OR "Service_Time_DMC"."service_code"='683' OR "Service_Time_DMC"."service_code"='686' OR "Service_Time_DMC"."service_code"='687' OR "Service_Time_DMC"."service_code"='688' OR "Service_Time_DMC"."service_code"='690' OR "Service_Time_DMC"."service_code"='696' OR "Service_Time_DMC"."service_code"='697' OR "Service_Time_DMC"."service_code"='699' OR "Service_Time_DMC"."service_code"='816') AND NOT ("Service_Time_DMC"."last_name"='AFC DAYS' OR "Service_Time_DMC"."last_name"='COMMUNITY LIVING SUPPORTS' OR "Service_Time_DMC"."last_name"='CONNECTIONS' OR "Service_Time_DMC"."last_name"='Contracted' OR "Service_Time_DMC"."last_name"='INGERSOLL' OR "Service_Time_DMC"."last_name"='INPATIENT DAY' OR "Service_Time_DMC"."last_name"='KUBLIN' OR "Service_Time_DMC"."last_name"='MERCER' OR "Service_Time_DMC"."last_name"='MOARC SERVICE PROVIDER' OR "Service_Time_DMC"."last_name"='NICKISSON' OR "Service_Time_DMC"."last_name"='Respite' OR "Service_Time_DMC"."last_name"='THARMAR') AND ("Service_Time_DMC"."begin_date">={ts '2008-09-01 00:00:00'} AND "Service_Time_DMC"."begin_date"<{ts '2008-10-01 00:00:00'})

ORDER BY "Service_Time_DMC"."last_name", "Service_Time_DMC"."begin_date"

If this works, you can link it to your other tables on the name field.

-LB

 

[RepRider]

I have tried both of the above and the error messages say that cdate is not a recognized function.

I think I may give up! Thanks for all the hard thinking you are doing to help me. It is very much appreciated!!

 

[lbass]

Sometimes a subquery will work in a command that won't work in a SQL expression. Try changing the Select part of the command to:

Select
round((
select avg(A."update_date" - C."begin_date")
from "Service_Time_DMC" A, "Progress_Note_View_dmc" B,
"Progress_Note" C
where A."consumer_service_ID" = B."consumer_service_ID" and
B."progress_note_ID" = C."progress_note_ID" and
A."last_name" = "Service_Time_DMC"."last_name"
),0) "Ave", "Service_Time_DMC"."last_name"

-LB