locked
Selecting columns from a stored procedure RRS feed

  • Question

  • User2004340689 posted

    Hi everyone

    I have the below stored procedure and I want to select all columns to display them on the web form using "where clause" by specifying the "regno" provided by user in a textbook named txtboxsearch or "date from and to" by user selecting time on the displayed calender. The problem am having is how to select the column names like "totalhourabsent" and "totalhourpresent" that aren't in the database table but rather in the stored procedure.

    Below is my stored procedure:

    CREATE PROCEDURE [dbo].[GetAttendanceByHours]   AS   BEGIN
    
    SELECT *, 
           Isnull(no_hrs_present, 0) * 100 / ( 
           ( Isnull(no_hrs_present, 0) ) + ( Isnull( 
           no_hrs_absnt, 0) ) ) PRSNT_PERC, 
           Isnull(no_hrs_absnt, 0) * 100 / ( ( Isnull(no_hrs_present, 0) ) + ( 
                                             Isnull( 
                                             no_hrs_absnt, 0) ) )   ABSNT_PERC 
    FROM   (SELECT regno, 
                   firstname, 
                   lastname, 
                   Max(CASE 
                         WHEN status = 'P' THEN cnt 
                       END) NO_HRS_PRESENT, 
                   Max(CASE 
                         WHEN status = 'A' THEN cnt 
                       END) NO_HRS_ABSNT 
            FROM   (SELECT regno, 
                           firstname, 
                           lastname, 
                           status, 
                           Count(status) CNT 
                    FROM   attendancetable 
                    GROUP  BY regno, 
                              firstname, 
                              lastname, 
                              status) A 
            GROUP  BY regno, 
                      firstname, 
                      lastname) tmp 
    
    END

    Monday, April 25, 2016 6:15 PM

Answers

  • User-219423983 posted

    Hi Allenteeno,

    The problem am having is how to select the column names like "totalhourabsent" and "totalhourpresent" that aren't in the database table but rather in the stored procedure.

    I think you’d better replace the “Max” with “Sum” to get the total sub item counts and then remove the “Group by” from the outside select query.

    I have created a demo and you could have a look. You could compare this demo to modify your code according to your need.

    declare @t table(id int, [Status] varchar(4), [hours] int)
    insert @t values (1,'P',8),(1,'A',4),(1,'P',4),(1,'P',4),(1,'A',8),
    (2,'P',8),(2,'P',8),(2,'A',8),(2,'A',4),(2,'P',4)
    ;with cte as
    (
    select 
    	id,
    	sum(case [Status] 
    		when 'A' then [hours]
    		else 0
    		end
    		) as AbsentCount,
    	sum(case [Status] 
    		when 'P' then [hours]
    		else 0
    		end
    		) as PresentCount
    from @t
    group by id
    )
    select 
    	id,AbsentCount,PresentCount, (AbsentCount+PresentCount) as [TotalHours],
    	AbsentCount*100/(AbsentCount+PresentCount) as Absent_PERC,
    	PresentCount*100/(AbsentCount+PresentCount) as Absent_PERC
    from cte
    

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 26, 2016 5:04 AM