Calculated fields in Views?

Hi
I am running an SQL server with an MS Access front-end.
One of my main forms is a list, which have a number of
calculated fields within the query that sits behind it (in
Access, not in SQL server).
I would like to bring these calculations into a View in
SQL server, to speed things up as I think this is what is
causing this list to hang for a while when it first opens.
The two calcuations are as follows:
DaysOnHold: IIf([DateUnsuspended]-[DateSuspended] Is
Null,0,[DateUnsuspended]-[DateSuspended])
WeeksActive: IIf([DateClosed] Is Null,Int((Date()-
[JobVacant]-[DaysOnHold])/7),Int(([DateClosed]-[JobVacant]-
[DaysOnHold])/7))
I've discovered that the functions used above are
incompatible with SQL server (I tried creating them
in 'Views' within Enterprise Manager but gave me all sorts
of errors.)
If anyone could assist with the correct phrasing of the
above for the VIews, I'd be extremely grateful!!
Thanks
Russell
hi russell,
since the im not sure what is your exact expression in the query , by
doing some assumption you can convert existing IIF conditions using CASE and
ISNULL functions.
IIf([DateUnsuspended]-[DateSuspended] Is
Null,0,[DateUnsuspended]-[DateSuspended])
above expression can be converted to SQL server using isnull condition.
Ex:
isnull( [DateUnsuspended]-[DateSuspended], 0)
WeeksActive: IIf([DateClosed] Is Null,Int((Date()-
[JobVacant]-[DaysOnHold])/7),Int(([DateClosed]-[JobVacant]-
[DaysOnHold])/7))
above expression can be converted to SQL server using CASE expression.
Ex:
case when [DateClosed] is null then
( datepart(dd,getdate()) - [JobVacant]-[DaysOnHold])/7
else
( datepart(dd,[DateClosed]) - [JobVacant]-[DaysOnHold])/7 end
Look in books online on the topics "date functions", CASE, ISNULL
Vishal Parkar
vgparkar@.yahoo.co.in
|||Hi Vishal
Thanks for your help. However, I have tried to enter this
expression in a View Column, but get the following error
message:
The Query Designer does not support the CASE SQL construct.
What does this mean? Is there another way to create views
which will allow me to use this CASE function?
Thanks
Russell

>--Original Message--
> hi russell,
> since the im not sure what is your exact expression in
the query , by
>doing some assumption you can convert existing IIF
conditions using CASE and
>ISNULL functions.
> IIf([DateUnsuspended]-[DateSuspended] Is
>Null,0,[DateUnsuspended]-[DateSuspended])
> above expression can be converted to SQL server using
isnull condition.
> Ex:
> isnull( [DateUnsuspended]-[DateSuspended], 0)
> WeeksActive: IIf([DateClosed] Is Null,Int((Date()-
> [JobVacant]-[DaysOnHold])/7),Int(([DateClosed]-
[JobVacant]-
> [DaysOnHold])/7))
> above expression can be converted to SQL server using
CASE expression.
> Ex:
> case when [DateClosed] is null then
> ( datepart(dd,getdate()) - [JobVacant]-[DaysOnHold])/7
> else
> ( datepart(dd,[DateClosed]) - [JobVacant]-
[DaysOnHold])/7 end
> Look in books online on the topics "date functions",
CASE, ISNULL
> --
> Vishal Parkar
> vgparkar@.yahoo.co.in
>
>.
>
|||hi russell,
Make use of Query analyzer rather than these tools. With Query analyzer
you can execute all t-sql commands and tools like "query designer" have
limited functionality.
Vishal Parkar
vgparkar@.yahoo.co.in
|||Vishal
Thanks for your help! Figured it out and it works!
Thanks again
Russell

>--Original Message--
> hi russell,
> Make use of Query analyzer rather than these tools.
With Query analyzer
>you can execute all t-sql commands and tools like "query
designer" have
>limited functionality.
> --
> Vishal Parkar
> vgparkar@.yahoo.co.in
>
>.
>

Calculated field in a Cube

Hi!

I need your help!..

I need aid to obtain a value in a calculated field, I am using cube with dimension in the date.

The idea is that as of a specific month the accumulated sum of a value is obtained in a calculated field, starting off from the month of January of he himself year that the specified month previously.

For example:

If I indicate the month of April, the sum of the value had to be:

Total = Value January + Value February + Value March + Abril Value

The previous months are of he himself year that the month of April and does not have to take values from the previous year.

If it indicated the month of December it must add the 12 months of the year to which that month corresponds I hope somebody can help me.

From already, thank you very much!

Assuming that your date dimension has its levels tagged with the appropriate time types, then you could simply use YTD(), like:

Sum(YTD([Date].CurrentMember), [Measures].[MyValue])

If the date levels aren't appropriately tagged, PeriodsToDate() is a more general function, like:

Sum(PeriodsToDate([Date].[Year].[Year], [Date].CurrentMember), [Measures].[MyValue])

CAL licenses - don't understand!

