Group Rows in a table

I need to group by the description so i get in a same row all the days of the week for example the 'Unjustified' must be shown once in a single row but when i'm using the Group By statement i'm not getting them concatenate Can someone help me with this.. Thank You

select Emp.ID, 
sum(case when ta_timecarddet.trstype = 6 or ta_timecarddet.trstype = 11 then ta_timecard.scheduledwork else case when ta_timecardlinks.fromtimer = ta_timecard.schedintime then ta_timecardlinks.effdurationr + ta_timecard.rndminin else case when ta_timecardlinks.totimer = ta_timecard.schedouttime then ta_timecardlinks.effdurationr - ta_timecard.rndminout else ta_timecardlinks.effdurationr end end end) / ta_timecard.scheduledwork as SchedWork,

case when absence.description is null then case when absence1.description is null then 'Unjustified' else absence1.description end else absence.description end as description,

case when dbo.ta_dayofweek(ta_timecard.schedindate) = 'Mon' then sum(case when ta_timecarddet.trstype = 6 or ta_timecarddet.trstype = 11 then ta_timecard.scheduledwork else case when ta_timecardlinks.fromtimer = ta_timecard.schedintime then ta_timecardlinks.effdurationr + ta_timecard.rndminin else case when ta_timecardlinks.totimer = ta_timecard.schedouttime then ta_timecardlinks.effdurationr - ta_timecard.rndminout else ta_timecardlinks.effdurationr end end end) / ta_timecard.scheduledwork end,
case when dbo.ta_dayofweek(ta_timecard.schedindate) = 'Tue' then sum(case when ta_timecarddet.trstype = 6 or ta_timecarddet.trstype = 11 then ta_timecard.scheduledwork else case when ta_timecardlinks.fromtimer = ta_timecard.schedintime then ta_timecardlinks.effdurationr + ta_timecard.rndminin else case when ta_timecardlinks.totimer = ta_timecard.schedouttime then ta_timecardlinks.effdurationr - ta_timecard.rndminout else ta_timecardlinks.effdurationr end end end) / ta_timecard.scheduledwork end,
case when dbo.ta_dayofweek(ta_timecard.schedindate) = 'Wed' then sum(case when ta_timecarddet.trstype = 6 or ta_timecarddet.trstype = 11 then ta_timecard.scheduledwork else case when ta_timecardlinks.fromtimer = ta_timecard.schedintime then ta_timecardlinks.effdurationr + ta_timecard.rndminin else case when ta_timecardlinks.totimer = ta_timecard.schedouttime then ta_timecardlinks.effdurationr - ta_timecard.rndminout else ta_timecardlinks.effdurationr end end end) / ta_timecard.scheduledwork end,
case when dbo.ta_dayofweek(ta_timecard.schedindate) = 'Thu' then sum(case when ta_timecarddet.trstype = 6 or ta_timecarddet.trstype = 11 then ta_timecard.scheduledwork else case when ta_timecardlinks.fromtimer = ta_timecard.schedintime then ta_timecardlinks.effdurationr + ta_timecard.rndminin else case when ta_timecardlinks.totimer = ta_timecard.schedouttime then ta_timecardlinks.effdurationr - ta_timecard.rndminout else ta_timecardlinks.effdurationr end end end) / ta_timecard.scheduledwork end,
case when dbo.ta_dayofweek(ta_timecard.schedindate) = 'Fri' then sum(case when ta_timecarddet.trstype = 6 or ta_timecarddet.trstype = 11 then ta_timecard.scheduledwork else case when ta_timecardlinks.fromtimer = ta_timecard.schedintime then ta_timecardlinks.effdurationr + ta_timecard.rndminin else case when ta_timecardlinks.totimer = ta_timecard.schedouttime then ta_timecardlinks.effdurationr - ta_timecard.rndminout else ta_timecardlinks.effdurationr end end end) / ta_timecard.scheduledwork end,
case when dbo.ta_dayofweek(ta_timecard.schedindate) = 'Sat' then sum(case when ta_timecarddet.trstype = 6 or ta_timecarddet.trstype = 11 then ta_timecard.scheduledwork else case when ta_timecardlinks.fromtimer = ta_timecard.schedintime then ta_timecardlinks.effdurationr + ta_timecard.rndminin else case when ta_timecardlinks.totimer = ta_timecard.schedouttime then ta_timecardlinks.effdurationr - ta_timecard.rndminout else ta_timecardlinks.effdurationr end end end) / ta_timecard.scheduledwork end,
case when dbo.ta_dayofweek(ta_timecard.schedindate) = 'Sun' then sum(case when ta_timecarddet.trstype = 6 or ta_timecarddet.trstype = 11 then ta_timecard.scheduledwork else case when ta_timecardlinks.fromtimer = ta_timecard.schedintime then ta_timecardlinks.effdurationr + ta_timecard.rndminin else case when ta_timecardlinks.totimer = ta_timecard.schedouttime then ta_timecardlinks.effdurationr - ta_timecard.rndminout else ta_timecardlinks.effdurationr end end end) / ta_timecard.scheduledwork end

from ta_timecarddet
inner join ta_timecard on ta_timecarddet.timecardid = ta_timecard.id
inner join emp on ta_timecard.empid = emp.id
inner join ta_schedulehist on ta_timecard.schedhistid = ta_schedulehist.id
left outer join ta_timecardlinks on ta_timecarddet.id = ta_timecardlinks.tctrsid
left outer join absence on ta_timecard.leavetype = absence.id
left outer join absence absence1 on ta_timecardlinks.leavetype = absence1.id

where  ta_timecarddet.trstype in (1, 5, 6, 11)
and ta_timecard.worktype in (1, 4, 5, 8)

Group BY absence.description, ta_timecard.ScheduledWork, Emp.ID,absence1.description, dbo.ta_dayofweek(ta_timecard.schedindate)

Answers


You need to switch where you are doing your sum, for the sake of not repeating your entire query and trying to make it visible without loads of scrolling I will demonstrate with a single column:

Note, I've also changed your expressions from case when .. else case when ... else to a single case statement with multiple whens.

sum(case when dbo.ta_dayofweek(ta_timecard.schedindate) = 'Mon' then 
            case when ta_timecarddet.trstype = 6 or ta_timecarddet.trstype = 11 
                    then ta_timecard.scheduledwork 

                when ta_timecardlinks.fromtimer = ta_timecard.schedintime 
                    then ta_timecardlinks.effdurationr + ta_timecard.rndminin 

                when ta_timecardlinks.totimer = ta_timecard.schedouttime 
                    then ta_timecardlinks.effdurationr - ta_timecard.rndminout 

                else ta_timecardlinks.effdurationr 
            end 
        else 0 end)  / 
    sum(case when dbo.ta_dayofweek(ta_timecard.schedindate) = 'Mon' 
                then ta_timecard.scheduledwork
            else 0
        end),

You will also need to remove ta_timecard.ScheduledWork and dbo.ta_dayofweek(ta_timecard.schedindate) from your group by.

Essentially you are placing things you want as conditions, but not in the group by inside the sum statement, so they don't need to be included in the group by.


Need Your Help

How to Create Custom View in Windows Event Viewer using API?

nsis custom-view eventviewer

Is there an API to create a Custom View in Windows Event Viewer?

CakePHP - Form->select doesn't have a wrapping div?

php forms cakephp select wrap

I'm wondering why $this->Form->select() in CakePHP 2.1 doesnt come with a wrapping div and label.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.