Results 1 to 1 of 1
  1. #1
    Join Date
    Aug 2009
    Posts
    262

    Unanswered: Urgent Help needed with this master procedure

    I have been working at oracle but now from past week i needed to work on SQL-server 2005. I am totally new to this free-style procedure coding.

    I have 4 colums to work against , and generate a report of overall progress of a month or any number of days and frequently dialed numbers . as well as daily frequent numbers .
    here is my procedure

    ALTER proc mainproc_bcm_master]
    as
    set nocount on
    begin
    create table #tempdcrd (
    RID INT IDENTITY ( 1,1 ),
    stn int NULL,
    Calltime datetime NULL,
    phone varchar(50),
    duration numeric (18,4) NULL,
    endtime AS (CONVERT([datetime],dateadd(S,[duration],[calltime]),(0))),
    rdate AS (CONVERT([varchar](12),[calltime],101)),
    [lessmin1] AS (case when [duration]<(60) then (1) else (0) end),
    [min1to3] AS (case when [duration]>=(60) AND [duration]<=(180) then (1) else (0) end),
    [min3] AS (case when [duration]>(180) then (1) else (0) end))

    insert into #tempdcrd (stn,calltime,phone,duration) select cast(line as int),
    calltime,right(left(phone,11),10),cast(seconds as numeric (18,4))
    from BCM_FileData ORDER BY STN,CALLTIME

    ----------- start Procedure

    select
    STN ,
    RDATE AS REPORT_DATE,
    COUNT(distinct(rdate)) as REPORT_DAYS,
    convert(varchar, min(calltime),108) starttime,
    convert(varchar,max(endtime),108) endtime,
    count(calltime) NO_OF_CALLS,
    '8-HOURS or 640-MIN' AS WORKING_HOURS,
    (SUM(DURATION)) as TOTAL_CALLTIME,
    ((sum(duration))/28800)*100 as AVGWORKING_RATIO ,
    ((28800-(sum(duration)))/28800)*100 as AVGIDLE_RATIO ,
    (sum(duration))/(count(calltime)) as AVGCALL_DURATION,
    sum(lessmin1) as total_lessmin1_Call,
    sum(min1to3) as total_1to3min_call,
    sum(min3) as total_longcall
    into #tempdcrd_1
    from #tempdcrd
    group by stn,rdate
    order by stn

    -- create temptable to hold values of time interval of all calls



    --Area 1
    SELECT STN,rdate,CASE WHEN
    DATEDIFF (S,
    (SELECT ENDTIME FROM #tempdcrd DD WHERE D.RID=DD.RID AND D.STN=DD.STN and D.RDATE=DD.RDATE ),
    (SELECT top(1)CALLTIME FROM #tempdcrd DD WHERE D.STN=DD.STN AND DD.RID>D.RID AND DD.RDATE>D.RDATE )) >=0
    THEN DATEDIFF (S,
    (SELECT ENDTIME FROM #tempdcrd DD WHERE D.RID=DD.RID AND D.STN=DD.STN and D.RDATE=DD.RDATE ),
    (SELECT top(1) CALLTIME FROM #tempdcrd DD WHERE D.STN=DD.STN AND DD.RID>D.RID AND DD.RDATE>D.RDATE))
    WHEN DATEDIFF (S,
    (SELECT ENDTIME FROM #tempdcrd DD WHERE D.RID=DD.RID AND D.STN=DD.STN and D.RDATE=DD.RDATE),
    (SELECT TOP(1) CALLTIME FROM #tempdcrd DD WHERE D.STN=DD.STN AND DD.RID>D.RID AND DD.RDATE>D.RDATE)) IS NULL
    THEN SUM(D.DURATION)
    ELSE DATEDIFF (S,
    (SELECT ENDTIME FROM #tempdcrd DD WHERE D.RID=DD.RID AND D.STN=DD.STN ),
    (SELECT TOP(1) CALLTIME FROM #tempdcrd DD WHERE D.STN=DD.STN AND DD.RID=D.RID ))

    END AS Call_interval
    INTO #TEMPDCRD_2
    FROM #TEMPDCRD D
    GROUP BY STN,RDATE,rid
    order by 1



    Area 1-1
    -- creating temp table for calculated interval of calls group by stn (or extention)

    select stn,(sum(call_interval)/60) as Tcall_intervall
    into #tempintervall_t
    from #tempdcrd_2
    group by stn order by 1

    select stn,rdate,(sum(call_interval)/60) as Dcall_intervall
    into #tempintervall_d
    from #tempdcrd_2
    group by stn,rdate order by stn


    -- creating temp table to hold values for the monthly ratios , all ratios have been driven against
    -- sum (total day) againt total working days (i.e 24 days)


    select stn,
    SUM(REPORT_DAYS) AS NOOFDAYS,
    sum(NO_OF_CALLS) as calling_gtotal ,
    max(REPORT_DATE) as End_RDATE,
    ((sum(total_calltime)*(SUM(REPORT_DAYS)))/(28800*(SUM(REPORT_DAYS))))*100 as working_gratio,
    ((28800-(sum(total_Calltime)))/(28800*(SUM(REPORT_DAYS))))*100 as idle_gratio,
    sum(AVGWORKING_RATIO)/count(report_days) as avgcall_gratio,
    sum(total_calltime) as totalg_call_duration
    into #tempdcrd_G
    from #tempdcrd_1 group by stn


    -- creating a main physical table for crystal report,( required)
    select
    a.stn as STN ,
    a.report_date,
    a.starttime,
    a.endtime,
    a.no_of_calls,
    a.working_hours,
    a.total_calltime,
    a. AVGWORKING_RATIO,
    a.AVGIDLE_RATIO,
    a.AVGCALL_DURATION,
    a.total_lessmin1_Call,
    a.total_1to3min_call,
    a.total_longcall,
    B.End_RDATE,
    B.NOOFDAYS,
    b.calling_gtotal,
    b.totalg_call_duration,
    c.Tcall_intervall,
    d.Dcall_intervall,
    b.working_gratio,
    b.idle_gratio,
    b.avgcall_gratio
    into #dcrd_masterx
    from #tempdcrd_1 a, #tempdcrd_G b ,#tempintervall_t c ,#tempintervall_d d
    where a.stn=b.stn and b.stn=c.stn and c.stn=d.stn
    and a.report_date=d.rdate


    select
    stn as STN ,
    report_date,
    starttime,
    endtime,
    no_of_calls,
    working_hours,

    CONVERT(varchar(6), cast(total_calltime as int)/3600)
    + ':' + RIGHT('0' + CONVERT(varchar(2), (cast(total_calltime as int) % 3600) / 60), 2)
    + ':' + RIGHT('0' + CONVERT(varchar(2), cast(total_calltime as int) % 60), 2) as total_calltime,

    AVGWORKING_RATIO,
    AVGIDLE_RATIO,

    CONVERT(varchar(6), cast(AVGCALL_DURATION as int)/3600)
    + ':' + RIGHT('0' + CONVERT(varchar(2), (cast(AVGCALL_DURATION as int) % 3600) / 60), 2)
    + ':' + RIGHT('0' + CONVERT(varchar(2), cast(AVGCALL_DURATION as int) % 60), 2) as AVGCALL_DURATION,
    total_lessmin1_Call,

    total_1to3min_call,
    total_longcall,
    End_RDATE,
    NOOFDAYS,
    calling_gtotal,

    CONVERT(varchar(6), cast(totalg_call_duration as int)/3600)
    + ':' + RIGHT('0' + CONVERT(varchar(2), (cast(totalg_call_duration as int) % 3600) / 60), 2)
    + ':' + RIGHT('0' + CONVERT(varchar(2), cast(totalg_call_duration as int) % 60), 2) as totalg_call_duration,

    CONVERT(varchar(6), cast(Tcall_intervall as int)/3600)
    + ':' + RIGHT('0' + CONVERT(varchar(2), (cast(Tcall_intervall as int) % 3600) / 60), 2)
    + ':' + RIGHT('0' + CONVERT(varchar(2), cast(Tcall_intervall as int) % 60), 2) as Tcall_intervall,

    CONVERT(varchar(6), cast(Dcall_intervall as int)/3600)
    + ':' + RIGHT('0' + CONVERT(varchar(2), (cast(Dcall_intervall as int) % 3600) / 60), 2)
    + ':' + RIGHT('0' + CONVERT(varchar(2), cast(Dcall_intervall as int) % 60), 2) as Dcall_intervall,

    working_gratio,
    idle_gratio,
    avgcall_gratio
    into dcrd_master
    from #dcrd_masterx


    -- Drop temporary tables , clearing up tempdb. disk space freed up
    drop table #dcrd_masterx
    drop table #tempdcrd_1
    DROP table #TEMPDCRD_2
    drop table #tempintervall_d
    drop table #tempintervall_t
    drop table #tempdcrd_g


    -- Frequent daily number


    --Area2
    select stn,

    CONVERT(varchar(6), cast(max( duration) as int)/3600)
    + ':' + RIGHT('0' + CONVERT(varchar(2), (cast(max( duration) as int) % 3600) / 60), 2)
    + ':' + RIGHT('0' + CONVERT(varchar(2), cast(max( duration) as int) % 60), 2) as daily_f_dur ,
    max(phone)as daily_f_phone ,
    rdate
    into #daily_freq
    from #tempdcrd
    where rdate=rdate
    group by stn,rdate
    order by stn,rdate


    --- frequent number Overall


    --Area 3
    select cast(stn as int) as stn,phone,count(phone) as no_ofcalls,sum(Cast(duration as numeric)) as total_dur,
    max(duration) as maximum_call
    into #frequents
    from #tempdcrd
    group by phone,stn
    order by 3

    --select calculated result for frequent number
    select stn, case when ( select max(phone) from #frequents ff where ff.stn=f.stn and no_ofcalls>1) is null
    then 'No Number'
    else ( select max(phone) from #frequents ff where ff.stn=f.stn and no_ofcalls>1)
    end as frequent_no,
    case when (select max(total_dur) from #frequents ff where ff.stn=f.stn and no_ofcalls>1) is null
    then 0
    else (select max(total_dur) from #frequents ff where ff.stn=f.stn and no_ofcalls>1)
    end as f_total_dur,
    case when (select max(no_ofcalls) from #frequents ff where ff.stn=f.stn) is null
    then 0
    when (select max(no_ofcalls) from #frequents ff where ff.stn=f.stn) =1
    then 0
    else (select max(no_ofcalls) from #frequents ff where ff.stn=f.stn)
    end as timesdialed
    into #frequent_numx
    from #frequents f
    group by stn
    order by stn
    drop table #frequents

    select
    stn,
    frequent_no,
    CONVERT(varchar(6), cast(f_total_dur as int)/3600)
    + ':' + RIGHT('0' + CONVERT(varchar(2), (cast(f_total_dur as int) % 3600) / 60), 2)
    + ':' + RIGHT('0' + CONVERT(varchar(2), cast(f_total_dur as int) % 60), 2) as f_total_dur,
    timesdialed
    into #overall_freq
    from #frequent_numx

    drop table #frequent_numx

    --- end of frequent number overall
    select a.stn,case when (a.frequent_no) is null
    then 'none'
    else a.frequent_no
    end as frequent_no
    ,case when a.f_total_dur is null
    then '0'
    else a.f_total_dur
    end as f_total_dur
    ,case when a.timesdialed <>0
    then a.timesdialed
    else 0
    end as timesdialed
    , b.daily_f_phone, b.daily_f_dur,b.rdate
    into frequent_nums
    from #overall_freq a,#daily_freq b
    where a.stn=b.stn



    --- select statement for crystal report output
    select * from dcrd_master,frequent_nums
    where dcrd_master.stn=frequent_nums.stn
    and dcrd_master.report_date=frequent_nums.rdate
    -- drop physical table at the end of use- sessions terminated
    drop table #tempdcrd
    drop table dcrd_master
    drop table frequent_nums
    drop table #daily_freq
    end --mainproc_bcm_master


    The Bold fileds are where i am having problem as they are not returning correct values.

    Area1 , Yesterday this area was taking 1 sec and today it is taking 54 sec to exec .
    Area 1-1 calling intervell is wrong .

    what i want from Area 1 and 1-1 is to take endtime from 1st row and start time of 2nd row. calculate the difference and return the value .
    and since there are multiple dates so it should do the calculations group by STN ( as userid) and rdate( per day )

    note: all calculations are in seconds.


    Area2 Is returning the frequent number correctly but not its correct duration . Insteal it is returning the over all duration of the day .

    in area 3 the Duration is correct but it is returning the over all frequent number instead of the frequent number of the day .


    Kindly put your best efforts into this as this tasks seems impossible to me.

    Regards
    Last edited by mishaalsy; 08-12-09 at 02:36.

Posting Permissions

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