Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Feb 2006
    Posts
    42

    Unanswered: to use function in a query

    hi
    i hve select query where i display many columns with many conditions from 4 tables. in displaying using 2 column outputs i need to do calculations and on one another and display. so i wrote scalar function. but calling function is not possible to retrive all columns and insert into query.. how to do this .. help me in suggesting..
    chakri

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2006
    Posts
    42
    Site Name Operator Name Total Work Time Total No of Calls Avg Work Time "Total Abandoned
    Time" "Total No of Abandoned
    Calls " "Avg Abandoned
    Time"
    Site 1 Operator 1 0:50:00 10 0:05:00 0:25:00 5 0:05:00
    Operator 2 0:50:00 5 0:10:00 0:30:00 3 0:10:00
    Operator 3 0:51:00 3 0:17:00 0:21:00 3 0:07:00
    Operator 4 0:50:00 4 0:12:30 0:20:00 4 0:05:00
    Operator 5 0:50:00 2 0:25:00 0:30:00 2 0:15:00
    Site 1 Total 4:11:00 24 0:10:00 2:06:00 17 0:07:00
    Site 2 Operator 6 0:50:00 10 0:05:00 0:25:00 5 0:05:00
    Operator 7 0:50:00 5 0:10:00 0:30:00 3 0:10:00
    Operator 8 0:51:00 3 0:17:00 0:21:00 3 0:07:00
    Operator 9 0:50:00 4 0:12:30 0:20:00 4 0:05:00
    Operator 10 0:50:00 2 0:25:00 0:30:00 2 0:15:00
    Site 2 Total 4:11:00 24 0:10:00 2:06:00 17 0:07:00
    Grand Total 8:22:00 48 0:10:00 2:06:00 38 0:07:00

    This is report which i want. this is when input of startdate , endate , siteid and operator id are given corresponding details is the report.
    Last edited by chakri; 02-15-06 at 10:28.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Any function can take multiple input parameters.

    A table-function (not scalar) can return multiple output values and records.

    Sorry, but it is not clear exactly what you want.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2006
    Posts
    42
    This is the query i am writing..
    select rs.site_name, -- as [site_name]
    ro.operator_login_name,-- as [operator_name]
    convert(varchar,rol.login_time- rol.logout_time, 108),-- as [total_work_time]
    count(cl.call_log_id) -- as [total_no_of_calls]
    -- Training.Testuser.GetAvgTime(@l_total_work_time, @l_call_log_id)-- as [avg_work_time]
    /* convert(varchar,cl.call_router_end_time - cl.call_router_arrival_time,108),-- as [total_abandoned_time]
    count(cl.call_status_id) as [total_no_of_abandoned_calls] */
    from ref_operator_log rol, ref_site rs, ref_operator ro, call_log cl, ref_call_status rcs

    where cl.call_status_id = rcs.call_status_id
    and rol.login_time >= '2006-01-19 00:00:00' -- @p_start_date
    and rol.logout_time <= '2006-01-19 23:59:59' -- @p_end_date
    and cl.call_start_time >= '2006-01-19 00:00:00' -- @p_start_date
    and cl.call_end_time <= '2006-01-19 23:59:59' -- @p_end_date
    /* and cl.call_status_id = rcs.call_status_id
    and cl.call_status_id = 3) */
    and rs.site_id = cl.site_id
    and rol.operator_id = ro.operator_id
    and ro.operator_id = cl.operator_id

    group by rs.site_name, ro.operator_login_name, rol.login_time, rol.logout_time/*, cl.call_status_id,
    cl.call_router_arrival_time, cl.call_router_end_time*/


    this is the scalar function,
    create function GetAvgTime
    (
    @tot_time datetime,
    @tot_no_calls int
    )
    returns varchar(8)
    as
    begin

    declare @strHr varchar(2), @strMin varchar(2), @strSec varchar(2), @fltAvgSec int, @strResult varchar(8),
    @intLenHr int, @intLenMin int, @intLenSec int, @intMin int

    select @fltAvgSec=(DATEPART(hh,@tot_time)*3600 + DATEPART(n,@tot_time)*60 + DATEPART(s,@tot_time))/@tot_no_calls

    select @strHr = CONVERT(varchar(2), @fltAvgSec / 3600), @intMin=(@fltAvgSec % 3600) / 60,
    @strMin = CONVERT(varchar(2),@intMin),
    @strSec = CONVERT(varchar(2),(@fltAvgSec % 3600) - @intMin*60)

    select @intLenHr=LEN(@strHr), @intLenMin=LEN(@strMin), @intLenSec=LEN(@strSec)

    set @strResult=REPLICATE('0',2-@intLenHr)+@strHr+':'+REPLICATE('0',2-@intLenMin)+@strMin+':'+REPLICATE('0',2-@intLenSec)+@strSec

    return @strResult
    end

    what i want:
    in query i gave -- comments instead of that this function should be replaced. but function returns only 1 value. my query results many coulmn in the report which i posted.
    and /* */ comments which i gave is for abandoned time, in which same query how to write for total of calls attendend as well as abandoned calls. here abandoned is miss calls. wher attendend condtion and abandoned calls condition is contraversy.. how should i fallow/start

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...so much to fix here...

    Let's start with your SELECT syntax, where you really should be using JOINs rather than WHERE statements. Like this:
    Code:
    from	ref_operator_log rol,
    	inner join ref_operator ro on rol.operator_id = ro.operator_id
    	inner join call_log cl on ro.operator_id = cl.operator_id
    	inner join ref_site on cl.site_id = rs.site_id
    	inner join ref_call_status rcs on cl.call_status_id = rcs.call_status_id 
    where	rol.login_time >= '2006-01-19 00:00:00' -- @p_start_date 
    	and rol.logout_time <= '2006-01-19 23:59:59' -- @p_end_date
    	and cl.call_start_time >= '2006-01-19 00:00:00' -- @p_start_date 
    	and cl.call_end_time <= '2006-01-19 23:59:59' -- @p_end_date 
    	/*
    	and cl.call_status_id = 3
    	*/
    group by rs.site_name,
    	ro.operator_login_name,
    	rol.login_time,
    	rol.logout_time
    	/*
    	,cl.call_status_id,
    	cl.call_router_arrival_time,
    	cl.call_router_end_time
    	*/
    Now on to your function, which looks needlessly complex. Why can't you jsut do something like this?:
    Code:
    create function GetAvgTime 
    	(@tot_time datetime, 
    	@tot_no_calls int) 
    returns datetime
    as
    begin 
    
    return dateadd(minute, datediff(minute, 0, @tot_time)/@tot_no_calls, 0)
    
    end
    ...which is simple enough that you could code it directly in your SELECT statement rather than as a function if you want.

    Now, you seem to be asking why you are not getting different values from the function for each row. Well, you are getting the same values for each row of the result set because you are submitting the same parameter values for each row. @l_total_work_time and @l_call_log_id do not change during the execution of the sql statement. You would need to include some value from a table as a parameter if you want to get different values in your results.

    Clean up your code, simplify, and then lets see where that gets you.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2006
    Posts
    42
    thakq ur help is great and it leaded me to this ..

    CREATE procedure list
    (
    @p_start_date datetime,
    @p_end_date datetime,
    @p_site_list varchar(10),
    @p_operator_list varchar(50)
    )
    as

    begin
    select @p_start_date = case when @p_start_date = ' ' or @p_start_date is null then null
    else @p_start_date
    end
    select @p_end_date = case when @p_end_date = ' ' or @p_end_date is null then null
    else @p_end_date
    end
    if @p_start_date is null or @p_end_date is null
    begin
    /* error: enter the value */
    return
    end
    else

    if exists(select 1 from ref_operator ro, ref_site rs
    where ro.operator_id = @p_operator_list or rs.site_id = @p_site_list )
    begin
    declare @l_site_name varchar(50),
    @l_operator_name varchar(50),
    @l_tot_work_time datetime,
    @l_tot_no_of_calls int,
    @l_avg_work_time varchar(8),
    @l_tot_abondened_time datetime,
    @l_tot_no_of_abondened_calls int,
    @l_avg_abandoned_time varchar(8)

    select @l_site_name = rs.site_name, -- as [site_name]
    @l_operator_name = ro.operator_login_name,-- as [operator_name]
    @l_tot_work_time = convert(varchar,rol.login_time- rol.logout_time, 108),-- as [total_work_time]
    @l_tot_no_of_calls = count(cl.call_log_id), -- as [total_no_of_calls]
    @l_avg_work_time = convert(varchar(8),dateadd(n, datediff(n, 0, @l_tot_work_time)/@l_tot_no_of_calls, 0),108)
    from ref_operator_log rol
    inner join ref_operator ro on rol.operator_id = ro.operator_id
    inner join call_log cl on ro.operator_id = cl.operator_id
    inner join ref_site rs on cl.site_id = rs.site_id
    inner join ref_call_status rcs on cl.call_status_id = rcs.call_status_id
    where rol.login_time >= @p_start_date
    and rol.logout_time <= @p_end_date
    and cl.call_start_time >= @p_start_date
    and cl.call_end_time <= @p_end_date
    and cl.operator_id = @p_operator_list
    and cl.site_id = @p_site_list
    and rol.operator_id = @p_operator_list
    group by rs.site_name,
    ro.operator_login_name,
    rol.login_time,
    rol.logout_time

    /* script for Abandoned calls

    declare @l_site_name varchar(50),
    @l_operator_name varchar(50),
    @l_tot_abondened_time datetime,
    @l_tot_no_of_abondened_calls int,
    @l_avg_abandoned_time varchar(8)*/
    select @l_tot_abondened_time = convert(varchar,cl.call_router_end_time - cl.call_router_arrival_time,108), -- as [total_abandoned_time],
    @l_tot_no_of_abondened_calls = count(cl.call_status_id), --as [total_no_of_abondened_calls]
    @l_avg_abandoned_time = convert(varchar(8),dateadd(n, datediff(n, 0, @l_tot_abondened_time)/@l_tot_no_of_abondened_calls, 0),108)-- as [average_abandoned_calls]
    from call_log cl
    inner join ref_site rs on cl.site_id = rs.site_id
    inner join ref_operator ro on cl.operator_id = ro.operator_id
    inner join ref_operator_log rol on ro.operator_id = rol.operator_id
    inner join ref_call_status rcs on cl.call_status_id = rcs.call_status_id
    where cl.operator_id = @p_operator_list
    and cl.site_id = @p_site_list
    and rol.operator_id= @p_operator_list
    and rol.login_time >= @p_start_date
    and rol.logout_time <= @p_end_date
    and cl.call_router_arrival_time>= @p_start_date
    and cl.call_router_end_time <= @p_end_date
    and lower(upper(rcs.call_status)) = 'abondened'
    group by rs.site_name,
    ro.operator_login_name,
    rol.login_time,
    rol.logout_time,
    cl.Call_router_arrival_time,
    cl.call_router_end_time,
    ro.operator_id

    /* Display the output */
    select @l_site_name, @l_operator_name,@l_tot_work_time, @l_tot_no_of_calls ,@l_avg_work_time,
    @l_tot_abondened_time, @l_tot_no_of_abondened_calls, @l_avg_abandoned_time
    union all
    select /*@l_site_name, @l_operator_name,*/@l_tot_work_time, @l_tot_no_of_calls ,@l_avg_work_time,
    @l_tot_abondened_time, @l_tot_no_of_abondened_calls, @l_avg_abandoned_time


    end
    else
    begin
    /* There are no abandend records */
    return
    end
    end
    GO

    but if u could notice i want as
    Site Name:OperatorName:TotalWorkTime:TotalNoofCalls:Avg WorkTime:Total AbandonedTime:Total No of AbandonedCalls :Avg AbandonedTime
    Site 1 Operator 1 0:50:00 10 0:05:00 0:25:00 5 0:05:00
    Operator 2 0:50:00 5 0:10:00 0:30:00 3 0:10:00
    Operator 3 0:51:00 3 0:17:00 0:21:00 3 0:07:00
    Operator 4 0:50:00 4 0:12:30 0:20:00 4 0:05:00
    Operator 5 0:50:00 2 0:25:00 0:30:00 2 0:15:00
    Site 1 Total 4:11:00 24 0:10:00 2:06:00 17 0:07:00
    Site 2 Operator 6 0:50:00 10 0:05:00 0:25:00 5 0:05:00
    Operator 7 0:50:00 5 0:10:00 0:30:00 3 0:10:00
    Operator 8 0:51:00 3 0:17:00 0:21:00 3 0:07:00
    Operator 9 0:50:00 4 0:12:30 0:20:00 4 0:05:00
    Operator 10 0:50:00 2 0:25:00 0:30:00 2 0:15:00
    Site 2 Total 4:11:00 24 0:10:00 2:06:00 17 0:07:00
    Grand Total 8:22:00 48 0:10:00 2:06:00 38 0:07:00

    seperated columns by :. here there's total after every sitelist whre operators present in the site is available. similarly for site 2 also and atlast grand total.. how come it gets. i tried using compare, but it gets me in differnt way which i dont need it in that way. i am trying using union all but u plz suggest me for this also.. thanq very much..
    -------
    chakri

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    /*
    OK. Some more comments and questions.

    This code:
    Code:
    select @p_start_date = case when @p_start_date = ' ' or @p_start_date is null then null else @p_start_date end
    ...can be more simply written as:
    Code:
    set	@p_start_date = nullif(@p_start_date, ' ')
    ...and likewise for the next line.

    This clause:
    Code:
    if exists
    	(select	*
    	from	ref_operator ro,
    			ref_site rs
    	where	ro.operator_id = @p_operator_list
    			or rs.site_id = @p_site_list )
    ...will evaluate to true if @p_operator_list exists in ref_operator AND @p_site_list exists in ref_site operator. Is that what you want? Using a cross-join for this is kind of an odd method, but I suppose it works. Are there no relationships between ref_operator and ref_site that need to be checked (joined)?

    Rewrite:
    Code:
    and rol.login_time >= @p_start_date
    and rol.logout_time <= @p_end_date
    ...as:
    Code:
    and rol.login_time between @p_start_date and @p_end_date
    ...and do the same with the other daterange comparisons.

    This is completely redundant:
    Code:
    and lower(upper(rcs.call_status)) = 'abondened'
    ...as:
    Code:
    and lower(rcs.call_status) = 'abondened'
    ...is sufficient, and neither may be necessary depending upon your collation setting. Also, check your spelling of "abandoned".

    The FROM clauses for both your statements are logically identical, so use identical syntax for each:
    Code:
    from	ref_operator_log rol
    	inner join ref_operator ro on rol.operator_id = ro.operator_id
    	inner join call_log cl on ro.operator_id = cl.operator_id
    	inner join ref_site rs on cl.site_id = rs.site_id
    	inner join ref_call_status rcs on cl.call_status_id = rcs.call_status_id
    Likewise with your WHERE clauses:
    Code:
    cl.operator_id = @p_operator_list 
    	and cl.site_id = @p_site_list 
    	and rol.operator_id= @p_operator_list
    	and rol.login_time between @p_start_date and @p_end_date
    	and cl.call_router_arrival_time between @p_start_date and @p_end_date
    This code:
    Code:
    			/* Display the output */
    			select	@l_site_name,
    					@l_operator_name,
    					@l_tot_work_time,
    					@l_tot_no_of_calls,
    					@l_avg_work_time,
    					@l_tot_abondened_time,
    					@l_tot_no_of_abondened_calls,
    					@l_avg_abandoned_time
    			union all
    			select	/*@l_site_name,
    					@l_operator_name,*/
    					@l_tot_work_time,
    					@l_tot_no_of_calls,
    					@l_avg_work_time,
    					@l_tot_abondened_time,
    					@l_tot_no_of_abondened_calls,
    					@l_avg_abandoned_time
    ...won't execute at all, since you have commented out two columns, and even if you uncomment them it will only return two identical records. So what is it supposed to be doing?

    Here is your code, cleaned up substantially. If you post again, please use the [ code ] and [ /code ] tags (without spaces) to retain formatting.
    Please post code that parses and executes without error, along with a sample of actual output, and the output you want to get.
    Code:
    CREATE procedure list
    	(@p_start_date datetime,
    	@p_end_date datetime,
    	@p_site_list varchar(10),
    	@p_operator_list varchar(50))
    as 
    
    begin
    	set	@p_start_date = nullif(@p_start_date, ' ')
    	set	@p_end_date = nullif(@p_end_date, ' ')
    
    	if @p_start_date is null or @p_end_date is null 
    		begin
    			/* error: enter the value */
    			return
    		end
    	else if exists
    			(select	*
    			from	ref_operator ro,
    					ref_site rs
    			where	ro.operator_id = @p_operator_list
    					or rs.site_id = @p_site_list )
    		begin
    			declare	@l_site_name varchar(50), 
    					@l_operator_name varchar(50), 
    					@l_tot_work_time datetime, 
    					@l_tot_no_of_calls int,
    					@l_avg_work_time varchar(8), 
    					@l_tot_abondened_time datetime, 
    					@l_tot_no_of_abondened_calls int,
    					@l_avg_abandoned_time varchar(8)
    
    			select	@l_site_name = rs.site_name, -- as [site_name]
    					@l_operator_name = ro.operator_login_name,-- as [operator_name]
    					@l_tot_work_time = convert(varchar,rol.login_time- rol.logout_time, 108),-- as [total_work_time]
    					@l_tot_no_of_calls = count(cl.call_log_id), -- as [total_no_of_calls]
    					@l_avg_work_time = convert(varchar(8),dateadd(n, datediff(n, 0, @l_tot_work_time)/@l_tot_no_of_calls, 0),108)
    			from	ref_operator_log rol
    					inner join ref_operator ro on rol.operator_id = ro.operator_id
    					inner join call_log cl on ro.operator_id = cl.operator_id
    					inner join ref_site rs on cl.site_id = rs.site_id
    					inner join ref_call_status rcs on cl.call_status_id = rcs.call_status_id 
    			where	cl.operator_id = @p_operator_list 
    					and cl.site_id = @p_site_list 
    					and rol.operator_id= @p_operator_list
    					and rol.login_time between @p_start_date and @p_end_date
    					and cl.call_router_arrival_time between @p_start_date and @p_end_date
    			group by rs.site_name,
    					ro.operator_login_name,
    					rol.login_time,
    					rol.logout_time
    
    			/* script for Abandoned calls*/
    			select	@l_tot_abondened_time = convert(varchar,cl.call_router_end_time - cl.call_router_arrival_time,108), -- as [total_abandoned_time],
    					@l_tot_no_of_abondened_calls = count(cl.call_status_id), --as [total_no_of_abondened_calls]
    					@l_avg_abandoned_time = convert(varchar(8),dateadd(n, datediff(n, 0, @l_tot_abondened_time)/@l_tot_no_of_abondened_calls, 0),108)-- as [average_abandoned_calls]
    			from	ref_operator_log rol
    					inner join ref_operator ro on rol.operator_id = ro.operator_id
    					inner join call_log cl on ro.operator_id = cl.operator_id
    					inner join ref_site rs on cl.site_id = rs.site_id
    					inner join ref_call_status rcs on cl.call_status_id = rcs.call_status_id 
    			where	cl.operator_id = @p_operator_list 
    					and cl.site_id = @p_site_list 
    					and rol.operator_id= @p_operator_list
    					and rol.login_time between @p_start_date and @p_end_date
    					and cl.call_router_arrival_time between @p_start_date and @p_end_date
    					and lower(rcs.call_status) = 'abondened'
    			group by rs.site_name, 
    					ro.operator_login_name,
    					rol.login_time,
    					rol.logout_time, 
    					cl.Call_router_arrival_time, 
    					cl.call_router_end_time, 
    					ro.operator_id
    
    			/* Display the output */
    			select	@l_site_name,
    					@l_operator_name,
    					@l_tot_work_time,
    					@l_tot_no_of_calls,
    					@l_avg_work_time,
    					@l_tot_abondened_time,
    					@l_tot_no_of_abondened_calls,
    					@l_avg_abandoned_time
    			union all
    			select	/*@l_site_name,
    					@l_operator_name,*/
    					@l_tot_work_time,
    					@l_tot_no_of_calls,
    					@l_avg_work_time,
    					@l_tot_abondened_time,
    					@l_tot_no_of_abondened_calls,
    					@l_avg_abandoned_time
    		end
    	else
    		begin 
    			/* There are no abandend records */
    			return 
    		end
    end
    GO
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2006
    Posts
    42
    these arre the table
    call_log( call_log_id (PK) identity(1,1) bigint,
    operator_id(FK: ref_operator.operator_id) bigint,
    site_id (FK: ref_site.site_id) smallint,
    call_router_arrival_time,
    call_router_end_time,
    call_start_time,
    call_end_time,
    call_status_id(FK: ref_call_status.call_status_id))

    ref_call_status(call_status_id(PK) identity(1,1),
    call_status varchar(100))

    ref_site(site_id(PK) identity (1,1) smallint,
    site_name varchar(50),
    status_id bit)

    ref_operator(operator_id(PK) identity(1,1) bigint,
    operator_login_name varchar(100),
    operator_pwd varchar(50),
    operator_site_id(FK: ref_site.site_id,
    operator_group_id smallint,
    last_cahnged_id (FK: ref_operator.operator_id) bigint,
    status_id bit)

    Code:
    inputs: start Date------ enddate------- site (combobox,default All) operator (combobox,default All)
    
    siteName operatorname totalworkTime TotNoOfCalls AvgWorkTime TotAbandTime TotNoAbandTime AvgAbandTime
    --------  ----------- ------------ -------------- --------    ----------   -----------    -----------    
    Site1	 operator1	0:50:00		10	0:05:00		0:21:00	     3		  0:07:00		
    	 operator2	0:50:00		4	0:12:30		0:30:00	     3 		  0:10:00
    	 operaotr3	0:51:00		3	0:17:00		0:25:00	     5		  0:05:00	
    Site1Tot		----------  ---------   ----------     ---------  --------      ---------- 
    			xx:xx:xx	17	x:xx:xx		x:xx:xx	     11		  x:xx:xx 
    			----------  ---------   ----------     ---------  --------      ---------- 
    Site2	operator8	0:50:00		10	0:05:00		0:21:00	     3		  0:07:00		
    	operator9	0:50:00		4	0:12:30		0:30:00	     3 		  0:10:00
    Site2Tot 		----------  ---------   ----------     ---------  --------      ---------- 
    			xx:xx:xx	14	x:xx:xx		x:xx:xx	     6		  x:xx:xx 
    			----------  ---------   ----------     ---------  --------      ---------- 		
    GrandTot		xx:xx:xx	31	XX:XX:XX	X:XX:XX	     17		X:XX:XX
    hope u have clear picture now. Thanks for all ur doing..
    chakri

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So is you question on how to get subtotals and grand totals, and hierarchical groupings in your output? This is extremely difficult to do in SQL Server, which is not designed for formatting output. The subtotals, totals, and indented formatting in your example output should be handled by your reporting tool (Crystal, Access, whatever), which should be able to do this easily. That is what they are designed to do.
    What are you ultimately going to use to display your data? Please don't say "Query Analyzer", because Query Analyzer is a development tool, and should not be used for either input or reporting.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2006
    Posts
    42
    hi, i got way to get total and subtotal.. ie using rollup. and its working, but the prob is i cannot implement for this. if u could help me using and combining and generate report using table structure which i posted. it will be a big pleasure .. waiting for your reply..
    chakri

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What tool are you using to generate your reports?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2006
    Posts
    42
    sql server 2000, and is it possible to retrive the data in the above format which i showed . by using rollup its working but if u notice tables i was unable to get the data using single query without using local variables and without using loops. could u help me by generating report. thanks in adv
    -----
    chakri
    Last edited by chakri; 02-21-06 at 11:28.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    As I stated above, SQL Server is not a reporting tool, and neither is Query Analyzer. The type of formatting you want to do needs to be done using a reporting tool such as Active Reports, Crystal Reports, MS Access, or even an MS Excel pivot table. To do this in SQL Server would be needlessly complex and ill-advised.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Feb 2006
    Posts
    42
    sorry to repeat again, my requirement is to perform as been assigned. i understand what u say, but these type of queries i even got and performed using rollup in sql server 2000. sample i give u
    Code:
    	select case when cl.originating_user is not null and rcsr.call_status_reason is null then 'Grand Total' 
                         when cl.originating_user is null then 'Sub Total' 
                         else cl.originating_user
                         end as originating_user, 
                   case when cl.originating_user is null and rcsr.call_status_reason is null then ''
                         when rcsr.call_status_reason is null then ''
                         else rcsr.call_status_reason
                         end as status_reason ,
                   count(cl.call_log_id) as 'no_of_calls' 
    		from call_log cl, 
    			ref_call_status_reason rcsr,ref_call_status rcs
    		where rcsr.call_status_id = cl.call_status_id 
    		and call_start_time between @p_start_date and @p_end_date
    		group by cl.originating_user,rcsr.call_status_reason
    		with rollup
    so i need to do for the one i am looking for..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •