Use of------------------case---when----------------------------
Select UserAccount, Eatingdate,
case where sum (a) =1 then ' breakfast ' else ' end as ' breakfast ',
case where sum (b) =1 then ' lunch ' else ' end as ' lunch ',
case where sum (c) =1 then ' dinner ' else ' end as ' dinner '
From (
Select UserAccount, Eatingdate,
Case when menutype= ' breakfast ' then 1 else 0 end as a,
Case when menutype= ' lunch ' then 1 else 0 end as B,
Case when menutype= ' dinner ' then 1 else 0 end as C
From View_menureservation
where useraccount = ' 7923 '
and eatingdate between ' 2016-12-01 ' and ' 2016-12-31 '
) T
Group BY UserAccount, Eatingdate
ORDER BY eatingdate ASC
Nested subqueries when---------------------queries-----------------------------------------
SELECT tb.account, ' 2016-10 ' month,
(Max (case signstatus when ' on time ' then total ELSE 0 END) +
Max (case Signstatus if ' take some ' then total ELSE 0 END) +
Max (case Signstatus ' late ' then total ELSE 0 END)
) Sumday,
(Max (case signstatus when ' punctual ' then total ELSE 0 END)
+ MAX (case signstatus if ' take some ' then total ELSE 0 END)) OnTime,
Max (case signstatus when ' late ' then total ELSE 0 END) late,
Max (case signstatus when ' leave ' then total ELSE 0 END) leave,
Max (case Signstatus ' leave early ' then total ELSE 0 END) absenteeism,
(SELECT COUNT (*) from tb_workattendance
where workstatus= ' overtime ' and
Docdate between ' 2016-10-1 ' and ' 2016-10-31 ' and account = Tb.account and signtype= ' work ' GROUP by Workstatus, overtime,
0 Dayoff,
(select sum (totalhour) Totalhour from tb_restapply where docdate between ' 2016-11-01 ' and ' 2016-11-30 ' and ACCOUNT=TB.A Ccount GROUP by account) Totalhour
From
Select COUNT (1) Total, signstatus, account
From Tb_workattendance
where docdate between ' 2016-10-1 ' and ' 2016-10-31 '
Group BY Signstatus,account)
Tb
Group by Account
-----------------------------------------------------------------------------------
T-SQL query