Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sorting on Dynamic Groups and Running Totals for Groups

Status
Not open for further replies.

Garyjr123

MIS
Sep 14, 2010
139
US
I have a report that has it's grouping linked to a dynamic grouping filter which I want to do a custom sort on. Basically, the table and field are a Shift Type Name that sorts in alphabetical order. What sort formula should I use? OR_LOC_SHIFT_TIMES.SHIFT_NAME that is linked to another table ZC_SHIFT_TYPE.TITLE (HCGH MAIN, HCGH TCAS, SH MAIN, SMH MAIN, SMH MOPS, SMH SASC). When you select a value from ZC_SHIFT_TYPE.TITLE (i.e. ?Shift Type) in parameters then the OR_LOC_SHIFT_TIMES.SHIFT_NAME will display as a dynamic group (i.e. ?Group 3 = Shift).

//Depending on the specified third item to group by, populated with
//data from any of a number of items. If sorting by date, populates
//in difficult to read but easily-sortable format

if {?Group 3}="Day of Week" then {@Day # of Week} else
If {?Group 3}="Month" then {@Snapshot Month} else
if {?Group 3}="None" then "None" else
If {?Group 3}="Location" then {@Location Name} else
If {?Group 3}="Date" then {@DateSort} else
If {?Group 3}="Room" then {@Room Name} else
If {?Group 3}="Shift" then {@Shift Name}OR_LOC_SHIFT_TIMES.SHIFT_NAME

OR_LOC_SHIFT_TIMES.SHIFT_NAME
1st PM
2nd PM
3rd PM
4th PM
Afternoon
AM
Day/Prime
Early AM
First Eve
Fourth Eve
Morning
Night
OC Night
OC PM
Second Eve
Third Eve

Finally, I created a running total for ?group 3 on Room_ID to find out how many rooms the report is looking at for that shift but now I want to sum those totals in the other two groups (group 1 and group 2) for a grand total (group 1) and a location total (group 2). How would I go about that?

Thank you,

Gary

P.S.

This is the SQL for the report:


SELECT "CLARITY_LOC"."LOC_NAME", "CLARITY_SER_room"."PROV_NAME", "OR_UTIL_ROOM"."LOG_ID", "OR_UTIL_ROOM"."CASE_ID", "OR_UTIL_ROOM"."SNAPSHOT_DATE", "OR_UTIL_ROOM"."SLOT_TYPE", "OR_UTIL_ROOM"."ROOM_ID", "OR_UTIL_ROOM"."PROC_TIME_YN", "OR_UTIL_ROOM"."SLOT_LENGTH", "OR_UTIL_ROOM"."START_TIME", "OR_UTIL_ROOM"."END_TIME", "OR_UTIL_ROOM"."SNAPSHOT_NUMBER", "OR_SER_SURG_SRVC"."LOC_ID", "OR_LOG_ALL_SURG"."LINE", "OR_CASE_ALL_SURG"."LINE", "OR_CASE"."ADD_ON_CASE_YN", "OR_LOC_SHIFT_TIMES"."SHIFT_NAME", "OR_LOC_SHIFT_TIMES"."SHIFT_START_TIME", "OR_LOC_SHIFT_TIMES"."SHIFT_END_TIME", "OR_LOC_SHIFT_TIMES"."FACILITY_ID", "ZC_SHIFT_TYPE"."TITLE", "DATE_DIMENSION"."HOLIDAY_YN", "DATE_DIMENSION"."WEEKEND_YN", "OR_LOG_ALL_SURG"."SURG_ID", "CLARITY_SER_log_phys"."PROV_NAME", "OR_CASE_ALL_SURG"."SURG_ID", "CLARITY_SER_case_phys"."PROV_NAME", "CLARITY_SER_log_phys"."CLINICIAN_TITLE", "CLARITY_SER_case_phys"."CLINICIAN_TITLE", /********************************************************************************/
/* This formula returns the line for first primary surgeon for a surgery. */
/* This is to used to avoid duplicate rows getting returned since a primary */
/* surgeon can be listed multiple times for in and out of room times. */
/********************************************************************************/

case when "OR_LOG_ALL_SURG"."LOG_ID" is null then null
else
(select min(allsurg.LINE)
from OR_LOG_ALL_SURG allsurg
where allsurg.PANEL=1
and allsurg.ROLE_C=1
and allsurg.LOG_ID = "OR_LOG_ALL_SURG"."LOG_ID"
)
end, /********************************************************************************/
/* This formula returns the line for first primary surgeon for a surgery. */
/* This is to used to avoid duplicate rows getting returned since a primary */
/* surgeon can be listed multiple times for in and out of room times. */
/********************************************************************************/

case when "OR_CASE_ALL_SURG"."OR_CASE_ID" is null then null
else
(select min(allsurg.LINE)
from OR_CASE_ALL_SURG allsurg
where allsurg.PANEL=1
and allsurg.ROLE_C=1
and allsurg.OR_CASE_ID = "OR_CASE_ALL_SURG"."OR_CASE_ID"
)
end
FROM {oj (((((((((("CLARITY"."dbo"."OR_UTIL_ROOM" "OR_UTIL_ROOM" LEFT OUTER JOIN "CLARITY"."dbo"."OR_SER_SURG_SRVC" "OR_SER_SURG_SRVC" ON "OR_UTIL_ROOM"."ROOM_ID"="OR_SER_SURG_SRVC"."PROV_ID") LEFT OUTER JOIN "CLARITY"."dbo"."CLARITY_SER" "CLARITY_SER_room" ON "OR_UTIL_ROOM"."ROOM_ID"="CLARITY_SER_room"."PROV_ID") LEFT OUTER JOIN "CLARITY"."dbo"."OR_CASE_ALL_SURG" "OR_CASE_ALL_SURG" ON "OR_UTIL_ROOM"."CASE_ID"="OR_CASE_ALL_SURG"."OR_CASE_ID") LEFT OUTER JOIN "CLARITY"."dbo"."OR_LOG_ALL_SURG" "OR_LOG_ALL_SURG" ON "OR_UTIL_ROOM"."LOG_ID"="OR_LOG_ALL_SURG"."LOG_ID") LEFT OUTER JOIN "CLARITY"."dbo"."OR_CASE" "OR_CASE" ON "OR_UTIL_ROOM"."CASE_ID"="OR_CASE"."OR_CASE_ID") LEFT OUTER JOIN "CLARITY"."dbo"."OR_LOC_SHIFT_TIMES" "OR_LOC_SHIFT_TIMES" ON ("OR_UTIL_ROOM"."START_TIME"<"OR_LOC_SHIFT_TIMES"."SHIFT_END_TIME") AND ("OR_UTIL_ROOM"."END_TIME">"OR_LOC_SHIFT_TIMES"."SHIFT_START_TIME")) LEFT OUTER JOIN "CLARITY"."dbo"."DATE_DIMENSION" "DATE_DIMENSION" ON "OR_UTIL_ROOM"."SNAPSHOT_DATE"="DATE_DIMENSION"."CALENDAR_DT") LEFT OUTER JOIN "CLARITY"."dbo"."ZC_SHIFT_TYPE" "ZC_SHIFT_TYPE" ON "OR_LOC_SHIFT_TIMES"."SHIFT_TYPE_C"="ZC_SHIFT_TYPE"."SHIFT_TYPE_C") LEFT OUTER JOIN "CLARITY"."dbo"."CLARITY_SER" "CLARITY_SER_case_phys" ON "OR_CASE_ALL_SURG"."SURG_ID"="CLARITY_SER_case_phys"."PROV_ID") LEFT OUTER JOIN "CLARITY"."dbo"."CLARITY_SER" "CLARITY_SER_log_phys" ON "OR_LOG_ALL_SURG"."SURG_ID"="CLARITY_SER_log_phys"."PROV_ID") LEFT OUTER JOIN "CLARITY"."dbo"."CLARITY_LOC" "CLARITY_LOC" ON "OR_SER_SURG_SRVC"."LOC_ID"="CLARITY_LOC"."LOC_ID"}
WHERE ("OR_UTIL_ROOM"."SNAPSHOT_DATE">={ts '2014-10-01 00:00:00'} AND "OR_UTIL_ROOM"."SNAPSHOT_DATE"<{ts '2014-11-01 00:00:00'}) AND "OR_UTIL_ROOM"."SNAPSHOT_NUMBER"=4 AND ("OR_UTIL_ROOM"."LOG_ID" IS NULL OR "OR_LOG_ALL_SURG"."LINE"=/********************************************************************************/
/* This formula returns the line for first primary surgeon for a surgery. */
/* This is to used to avoid duplicate rows getting returned since a primary */
/* surgeon can be listed multiple times for in and out of room times. */
/********************************************************************************/

case when "OR_LOG_ALL_SURG"."LOG_ID" is null then null
else
(select min(allsurg.LINE)
from OR_LOG_ALL_SURG allsurg
where allsurg.PANEL=1
and allsurg.ROLE_C=1
and allsurg.LOG_ID = "OR_LOG_ALL_SURG"."LOG_ID"
)
end) AND ("OR_UTIL_ROOM"."CASE_ID" IS NULL OR "OR_CASE_ALL_SURG"."LINE"=/********************************************************************************/
/* This formula returns the line for first primary surgeon for a surgery. */
/* This is to used to avoid duplicate rows getting returned since a primary */
/* surgeon can be listed multiple times for in and out of room times. */
/********************************************************************************/

case when "OR_CASE_ALL_SURG"."OR_CASE_ID" is null then null
else
(select min(allsurg.LINE)
from OR_CASE_ALL_SURG allsurg
where allsurg.PANEL=1
and allsurg.ROLE_C=1
and allsurg.OR_CASE_ID = "OR_CASE_ALL_SURG"."OR_CASE_ID"
)
end) AND "OR_SER_SURG_SRVC"."LOC_ID"=110303 AND "OR_UTIL_ROOM"."ROOM_ID"<>'107420' AND "CLARITY_SER_room"."PROV_NAME"<>'107420' AND "ZC_SHIFT_TYPE"."TITLE"='HCGH Main' AND "OR_LOC_SHIFT_TIMES"."FACILITY_ID"=1




 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top