January 29, 2012 at 10:43 am
guyz i wanna add total in my sql query i want to show in cross tab
DECLARE @query1 NVARCHAR(max)
SET @query1 = 'select case when( (select COUNT(id) from ##cmd_result where ##cmd_result.temp=FD.Work_Date ) > 0 )then CONVERT(VARCHAR(20), FD.Work_Date, 103)+ ''-'' + ''PH'' else CONVERT(VARCHAR(20), FD.Work_Date, 103) end as Total_Dates from form_details FD
WHERE FD.Work_Date BETWEEN '''+CONVERT(nvarchar(30), @start_Date , 110) +''' and '''+CONVERT(varchar(30),@end_Date, 110) +'''
GROUP BY FD.Work_Date
ORDER BY FD.Work_Da
January 29, 2012 at 10:45 am
but am getting
ISNULL([2012-01-01],0) +
ISNULL([2012-01-01],0) +
ISNULL([2012-01-01],0) +
ISNULL([2012-01-01],0) +
ISNULL([2012-01-01],0) +
MY SP IS
select @col = COALESCE(@col + ',','')+ QUOTENAME(Work_Date)
from form_Details WHERE Work_Date BETWEEN ''+CONVERT(nvarchar(30),@start_Date , 110) +'' and ''+CONVERT(varchar(30),@end_Date, 110) +''
GROUP BY Work_Date
ORDER BY Work_Date
PRINT @col
DECLARE @query1 NVARCHAR(max)
SET @query1 = 'select case when( (select COUNT(id) from ##cmd_result where ##cmd_result.temp=FD.Work_Date ) > 0 )then CONVERT(VARCHAR(20), FD.Work_Date, 103)+ ''-'' + ''PH'' else CONVERT(VARCHAR(20), FD.Work_Date, 103) end as Total_Dates from form_details FD
WHERE FD.Work_Date BETWEEN '''+CONVERT(nvarchar(30), @start_Date , 110) +''' and '''+CONVERT(varchar(30),@end_Date, 110) +'''
GROUP BY FD.Work_Date
ORDER BY FD.Work_Date'
SET @sql = 'SELECT Name ,BlockType, Block_Name ,'+@col+' from
(
select EM.Employee_Name as Name ,
BN.Block_Name as Block_Name,
FD.Work_Date AS Date_Issued ,
CASE BN.BlockType WHEN 1 THEN ''Domestic'' ELSE ''OverSeas'' END AS BlockType,
sum(FD.Work_Hours) AS HOURS
from Employee_Master EM , Block_Master BN , Form_Details FD where Em.Employee_Master_Id = FD.Employee_Id
and BN.Block_Master_Id = FD.Block_Id
and FD.Work_Date BETWEEN '''+CONVERT(nvarchar(30), @start_Date , 110) +''' and '''+CONVERT(varchar(30),@end_Date, 110) +'''
AND EM.Employee_Master_Id = ' + convert(nvarchar,@Employee_Id)+'
GROUP BY BlockType,work_date ,Block_Name , EM .Employee_Name ,BlockType
) p PIVOT (MAX(HOURS) FOR Date_Issued IN ( ' + @col + ' )) AS pvt
order BY blocktype '
PRINT(@sql)
EXEC(@sql)
January 29, 2012 at 2:21 pm
Using PIVOT makes it rather complex to add the total dynamically.
You might want to use the DynamicCrossTab approach instead. For details please see the related link in my signature.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply