SELECT @col = (SELECT DISTINCT quotename(Work_date) + ','
               FROM   form_Details
               WHERE  Work_Date BETWEEN @start_Date and @end_Date
               ORDER  BY Work_Date
               FOR XML PATH(''))
SLEECT @col = substring(@col, 1, len(@col) - 1)

I've also cleaned up the kludgy way you deal with the data parameters.

The big query can also be simplified:

SET @sqlquery =
   ';with cte as  (
  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 
  INNER JOIN Form_Details FD ON Em.Employee_Master_Id = FD.Employee_Id 
  INNER JOIN Block_Master BN ON BN.Block_Master_Id = FD.Block_Id      
  WHERE FD.Work_Date BETWEEN @start_Date and  @end_Date
    AND EM.Employee_Master_Id = @Employee_Id
  GROUP BY  BlockType,work_date, Block_Name,  EM.Employee_Name)  
SELECT Name, BlockType, Block_Name, ' + @col + ', ' + @ColsSum+ ' As Total   
from  CTE   
PIVOT (MAX(HOURS)  FOR Date_Issued IN ( ' +  @col + ' ) ) AS pvt 
UNION ALL 
  SELECT Name, BlockType, Block_Name, '+@col+', ' + @ColsSum + ' As Total
  FROM (select ''Total'' as Name, BlockType, space(20) as Block_Name, 
        Hours, Date_Issued 
        FROM CTE) P 
  PIVOT (SUM(hours) FOR Date_Issued IN (' + @Col + ') ) as pvt 
order BY blocktype '

Then you run this as

EXEC sp_executesql 
     @sqlquery, 
     N'@start_date datetime, @end_date datetime, @Employee_id int',
     @start_date, @end_date, @Employee_id

Simpler, cleaner and more efficient!

Now that I have been able to read you question, I think the answer to you question is

SELECT @headers = (SELECT DISTINCT quotename(Work_date) + ' AS ' +
                                   quotename(CONVERT(varchar(2),getdate(),103)) + ', '
               FROM   form_Details
               WHERE  Work_Date BETWEEN @start_Date and @end_Date
               ORDER  BY Work_Date
               FOR XML PATH(''))
SLEECT @headers = substring(@headers, 1, len(@headers) - 1)

;with cte as  (
  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
  INNER JOIN Form_Details FD ON Em.Employee_Master_Id = FD.Employee_Id
  INNER JOIN Block_Master BN ON BN.Block_Master_Id = FD.Block_Id     
WHERE FD.Work_Date BETWEEN '01-01-2012' and '01-02-2012'     
AND EM.Employee_Master_Id = 1
  GROUP BY  BlockType,work_date, Block_Name,  EM.Employee_Name) 
SELECT Name, BlockType, Block_Name, [2012-01-01] AS [03], [2012-01-02] AS [03], [2012-01-01]+[2012-01-02] As Total  
from  CTE  
PIVOT (MAX(HOURS)  FOR Date_Issued IN ( [2012-01-01],[2012-01-02] ) ) AS pvt
UNION ALL
  SELECT Name, BlockType, Block_Name, [2012-01-01] AS [03], [2012-01-02] AS [03], [2012-01-01]+[2012-01-02] As Total
  FROM (select 'Total' as Name, BlockType, space(20) as Block_Name,
        Hours, Date_Issued
        FROM CTE) P
  PIVOT (SUM(hours) FOR Date_Issued IN ([2012-01-01] AS [03], [2012-01-02] AS [03]) ) as pvt
order BY blocktype

 

incorrect index near AS ...its print 03 for all and also i cant use ordery by

quote:

---

Originally posted by visakh16

just use DATENAME(dd,datefield)

for getting it in 2 digits

use

RIGHT('00' + DATENAME(dd,datefield),2)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---