In which quarter is [date] of fiscal year?
Author Message
In which quarter is [date] of fiscal year?

I need to evaluate a date against an annual date range to determine which quarter the date falls in within the annual date range.

We track policies and commissions. We need to know in which Policy Quarter a particular commission was received. Although the Policy Year is not realy a
fiscal year,  it does works much the same. I found a function (below) and tried it out with no success. Keep in mind that each policy has its own
Policy year with only about half being the same as a calendar year. anyway I have not had any luck using the function. Could someone tell me what I am doing wrong?

I got the function from this posting here
http://www.*-*-*.com/

In a query the following returns "#Error"
Expr1: FiscalQuarter([AccountingMonth],[CommissionEffectiveDate])

Function FiscalQuarter(myDate As Date, Optional iFiscalYrStartMonth As Integer = 6) As Integer
'pass in the date you want to evaluate and
'the month that starts the fiscal year
Dim iMonth As Integer
iMonth = Val(Str(myDate))
Select Case iMonth
Case 1 To 3
FiscalQuarter = 3
Case 4 To 6
FiscalQuarter = 4
Case 7 To 9
FiscalQuarter = 1
Case 10 To 12
FiscalQuarter = 2
End Select
End Function

Thanks
Tony Vrolyk

Tue, 03 May 2005 03:22:18 GMT
In which quarter is [date] of fiscal year?

See if this won't do what you want.

Steve

Public Function GetFiscalQtr(ByVal X As Date, dteFYE As Date)
'X = date in question, dteFYE = Fiscal Year End
Dim m As Integer
Dim FMonthStart As Integer
Dim FDayStart As Integer
Dim FYearOffset As Integer

FMonthStart = Month(dteFYE + 1)
FDayStart = Day(dteFYE + 1)
FYearOffset = Year(X) - Year(dteFYE)

m = Month(X) - FMonthStart + 1
If Day(X) < FDayStart Then m = m - 1
If m < 1 Then m = m + 12

Select Case m
Case 1 To 3
GetFiscalQtr = 1
Case 4 To 6
GetFiscalQtr = 2
Case 7 To 9
GetFiscalQtr = 3
Case 10 To 12
GetFiscalQtr = 4
End Select

End Function

I need to evaluate a date against an annual date range to determine which quarter the date falls in within the annual date range.

We track policies and commissions. We need to know in which Policy Quarter a particular commission was received. Although the Policy Year is not realy a
fiscal year,  it does works much the same. I found a function (below) and tried it out with no success. Keep in mind that each policy has its own
Policy year with only about half being the same as a calendar year. anyway I have not had any luck using the function. Could someone tell me what I am doing wrong?

I got the function from this posting here

In a query the following returns "#Error"
Expr1: FiscalQuarter([AccountingMonth],[CommissionEffectiveDate])

Function FiscalQuarter(myDate As Date, Optional iFiscalYrStartMonth As Integer = 6) As Integer
'pass in the date you want to evaluate and
'the month that starts the fiscal year
Dim iMonth As Integer
iMonth = Val(Str(myDate))
Select Case iMonth
Case 1 To 3
FiscalQuarter = 3
Case 4 To 6
FiscalQuarter = 4
Case 7 To 9
FiscalQuarter = 1
Case 10 To 12
FiscalQuarter = 2
End Select
End Function

Thanks
Tony Vrolyk

Tue, 03 May 2005 03:42:55 GMT
In which quarter is [date] of fiscal year?

As I was reading though your function I started getting the idea of what you were doing and then I modified it to the following. This works better for me since policy years are not defined as 365 days. For instance a policy may have an effective date of 07/01/02 and a renewal date of 07/01/03. If I use the renewal date as the dteFYE with your function then the first day of the 3rd, 6th and 9th month will return as the previous quarter. So I adjusted the function to work with the effective date (or the Fiscal Year Start).

I've tested it and it seems to be working. Let me know if you think I am missing anything and thanks for the help.

Tony

Public Function GetFiscalQtr(ByVal myDate As Date, dteFYS As Date)
'myDate = date in question, dteFYS = Fiscal Year Start
Dim m As Integer
Dim FMonthStart As Integer
Dim FMonthCurrent As Integer

