none
monthly data fetch from the database and make report on excel through c-sharp

    Question

  • |I have the following query which shows the report for the date assigned to it means it give the daily records.Now i want to fetch the monthly records and by reporting template in VS (c sharp) I want to make the report on excel which fill the cells for one month.Please help me i am wasting too much time for doing it also i am new to this.

    here is my query,

    DECLARE @ReportDate AS DATETIME
    DECLARE @ReportStartDate AS DATETIME
    SET @ReportDate = CONVERT(VARCHAR,'2013-02-28',101)
    SET @ReportStartDate = '2013-02-01'
    DECLARE @CampaignID AS CHAR(3)
    SET @CampaignID= 'EQH'

    Declare @StartCalldateUNIX AS INTEGER
    Declare @EndCalldateUNIX AS INTEGER
    SET @StartCalldateUNIX = CONVERT(VARCHAR, DATEDIFF(s,'1970-01-01',@ReportDate)) --+ (CASE WHEN @StartDate between '2012-11-4' and '2013-02-14' THEN 18000 ELSE 14400 END)
    SET @EndCalldateUNIX = CONVERT(VARCHAR, DATEDIFF(s,'1970-01-01',@ReportDate) ) + 86399 --+(CASE WHEN @StartDate between '2012-11-4' and '2013-02-14' THEN 18000 ELSE 14400 END)

    --select @StartCalldateUNIX 
    --select @EndCalldateUNIX

    Select 
    (Select Sum(logintime) from trginbound..rptlogins_inbound (Nolock) where campaignid = 'EQH' and rptdate = @ReportDate and loginend <> '0')/3600 [Total Hours],
    sum (case when verb = 'ENTERQUEUE' then 1 else 0 end )[Total Calls / NCO],
    sum (case when verb = 'EXITWITHKEY' then 1 else 0 end )[Voice Mail],
    sum (case when verb = 'EXITWITHTIMEOUT' then 1 else 0 end )[Timeout],
    sum (case when verb = 'CONNECT' then 1 else 0 end )[Calls Handled],
    sum(case when (verb = 'ABANDON' ) then 1 else 0 end) [Abondand Calls],
    3 [ServiceLevel],
    4 [Ave.HoldTime],
    5 [AHT],
    6 [ASA]


    from trginbound.asterisk.queue_log (Nolock)
    where [TIME_ID] BETWEEN @StartCalldateUNIX and @EndCalldateUNIX and [queue] = 'EquinixHD' 

    this query is for daily and i want the monthly record plz help me..

    Thursday, March 21, 2013 12:34 PM

Answers

  • Hello,

    You wrote your report is for daily. But I could not see anything about that. There is only 3600 I think it means 60*60 seconds for one hour.

    For converting your data to Excel, etc, I'll suggest you to use SQL Server Reporting Services.

    Create a stored procedure for data source of your report, and set the output type to Excel.

    Then you can even email the reports automatically to a list of recipients. If you have the chance to use SSRS, try to use it.


    SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

    Thursday, March 21, 2013 12:52 PM