Eliminating field in group by clause

[luvmy2kids]

I have the below code:

'Week1' = case when checkdate = dateadd(wk,-4, '2009-9-04') then ISNULL(d.units,0) else 0 end,
'Week2' = case when checkdate = dateadd(wk,-3, '2009-9-04') then ISNULL(d.units,0) else 0 end,
'Week2' = case when checkdate = dateadd(wk,-2, '2009-9-04') then ISNULL(d.units,0) else 0 end,
'Week4' = case when checkdate = dateadd(wk,-1, '2009-9-04') then ISNULL(d.units,0) else 0 end

I want to return one line with each week across the top like this:

Week1 Week2 Week3 Week4
1 2 3 4

Because I have to include the checkdate in my group by clause, it displays my results as:

Week1
Week2, etc.

How can I write my case statements so I won't have to include checkdate in the group by clause. I can't do a sum because it will return incorrect results

Thanks a bunch!!!

 

[markros]

If your DateToCompare is fixed, then use 4 different select statements as derived tables and join them based on ID. I posted a similar sample today.

 

[markros]

Here is the sample thread183-1567658

 

[luvmy2kids]

Here is my complete SQL statement:

create table #emaildata (intid int, fieldid int, extid int, employeeid int, socsecno varchar(11), firstname varchar(255), lastname varchar(255), checkdate datetime, units int,
state varchar(50), billdesc varchar(255), name varchar(255))

insert into #emaildata (intid, fieldid, extid, employeeid, socsecno, firstname, lastname, checkdate, units, state, billdesc, name)

(
select x.intid, x.fieldid, x.extid, e.employeeid, e.socsecno, e.firstname, e.lastname, u.checkdate, u.units, r.state, wr.billdesc, c.name
from ss_hcsscsa.dbo.empemployee e
LEFT OUTER JOIN integrateprod.dbo.intxref x on e.employeeid = x.extid and x.subscriberid = 2 and x.fieldid = 186
INNER JOIN ss_hcsscsa.dbo.premployeeded pd on pd.employeeid = e.employeeid and pd.dedstatusid = 1
INNER JOIN ss_hcsscsa.dbo.prdedtype t on t.dedtypeid = pd.dedtypeid
INNER JOIN
(
select p.employeeid, sum(units) as units, checkdate
from ss_hcsscsa.dbo.prchecktc t
inner join ss_hcsscsa.dbo.prcheckheader p on p.checkhistid = t.checkhistid
where t.paycodeid in (1,2,3,4,5,6,7) and checkdate >= dateadd(wk,-4, getdate())
group by employeeid, checkdate
)u on u.employeeid = e.employeeid
INNER JOIN ss_hcsscsa.dbo.woworkorder w ON w.employeeid = e.employeeid
INNER JOIN ss_hcsscsa.dbo.worequest wr ON wr.worid = w.worid
INNER JOIN ss_hcsscsa.dbo.CustCustomer c ON c.custid = wr.custid
INNER JOIN
(
Select
CASE WHEN e.wloactiveflag = 1 then e.CITY else c.city END as State, e.employeeid
from ss_hcsscsa.dbo.woworkorder wo
INNER JOIN ss_hcsscsa.dbo.woRequest w ON wo.worid = w.worid
INNER JOIN ss_hcsscsa.dbo.custaddress c ON c.custaddrid = w.custaddrid
INNER JOIN ss_hcsscsa.dbo.empemployee e ON e.employeeid = wo.employeeid
) r on r.employeeid = e.employeeid

Where
t.deddesc in ('Dental Ins', 'Medical Ins', 'Vision Ins')
and
r.state <> 'HI'
group by e.employeeid, e.socsecno, e.firstname, e.lastname, x.intid, x.fieldid, x.extid, u.units, r.state, wr.billdesc, c.name, u.checkdate
)

Select
d.employeeid,
'Recruiter' = pl2.fullname,
d.socsecno,
d.firstname, d.lastname,
'Email Address' = isnull(email,email2),
d.name,
--s.pcdesc,
--max(d.billdesc) as jobdescription,
--d.units,
'Week1' = case when checkdate = dateadd(wk,-4, '2009-9-04') then ISNULL(d.units,0) else 0 end ,
'Week2' = case when checkdate = dateadd(wk,-3, '2009-9-04') then ISNULL(d.units,0) else 0 end,
'Week2' = case when checkdate = dateadd(wk,-2, '2009-9-04') then ISNULL(d.units,0) else 0 end,
'Week4' = case when checkdate = dateadd(wk,-1, '2009-9-04') then ISNULL(d.units,0) else 0 end

from #emaildata d
left outer join integrateprod.dbo.intxref i on i.intid = d.intid and i.fieldid = 186 and i.subscriberid = 1
LEFT OUTER JOIN candidates c on c.candidateid = i.extid
LEFT OUTER JOIN
(
Select ch.candidateid, max(ch.activitydate) as activitydate
from CandidateHistory ch
inner join HistoryCategories hc on hc.ID = ch.CategoryID and hc.StageID = 6
Group By ch.candidateid
) as pl on pl.candidateid = c.candidateid

LEFT OUTER JOIN
(
Select max(activitydate) as activitydate, fullname, candidateid
from candidatehistory c
inner join HistoryCategories hc on hc.ID = c.CategoryID and hc.StageID = 6
inner join users s on s.userid = c.activityuserid
Group by fullname, candidateid
)
pl2 on pl2.activitydate = pl.activitydate and pl2.candidateid = pl.candidateid
where d.units < 30 and employeeid = 39121
Group by d.employeeid, d.socsecno, d.firstname, d.lastname, c.email, c.email2, pl2.fullname, d.name, d.units, d.checkdate

Order by 6

drop table #emaildata

How will I go about the suggestion you posted? Or do I need to go another route?

Thanks!!