FMonthStart = Month(dteFYS)
FMonthCurrent = Month(myDate)

m = FMonthCurrent - FMonthStart
If m < 1 Then m = m + 12

Select Case m
Case 0 To 2
GetFiscalQtr = 1
Case 3 To 5
GetFiscalQtr = 2
Case 6 To 8
GetFiscalQtr = 3
Case 9 To 11
GetFiscalQtr = 4
End Select

End Function
See if this won't do what you want.

Steve

Public Function GetFiscalQtr(ByVal X As Date, dteFYE As Date)
'X = date in question, dteFYE = Fiscal Year End
Dim m As Integer
Dim FMonthStart As Integer
Dim FDayStart As Integer
Dim FYearOffset As Integer

FMonthStart = Month(dteFYE + 1)
FDayStart = Day(dteFYE + 1)
FYearOffset = Year(X) - Year(dteFYE)

m = Month(X) - FMonthStart + 1
If Day(X) < FDayStart Then m = m - 1
If m < 1 Then m = m + 12

Select Case m
Case 1 To 3
GetFiscalQtr = 1
Case 4 To 6
GetFiscalQtr = 2
Case 7 To 9
GetFiscalQtr = 3
Case 10 To 12
GetFiscalQtr = 4
End Select

End Function

I need to evaluate a date against an annual date range to determine which quarter the date falls in within the annual date range.

We track policies and commissions. We need to know in which Policy Quarter a particular commission was received. Although the Policy Year is not realy a
fiscal year,  it does works much the same. I found a function (below) and tried it out with no success. Keep in mind that each policy has its own
Policy year with only about half being the same as a calendar year. anyway I have not had any luck using the function. Could someone tell me what I am doing wrong?

I got the function from this posting here

In a query the following returns "#Error"
Expr1: FiscalQuarter([AccountingMonth],[CommissionEffectiveDate])

Function FiscalQuarter(myDate As Date, Optional iFiscalYrStartMonth As Integer = 6) As Integer
'pass in the date you want to evaluate and
'the month that starts the fiscal year
Dim iMonth As Integer
iMonth = Val(Str(myDate))
Select Case iMonth
Case 1 To 3
FiscalQuarter = 3
Case 4 To 6
FiscalQuarter = 4
Case 7 To 9
FiscalQuarter = 1
Case 10 To 12
FiscalQuarter = 2
End Select
End Function

Thanks
Tony Vrolyk

Tue, 03 May 2005 06:44:00 GMT
In which quarter is [date] of fiscal year?

I just caught my own mistake
If m < 1 Then m = m + 12
should be...
If m < 0 Then m = m + 12

thanks again

As I was reading though your function I started getting the idea of what you were doing and then I modified it to the following. This works better for me since policy years are not defined as 365 days. For instance a policy may have an effective date of 07/01/02 and a renewal date of 07/01/03. If I use the renewal date as the dteFYE with your function then the first day of the 3rd, 6th and 9th month will return as the previous quarter. So I adjusted the function to work with the effective date (or the Fiscal Year Start).

I've tested it and it seems to be working. Let me know if you think I am missing anything and thanks for the help.

Tony

Public Function GetFiscalQtr(ByVal myDate As Date, dteFYS As Date)
'myDate = date in question, dteFYS = Fiscal Year Start
Dim m As Integer
Dim FMonthStart As Integer
Dim FMonthCurrent As Integer

FMonthStart = Month(dteFYS)
FMonthCurrent = Month(myDate)

m = FMonthCurrent - FMonthStart
If m < 1 Then m = m + 12

Select Case m
Case 0 To 2
GetFiscalQtr = 1
Case 3 To 5
GetFiscalQtr = 2
Case 6 To 8
GetFiscalQtr = 3
Case 9 To 11
GetFiscalQtr = 4
End Select

End Function

See if this won't do what you want.

Steve

Public Function GetFiscalQtr(ByVal X As Date, dteFYE As Date)
'X = date in question, dteFYE = Fiscal Year End
Dim m As Integer
Dim FMonthStart As Integer
Dim FDayStart As Integer
Dim FYearOffset As Integer

