locked
Access query for calculating data RRS feed

  • 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)
     
    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
    Friday, April 18, 2014 1:19 AM

Answers

  • 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 Anonymous 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 Anonymous 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