Date problem

[MSBrady]

Crystal v10
SQL Server 2005

Hello,

I have a Monthly Client Hours Report I am working on. It's basically a manual crosstab. A SP on the db fires and populates a temp table with the data my report needs. The data use Fiscal Periods (12) and Transaction Dates. Example would be 200610 and 10/13/2006. I need to plug the hours an employee works into buckets on the report that correspond to the months. The months are listed across the top from Jan-Dec with a Total at the end. Hours can be listed in chunks such that there are multiple Detail records possible for an employee on a given date. Hours are posted to Fiscal Period but may not necessarily belong in that period. If it wasn't for the specific way the customer wants the hours buckets populated this would be a no-brainer. Rather than modify the program that the employees use to enter hours they want to control the buckets from the report based on the following rules:
If the hours are posted to Fiscal Period 200610 and the Transaction Date is less than the last day of the month those hours go in Oct.
If the hours are posted to Fiscal Period 200610 and the month of the Transaction Date is greater than the month of the Fiscal Period (ex. 11/01/2006) those hours belong in Nov.
If the two are equal than the hours go in whatever month they are.
Ex.
200610 10/02/2006 Oct.
200611 10/03/2006 Nov.
200610 11/03/2006 Nov.

whileprintingrecords;
numbervar m10;
numbervar m11;
numbervar m12;
numbervar m1;
numbervar m2;
numbervar m3;
numbervar m4;
numbervar m5;
numbervar m6;
numbervar m7;
numbervar m8;
numbervar m9;

if {xwrk_Hours.FNMonth} = 10
and {xwrk_Hours.Date_Entered} <= {@LastDayofMonth}
then m10 := m10 + {xwrk_Hours.Hours}
else if Month({xwrk_Hours.Date_Entered}) = 10 and {xwrk_Hours.FNMonth} = 11
then m11 := m11 + {xwrk_Hours.Hours}
else
if {xwrk_Hours.FNMonth} = 11
and {xwrk_Hours.Date_Entered} <= {@LastDayofMonth}
then m11 := m11 + {xwrk_Hours.Hours}
else if Month({xwrk_Hours.Date_Entered}) = 11 and {xwrk_Hours.FNMonth} = 12
then m12 := m12 + {xwrk_Hours.Hours}
else
if {xwrk_Hours.FNMonth} = 12
and {xwrk_Hours.Date_Entered} <= {@LastDayofMonth}
then m12 := m12 + {xwrk_Hours.Hours}
else if Month({xwrk_Hours.Date_Entered}) = 12 and {xwrk_Hours.FNMonth} = 1
then m1 := m1 + {xwrk_Hours.Hours}
else
if {xwrk_Hours.FNMonth} = 1
and {xwrk_Hours.Date_Entered} <= {@LastDayofMonth}
then m1 := m1 + {xwrk_Hours.Hours}
else if Month({xwrk_Hours.Date_Entered}) = 1 and {xwrk_Hours.FNMonth} = 2
then m2 := m2 + {xwrk_Hours.Hours}
else
if {xwrk_Hours.FNMonth} = 2
and {xwrk_Hours.Date_Entered} <= {@LastDayofMonth}
then m2 := m2 + {xwrk_Hours.Hours}
else if Month({xwrk_Hours.Date_Entered}) = 2 and {xwrk_Hours.FNMonth} = 3
then m3 := m3 + {xwrk_Hours.Hours}
else
if {xwrk_Hours.FNMonth} = 3
and {xwrk_Hours.Date_Entered} <= {@LastDayofMonth}
then m3 := m3 + {xwrk_Hours.Hours}
else if Month({xwrk_Hours.Date_Entered}) = 3 and {xwrk_Hours.FNMonth} = 4
then m4 := m4 + {xwrk_Hours.Hours}
else
if {xwrk_Hours.FNMonth} = 4
and {xwrk_Hours.Date_Entered} <= {@LastDayofMonth}
then m4 := m4 + {xwrk_Hours.Hours}
else if Month({xwrk_Hours.Date_Entered}) = 4 and {xwrk_Hours.FNMonth} = 5
then m5 := m5 + {xwrk_Hours.Hours}
else
if {xwrk_Hours.FNMonth} = 5
and {xwrk_Hours.Date_Entered} <= {@LastDayofMonth}
then m5 := m5 + {xwrk_Hours.Hours}
else if Month({xwrk_Hours.Date_Entered}) = 5 and {xwrk_Hours.FNMonth} = 6
then m6 := m6 + {xwrk_Hours.Hours}
else
if {xwrk_Hours.FNMonth} = 6
and {xwrk_Hours.Date_Entered} <= {@LastDayofMonth}
then m6 := m6 + {xwrk_Hours.Hours}
else if Month({xwrk_Hours.Date_Entered}) = 6 and {xwrk_Hours.FNMonth} = 7
then m7 := m7 + {xwrk_Hours.Hours}
else
if {xwrk_Hours.FNMonth} = 7
and {xwrk_Hours.Date_Entered} <= {@LastDayofMonth}
then m7 := m7 + {xwrk_Hours.Hours}
else if Month({xwrk_Hours.Date_Entered}) = 7 and {xwrk_Hours.FNMonth} = 8
then m8 := m8 + {xwrk_Hours.Hours}
else
if {xwrk_Hours.FNMonth} = 8
and {xwrk_Hours.Date_Entered} <= {@LastDayofMonth}
then m8 := m8 + {xwrk_Hours.Hours}
else if Month({xwrk_Hours.Date_Entered}) = 8 and {xwrk_Hours.FNMonth} = 9
then m9 := m9 + {xwrk_Hours.Hours}
else
if {xwrk_Hours.FNMonth} = 9
and {xwrk_Hours.Date_Entered} <= {@LastDayofMonth}
then m9 := m9 + {xwrk_Hours.Hours}
else if Month({xwrk_Hours.Date_Entered}) = 9 and {xwrk_Hours.FNMonth} = 10
then m10 := m10 + {xwrk_Hours.Hours}

