# Access query for calculating data

• ### Question

• User810354248 posted

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)

@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
(
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
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`
Friday, April 18, 2014 1:19 AM

• User-821857111 posted

`i want this procedure to be implemented in my access database as a query or something like that how can it be done`

Access doesn't support variables in queries, control of flow code, paging, common table expressions or batch statements so you cannot implement your query as-is. Your best bet is to retrieve all the data into some kind of collection (DataTable perhaps) and then use C#/LINQ to query and transform it in the client app.

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Friday, April 18, 2014 4:33 AM

### All replies

• User-821857111 posted

`i want this procedure to be implemented in my access database as a query or something like that how can it be done`

Access doesn't support variables in queries, control of flow code, paging, common table expressions or batch statements so you cannot implement your query as-is. Your best bet is to retrieve all the data into some kind of collection (DataTable perhaps) and then use C#/LINQ to query and transform it in the client app.

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Friday, April 18, 2014 4:33 AM
• User810354248 posted

is there any process to do this

Wednesday, April 30, 2014 9:29 PM
• User-821857111 posted

I'm not sure what you are asking. There are no wizards or shortcuts to doing this. You simply have to retrieve the data from the database and then use C# to filter and manipulate it according to your business needs. Here's a link to instructions on using Linq to Dataset in case you decide to follow that route: http://msdn.microsoft.com/en-us/library/bb386977(v=vs.110).aspx

Thursday, May 1, 2014 2:39 AM