Function to convert dates to quarters

[Guest]

I have a function QtrVal that looks at the Ef_id and Th_id of a record and determines the correct qty to put in the quarter. The function is called from a query (ex: Sum((QtrVal([Ef_Id],[Th_Id],1,[RLI_AL_CT_QY]))) AS QTR1, Sum((QtrVal([Ef_Id],[Th_Id],2,[RLI_AL_CT_QY]))) AS QTR2),this is repeated based on the number of quarters you want to view.

Table has the following columns and data:
PE=5421R Ef_id = 01 Oct 2003 Th_id = 30 Sep 2004 RLI = 50
PE=5421R Ef_id = 01 Oct 2005 Th_id = 30 Sep 2006 RLI = 65
PE=5421R Ef_id = 01 Oct 2006 Th_id = 31 Dec 4712 RLI = 80

When I run the query I should get an output record like this:
PE Qtr1 Qtr2 Qtr3 Qtr4 Qtr5 Qtr6 Qtr7 Qtr8 Qtr9 Qtr10
5421R 50 50 50 50 65 65 65 65 80 80

What I'm getting is this:
PE Qtr1 Qtr2 Qtr3 Qtr4 Qtr5 Qtr6 Qtr7 Qtr8 Qtr9 Qtr10
5421R 50 50 50 50 115 65 65 65 145 80

Where ever the th_id changes, it's adding the previous qtrs number. Will gladly send a small db and code to someone.

 

[Guest]

Perhaps add one from the result for the quarter as identified for Th_Id. It would seem Td_Id is returning an answer that is one too low for your needs.

David Atkins, MCP

p.s. If you want me to look at it, email it to (e-mail address removed). Afterall, I don't get enough spam as it is.