Returning Data Set in Correct Order

[luvmy2kids]

I have the below code:

DECLARE @quarter int
DECLARE @year varchar(4)
DECLARE @idedate1 datetime
DECLARE @idedate2 datetime
DECLARE @idedate3 datetime

SET @quarter = 2
SET @year = 2008


select @idedate1 = case
when @quarter = 1 then '1/12/' + Convert(VarChar(4), @Year)
when @quarter = 2 then '4/12/' + Convert(VarChar(4), @Year)
when @quarter = 3 then '7/12/' + Convert(VarChar(4), @Year)
when @quarter = 4 then '10/12/' + Convert(VarChar(4), @Year)
END

select @idedate2 = case
when @quarter = 1 then '2/12/' + Convert(VarChar(4), @Year)
when @quarter = 2 then '5/12/' + Convert(VarChar(4), @Year)
when @quarter = 3 then '8/12/' + Convert(VarChar(4), @Year)
when @quarter = 4 then '11/12/' + Convert(VarChar(4), @Year)
END

select @idedate3 = case
when @quarter = 1 then '3/12/' + Convert(VarChar(4), @Year)
when @quarter = 2 then '6/12/' + Convert(VarChar(4), @Year)
when @quarter = 3 then '9/12/' + Convert(VarChar(4), @Year)
when @quarter = 4 then '12/12/' + Convert(VarChar(4), @Year)
END

select d.abbrev, @idedate1,
'employeecount1' = Case when datepart("m", @idedate1)in (1,4,7,10) then count(distinct p.employeeid) --as 'employeecount1'
Else '' end, 'month1'
from prcheckheader p (READCOMMITTED)
INNER JOIN empemployee e on e.employeeid = p.employeeid
INNER JOIN prchecktax x on x.checkhistid = p.checkhistid
INNER JOIN TaxEntity d on d.taxentityid = x.taxentityid and d.VrtxTaxID = 29
--LEFT OUTER JOIN sspayperiods b (READCOMMITTED) on b.payperiodid = p.payperiodid
where checkdate between @idedate1 and dateadd(day, 6, @idedate1)
Group by d.abbrev

UNION

select d.abbrev, @idedate2,
'employeecount2' = Case when datepart("m", @idedate2)in (2,5,8,11) then count(distinct p.employeeid) --as 'employeecount2'
Else '' end, 'month2'
from prcheckheader p (READCOMMITTED)
INNER JOIN empemployee e on e.employeeid = p.employeeid
INNER JOIN prchecktax x on x.checkhistid = p.checkhistid
INNER JOIN TaxEntity d on d.taxentityid = x.taxentityid and d.VrtxTaxID = 29
--LEFT OUTER JOIN sspayperiods b (READCOMMITTED) on b.payperiodid = p.payperiodid
where checkdate between @idedate2 and dateadd(day, 6, @idedate2)
Group by d.abbrev

UNION

select d.abbrev, @idedate3,
'employeecount3' = Case when datepart("m", @idedate3)in (3,6,9,12) then count(distinct p.employeeid) --as 'employeecount3'
Else '' end, 'month3'
from prcheckheader p (READCOMMITTED)
INNER JOIN empemployee e on e.employeeid = p.employeeid
INNER JOIN prchecktax x on x.checkhistid = p.checkhistid
INNER JOIN TaxEntity d on d.taxentityid = x.taxentityid and d.VrtxTaxID = 29
--LEFT OUTER JOIN sspayperiods b (READCOMMITTED) on b.payperiodid = p.payperiodid
where checkdate between @idedate3 and dateadd(day, 6, @idedate3)
Group by d.abbrev


The result set displays as:

Abbrev Date employeecount1
AL 5/12/2008 1
AL 6/12/2008 1
AL 4/12/2008 1

What I am trying to accomplish is:

Abbrev Date1 Date2 Date3
AL 1 1 1

Is there a way to get this result with the code I have?

Thanks,

 

[markros]

Yes, for your case if you're using SQL Server 2005 or up, you need to look at PIVOT command.

 

[luvmy2kids]

Thanks! I will try this.