SSRS Time Calculations/Query

  • Hi I'm working on a report in SSRS. I have a dataset that uses query 1 below and works fine. The problem I run into is when I expand the where clause, as is shown in the 2nd query, SSRS stops displaying the time calculation for ATA correctly. I've tried using combinations of "case when" and isnull in the denominator of the ATA calculation to deal with dividing by 0 or empty data, but the calculation continues to be incorrect in SSRS for the 2nd query. Any tips would be greatly appreciated. I've included sample data below. The correct ATA value for Query 2, for Oct 13 should be 01:24.

    -----Query 1-------

    select

    a.[MonthID],a.[VDN], g.[QVDN],q.[Row],q.[Month],q.[FK_VDN],

    cast(convert(datetime,(sum(cast(convert(datetime,g.[Avg Abandoned Time]) as float)*convert(int,g. [Abandoned Calls])))

    /sum(convert(int,g.[Abandoned Calls]))) as time(0)) as "ATA"

    from [VisaNA_CallVol].[dbo].[SigTableHeaders] as q

    Full Outer join

    [VisaNA_CallVol].[dbo].[VisaNAProdMonthID2] as g

    on q.[MonthID] = g.[MonthID] and q.[FK_VDN] = cast(g.[QVDN] as float)

    Full Outer join

    [VisaNA_CallVol].[dbo].[VisaMonthCallVol] as a

    on q.[FK_VDN]=a.[VDN] and q.[MonthID]=a.[MonthID]

    WHERE q.[FK_VDN] IN ('2014572','2014552','2014580','2014635','2014607','2014608')

    Group By a.[MonthID],a.[VDN], g.[QVDN],q.[Row],q.[Month],q.[FK_VDN]

    ----------Query 2 Expanded Where Clause-------

    select

    a.[MonthID],a.[VDN], g.[QVDN],q.[Row],q.[Month],q.[FK_VDN],

    cast(convert(datetime,(sum(cast(convert(datetime,g.[Avg Abandoned Time]) as float)*convert(int,g.[Abandoned Calls])))

    /sum(convert(int,g.[Abandoned Calls]))) as time(0)) as "ATA"

    from [VisaNA_CallVol].[dbo].[SigTableHeaders] as q

    Full Outer join

    [VisaNA_CallVol].[dbo].[VisaNAProdMonthID2] as g

    on q.[MonthID] = g.[MonthID] and q.[FK_VDN] = cast(g.[QVDN] as float)

    Full Outer join

    [VisaNA_CallVol].[dbo].[VisaMonthCallVol] as a

    on q.[FK_VDN]=a.[VDN] and q.[MonthID]=a.[MonthID]

    WHERE q.[FK_VDN] IN ('2014579','2014568','2014571','2014566','2014551','2014600','2014633','2014634','2014572','2014552','2014580','2014635','2014607','2014608','2014583','2014575','2014555','2014585','2014577','2014557','2014611','2014586','2014578','2014558','2014613','2014584','2014576','2014556','2014581','2014573','2014553','2014609','2014640','2014641','2014582','2014574','2014554')

    Group By a.[MonthID],a.[VDN], g.[QVDN],q.[Row],q.[Month],q.[FK_VDN]

    -------SigTableHeaders ddl------

    CREATE TABLE [dbo].[SigTableHeaders](

    [Row] [nvarchar](255) NULL,

    [Month] [float] NULL,

    [MonthID] [float] NULL,

    [FK_VDN] [float] NULL

    ) ON [PRIMARY]

    --------SigTableHeaders Sample Data-------

    SELECT'Calls Offered to VDN','41548','1','2.01457e+006',UNION ALL

    SELECT'Calls Offered to VDN','41548','1','2.01457e+006',UNION ALL

    SELECT'Calls Offered to VDN','41548','1','2.01455e+006',UNION ALL

    SELECT'Calls Offered to VDN','41548','1','2.01457e+006',UNION ALL

    SELECT'Calls Offered to VDN','41548','1','2.01455e+006',UNION ALL

    SELECT'Calls Offered to VDN','41548','1','2.01457e+006',UNION ALL

    SELECT'Calls Offered to VDN','41548','1','2.01455e+006',UNION ALL

    SELECT'Calls Offered to VDN','41548','1','2.01457e+006',UNION ALL

    SELECT'Calls Offered to VDN','41548','1','2.01455e+006',UNION ALL

    SELECT'Calls Offered to VDN','41548','1','2.01458e+006',UNION ALL

    SELECT'Calls Offered to VDN','41548','1','2.01456e+006',UNION ALL

    SELECT'Calls Offered to VDN','41548','1','2.01458e+006',UNION ALL

    SELECT'Calls Offered to VDN','41548','1','2.01456e+006',UNION ALL

    SELECT'Calls Offered to VDN','41548','1','2.01458e+006',UNION ALL

    SELECT'Calls Offered to VDN','41548','1','2.01456e+006',UNION ALL

    SELECT'Calls Offered to VDN','41548','1','2.01458e+006',UNION ALL

    SELECT'Calls Offered to VDN','41548','1','2.01456e+006',UNION ALL

    SELECT'All Calls Offered to VDN','41548','1','2.01457e+006',UNION ALL

    SELECT'All Calls Offered to VDN','41548','1','2.01457e+006',UNION ALL

    SELECT'All Calls Offered to VDN','41548','1','2.01455e+006',UNION ALL

    SELECT'All Calls Offered to VDN','41548','1','2.01457e+006',UNION ALL

    SELECT'All Calls Offered to VDN','41548','1','2.01455e+006',UNION ALL

    SELECT'All Calls Offered to VDN','41548','1','2.01457e+006',UNION ALL

    SELECT'All Calls Offered to VDN','41548','1','2.01455e+006',UNION ALL

    SELECT'All Calls Offered to VDN','41548','1','2.01457e+006',UNION ALL

    SELECT'All Calls Offered to VDN','41548','1','2.01455e+006',UNION ALL

    SELECT'All Calls Offered to VDN','41548','1','2.01458e+006',UNION ALL

    SELECT'All Calls Offered to VDN','41548','1','2.01456e+006',UNION ALL

    SELECT'All Calls Offered to VDN','41548','1','2.01458e+006',UNION ALL

    SELECT'All Calls Offered to VDN','41548','1','2.01456e+006',UNION ALL

    SELECT'All Calls Offered to VDN','41548','1','2.01458e+006',UNION ALL

    SELECT'All Calls Offered to VDN','41548','1','2.01456e+006',UNION ALL

    SELECT'All Calls Offered to VDN','41548','1','2.01458e+006',UNION ALL

    SELECT'All Calls Offered to VDN','41548','1','2.01456e+006',UNION ALL

    SELECT'Calls Offered to VDN 800-953-7392','41548','1','2.01457e+006',UNION ALL

    SELECT'Calls Offered to VDN 888-877-1021','41548','1','2.01457e+006',UNION ALL

    SELECT'Calls Offered to VDN 630-350-4551','41548','1','2.01455e+006',UNION ALL

    SELECT'Calls Offered to VDN 800-831-2651','41548','1','2.01457e+006',UNION ALL

    SELECT'Calls Offered to VDN 630-350-4552','41548','1','2.01455e+006',UNION ALL

    SELECT'Calls Offered to VDN 888-427-5056','41548','1','2.01457e+006',UNION ALL

    SELECT'Calls Offered to VDN 630-350-4553','41548','1','2.01455e+006',UNION ALL

    SELECT'Calls Offered to VDN 877-257-8087','41548','1','2.01457e+006',UNION ALL

    SELECT'Calls Offered to VDN 630-350-4554','41548','1','2.01455e+006',UNION ALL

    SELECT'Calls Offered to VDN 877-252-4268','41548','1','2.01458e+006',UNION ALL

    SELECT'Calls Offered to VDN 630-350-4555','41548','1','2.01456e+006',UNION ALL

    SELECT'Calls Offered to VDN 877-252-4279','41548','1','2.01458e+006',UNION ALL

    SELECT'Calls Offered to VDN 630-350-4556','41548','1','2.01456e+006',UNION ALL

    SELECT'Calls Offered to VDN 800-990-9601','41548','1','2.01458e+006',UNION ALL

    SELECT'Calls Offered to VDN 630-350-4557','41548','1','2.01456e+006',UNION ALL

    SELECT'Calls Offered to VDN 866-807-3981','41548','1','2.01458e+006',UNION ALL

    SELECT'Calls Offered to VDN 630-350-4558','41548','1','2.01456e+006',UNION ALL

    SELECT'Hangups During Prompter','41548','1','2.01457e+006',UNION ALL

    SELECT'Hangups During Prompter','41548','1','2.01457e+006',UNION ALL

    SELECT'Hangups During Prompter','41548','1','2.01455e+006',UNION ALL

    SELECT'Hangups During Prompter','41548','1','2.01457e+006',UNION ALL

    SELECT'Hangups During Prompter','41548','1','2.01455e+006',UNION ALL

    SELECT'Hangups During Prompter','41548','1','2.01457e+006',UNION ALL

    SELECT'Hangups During Prompter','41548','1','2.01455e+006',UNION ALL

    SELECT'Hangups During Prompter','41548','1','2.01457e+006',UNION ALL

    SELECT'Hangups During Prompter','41548','1','2.01455e+006',UNION ALL

    SELECT'Hangups During Prompter','41548','1','2.01458e+006',UNION ALL

    SELECT'Hangups During Prompter','41548','1','2.01456e+006',UNION ALL

    SELECT'Hangups During Prompter','41548','1','2.01458e+006',

    -------VisaNAProdMonthID2 ddl---------

    CREATE TABLE [dbo].[VisaNAProdMonthID2](

    [MonthID] [varchar](50) NULL

    [QVDN] [varchar](50) NULL,

    [Abandoned Calls] [varchar](50) NULL,

    [Avg Abandoned Time] [varchar](50) NULL,

    ) ON [PRIMARY]

    ------VisaNAProdMonthID2 Sample Data------

    SELECT'1','','183','0:01:22',UNION ALL

    SELECT'1','','28','0:01:10',UNION ALL

    SELECT'1','2014587','28','0:01:10',UNION ALL

    SELECT'1','','3','0:00:04',UNION ALL

    SELECT'1','2014603','3','0:00:04',UNION ALL

    SELECT'1','','3','0:01:08',UNION ALL

    SELECT'1','2014594','3','0:01:08',UNION ALL

    SELECT'1','','141','0:01:24',UNION ALL

    SELECT'1','2014579','134','0:01:26',UNION ALL

    SELECT'1','2014580','6','0:01:02',UNION ALL

    SELECT'1','2014581','1','0:00:02',UNION ALL

    SELECT'1','2014585','0','0:00:00',UNION ALL

    SELECT'1','2014586','0','0:00:00',UNION ALL

    SELECT'1','','8','0:01:55',UNION ALL

    SELECT'1','2018133','4','0:01:06',UNION ALL

    SELECT'1','2018134','4','0:02:44',UNION ALL

    SELECT'1','','0','0:00:00',UNION ALL

    SELECT'1','2014649','0','0:00:00',UNION ALL

    SELECT'1','','0','0:00:00',UNION ALL

    SELECT'1','2018185','0','0:00:00',

    ------VisaMonthCallVol ddl----

    CREATE TABLE [dbo].[VisaMonthCallVol](

    [MonthID] [varchar](50) NULL,

    [VDN] [varchar](50) NULL,

    ) ON [PRIMARY]

    ------VisaMonthCallVol Sample Data--------

    SELECT'1','2014551',UNION ALL

    SELECT'1','2014566',UNION ALL

    SELECT'1','2014571',UNION ALL

    SELECT'1','2014572',UNION ALL

    SELECT'1','2014573',UNION ALL

    SELECT'1','2014607',UNION ALL

    SELECT'1','2014608',UNION ALL

    SELECT'1','2014609',UNION ALL

    SELECT'1','2014633',UNION ALL

    SELECT'1','2014634',UNION ALL

    SELECT'1','2014635',UNION ALL

    SELECT'1','2014557',UNION ALL

    SELECT'1','2014558',UNION ALL

    SELECT'1','2014574',UNION ALL

    SELECT'1','2014575',UNION ALL

    SELECT'1','2014576',UNION ALL

    SELECT'1','2014577',UNION ALL

    SELECT'1','2014578',UNION ALL

    SELECT'1','2014613',UNION ALL

    SELECT'1','2014640',UNION ALL

    SELECT'1','2014641',

  • Duplicate post. To keep from splitting up answers, please don't post replies here. Please see the same post at the following URL. Thanks.

    http://www.sqlservercentral.com/Forums/Topic1518687-2799-1.aspx

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply