We help IT Professionals succeed at work.

# Need to count number of Mondays, Tuesdays, etc in a given month

on
563 Views
I am using Crystal Reports XI and a SQL database.   I have grouped first by Days of the week in Specified Order where I group by Monday, Tuesday, Wednesday etc using the following:
DayofWeek ({Table. Decision_Date})
Then I group by ({Table.Decision_Date}) for each day.
Then I use a formula in Group #2 to determine how many days of the week are in that month.  See below:
My problem is I don't have entries for every Monday in the month.
For example, I have no entries in 6/2008 for June 30th which is a Monday.  Therefore my report only counts 4 Mondays in that month instead of 5 Mondays.

shared numbervar MondayCounter;
shared numbervar TuesdayCounter;
shared numbervar WednesdayCounter;
shared numbervar ThursdayCounter;
shared numbervar FridayCounter;
shared numbervar SaturdayCounter;
shared numbervar SundayCounter;

if DayOfWeek ({Table.DECISION_DATE}) = 1 then
SundayCounter := SundayCounter + 1
else if DayOfWeek ({Table.DECISION_DATE}) = 2 then
MondayCounter := MondayCounter + 1
else if DayOfWeek ({Table.DECISION_DATE}) = 3 then
TuesdayCounter := TuesdayCounter + 1
else if DayOfWeek ({Table.DECISION_DATE}) = 4 then
WednesdayCounter := WednesdayCounter + 1
else if DayOfWeek ({Table.DECISION_DATE}) = 5 then
ThursdayCounter := ThursdayCounter + 1
else if DayOfWeek ({Table.DECISION_DATE}) = 6 then
FridayCounter := FridayCounter + 1
else if DayOfWeek ({Table.DECISION_DATE}) = 7 then
SaturdayCounter := SaturdayCounter + 1
else
0
Comment
Watch Question

## View Solution Only

Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)

Commented:
I solved my own problem, but I thought I'd share it.  I created a formula called @DayCounter and placed it in the Report Header. chaverly

shared numbervar MondayCounter;
shared numbervar TuesdayCounter;
shared numbervar WednesdayCounter;
shared numbervar ThursdayCounter;
shared numbervar FridayCounter;
shared numbervar SaturdayCounter;
shared numbervar SundayCounter;
numberVar nMonth := 6;   // You could instead use Month({Table.Datefield} to get this value
numberVar nYear := 2008;

dateTimeVar Start := CDateTime(nYear, nMonth, 1);
dateTimeVar x := Start;

while x <= End do
(

( if DayOfWeek (x) = 1 then
SundayCounter := SundayCounter + 1
else if DayOfWeek (x) = 2 then
MondayCounter := MondayCounter + 1
else if DayOfWeek (x) = 3 then
TuesdayCounter := TuesdayCounter + 1
else if DayOfWeek (x) = 4 then
WednesdayCounter := WednesdayCounter + 1
else if DayOfWeek (x) = 5 then
ThursdayCounter := ThursdayCounter + 1
else if DayOfWeek (x) = 6 then
FridayCounter := FridayCounter + 1
else if DayOfWeek (x) = 7 then
SaturdayCounter := SaturdayCounter + 1
else
0);

);
SaturdayCounter;  //this is used merely to test.  I could put here any shared variable listed above.

Commented:
Thank you.  This helped, but I was able to figure it out.  I thought I'd share the solution.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

###### Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

Deciding to stick with EE.

Mohamed Asif

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
###### Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Connect with Certified Experts to gain insight and support on specific technology challenges including:

• Troubleshooting
• Research
• Professional Opinions
Unlock the solution to this question.

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.