User810354248 posted
i was using this SQL stored procedure for a calculation of data which used to display in asp.net web page.
USE [BaijuEP]
GO
/****** Object: StoredProcedure [dbo].[Report] Script Date: 04/18/2014 09:06:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Report](
@year int,
@month int
)
AS
BEGIN
SET NOCOUNT ON
declare @d smalldatetime
declare @total int
declare @dd smalldatetime
set @d = CAST(CAST(@year AS varchar) + '-' + CAST(@month AS varchar) + '-1' as smalldatetime)
set @total=datediff(day, @d, dateadd(month, 1, @d))
set @dd = CAST(CAST(@year AS varchar) + '-' + CAST(@month AS varchar) + '-'+CAST(@total AS varchar) as smalldatetime)
declare @R_perday decimal(18, 3)
set @R_perday=
case
when @total=31 then 2.471612903
when @total=30 then 2.554
when @total=29 then 2.642068966
when @total=28 then 2.736428571
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 kamyno,Rank,Trade, Name,Pl,Appt,Cat, 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_present 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 kamyno, Rank,Trade, Name,Pl,Cat,Appt,CDPR, count(d) AS Days_Absent, @total-count(d) as Days_Present from
(
select * from
(
SELECT STAFF.kamyno, DATEADD(DAY, i-1, Rum.Out_dt) as d,Staff.Rank,Staff.Trade,Staff.Name,Staff.Pl,Staff.Appt,Staff.Cat,Staff.CDPR
FROM Nums, Rum,
STAFF
where i <= 1+DATEDIFF(DAY, Rum.Out_dt, Rum.In_dt)
AND STAFF.kamyno=Rum.kamyno
) a
where month(d)=@month
and year(d)=@year
) b
group by kamyno,[Rank],Trade,Name,Pl,Cat,Appt,CDPR
union
select kamyno,[Rank],Trade, Name ,Pl,Cat,Appt,CDPR, 0 AS Days_Absent, @total as Days_Present
from STAFF
WHERE NOT kamyno IN (
select kamyno from Rum
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
)
AND STAFF.TORS <= @dd
) t
END
i want this procedure to be implemented in my access database as a query or something like that how can it be done