That works, although it's ugly, until I try to seperate across years like 200612 and 01/03/2007. So then I started to try this:

if {xwrk_Hours.FNMonth} = Month({xwrk_Hours.Date_Entered})
then VMonth = 1
else 
if {xwrk_Hours.FNMonth} > Month({xwrk_Hours.Date_Entered})
then VMonth = 2
else 
if {xwrk_Hours.FNMonth} < Month({xwrk_Hours.Date_Entered})
then VMonth = 3

if VMonth = 1
then {xwrk_Hours.Hours}
else 
if VMonth = 2
then {xwrk_Hours.Hours}
else

but got lost in my head somewhere.

So to net it all out I need help with a formula or combination of formulas that will assign hours to monthly buckets following the above stated rules and breaking across years correctly. Year is defined as the year part of the Fiscal Period and I am also grouping on Year.

I am happy to add any additional information I may have left out, just ask.

TIA for the help.

 

[synapsevampire]

The whole thing is kinda ugly to review, and might be simplified greatly.

So I guess you're saying that you have a report for a fiscal year which starts in October. The months don't change from month t month, you're only showing the October to September?

Your example data doesn't match your formulas, which further confuses things, you use 200610 in the example, then you reference a field called fnmonth, which is apparently just the month. Why don't you show real examples instead of introducing unknown fields in the formulas?

Is there also a year field?

Try a formula of:

if {xwrk_Hours.FNMonth} <= month({xwrk_Hours.Date_Entered})
and
{xwrk_Hours.FNYear} <= year({xwrk_Hours.Date_Entered})
then
cdate({xwrk_Hours.FNYear} ,{xwrk_Hours.FNMonth},1)
else
if {xwrk_Hours.FNMonth} < month({xwrk_Hours.Date_Entered})
and
{xwrk_Hours.FNYear} <= year({xwrk_Hours.Date_Entered})
then
cdate(year({xwrk_Hours.Date_Entered}),month(year({xwrk_Hours.Date_Entered}),1)

Now you have the date identified for each period, and can refrence this date in a grouping by month and year or whatever and use conventional formulas for aggregates or manually create sums.

If you don't have a year field, parse the year out of the fiscal period field.

At any rate, you're overcomplicating this, and again, your example doesn't match the formulas, so you're supplying bad information.

-k

 

[MSBrady]

K,

Thanks for your reply. You are correct that I'm overcomplicating things. I just didn't know any other way to explain it. The Fiscal Period runs from Jan. to Dec. I was only showing Sept. to Nov. as example. FNMonth and FNYear are formulas that are parsed out of Fiscal Period. So you're saying take the 2 values and meld them into a single date based on the report criteria and drop the hours into the respective buckets based on that melded value? Is that close?

thanks

 

[synapsevampire]

Yeah, that's basically it.

Create a valid date based on your criteria, then you can use a crosstab or conventional summaries as well. In either case it makes it very easy then to do a manual crosstab as your original post intended.

-k

 

[MSBrady]

Thanks a lot. I will try this today.