Quote:
sir i don't know what to do in this ?
So start by getting the data into a aggregated form:
SELECT DatePart(ww, Date_of_Joining) AS WeekNo, COUNT(*) AS PerMonth
FROM tblEmployeeDetails
GROUP BY DatePart(ww, Date_of_Joining)
This gives you the info you need, without the zeroed info, and without the PIVOT.
Next, add the zero information: That's complex, and the simplest way to do it is to create a table of all the week numbers: a single column, containing 1 to 53 inclusive.
SELECT w.WeekNo AS WeekNo, COUNT(s.EMPId) AS PerMonth
FROM tblEmployeeDetails s
RIGHT JOIN dbo.WeekNumbers w ON w.WeekNo = DatePart(ww, s.Date_of_Joining)
WHERE w.WeekNo BETWEEN 1 AND 5
GROUP BY w.WeekNo
Now, you have the raw data you need:
WeekNo PerMonth
1 0
2 0
3 2
4 1
5 1
And all you have left to do is PIVOT that. I'll leave that to you! :laugh: