| Author |
Message |
Kevinbwdsb
Newbie
Joined: 02 Mar 2016
Online Status: Offline
Posts: 10
|
   Topic: Group and Sum using date ranges
Posted: 10 Feb 2017 at 9:31am |
|
Hi I am in need of a way to count the number of received vs closed calls in a specific date range. When I group on the received date and then add a summary the number is right for received but when I add a summary for the number of closed calls - the number is always equal to or very close to those received - running a SQL script the numbers or closed is completely wrong. I tried grouping on closed as well but then grouping gets messed up for closed. I also need to add a total for remaining Open that includes the weeks prior. I can run three separate reports and get the right numbers, but when I try to combine the formulas and get it all on one report every total is wrong. I've tried a cross tab but it is ugly and the totals are still wrong, running totals don't work in headers and putting everything in the footer didn't seem to work. I have to limited the calls to between the range (parameter) of beginning to End for Rec'd and Closed but remaining open can include calls prior to the beginning date if that makes sense.
Any help would be appreciated...
I want it to look like:
February Received Closed Open
Week Beginning Feb 5 150 129 75
Feb 12 100 105 70
Feb 19 160 130 100
etc
March
Week Beginning March 5 Etc Etc
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 13 Feb 2017 at 2:52am |
|
what does your data set look like?
|
|
IP Logged |
|
Kevinbwdsb
Newbie
Joined: 02 Mar 2016
Online Status: Offline
Posts: 10
|
Posted: 14 Feb 2017 at 5:24am |
|
Hi DBlank,
My data set is in SQL - what exactly do you need to know - I am using HEAT Call logging to collect the data
There are fields for Date Received, Date Closed etc. Dates are stored as 'yyyy-mm-dd' and I am using formulas to ensure the first day of the week is Monday not Sunday and then converting to text.
Thanks
Kevin
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 Feb 2017 at 3:01am |
|
Is your data set one row with multiple date columns, like RecievedDate, OpenDate, CloseDate
or is it one row with one date field and another field that identifies what the date is for?
|
|
IP Logged |
|
Kevinbwdsb
Newbie
Joined: 02 Mar 2016
Online Status: Offline
Posts: 10
|
Posted: 15 Feb 2017 at 3:31am |
|
Hi DBlank,
Data set is row with multiple date columns.
Thanks
Kevin
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 Feb 2017 at 3:44am |
|
The issue is each row gets grouped into one group and the remaining dates may need to be another group. Can you write a stored proc or the like to convert this into the data set that wold support your output need?
Basically you can do 3 select statements and union them together, somehting like:
select pkid, ReceivedDate as [ReportDate], 'Received' as [ReportType]
from table where ReceivedDate is not null
UNION
select pkid, OpenDate as [ReportDate], 'Opened' as [ReportType]
from table where OpenDate is not null
UNION
select pkid, CloseDate as [ReportDate], 'Closed' as [ReportType]
from table where CloseDate is not null
Thsi data set allows you to group on the singular Date field and then do counts (crosstab column group) by the 'ReportType' field.
|
|
IP Logged |
|
Kevinbwdsb
Newbie
Joined: 02 Mar 2016
Online Status: Offline
Posts: 10
|
Posted: 15 Feb 2017 at 5:14am |
|
Hi DBlank,
I will talk to my DBA about doing this.
Thanks
Kevin
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 Feb 2017 at 5:45am |
|
Note you can also use a Crystal Command to do this inside Crystal if needed. I would also make sure this approach, or the selected fields, support all your report requirements.I was only focused on what you posted about.
Edited by DBlank - 15 Feb 2017 at 5:45am
|
|
IP Logged |
|
|
|