Hi
I'm in the process of migrating from hosting web sites on a shared server
and just buying SQL Server DBs when I need them, to a dedicated server which
I have to administer. So now I need to buy a 'proper' copy of SQL Server as
well as the Developer Edition version I develop on.
I read everything I could find about this on the MS site and ended up none
the wiser. If I have a one-processor server serving web sites from its own
copy of IIS, am I ok with the Standard Edition plus 5 CALs? If I host 10
sites on the server, each with its own db, does this use 1 CAL or 10?
I'm so confused!
Thanks for any clarification.Read about "multiplexing". You need to go Per Processor if you don't want to
ensure that everyone
who accesses your web site has a SQL Server CAL.
http://www.microsoft.com/sql/howtobuy/faq.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim P" <TimP@.discussions.microsoft.com> wrote in message
news:409D6E35-21E7-4E12-BE01-32BAD2382BA1@.microsoft.com...
> Hi
> I'm in the process of migrating from hosting web sites on a shared server
> and just buying SQL Server DBs when I need them, to a dedicated server whi
ch
> I have to administer. So now I need to buy a 'proper' copy of SQL Server a
s
> well as the Developer Edition version I develop on.
> I read everything I could find about this on the MS site and ended up none
> the wiser. If I have a one-processor server serving web sites from its own
> copy of IIS, am I ok with the Standard Edition plus 5 CALs? If I host 10
> sites on the server, each with its own db, does this use 1 CAL or 10?
> I'm so confused!
> Thanks for any clarification.|||"Tim P" wrote:

> Hi
> I'm in the process of migrating from hosting web sites on a shared server
> and just buying SQL Server DBs when I need them, to a dedicated server whi
ch
> I have to administer. So now I need to buy a 'proper' copy of SQL Server a
s
> well as the Developer Edition version I develop on.
> I read everything I could find about this on the MS site and ended up none
> the wiser. If I have a one-processor server serving web sites from its own
> copy of IIS, am I ok with the Standard Edition plus 5 CALs? If I host 10
> sites on the server, each with its own db, does this use 1 CAL or 10?
> I'm so confused!
> Thanks for any clarification.
If you are hosting a publicly accessible website then you need to purchase a
Processor licence.
If you are hosting an intranet site where you can determine and control the
number of users then you have the option of Processor or CAL licensing. You
need one CAL per end-user or end-user device.
Licensing issues are often difficult and consfusing so if you have any
doubts consult a Microsoft rep or authorised dealer.
David Portas
SQL Server MVP
--|||Ah, that makes sense.
Thanks guys!
"David Portas" wrote:

> "Tim P" wrote:
>
> If you are hosting a publicly accessible website then you need to purchase
a
> Processor licence.
> If you are hosting an intranet site where you can determine and control th
e
> number of users then you have the option of Processor or CAL licensing. Yo
u
> need one CAL per end-user or end-user device.
> Licensing issues are often difficult and consfusing so if you have any
> doubts consult a Microsoft rep or authorised dealer.
> --
> David Portas
> SQL Server MVP
> --
>

Caching of linked server data

Hi
I'm currently setting up a SQL Server 2000. My configuration is:
Instance MyCompany running on ServerA
Instance TheirCompany running on ServerB
TheirCompany is mapped as linked server into instance MyCompany
In my situation, the data in TheirCompany is only located on ServerB due to
legal restrictions. All data access happens through ServerA. Therefore, it
would be favourable if ServerA would keep the data of TheirCompany in its
cache and not query the remote server for every SELECT.
Can this somehow be configured. (Like saying TheirCompany.DataAccessType =
not volatile, from cache or something).
Are there any other suggestions on how to optimize performance? I'm aware of
the query plans for joins on remote tables, I'm much more looking for a way
to keep the SQL Server from fetching the data every time, as it won't have
changed FOR SURE!
Thanks in advance
Simon
posted to: microsoft.public.sqlserver.setup;
microsoft.public.sqlserver.server; microsoft.public.sqlserver.clusteringSorry, but there is no way to cache remote server data on a local server...
If you wish the data to be local you'd have to make a copy in a local
database...
You might consider making a copy in tempdb, and sharing that among users. It
will automatically be deleted when the Server is re-booted.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Simon Niederberger" <dontreply@.hotmail.com> wrote in message
news:Ojd11uL8DHA.3348@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm currently setting up a SQL Server 2000. My configuration is:
> Instance MyCompany running on ServerA
> Instance TheirCompany running on ServerB
> TheirCompany is mapped as linked server into instance MyCompany
> In my situation, the data in TheirCompany is only located on ServerB due
to
> legal restrictions. All data access happens through ServerA. Therefore, it
> would be favourable if ServerA would keep the data of TheirCompany in its
> cache and not query the remote server for every SELECT.
> Can this somehow be configured. (Like saying TheirCompany.DataAccessType =
> not volatile, from cache or something).
> Are there any other suggestions on how to optimize performance? I'm aware
of
> the query plans for joins on remote tables, I'm much more looking for a
way
> to keep the SQL Server from fetching the data every time, as it won't have
> changed FOR SURE!
> Thanks in advance
> Simon
> posted to: microsoft.public.sqlserver.setup;
> microsoft.public.sqlserver.server; microsoft.public.sqlserver.clustering
>|||Could you replicate the data from your source to the target server. As long
as the data is treated as Read-Only on the target server, this could work.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Simon Niederberger" <dontreply@.hotmail.com> wrote in message
news:Ojd11uL8DHA.3348@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm currently setting up a SQL Server 2000. My configuration is:
> Instance MyCompany running on ServerA
> Instance TheirCompany running on ServerB
> TheirCompany is mapped as linked server into instance MyCompany
> In my situation, the data in TheirCompany is only located on ServerB due
to
> legal restrictions. All data access happens through ServerA. Therefore, it
> would be favourable if ServerA would keep the data of TheirCompany in its
> cache and not query the remote server for every SELECT.
> Can this somehow be configured. (Like saying TheirCompany.DataAccessType =
> not volatile, from cache or something).
> Are there any other suggestions on how to optimize performance? I'm aware
of
> the query plans for joins on remote tables, I'm much more looking for a
way
> to keep the SQL Server from fetching the data every time, as it won't have
> changed FOR SURE!
> Thanks in advance
> Simon
> posted to: microsoft.public.sqlserver.setup;
> microsoft.public.sqlserver.server; microsoft.public.sqlserver.clustering
>