I have forumla in crystal , how do i write same formula in sql server

[sandeep0000]

sorry brain freeze
this is a formula in crystal, how can i do the same forumla as a code in my sql server query


truncate ((today - {mc_AgeChange.DOB})/365.25)

 

[AlexCuse]

is this for age (in years)?

If so, try this:

select datediff(year, mc_AgeChange.DOB, getdate())

I have a very accurate function for returning age that is probably floating around here somewhere, I'll see if I can track it down. If you only want years though, that should suffice.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[sandeep0000]

im getting different numbers then my crystal report , with that forumla

 

[AlexCuse]

what does truncate do? does it always round down?

For example if your birthday was 06/10/1981 what does your crystal forumla return? What about 10/10/1980?


Ignorance of certain subjects is a great part of wisdom

 

[sandeep0000]

let be more clear of the forumal in crystal

there is a formula in crstal call 'age'

truncate ((today - {mc_AgeChange.DOB})/365.25)

then in my record selection i have this

{@Age} in [0.00, 17.00, 20.00, 5.00, 64.00]

 

[sandeep0000]

trucate = Use this function whenever the characters to the right of the decimal are not needed for a report or calculation.

 

[AlexCuse]

How about:

where floor(datediff(day, DOB, getdate())/365.25) in (0, 17, 20, 5, 64)

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ultrav]

Not sure if this shows all the selections/formulas but there is a menu selection (under Database menu in 8.5) called "Show SQL Query". I have found this to be very useful when trying to duplicate a selection from a crystal report.

Celeste

 

[sandeep0000]

thanks that worked with floor

i have one last one , sorry

here is my crystal formula called 'calculating aging'

If {@age in mos} = 11
then "turns 1"
else

If {@age in mos} = 71
then "turns 6"
else

If {@age in mos} = 215
then "turns 18"
else

If {@age in mos} = 251
then "turns 21"
else

If {@age in mos} = 779
then "turns 65"


in my record selection i have

not ({@Calculate Aging} like "")

im good at crystal and have ok in SQL, sorry about this i really appriciate it.

 

[sandeep0000]

any ideas on this one, this is the last thing for the report to work

 

[AlexCuse]

Hiya sandeep,

I think this might do the trick for you. I suggest you read SQL Server help on the CASE statement, it is a good one to know

[COLOR=blue]select[/color]
[COLOR=blue]case[/color] [COLOR=#FF00FF]datediff[/color]([COLOR=#FF00FF]month[/color], DOB, [COLOR=#FF00FF]getdate[/color]())
	[COLOR=blue]when[/color] 11 [COLOR=blue]then[/color] [COLOR=red]'turns 1'[/color]
	[COLOR=blue]when[/color] 71 [COLOR=blue]then[/color] [COLOR=red]'turns 6'[/color]
	[COLOR=blue]when[/color] 215 [COLOR=blue]then[/color] [COLOR=red]'turns 18'[/color]
	[COLOR=blue]when[/color] 251 [COLOR=blue]then[/color] [COLOR=red]'turns 21'[/color]
	[COLOR=blue]when[/color] 779 [COLOR=blue]then[/color] [COLOR=red]'turns 65'[/color]
	[COLOR=blue]else[/color] [COLOR=red]'N/A'[/color]
[COLOR=blue]end[/color] [COLOR=blue]as[/color] MyColumn

HOpe it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ESquared]

sandeep0000,

365.24 is more accurate than 365.25. And you know even that won't be perfectly accurate, right? Does that matter?

Would you confirm that what you're looking for is a flag when a person is going to turn certain ages within the next month?

If down-to-the-day accuracy is required,d in situations such as my birthday being on March 31 and it's February 28, is that considered one month until my birthday or should it wait to be one month until March 1?

Alex,

Datediff counts the number of boundaries crossed, not the elapsed time. Look:

datediff(yy, '2006-12-31', '2007-01-01')

is going to return 1 year. Better is:

floor(convert(float, @Date2 - @Date1) / 365.24)

which would return 0.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[ESquared]

Oh, and same thing exists with month. Using datediff will essentially give results of 11 months +-1: from 10 months + 1 day to 11 months + 30 days. It all depends on whether the report consumer is expecting information about next month, no matter what day it is in this month, or within one month from today.

~ 10 months + 1 day = 11: January 31, 2007 to December 1, 2007
~ 11 months + 30 days = 11 : January 1, 2007 to December 31, 2007

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[AlexCuse]

Thanks ESquared,

I did notice that when I tested that with years (hence the change I made), but I did not see an easy way to do this for months. Since I have not seen the formula I am trying to replicate, its' rather hard to guess what level of precision is needed.

I have a perfecly precise age calculation function (it is never off, even by a day), but it was not written by me, so I don't really want to post it as my own.

Sandeep - can you post the formula you are using now, so that we may understand the level of precision you need in this calculation?

Good LUck,

Alex

Ignorance of certain subjects is a great part of wisdom