hi
following is a working sql query
declare @dte as datetime ='2015-01-01'
declare @StDt as Datetime = DATEADD(mm,DATEDIFF(mm,0,@dte), 0)
declare @EnDt as datetime = DATEADD( DD, -1, DATEADD(mm,DATEDIFF(mm,0,@dte) + 1, 0));
DECLARE @query AS NVARCHAR(MAX);
;WITH Dates AS(
SELECT DATEADD( DD, ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) - 1, @StDt) Month_date
FROM (VALUES(0),(0),(0),(0),(0),(0))E(N),
(VALUES(0),(0),(0),(0),(0),(0))E2(N)
)
SELECT @Query = 'SELECT m.empno ' + CHAR(13)
+ (SELECT CHAR(9) + ',MAX( CASE WHEN sdate = ''' + CONVERT( CHAR(8), Month_date, 112) + ''' THEN status END) AS ' + QUOTENAME( CONVERT( char(2), Month_date, 103)) + CHAR(13)
FROM Dates
WHERE Month_date BETWEEN '2015-01-01' AND '2015-01-15'
ORDER BY Month_date
FOR XML PATH(''),TYPE).value('.','varchar(max)')
+ '
FROM employee m
LEFT
JOIN shiftdetails b ON m.empno = b.empno
GROUP BY m.empno '
EXEC sp_executesql @Query
my requirement is i want to make a change in the following line
WHERE Month_date BETWEEN '2015-01-01' AND '2015-01-15'
i want to change the above line as
WHERE Month_date BETWEEN '"+stdate+"' AND '"+endate+"'
when i try this code it showed error.
next requirement is
FROM employee m
LEFT
JOIN shiftdetails b ON m.empno = b.empno
GROUP BY m.empno '
here i want to add one where condition as
FROM employee m
LEFT
JOIN shiftdetails b ON m.empno = b.empno where b.empno='"+empno+"' and b.shifttype='MS'
GROUP BY m.empno '
how it is possible