Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Sum Months Question Post Reply Post New Topic
Author Message
SaxmanTC
Newbie
Newbie
Avatar

Joined: 10 Sep 2010
Location: United States
Online Status: Offline
Posts: 7
Quote SaxmanTC Replybullet Topic: Sum Months Question
     Posted: 13 Sep 2010 at 4:17am
I have two columns that I need to count the number of months a client is active in our program.
 
From the Data I have:
 
Column C: AdmDate (yyyy,mm,dd)
Column D: DscDate (yyyy,mm,dd)
Column E: Total Months in Program
 
(Example: 2009-11-01 to 2010-05-01 would equal a sum of 7 months)
 
One additional requirement is a null value. If the AdmDate = DscDate return a 0 result in the column.
 
These dates are strings and not dates, so I'm thinking that I might need to convert the string to date first before doing this?
 
Any suggestions?
 


Edited by SaxmanTC - 13 Sep 2010 at 4:36am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 13 Sep 2010 at 5:14am
try
datediff('m',date(table.admdate),(if isnull(table.DscDate) then currentdate else date(table.DscDate))
IP IP Logged
SaxmanTC
Newbie
Newbie
Avatar

Joined: 10 Sep 2010
Location: United States
Online Status: Offline
Posts: 7
Quote SaxmanTC Replybullet Posted: 13 Sep 2010 at 5:16am
I've got this working, but I wonder if I have too much going on here. Or better put, could I have simplified this?
 
I made a formula called Convert String to AdmDate:
 
DateValue ({Inactive_Customers___by_Name.AdmDate})
 
Then a formula called Convert String to CDateTime:
 
DateValue ({@CDateTime})
 
Then a formula that did the sum:
 
datediff('m',{@Convert String To AdmDate},{@Convert String to CDateTime})
 
It's working fine, but is there an easier way to do this? Just curious...
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 13 Sep 2010 at 5:34am
sorry, i assumed the null issue you mentioned was that if the clinet is still active the dscdate field would be null so the formula i gave you replaces null with today to coujnt active up to today.
youc an simplify your formula by just replacing the formulas inthe datediff with the formulas you wrote to get conversions:
datediff('m',DateValue ({Inactive_Customers___by_Name.AdmDate}),DateValue ({Inactive_Customers___by_Name.DscDate}))


Edited by DBlank - 13 Sep 2010 at 5:35am
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.