My database is SQL and i am using a stored procedure as under. The output is almost correct and i want to rectfy a small error in it
ALTER PROCEDURE [dbo].[Report](
@year int,
@month int
)
AS
BEGIN
SET NOCOUNT ON
declare @d smalldatetime
declare @total int
set @d = CAST(CAST(@year AS varchar) + '-' + CAST(@month AS varchar) + '-1' as smalldatetime)
set @total=datediff(day, @d, dateadd(month, 1, @d))
declare @R_perday decimal(18, 3)
set @R_perday=
case
when @total=31 then 2.47
when @total=30 then 2.55
when @total=29 then 2.64
when @total=28 then 2.73
end
;WITH
L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS i FROM L4)
select PNO, Name, Days_Absent, Days_Present,
@R_perday as R_perday,
--case when Days_Absent=0 then @R_perday*Days_Present else @R_perday*Days_Absent end as C_Allce,
@R_perday*Days_Absent as C_Allce,
case
when Days_Present>=20 then 38
when Days_Present<=9 then 0
when Days_Present>=10 then 19
end as RC_Allce
from
(
select PNO, Name, count(d) AS Days_Absent, @total-count(d) as Days_Present from
(
select * from
(
SELECT STAFF.PNO, DATEADD(DAY, i-1, Out_dt) as d,
NAME
FROM Nums, ABSENCE,
STAFF
where i <= 1+DATEDIFF(DAY, Out_dt, In_dt)
AND STAFF.PNO=ABSENCE.PNO
) a
where month(d)=@month
and year(d)=@year
) b
group by PNO, Name
union
select PNO, Name, 0 AS Days_Absent, @total as Days_Present
from STAFF
WHERE NOT PNO IN (
select pno from ABSENCE
where (month(Out_dt)=@month and year(Out_dt)=@year)
OR (month(In_dt)=@month and year(In_dt)=@year)
OR @d BETWEEN Out_dt AND In_dt
)
) t
END
i am listing my tables as under
STAFF
PNO Name Str_dt
214 Raju 1 Jan 2013
365 tamas 21 Feb 2012
785 King 3 Jan 2014
963 Sonu 2 Dec 2012
ABSENCE
PNO Name Status Out_dt In_dt
214 Raju Leave 1 Sep 2013 12 Sep 2013
365 tamas Out Duty 5 Sep 2013 10 Sep 2013
214 Raju Leave 16 Sep 2013 18 sep 2013
in the above procedure i didnt use the str_dt which is the person joined in that comapnt initially
The mistake i had noticed is that
if a person joines in 3 jan 2014 and the date and year selected in dropdownlist is Dec 2013.
the person who had joined in 3 Jan 2014 is also gets displayed in it. but it should not be. i tried to add some clause with str_dt but it is not working.
please help me to get through