FMonthStart = Month(dteFYE + 1)
FDayStart = Day(dteFYE + 1)
FYearOffset = Year(X) - Year(dteFYE)

m = Month(X) - FMonthStart + 1
If Day(X) < FDayStart Then m = m - 1
If m < 1 Then m = m + 12

Select Case m
Case 1 To 3
GetFiscalQtr = 1
Case 4 To 6
GetFiscalQtr = 2
Case 7 To 9
GetFiscalQtr = 3
Case 10 To 12
GetFiscalQtr = 4
End Select

End Function

I need to evaluate a date against an annual date range to determine which quarter the date falls in within the annual date range.

We track policies and commissions. We need to know in which Policy Quarter a particular commission was received. Although the Policy Year is not realy a
fiscal year,  it does works much the same. I found a function (below) and tried it out with no success. Keep in mind that each policy has its own
Policy year with only about half being the same as a calendar year. anyway I have not had any luck using the function. Could someone tell me what I am doing wrong?

I got the function from this posting here

In a query the following returns "#Error"
Expr1: FiscalQuarter([AccountingMonth],[CommissionEffectiveDate])

Function FiscalQuarter(myDate As Date, Optional iFiscalYrStartMonth As Integer = 6) As Integer
'pass in the date you want to evaluate and
'the month that starts the fiscal year
Dim iMonth As Integer
iMonth = Val(Str(myDate))
Select Case iMonth
Case 1 To 3
FiscalQuarter = 3
Case 4 To 6
FiscalQuarter = 4
Case 7 To 9
FiscalQuarter = 1
Case 10 To 12
FiscalQuarter = 2
End Select
End Function

Thanks
Tony Vrolyk

Tue, 03 May 2005 06:48:59 GMT
In which quarter is [date] of fiscal year?
On Thu, 14 Nov 2002 13:22:18 -0600, "Tony Vrolyk"

Quote:

>I need to evaluate a date against an annual date range to determine
>which quarter the date falls in within the annual date range.

These are facts.  Store them in a table.

--
Mike Sherrill
Information Management Systems

Sun, 08 May 2005 02:40:47 GMT
In which quarter is [date] of fiscal year?
Thanks, but they alreay are. I don't think you quite understood my initial
post

Quote:
> On Thu, 14 Nov 2002 13:22:18 -0600, "Tony Vrolyk"

> >I need to evaluate a date against an annual date range to determine
> >which quarter the date falls in within the annual date range.

> These are facts.  Store them in a table.

> --
> Mike Sherrill
> Information Management Systems

Sun, 08 May 2005 04:42:01 GMT
In which quarter is [date] of fiscal year?
On Tue, 19 Nov 2002 14:42:01 -0600, "Tony Vrolyk"

Quote:

>Thanks, but they alreay are. I don't think you quite understood my initial
>post

If you're trying to use a function to calculate the quarter a date
falls in, it's more likely you didn't understand my reply. <g>

Quote:
>In a query the following returns "#Error"
>    Expr1: FiscalQuarter([AccountingMonth],[CommissionEffectiveDate])

The function FiscalQuarter, as you posted it, takes a date as its
first parameter and an integer as its second.  It *looks* like you're
passing an integer first, and a date second.  But that's not the real
problem.  (Not from a database design perspective, anyway.)

When you say "each policy has its own Policy year", what do you mean?
Say we have policy '111-222-333-444', and it starts on 01-Jun-2002.
Do its first four policy quarters look like this?

PolicyNum        PolicyYear  Quarter        Start           End
--
111-222-333-444        2002        1  01-Jun-2002   31-Aug-2002
111-222-333-444        2002        2  01-Sep-2002   30-Nov-2002
111-222-333-444        2002        3  01-Dec-2002   28-Feb-2003
111-222-333-444        2002        4  01-Mar-2003   31-May-2003

If not, what do they look like?

--
Mike Sherrill
Information Management Systems

