Hello,
I am using Firebird 2.5.5. and see following behaiviour:
1. One table is used to get the weekdays in a list
2. The other table is joined to identify those working schedules for
these days
3. If the second table has a value in the field ANMERKUNG the sorting
order inside List() changes:
a. Without ANMERKUNG - thats the expected result:
WOCHE LIST RES_ID BAUSTELLE AVG
2016-15 [MO],[DI],[MI],[DO],[FR] 12
URLAUB 1
2016-15 [MO],[DI],[MI],[DO],[FR] 34
URLAUB 1
b. With ANMERKUNG - the day with ANMERKUNG IS sorted first:
WOCHE LIST LIST1 RES_ID BAUSTELLE AVG
2016-15 [DI],[MO],[MI],[DO],[FR] Hallo 12
URLAUB 1
2016-15 [DO],[MO],[DI],[MI],[FR] Irgendwo
34 URLAUB 1
Any idea how to overcome this?
Thanks
Niko
This ist the complete query:
WITH SORTEDDATE AS (
SELECT (CASE Extract (WEEKDAY
FROM x.DATUM) WHEN 1 THEN '[MO]' WHEN 2 THEN '[DI]' WHEN 3 THEN '[MI]' WHEN
4 THEN '[DO]' WHEN 5 THEN '[FR]' WHEN 6 THEN '[SA]' WHEN 0 THEN '[SO]' END)
AS DATESTRING,
x.RES_ID, x.BAUSTELLE, x.WOCHE, x.KAPAZITAET,
x.ANMERKUNG
FROM ARBEITSEINTEILUNG AS x
ORDER BY x.DATUM)
SELECT WOCHE,
LIST(DATESTRING),
--LIST(ANMERKUNG ), -- This changes the order of sorting inside the CTE
RES_ID,
BAUSTELLE,
AVG(KAPAZITAET)
FROM SORTEDDATE
WHERE WOCHE = '2016-15' AND BAUSTELLE = 'URLAUB'
GROUP BY
WOCHE, RES_ID, BAUSTELLE
ORDER BY WOCHE, RES_ID
