SSRS Time Calculations/Query

• scotsditch

  Ten Centuries

  Points: 1020

  More actions

  December 1, 2013 at 2:07 am

  #283043

  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',

• Jeff Moden

  SSC Guru

  Points: 1004441

  More actions

  December 1, 2013 at 3:52 pm

  #1669962

  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)