Mon, 09 May 2005 07:52:29 GMT
In which quarter is [date] of fiscal year?
occurs since each policy has an Effective Date and a Renewal Date stored in
the Policies table. However the, as insurance policies info is referenced
(this is an industry standard) the Renewal date is 366 days past the
Effective Date. In other words it is actually the Effective Date of the next
Policy year. So I needed to calculate that quarter in relation to the
Effective Date or it would be one day off.

Yes the quarters look like your example.

I don't see why my function is a problem. It takes the Effective date month
(as an integer) and subtracts it from the current date month (as an
integer). If the resulting value is under 0 than add 12. Then the Case
statement evaluates the number to return the correct quarter. I have tested
it several time and so far always returns the expected results.

If the Effective date is 02/01/2002 and the current date is 11/21/2002.
11-2 = 9
9 is over 0 so do nothing
9 resolves to quarter 3

If the Effective date is 11/01/2002 and the current date is 3/21/2003.
3-11 = -8
-8 + 12 = 4
4 resolves to quarter 2

Tony

Quote:
> On Tue, 19 Nov 2002 14:42:01 -0600, "Tony Vrolyk"

> >Thanks, but they alreay are. I don't think you quite understood my
initial
> >post

> If you're trying to use a function to calculate the quarter a date
> falls in, it's more likely you didn't understand my reply. <g>

> >In a query the following returns "#Error"
> >    Expr1: FiscalQuarter([AccountingMonth],[CommissionEffectiveDate])

> The function FiscalQuarter, as you posted it, takes a date as its
> first parameter and an integer as its second.  It *looks* like you're
> passing an integer first, and a date second.  But that's not the real
> problem.  (Not from a database design perspective, anyway.)

> When you say "each policy has its own Policy year", what do you mean?
> Say we have policy '111-222-333-444', and it starts on 01-Jun-2002.
> Do its first four policy quarters look like this?

>  PolicyNum        PolicyYear  Quarter        Start           End
>  --
>  111-222-333-444        2002        1  01-Jun-2002   31-Aug-2002
>  111-222-333-444        2002        2  01-Sep-2002   30-Nov-2002
>  111-222-333-444        2002        3  01-Dec-2002   28-Feb-2003
>  111-222-333-444        2002        4  01-Mar-2003   31-May-2003

> If not, what do they look like?

> --
> Mike Sherrill
> Information Management Systems

Tue, 10 May 2005 00:05:22 GMT
In which quarter is [date] of fiscal year?
9 resolves to quarter 4

Quote:
> occurs since each policy has an Effective Date and a Renewal Date stored
in
> the Policies table. However the, as insurance policies info is referenced
> (this is an industry standard) the Renewal date is 366 days past the
> Effective Date. In other words it is actually the Effective Date of the
next
> Policy year. So I needed to calculate that quarter in relation to the
> Effective Date or it would be one day off.

> Yes the quarters look like your example.

> I don't see why my function is a problem. It takes the Effective date
month
> (as an integer) and subtracts it from the current date month (as an
> integer). If the resulting value is under 0 than add 12. Then the Case
> statement evaluates the number to return the correct quarter. I have
tested
> it several time and so far always returns the expected results.

> If the Effective date is 02/01/2002 and the current date is 11/21/2002.
> 11-2 = 9
> 9 is over 0 so do nothing
> 9 resolves to quarter 3

> If the Effective date is 11/01/2002 and the current date is 3/21/2003.
> 3-11 = -8
> -8 + 12 = 4
> 4 resolves to quarter 2

> Tony

> > On Tue, 19 Nov 2002 14:42:01 -0600, "Tony Vrolyk"

> > >Thanks, but they alreay are. I don't think you quite understood my
> initial
> > >post

> > If you're trying to use a function to calculate the quarter a date
> > falls in, it's more likely you didn't understand my reply. <g>

> > >In a query the following returns "#Error"
> > >    Expr1: FiscalQuarter([AccountingMonth],[CommissionEffectiveDate])

> > The function FiscalQuarter, as you posted it, takes a date as its
> > first parameter and an integer as its second.  It *looks* like you're
> > passing an integer first, and a date second.  But that's not the real
> > problem.  (Not from a database design perspective, anyway.)

