July 2, 2013 at 5:54 am
alter procedure [dbo].[pocc]
(
@empid nvarchar(10),
@department varchar(10)
)
as
begin
create TABLE #TempEmployees
(
date datetime,
eid int,
remarks varchar(50)
)
-- Insert result from the SP to temp table
INSERT INTO #TempEmployees
EXEC dbo.at @empid,@department
--Verify the Insert records
SELECT
*
FROM #TempEmployees where eid=@empid
end
exec [pocc] 17074,''
when i exceute its giving me that result
date----------------------------eid--------remarks
2013-01-06 00:00:00.000-------17074---OFF DAY
2013-01-07 00:00:00.000-------17074---ABSENT
2013-01-07 00:00:00.000-------17074----Late
2013-01-08 00:00:00.000-------17074----HALFDAY
how i make a summary result like this
-----eid---offday-----absent-----late---------halfday---
-----17074--1----------1--------1----------1------
immad
July 2, 2013 at 8:01 am
Are you looking for something like this?
DECLARE @table TABLE
(eid INT, Remarks VARCHAR(10))
INSERT INTO @table
VALUES(17074, 'OFF DAY'),(17074, 'ABSENT'),
(17074, 'Late'),(17074, 'HALFDAY'),
(17074, 'ABSENT'), (17074, 'OFF DAY')
SELECT eid ,
SUM(CASE Remarks WHEN 'OFF DAY' THEN 1 ELSE 0 END) AS OffDay,
SUM(CASE Remarks WHEN 'ABSENT' THEN 1 ELSE 0 END) AS [Absent],
SUM(CASE Remarks WHEN 'LATE' THEN 1 ELSE 0 END) AS Late,
SUM(CASE Remarks WHEN 'HALFDAY' THEN 1 ELSE 0 END) AS HalfDay
FROM @table
GROUP BY eid
July 2, 2013 at 11:00 pm
well its calculating wrong halfday other wise query is fine thanks
immad
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply