Hi Karol



Thank you for the feedback, I understand what you mean, but how would I go about adding or changing to get the “group by”?



This is the result I get


NAME

MONDAY

TUESDAY

WEDNESDAY

THURSDAY

FRIDAY

SATURDAY

SUNDAY


JOHN DOE

OFF


JOHN DOE

OFF



JOHN DOE

YARD



JOHN DOE

YARD



JOHN DOE

SICK



JOHN DOE

SICK



JOHN DOE

SICK





And this is the result I want




NAME

MONDAY

TUESDAY

WEDNESDAY

THURSDAY

FRIDAY

SATURDAY

SUNDAY


JOHN DOE

SICK

SICK

SICK

YARD

YARD

OFF

OFF







Regards



Stef van der Merwe



From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: 05 January 2017 04:48 PM
To: firebird-support@yahoogroups.com; 'Stef' stef@... [firebird-support]
Subject: Re: [firebird-support] Case sql







Hi,



"case" is function and operate on row data.

If you have 7 rows then you got 7 rows after case

To reduce rows you need to "group by"





regards,

Karol Bieniaszewki









W dniu 2017-01-05 13:12:10 użytkownik 'Stef' stef@... [firebird-support] <firebird-support@yahoogroups.com> napisał:



Good day everybody,

I have the following query

__________________________________________________________
____________________

select

D.NAME as NAME,

(case (extract (Weekday from P.plandate )) when 1 then (P.PLANSTATUS)
else '' end) as Monday,

(case (extract (Weekday from P.plandate )) when 2 then (P.PLANSTATUS)
else '' end) as Tuesday,

(case (extract (Weekday from P.plandate )) when 3 then (P.PLANSTATUS)
else '' end) as Wednesday,

(case (extract (Weekday from P.plandate )) when 4 then (P.PLANSTATUS)
else '' end) as Thursday,

(case (extract (Weekday from P.plandate )) when 5 then (P.PLANSTATUS)
else '' end) as Friday,

(case (extract (Weekday from P.plandate )) when 6 then (P.PLANSTATUS)
else '' end) as Saturday,

(case (extract (weekday from P.plandate )) when 0 then (P.PLANSTATUS)
else '' end) as Sunday

from driverplan P

join DRIVERS D on P.DRIVERNR = D.DRIVERNR

where P.plandate >= '2017/01/01'

order by 1

__________________________________________________________
____________________

My question is why does it not return 1 row per driver, but instead I get 7
rows?

Else how can I achieve this?

Regards

Stef van der Merwe

Email stef@...

[Non-text portions of this message have been removed]







[Non-text portions of this message have been removed]