> > When you say "each policy has its own Policy year", what do you mean?
> > Say we have policy '111-222-333-444', and it starts on 01-Jun-2002.
> > Do its first four policy quarters look like this?

> >  PolicyNum        PolicyYear  Quarter        Start           End
> >  --
> >  111-222-333-444        2002        1  01-Jun-2002   31-Aug-2002
> >  111-222-333-444        2002        2  01-Sep-2002   30-Nov-2002
> >  111-222-333-444        2002        3  01-Dec-2002   28-Feb-2003
> >  111-222-333-444        2002        4  01-Mar-2003   31-May-2003

> > If not, what do they look like?

> > --
> > Mike Sherrill
> > Information Management Systems

Tue, 10 May 2005 01:25:35 GMT
In which quarter is [date] of fiscal year?
On Thu, 21 Nov 2002 10:05:22 -0600, "Tony Vrolyk"

Quote:

>I understand your first reply to store the info in a table.

quarters.  Stop and think about it for a minute.

Quote:
>So I needed to calculate that quarter

You need to *know* that quarter.  It doesn't follow that you need to
calculate it.

[snip]

Quote:
>I don't see why my function is a problem.
[snip]
>I have tested
>it several time and so far always returns the expected results.

No, you've *run* it several times.  Testing and running are different
things.

Quote:
>If the Effective date is 02/01/2002 and the current date is 11/21/2002.
>11-2 = 9
>9 is over 0 so do nothing
>9 resolves to quarter 3

? GetFiscalQtr("21-Nov-2002", "01-Feb-2002")
4

Try this.

? GetFiscalQtr("01-Feb-2000", "29-Nov-1999")
2
? DateDiff("d", "01-Feb-2000", "29-Nov-1999")
-64

My first quarter has more days than that.  YMMV.

--
Mike Sherrill
Information Management Systems

Thu, 12 May 2005 01:58:50 GMT
In which quarter is [date] of fiscal year?
Forgive me for getting harsh but this is starting to{*filter*}me off.

This is a perfect example of why I hate some posts that so-called experts
give to others questions. Rather than being helpful and giving reasons why
something doesn't work or examples of how to make it work, they give little
tidbits of useless crticism.

Quote:
>These are facts.  Store them in a table.

Great, what the hell does that mean? Do you mean store the policy year
beginning
and end dates? Done. Do you mean storing the quarter? Maybe a good idea but
how am going to tell by this pat response? Give details. Give reasons.
Give examples. It took three posts until you started making any sense. Not
all the posters around here can read between the "code" in your posts.

I frequently read posts where I understand what the poster but yet some
holier-then-thow god-of-programmers comes in and posts something useless
like "you'll need to explaing your db structure more before anyone can
answer you question." This frequenty happens to otherwise simple questions,
but because the poster didn't speak "Programmer" the responder felt it
necessary to basically berate the poster for not using the correct language.

To use a simple, non-programming example-
If some little old lady asks how she can install more memory and a new modem
in her CPU. Some "expert" will come back and say that she can't install
memory or a modem in her CPU because it is a small chip inside your computer
that  has it''s own buitl in cache memory and can not accept expansion
cards. Any idiot can tell you that when the little old lady said CPU, she
didn't mean her Central Pricessing Unit, she meant her whole computer. But
the "expert" can only speak "Computer Technician" and can't bring himself to
come down to everyone elses level and speak English.

As to the difference between "testing" and "running"  and "calculate" and
"know" - symantics. You obviously use the language of a seasoned programmer.
I am but a newcomer in these parts. If you were trying to be truly helpful
you should be able to red between the lines and respond appropriately rather
than posting in a way that implies I don't know what I am talking about.
Maybe I don't but you could say it in a much less confrontational style.

I still say that I am CALULATING the quarter. i am taking 2 dates and
subtracting the difference ... I'm sorry I had better be perfect in the way
I state this... I am calulating the difference between their respective
months and returning a number depending on what range the difference falls
in. In junior high math, subtacting was called a calculation. Maybe you
learned under New Math or Programmer Math. Sorry I missed those courses.
Even
you stated...

Quote:
>If you're trying to use a function to calculate the quarter a date falls

in...

If you have some usefull info to contribute, maybe you should try formating
your response as if you are talking to people who don't understand the
language. After all, if the poster is an expert programmer and understands
the lingo they wouldn't be here asking questions in the first place.

Lastly, you don't understand my whole situation or how exactly I am going to
be using this function. I am not trying to develop a function for the
masses, just for my particular circumstances. In my db the current date will
never be before or more then 11 months after the Policy Effective date.

I could go on but won't. Beleive it or not I don't think I iterated my point
as well as I could have but it will have to do.

tony

Quote:
> On Thu, 21 Nov 2002 10:05:22 -0600, "Tony Vrolyk"

> >I understand your first reply to store the info in a table.

> quarters.  Stop and think about it for a minute.

> >So I needed to calculate that quarter

> You need to *know* that quarter.  It doesn't follow that you need to
> calculate it.

> [snip]
> >I don't see why my function is a problem.
> [snip]
> >I have tested
> >it several time and so far always returns the expected results.

> No, you've *run* it several times.  Testing and running are different
> things.

> >If the Effective date is 02/01/2002 and the current date is 11/21/2002.
> >11-2 = 9
> >9 is over 0 so do nothing
> >9 resolves to quarter 3

>  ? GetFiscalQtr("21-Nov-2002", "01-Feb-2002")
>   4

> Try this.

>  ? GetFiscalQtr("01-Feb-2000", "29-Nov-1999")
>   2
>  ? DateDiff("d", "01-Feb-2000", "29-Nov-1999")
>  -64

> My first quarter has more days than that.  YMMV.

> --
> Mike Sherrill
> Information Management Systems

Sun, 15 May 2005 01:52:18 GMT
In which quarter is [date] of fiscal year?
On Tue, 26 Nov 2002 11:52:18 -0600, "Tony Vrolyk"

Quote:

>Forgive me for getting harsh but this is starting to{*filter*}me off.

[snip]

You're forgiven.

Quote:
>It took three posts until you started making any sense.

I must have missed the part where you asked me to explain what I
meant.  I'm *really* sorry I missed that part.  Really.

Let me see if I can express this in a way you'll understand.

1. Quarters are facts.
2. Don't store facts in code.
2. Store facts in tables.
3. Don't write code to calculate quarters.
4. Build a table to store your quarters.
5. Store your quarters in a table.
6. When you need to know a specific quarter, don't write code to
calculate it.  Look in up in your table.

Clear now?

--
Mike Sherrill
Information Management Systems

Fri, 27 May 2005 19:35:11 GMT
In which quarter is [date] of fiscal year?
Thanks for the fogiveness. I was getting frustrated. Much of what I said I
meant but maybe I could have gotten off my soap box a bit sooner. Sorry if I
offended you personally, I really was just ranting to the world in general.
have posted in. And of course on the whole most replies are very helpful...
anyway

Let me give you a few more details about my DB to make sure I understand
where you are suggesting storing the quarter.

tables:
Clients -> Policies -> CommissionReceipts

We have about 240 active clients with about 800 active policies. The
Policies table is where the Effective and Renewal dates are stored. These
constitute the Policy Year (similar to a fiscal year). Given that Effective
dates always fall on the 1st or 15th, that gives us 24 possible Policy
Years.

The CommissionReceipts table is where each commission received is recorded
along with its received date, accounting month, policy month and other info.
We want to know what quarter a commission receipt falls in. Typically each
policy will have 12 Receipts per year with occasional adjusting
transactions. If a policy renews it will have at least another 12 receipts
during the next year.

When renewing a policy will still be on the same 12 month cycle so any
month, even in a previous year will still resolve to the same quarter with
my function. If a policy were to change it's 'fiscal year' that would get
entered as a new separate policy.

Questions-
1. Are you suggesting storing the quarters in the policies table? Maybe
storing the first date of each quarter. Wouldn't that still require some
kind of calculation to determine in which quarter a Receipt falls?
2. Are you suggesting storing the quarter in the receipts table, for each
receipt? Again wouldn't thi still require a calculation when you write the
quarter to the CommissionReceipt record?
3. Are you suggesting storing quarters in another table altogether? If so,
what form would this take?

I don't see getting around comparing the CommissionReceipt date to something
else to determin the quarter. It seems to me the easiest to maybe store the
quarter number in the Receipts table when writing the record. But this still
requires some kind of calculation or comparison.

I think I understand your basic theory, but I am not sure how it applies
here given the number of policies and the variance in their Policy Years

Thanks
Tony

Quote:
> On Tue, 26 Nov 2002 11:52:18 -0600, "Tony Vrolyk"

> >Forgive me for getting harsh but this is starting to{*filter*}me off.
> [snip]

> You're forgiven.

> >It took three posts until you started making any sense.

> I must have missed the part where you asked me to explain what I
> meant.  I'm *really* sorry I missed that part.  Really.

> Let me see if I can express this in a way you'll understand.

> 1. Quarters are facts.
> 2. Don't store facts in code.
> 2. Store facts in tables.
> 3. Don't write code to calculate quarters.
> 4. Build a table to store your quarters.
> 5. Store your quarters in a table.
> 6. When you need to know a specific quarter, don't write code to
> calculate it.  Look in up in your table.

> Clear now?

> --
> Mike Sherrill
> Information Management Systems

Sun, 29 May 2005 06:06:06 GMT
In which quarter is [date] of fiscal year?
On Tue, 10 Dec 2002 16:06:06 -0600, "Tony Vrolyk"

Quote:

>have posted in.

That narrow distinction completely escaped me.  IAC, I doubt that
thoughtless ranting here will have any effect on the other groups.

Quote:
>Let me give you a few more details about my DB to make sure I understand
>where you are suggesting storing the quarter.

databases.  C.J. Date's _Introduction to Database Systems_ is the
functional dependencies.

Quote:
>If a policy renews it will have at least another 12 receipts
>during the next year.

Quote:
>When renewing a policy will still be on the same 12 month cycle so any
>month, even in a previous year will still resolve to the same quarter with
>my function.

If you say so.  Of course, unless you've fixed the bugs I pointed out
days ago, your function gives the wrong answer much of the time.

Quote:
>If a policy were to change it's 'fiscal year' that would get
>entered as a new separate policy.

>Questions-
>1. Are you suggesting storing the quarters in the policies table?

[snip]

Look at the functional dependencies between the columns you have and
the columns you need to add to store data about quarters.  It's not
rocket science; treat data about quarters just like you do any other
data.  I posted a reasonable guess ages ago.

--
Mike Sherrill
Information Management Systems

Sun, 05 Jun 2005 22:21:36 GMT
In which quarter is [date] of fiscal year?
Thanks

Quote:
> On Tue, 10 Dec 2002 16:06:06 -0600, "Tony Vrolyk"

groups I
> >have posted in.

> That narrow distinction completely escaped me.  IAC, I doubt that
> thoughtless ranting here will have any effect on the other groups.

> >Let me give you a few more details about my DB to make sure I understand
> >where you are suggesting storing the quarter.

> Let me suggest you buy a good book about designing relational
> databases.  C.J. Date's _Introduction to Database Systems_ is the
> functional dependencies.

> >If a policy renews it will have at least another 12 receipts
> >during the next year.

> >When renewing a policy will still be on the same 12 month cycle so any
> >month, even in a previous year will still resolve to the same quarter
with
> >my function.

> If you say so.  Of course, unless you've fixed the bugs I pointed out
> days ago, your function gives the wrong answer much of the time.

> >If a policy were to change it's 'fiscal year' that would get
> >entered as a new separate policy.

> >Questions-
> >1. Are you suggesting storing the quarters in the policies table?
> [snip]

> Look at the functional dependencies between the columns you have and
> the columns you need to add to store data about quarters.  It's not
> rocket science; treat data about quarters just like you do any other
> data.  I posted a reasonable guess ages ago.

> --
> Mike Sherrill
> Information Management Systems

Wed, 08 Jun 2005 00:02:02 GMT

 Page 1 of 2 [ 15 post ]

Relevant Pages