quote:

---

Originally posted by lionofdezert

-- exec [GetDashboardAdminResults] 'saravanan_r','P'
Alter PROCEDURE [dbo].[GetDashboardAdminResults]
(
@ViewMyAssignedRequestUser AS VARCHAR(50),
@Year AS VARCHAR(10)
)
AS
BEGIN

Declare @YearStatus int

if @Year = 'C'
Set @YearStatus = 0
else
if @Year = 'P'
Set @YearStatus = 1

Declare @sqlCommand varchar(8000),
@SQLstr VARCHAR(8000)
SET @sqlCommand = ' and a.assigneduser ='''
+ @ViewMyAssignedRequestUser + ' '

SELECT @SQLstr = 'Select A.SlNo,A.Detail,Sum(A.Jan) Jan,Sum(A.Feb) Feb,Sum(A.Mar) Mar,Sum(A.Apr) Apr,Sum(A.May) May,
Sum(A.Jun) Jun,Sum(A.Jul) Jul,Sum(A.Aug) Aug,Sum(A.Sep) Sep,Sum(A.Oct) Oct,Sum(A.Nov) Nov,Sum(A.[Dec]) [Dec],Sum(A.Total) Total From
(
Select ''3'' SlNo,
''Equipment Pending ''+ [Detail],
isnull(cast([1]as int),0) As Jan,
isnull(cast([2]as int),0) As Feb,
isnull(cast([3]as int),0) As Mar,
isnull(cast([4]as int),0) As Apr,
isnull(cast([5]as int),0) As May,
isnull(cast([6]as int),0) As Jun,
isnull(cast([7]as int),0) As Jul,
isnull(cast(as int),0) As Aug,
isnull(cast([9]as int),0) As Sep,
isnull(cast([10]as int),0) As Oct,
isnull(cast([11]as int),0) As Nov,
isnull(cast([12]as int),0) As [Dec],
(isnull(cast([1]as int),0) + isnull(cast([2]as int),0) + isnull(cast([3]as int),0) + isnull(cast([4]as int),0))
+ isnull(cast([5]as int),0) + isnull(cast([6]as int),0) + isnull(cast([7]as int),0) + isnull(cast(as int),0)
+ isnull(cast([9]as int),0) + isnull(cast([10]as int),0) + isnull(cast([11]as int),0) + isnull(cast([12]as int),0)
As Total
From
(
SELECT Month(a.createdDateTime) Mon,
a.id,
b.description,
CONVERT(VARCHAR(10), a.createdDateTime, 101) as submittedOn,
CONVERT(VARCHAR(10), a.requestDueDate , 101) as requestDueDate,
d.userFirstName ,
d.userLastName,
c.workflowEventDescription,
case when a.studioid is null then I.studioId else a.studioid end as assignedStudio,
CONVERT(VARCHAR(10), a.assignedDatetime, 101) as assignedDate,
a.requestStatus,
case when cast(case when a.requestStatus in (1,2) and a.equipment=0.00 then a.equipment else a.equipment end as varchar) = ''0.00'' then ''''
else cast(case when a.requestStatus in (1,2) and a.equipment=0.00 then a.equipment else a.equipment end as varchar)
end as equipment,
case when cast(case when a.requestStatus in (1,2) and a.labour=0.00 then a.labour else a.labour end as varchar) = ''0.00'' then ''''
else cast(case when a.requestStatus in (1,2) and a.labour=0.00 then a.labour else a.labour end as varchar)
end as labour,
a.soldOrLost,
a.assignedUser ,
a.equipment as equipment1,
a.labour as labour1
FROM tblRequestDetails a
join tbllookupdetails b on a.requestTypeId = b.id
join tblWorkflowEvents c on a.workflowStatus = c.id
left outer join tblUserDetails d on a.requestorid = d.userId
left outer join tblIndustryDetails I on I.id = a.companyIndustry
Where 1 = 1 and a.isactive =1
and a.requestStatus in (1,2,3)
' + ' ' + @sqlCommand + 'and YEAR(a.createdDateTime)= YEAR(getdate())-'
+ @YearStatus
+ '
and a.equipment is not null
and a.equipment > 0
--order by a.id desc
) up
pivot (
Sum(equipment1) For Mon IN([1], [2], [3], [4], [5], [6],[7], , [9], [10], [11], [12])
) As P1'

EXEC ( @SQLstr
)
END

--------------------------
http://connectsql.blogspot.com/

---