Schedule for School Year

[Sahubba]

Does anyone know how to create a code to get the school year and semester, so it can be used as parameter. This would be a schedule report that will run at the beginning and end of the school year and every semester.

The school year is July - June
The school semester is

1st Semester – August – December
2nd Semester – January – April
Summer Semester – May – July


Thanks

 

[MCuthill]

Bellkras,

...how to create a code to get the school year and semester?

Based on what? A date field, the current date, some determination of other criteria?
Or, do you want to key in a year and semester and return only certain records?

There are a few approaches that will work to formulaically determine the school year and semester - but more information is needed to write the formula's.

Please advise when you have a moment.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[Sahubba]

If scheduled_parameter = “weekly” then Date_Field in LastFullWeek Else
If scheduled_parameter = “monthly” then Date_Field in LastFullMonth Else
If scheduled_parameter = “semester” then
(If Month(CurrentDate) = 1 then (Year(Date_Field) = Year(CurrentDate)-1 and Week(Date_Field) in [Week 33 to Week 52]) Else
If Month(CurrentDate) = 5 then Week(Date_Field) in [Week 1 to Week 19] Else
Week(Date_Field) in [Week 20 to Week 32])
If scheduled_parameter = “Yearly” then Date_Field >= add_months(TRUNC(CURRENT_DATE,'YYYY'), 6 ) and PAT_ENC_APPT_TIME<CURRENT_DATE


does this makes sense? or answer your question?

 

[Sahubba]

sorry above thats how I want it to look like.

 

[MCuthill]

Thanks Bellkras,

I think I can work with that. It does seem more complex than your original posting.

This would be a schedule report that will run at the beginning and end of the school year and every semester

That being said, here we go.

Valid Parameter Values (as defined above):
1) Weekly
2) Monthly
3) Semester
4) Yearly

Please confirm the following assumptions:
1) If "Weekly": return the records where the date is in the [blue]LastFullWeek[/blue] (for today, it would return... Sept 25 - Oct 1, I think).

2) If "Monthly": return those records with a date in the [blue]LastFullMonth[/blue] (for today, it would return Sept 1 - 30)/

3) If "Semester":
Please clarify what you would like. By stating Months and then weeks above, I am not 100% what you are seeking and when. I think from reading the above - if it is May, Semester X; If January, Semester Y else Semester Z. Please state which Month Ran = what Semester reported. Are the semesters the full months as defined above (Semester 1 = Aug 1 to Dec 31 for example).

4) If "Yearly":
Please clarify.
By ">= add_months(TRUNC(CURRENT_DATE,'YYYY'), 6 )" do you mean those dates after this day, six months in the future (Apr 5th for today) and before "PAT_ENC_APPT_TIME". As with semester, please provide an example.

Once Semester & Yearly are better defined, a solution should be no trouble. Also, does there need to be a catch of ran at an unexpected time? You also stated the reports are ran at the beginning and ends of each year and semester, is the "semester end" report ran in a different month than the "semester start" report (and same for "start of year" vs "end of year")? If not, how would one differentiate between which report is sought?

Sorry for all the questions Bellkras, just want to make sure we don't head down the wrong path off the start with this solution.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[Sahubba]

Weekly – Yes it’s a function of crystal NOTE: some reports pull differently for the weekly runs. They wanted Friday to Thursday to be run on the Friday after. So for example tomorrow the weekly reports will run, and they will pull Sept 30th – Oct 6th. They had to be different.
Monthly – Yes also a function of crystal
Semester – January would be the 1st semester, May would be the 2nd semester, and August would be the Summer Semester. January would be the only problem because I would need to pull weeks 33 thru 52 of the previous year. The default is the weeks of the current year.
Yearly – Ok I think I got the year wrong. It needs to be this
Date_Field >= add_months(TRUNC(CURRENT_DATE,'YYYY'), -6 ) and PAT_ENC_APPT_TIME<CURRENT_DATE First day of the year – 6 months would be July 1 the previous year.


Please let me know if I gave you want you where looking for.

 

[MCuthill]

Bellkras,

That is exactly what I needed. I will work on a solution and post it shortly.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[Sahubba]

thank you!

 

[MCuthill]

Bellkras,

The following can be placed in your record selection:

[green]//Select the proper date range based on paramter chosen.
//Assumed field names: {?MyParameter} & {Table.MyDateField}
//Option #1: [b]Weekly[/b] (Assumed to be ran on Friday's)[/green]
[blue]IF[/blue] {?MyParameter} = "Weekly" [blue]THEN[/blue] {Table.MyDateField} [blue]in[/blue] [[blue]DateAdd[/blue]("d",-7,[blue]CurrentDate[/blue]) [blue]to[/blue] [blue]DateAdd[/blue]("d",-1,[blue]CurrentDate[/blue])] [blue]ELSE[/blue]
[green]//Option #2: [b]Monthly[/b][/green]
[blue]IF[/blue] {?MyParameter} = "Monthly" [blue]THEN[/blue] {Table.MyDateField} in [blue]LastFullMonth ELSE[/blue]
[green]//Option #3: [b]Semester[/b][/green]
[blue]IF[/blue] {?MyParameter} = "Semester" [blue]THEN[/blue]
(
   [green]Run Semester #1 (Aug - Dec) in January of following year.[/green] 
   [blue]IF Month[/blue]([blue]CurrentDate[/blue]) = 1 [blue]THEN[/blue] {Table.MyDateField} [blue]in[/blue] [[blue]Date[/blue]([blue]Year[/blue]([blue]CurrentDate[/blue])-1,8,1) [blue]to[/blue] [[blue]Date[/blue]([blue]Year[/blue]([blue]CurrentDate[/blue])-1,12,31)] [blue]ELSE[/blue]
   [green]Run Semester #2 (Jan - Apr) in May.[/green]
   [blue]IF Month[/blue]([blue]CurrentDate[/blue]) = 5 [blue]THEN[/blue] {Table.MyDateField} [blue]in[/blue] [[blue]Date[/blue]([blue]Year[/blue]([blue]CurrentDate[/blue]),1,1) [blue]to[/blue] [[blue]Date[/blue]([blue]Year[/blue]([blue]CurrentDate[/blue]),4,30)] [blue]ELSE[/blue]
   [green]Run Summer Semester (May - July) if not January or May.[/green]
   {Table.MyDateField} [blue]in[/blue] [[blue]Date[/blue]([blue]Year[/blue]([blue]CurrentDate[/blue]),5,1) [blue]to[/blue] [[blue]Date[/blue]([blue]Year[/blue]([blue]CurrentDate[/blue]),7,31)] 
) [blue]ELSE[/blue]
[green]//Option #4: [b]Yearly[/b] (Run all where {Table.MyDateField} is after July 1 of prior year and {Table.PAT_ENC_APPT_TIME} before CurrentDate)[/green]
[blue]IF[/blue] {?MyParameter} = "Yearly" [blue]THEN[/blue]
( 
   {Table.MyDateField} >= [blue]Date[/blue]([blue]Year[/blue]([blue]CurrentDate[/blue])-1,7,1) [blue]AND[/blue]
   {Table.PAT_ENC_APPT_TIME} < [blue]CurrentDate[/blue]
)

My apologies, but I did not have the time to develop a test database and report for this - the above is off the cuff and may need some polishing. Please advise should you have any questions

Hope this helps Bellkras, have a great day!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

oops!

Bellkras... pleaes place "//" at the front of the [green]green[/green] lines in the Semester section (they are comments).

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."