Counting records between dates
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22662
Printed Date: 29 Apr 2024 at 4:57am
Topic: Counting records between dates
Posted By: barry.ein
Subject: Counting records between dates
Date Posted: 22 Aug 2018 at 3:35am
Hi all,
I am creating a chart where the "On Change Of" is set to a command that gives me all dates between a few month period (the data is shown for each month).
For the show values I want to count how many records where opened between the first date of the command and each specific end of month.
Thanks,
Barry
|
Replies:
Posted By: DBlank
Date Posted: 22 Aug 2018 at 10:39am
On change of use the date field
in Order option set it to 'for each month'
Show values, set a count (or distinct count) of a primary key field in your data set
|
Posted By: barry.ein
Date Posted: 22 Aug 2018 at 10:36pm
I created a command that is used as the date field for "On Change Of" and changed it to show data for each month:
WITH barry AS
(SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 11, 0) DateValue
UNION ALL
SELECT DateValue + 1 FROM barry WHERE DateValue + 1 < (SELECT CURRENT_TIMESTAMP))
SELECT DateValue FROM barry OPTION (MAXRECURSION 0)
======================================================
Then I created 11 formulas as below for each month (in each formula the month number is 1-11:
if {PR.date_opened} > minimum({Command.DateValue})
and {PR.date_opened} < dateadd("m",1,DateSerial(year(minimum({Command.DateValue})),month(minimum({Command.DateValue})),1)-1)
then 1 else 0
==================================================
The problems are:
1.How can I combine the 11 formulas into one so I can
put is in the chart's Show Value(s) area?
2.I can't summarize the formulas i created
|
|