How to compare month between dates

I have seven dates on my crystal report.I need to compare month of each date with the date the report is made to run(@Datebeg is the field for report date). I am using formula on my report as:

if month({Date1})=month(cdate({@Datebeg})) and
month({Date2})=month(cdate({@Datebeg})) and
month({Date3})=month(cdate({@Datebeg})) and
month({Date4})=month(cdate({@Datebeg})) and
month({Date5})=month(cdate({@Datebeg})) and
month({Date6})=month(cdate({@Datebeg})) and
month({Date7})=month(cdate({@Datebeg}))
then (H1+H2+H3+H4+H5+H6+H7)
else
if month({Date1})=month(cdate({@Datebeg})) and
month({Date2})=month(cdate({@Datebeg})) and
month({Date3})=month(cdate({@Datebeg})) and
month({Date4})=month(cdate({@Datebeg})) and
month({Date5})=month(cdate({@Datebeg})) and
month({Date6})=month(cdate({@Datebeg})) and
month({Date7})<>month(cdate({@Datebeg}))
then (H1+H2+H3+H4+H5+H6)
else........

The output I am getting is 0,each time.
The months are not getting compared at all.If I put OR instead of AND,that gives me sum of all H(1..7). Please help me with the comparison.

Thanks in advance.
john ramanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Instead of catering to all possible combinations, create 7 separate formulas, each returning 1 or zero. Then, create a formula that simply sums those formulas.
Or do the same with a single formula and increment a counter variable.
In any case, start with the logic for just one month and confirm you are returning the expected result.
You may need to take care of NULL cases using IF IsNull() ...
0
mlmccCommented:
What are you trying to sum?

DO you want the Hn values summed for those who meet the selected month?

Can any of the months be 0?

You could do as Ido suggests.  Create 7 formulas like
Name - Month1
If Not IsNull({Date1})  AND   month({Date1})=month(cdate({@Datebeg}))  then
    H1
Else
    0

Open in new window


You can then sum the 7 formulas

mlmcc
0
john ramanAuthor Commented:
Hello,
Actually,you are right I need to sum the hours if their corresponding months matches with the report date.I have attached my entire formula for review.I think the And conditions are working but the Else are not working.
Kindly take a look at my formula and 218194.txt suggest me how should I improvise it.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

mlmccCommented:
Your data has 7 value fields and 7 corresponding date fields.

Can a record where Command.CSU4 date doesn't match the month have Command.CSU5 date match the month?  Or once a date is found that doesn't match, then the rest won't match?

I see in your formula you have some Command.CSU4 compares that are = even when Command.CSU3  is comparing for <>

mlmcc
0
James0628Commented:
It's probably just a typo, but, as mlmcc mentioned, you have Month({Command.CSU4}) = month(CDATE({@DateBeg})) in your last two If's, but <> for CSU3.  You probably intended for those CSU4 tests to be <>, but I wanted to make sure.

 mlmcc sort of covered this, but will any matching dates always be in order, and always start with CSU1?  Your formula looks for a series of matching dates, starting with CSU1.

 If CSU1 doesn't match, all of the If's fail and you get 0.  So if any of the dates in CSU2 through CSU7 match, but CSU1 does not, you get 0.

 Likewise, if multiple CSUx fields, starting with CSU1, match, but they're not sequential (CSU1, CSU2, CSU3, etc.), then you'll get 0, because your If's also check that the other CSUx fields do _not_ match.  For example, if CSU1, CSU2, CSU5 and CSU7 all match, your tests would fail, because CSU1 and CSU2 are =, but CSU3 and CSU4 are <>, while CSU5 and CSU7 are =.  None of your tests match that set of conditions.  Your formula assumes that everything after the last matching CSUx is not a match.

 If you simply want to include HRSx if CSUx is for the right month, then a separate formula for each CSUx/HRSx pair, as Ido and mlmcc suggested, is definitely the way to go.  But if the different CSUx fields are connected somehow, so that you need to check all of them together, what is the relationship between them?


 Also, I'll just throw this out there:

 If your data could include more than 1 year at a time, you may need to check more than just the month, unless you want anything in month X to be included, regardless of the year.

 James
0
john ramanAuthor Commented:
Hi,
You all are right.I simply want that if the month of the report date matches with any of the Csux,then its corresponding hours should get summed up.
If i am wrong with this formula,please help me in making 7 separate formula and then summing them up.
0
James0628Commented:
It's very simple.  For example:

if  Month ({Command.CSU1}) = month(CDATE({@DateBeg})) then
  {Command.HRS1}
else
  0

 You'd have similar formulas for CSU2 and HRS2, CSU3 and HRS3, and so on.

 If the CSUx fields could be null, you could add an IsNull test to each formula, as Ido and mlmcc suggested.  Note that the IsNull test _has_ to be first.  For example:

if  not IsNull ({Command.CSU1}) and
 Month ({Command.CSU1}) = month(CDATE({@DateBeg})) then
  {Command.HRS1}
else
  0

 If you're not sure if the CSUx fields could be null, you can include the IsNull test, just to play it safe.  It won't do any harm.


 If you just want the grand total from all of those formulas, you would create another formula like this:

Sum ({@CSU1 formula}) + Sum ({@CSU2 formula}) + Sum ({@CSU3 formula}) +
 Sum ({@CSU4 formula}) + Sum ({@CSU5 formula}) + Sum ({@CSU6 formula}) +
 Sum ({@CSU7 formula})

 Obviously you'd replace {@CSU1 formula}, {@CSU2 formula}, etc. with your actual formula names.

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
john ramanAuthor Commented:
Hello James0628,

Thank you for your assistance,
Will definitely try this out and let you know if its working or not.

Thank you so very much.
0
john ramanAuthor Commented:
Hello James0628,

Your suggested method did worked.Thank you.
0
john ramanAuthor Commented:
Thank you for your suggestions.It was indeed commendable.
0
James0628Commented:
I'm glad I was able to help, but, to be fair, that was basically the same thing that Ido and mlmcc suggested.  I just posted more details.  Ido's suggestion was for a count, instead of adding 7 separate fields, but it's the same basic idea.  Ido and mlmcc probably deserve some of the points.  You could ask to have the question re-opened and split the points between us.

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.