Row wise result in calculated table based on multiple query?

Question

question

Ankur A3 asked May 4, '22 Ankur A3 answered May 4, '22

Row wise result in calculated table based on multiple query?

Hi Team,

I have data in global table in below format:

I want to sum process time for each weekday separately in 1 calculated table using sql query. I am able to get results in separate calculated table using query SELECT SUM(ProcessTime) FROM GlobalTable WHERE Weekday=Mon. But I want to apply each weekday query in same query statement and get results in single calculated table row wise. Example

Weekday SUM(ProcessTime)

Mon 172

Tue 139

Wed 127

Thu 8

Fri 132

Sat 54

Sun 0

How this can be done?

Thank you!

Software Version:

FlexSim 20.0.10

calculated table multiple query rowwise

1651639013925.png (14.6 KiB)

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Answer 1 · 2022-05-04T06:07:29Z

Felix Möhlmann answered May 4, '22

Add a rule to partition by the weekday column. When you look at the query you will see that this utilizes the GROUP BY keyword. Weekdays that don't appear in the table will not be part of the result. So if it is important that "Sun 0" (by you example) is part of the result, it might be easiest to just add 'dummy rows' to the global table with a process time of 0, so all weekdays are present.

(Random numbers were used for this screenshot, so they don't match with the example from the question)

1651644368775.png (14.1 KiB)

1651644391096.png (27.4 KiB)

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Answer 2 · 2022-05-04T11:31:24Z

Ankur A3 answered May 4, '22

Thank you @Felix Möhlmann for your answer.

I also found 1 more way of doing it using query cases based on weekdays.

Thank you for your continuous support!

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Answer 3 · 2022-05-04T06:57:19Z

Ankur A3 answered May 4, '22 Felix Möhlmann commented May 4, '22

Thank you so much @Felix Möhlmann

Appreciate your quick response!

· 2

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Ankur A3 commented · May 04, 2022 at 09:33 AM

Hi @Felix Möhlmann

I would like to ask one more question if I can.

My objective to get utilization for each weekday. So after getting this sum of process time for each weekday, I want to divide it for total working hours formula. I am counting frequency of each day (like # of Mon, # of Tue, # of Wed..................) in overall simulation time then I want to divide the sum of process time (Mon) by (# of Mon*working hours). Simililarly, I want to do it for other days.

Day Sum Sum/Working Hours

Mon 172 172/(# of Mon*Working Hours)

Tue 139 139/(# of Tue*Working Hours)

-------------------------------------------------------------------

I am facing issue to populate it in single calculated table?

What is the best way to do it after calculating sum of process time for each week day?

Note: 6 working hours per day

Thank you!

0 ·

Felix Möhlmann Ankur A3 commented · May 04, 2022 at 10:20 AM

For the uitlization you'd do an "Aggregated operation on two columns". If the working hours are kept in a different table, you have to join the two tables (probably on the weekday as shared column).

UtilizationCalcTable.fsm

0 ·

1651659289164.png (87.0 KiB)

1651659329930.png (32.3 KiB)

utilizationcalctable.fsm (81.6 KiB)

question