Need to Calculate Grade Age

Need to calculate the Grade age based on the birthdate and Nov month and 30th Day of the current year.
I have a working datediff statement but I need to always but in the current year. I would like to have the statement get the current year. Then if the age is greater than xx and less than xx your age level is "xyz"

This works DateDiff("d" [Birthdate], 11/30/2004) /365.25 will return the age.

I want to replace the 2004 with a getdate yyyy so I do not need to maintain this statement.

Thanks in advance of a reply
GaryTry this:

Declare @.birthdate as smalldatetime,@.mydate as varchar(10)
SET @.birthdate = '07/11/1978'
SET @.mydate = '11/30/' + cast(year(getdate())as varchar)
DateDiff("d", @.birthdate, @.mydate) /365.25

|||Thanks that helped me with the project.sql

Need Performance Tips - Zip Code Locator

I have a stored

procedure used to lookup ad's similar to ebay. We want to allow the customer to

search based on their zip code in relation to the items location. This query

works but it takes too long. We only want to return the top 100 records. It also

uses indexed searching for the main search terms. Any ideas what we can do to

improve performance?

CREATE PROCEDURE Search
@.SearchText

varchar(200),
@.CategoryID int = Null,
@.TxtDesc bit = 0,
@.PriceMin money

= 0,
@.PriceMax money = 250000,
@.ZipCode varchar(5) = Null,
@.Distance

smallint = 0,
@.ManMul int = 1000,
@.ModMul int = 1000,
@.TitMul int =

100,
@.DesMul int = 1
AS
BEGIN
DECLARE @.CenterLat float
DECLARE

@.CenterLon float

-- Earth Radius In Miles
DECLARE @.EarthRadius

float
SET @.EarthRadius = 3958.76

-- Determine Lat/Lon For User's Zip

Code
SELECT @.CenterLat = Lat,
@.CenterLon = Long
FROM

List_ZipCodes
WHERE Zip_Code = @.ZipCode

DECLARE @.CntXAxis

float
DECLARE @.CntYAxis float
DECLARE @.CntZAxis float

SET @.CntXAxis

= cos(radians(@.CenterLat)) * cos(radians(@.CenterLon))
SET @.CntYAxis =

cos(radians(@.CenterLat)) * sin(radians(@.CenterLon))
SET @.CntZAxis =

sin(radians(@.CenterLat))

SELECT

TOP(100)
C.Classified_ID,
Manufacturer,
Model,
Title
FROM

Classifieds C
INNER JOIN Classifieds_Categories ON C.Classified_ID =

Classifieds_Categories.Classified_ID
LEFT OUTER JOIN

FREETEXTTABLE(Classifieds, (Manufacturer), @.SearchText)AS f1 ON C.Classified_ID

= f1.[Key]
LEFT OUTER JOIN FREETEXTTABLE(Classifieds, (Model), @.SearchText)AS

f2 ON C.Classified_ID = f2.[Key]
LEFT OUTER JOIN FREETEXTTABLE(Classifieds,

(Title), @.SearchText)AS f3 ON C.Classified_ID = f3.[Key]
INNER JOIN

List_ZipCodes AS ZC ON ZC.Zip_Code = C.Shipping_FromZip
WHERE

((COALESCE(f1.[Rank], 0)* @.ManMul) + (COALESCE(f2.[Rank], 0)* @.ModMul) +

(COALESCE(f3.[Rank], 0) * @.TitMul)) > 500
AND Active = 1
AND

Price_Asking >= @.PriceMin
AND Price_Asking <= @.PriceMax
AND

(Category_ID = COALESCE(@.CategoryID, Category_ID) OR Category_ID IN (SELECT

Category_ID FROM List_Categories WHERE Parent_Category_ID = @.CategoryID))
AND

Shipping_FromZip Is Not Null
AND (@.EarthRadius * acos((cos(radians(ZC.Lat))

* cos(radians(ZC.Long)))*@.CntXAxis + (cos(radians(ZC.Lat)) *

sin(radians(ZC.Long)))*@.CntYAxis + (sin(radians(ZC.Lat)))*@.CntZAxis)) <=

@.Distance
ORDER BY ((COALESCE(f1.[Rank], 0)* @.ManMul) + (COALESCE(f2.[Rank],

0)* @.ModMul) + (COALESCE(f3.[Rank], 0) * @.TitMul)) DESC
END

The ultimate cost for this query is coming from the FTS query. Basically, sqlserver will have to wait for the result to come back for each freetexttable() before it can join to the base table. And also, the resultset returned from the freetexttable() is not indexed. So, if there are lots of data in the resultset, the main query can take a long time to join. In case you have lots of data return from FTS, you might want to create a temp/working table to hold the data. You can then index them before joining with the base table.

need MDX help on calculated weighted average based on time

I am working on a project using financial data (chart of accounts) and I need to create a calculated member(measure).

The measure is the dollar amount for the balance sheet accounts as they roll up over the time dimension, they need to be weighted by the number of days in the period. So instead of it being a normal average it should be weighted by the days in the period.

I have Number of days in Month, Quarter, and Year as attributes in the Time Dimension([DaysInMonth], [DaysInQuarter], [DaysInYear])

Here is an example:

Qtr 1 = (Jan. balance x 31)+(Feb bal x 28)+(mar bal x 31)/total number of days in the period (90 in this example)

My Measure Name is Amount and My time Dimension/Heirarchy is [DimTime].[Calendar Time] respectively.

Has anyone created a similar measure? Can you show me the MDX? This is a tight timeline and any help would be GREATLY appreciated!

DRR

While I'm certain you can pull this query off with MDX, I wonder if you might be better served by storing the component values as measures. Think about it this way, no matter where you are in the cube, you will always have to pull balance data from the lowest level, multiply it by a number specific to that month, and then roll up both the number of days in the months affected and the weighted balance before then doing a division operation. I think you might run into performance problems.

So instead, I'd suggest creating two measures. One is DaysInReportingPeriod. The other is WeightedBalance which is your balance times days. Create these in your relational data warehouse or in the DSV. Then, add the measures to the cube with aggregation set to SUM and hide them (Set Visible=False). Then, all you have to do is create a calculation that divides [Measures].[WeightedBalance] by [Measures].[DaysInReportingPeriod].

Good luck,
Bryan

Need Help: How to schedule a report that has parameter?

Hi All:
I have created a report that is based on stored procedure and also has some
parameters. Manually after entering proper parameter values, I can run the
report properly.
My questions are:
1. How can I schedule this report?
2. While scheduling this report, is there anyway I can provide the
corresponding proper parameter values so that I can get my required report?
I would appreciate your help.
Thanks.
SamHave you try to use a subscription?
"Sue" wrote:
> Hi All:
> I have created a report that is based on stored procedure and also has some
> parameters. Manually after entering proper parameter values, I can run the
> report properly.
> My questions are:
> 1. How can I schedule this report?
> 2. While scheduling this report, is there anyway I can provide the
> corresponding proper parameter values so that I can get my required report?
> I would appreciate your help.
> Thanks.
> Sam
>|||Yes. But did not work out properly. Need help.
Here is my Case
+++++++++++++++++++++++++++++
I have a common report object residing on Reporting Service. The report
shows Monthly Activity for a Department. This report is based on a Stored
Procedure and has Department ID as a parameter.
Through an application different departments either can run this report or
create a schedule so that at the end of each month, any department can get
the report related to only its data.
How can I do that?
+++++++++++++++++++++++++
"Soan" wrote:
> Have you try to use a subscription?
> "Sue" wrote:
> > Hi All:
> >
> > I have created a report that is based on stored procedure and also has some
> > parameters. Manually after entering proper parameter values, I can run the
> > report properly.
> >
> > My questions are:
> >
> > 1. How can I schedule this report?
> >
> > 2. While scheduling this report, is there anyway I can provide the
> > corresponding proper parameter values so that I can get my required report?
> >
> > I would appreciate your help.
> >
> > Thanks.
> >
> > Sam
> >
> >