To rewrite the sqls to get the result in a single column

[pol]

Please can help me anybody for the following..

select
attdate
max(emp.mon_hrs) as mon_hrs ,
max(emp.tue_hrs) as tue_hrs ,
max(emp.wed_hrs) as wed_hrs ,
max(emp.thu_hrs) as thu_hrs,
max(emp.fri_hrs) as fri_hrs ,
max(emp.sat_hrs) as sat_hrs
from emp
group by attdate ;

How I can rewrite this as follows

Select
attdate
if(dayname(attdate)) = 'Monday' , max(emp.mon_hrs),if(dayname(attdate)) =
'Tuesday', max(emp.tue_hrs)
from emp
group by attdate ;

With thanks

Polachan

 

[Dirk Goldgar]

Please can help me anybody for the following..

select
attdate
max(emp.mon_hrs) as mon_hrs ,
max(emp.tue_hrs) as tue_hrs ,
max(emp.wed_hrs) as wed_hrs ,
max(emp.thu_hrs) as thu_hrs,
max(emp.fri_hrs) as fri_hrs ,
max(emp.sat_hrs) as sat_hrs
from emp
group by attdate ;

How I can rewrite this as follows

Select
attdate
if(dayname(attdate)) = 'Monday' , max(emp.mon_hrs),if(dayname(attdate)) =
'Tuesday', max(emp.tue_hrs)
from emp
group by attdate ;

With thanks

Polachan

This is the sort of thing you have to do when you have unnormalized data.
This might give you what you want:

SELECT
attdate,
Choose(Weekday(attdate),
Max(sun_hrs),
Max(mon_hrs),
Max(tue_hrs),
Max(wed_hrs),
Max(thu_hrs),
Max(fri_hrs),
Max(sat_hrs))
FROM emp
GROUP BY attdate

 

[Rudolf Lamour]

You could use UNION ALL operations:

SELECT FORMAT(attdate,"dddd") AS DayOfWeek,
MAX(mon_hrs) AS MaxHours
FROM emp
WHERE WEEKDAY(attdate,1) = 2
GROUP BY FORMAT(attdate,"dddd")
UNION ALL
SELECT FORMAT(attdate,"dddd"),
MAX(tue_hrs)
FROM emp
WHERE WEEKDAY(attdate,1) = 3
GROUP BY FORMAT(attdate,"dddd")
UNION ALL
SELECT FORMAT(attdate,"dddd"),
MAX(wed_hrs)
FROM emp
WHERE WEEKDAY(attdate,1) = 4
GROUP BY FORMAT(attdate,"dddd"),
UNION ALL
SELECT FORMAT(attdate,"dddd"),
MAX(thu_hrs)
FROM emp
WHERE WEEKDAY(attdate,1) = 5
GROUP BY FORMAT(attdate,"dddd"),
UNION ALL
SELECT FORMAT(attdate,"dddd"),
MAX(fri_hrs)
FROM emp
WHERE WEEKDAY(attdate,1) = 6
GROUP BY FORMAT(attdate,"dddd");

But this is really only a crude solution to an inherently poor table
design.
You only need the one column for the hours in place of the five as in any
row
only one of these five can legitimately have a value as far as I can see.
With a single column it becomes a simple query:

SELECT FORMAT(attdate,"dddd") AS DayOfWeek
MAX(hours) AS MaxHours
FROM emp
GROUP BY FORMAT(attdate,"dddd");

You can easily fill such a normalized table by appending rows from the
result
of a UNION ALL query similar to the above, but not grouping by the day of
the
week or aggregating the hours, e.g.

SELECT attdate, mon_hrs AS Hours
FROM emp
WHERE WEEKDAY(attdate,1) = 2
UNION ALL
SELECT attdate, tue_hrs
FROM emp
WHERE WEEKDAY(attdate,1) = 3
UNION ALL
<and so on>

Ken Sheridan
Stafford, England