Problem in between clause

Hi to all
i have a table which in which date is storing in three separate fields
all datatype char. Values storing are like
01 in day field Mar in month field and 2005 in year fields
. now when i try to get data between two dates results are not coming as
expected.
i m using following query
SELECT a.station_desc
,sum(b.ttl_appl_visited) Total_Token_Booked
,sum(b.ttl_urgent_tokens_today) Urgent_Token
FROM tbl_Value b
left JOIN dbo.Daily_Report_Station ON
dbo.tbl_line_2.Source_ID = dbo.Daily_Report_Station.Station_ID
where report_day between '15' and '07' and report_month between 'Feb'
and 'Mar'
and report_year between '2006' and '2006'
group by a.station_desc
if i give report day value like report_day between '10' and '20' it
returns me correct value but when first value is greater no result comes
as in the query
Regards,
Farid
*** Sent via Developersdex http://www.examnotes.net ***For a start something like
where report_year = '2006'
and (( report_month = 2 and report_day >= 15 ) or
( report_month = 3 and report_day < 7 ))
If you're stuck with the design you currently have, write a UDF that takes
year, month and day as arguments and returns a datetime, then use
between dbo.MyGetDate( '2006', 'Feb', '15' ) and dbo.MyGetDate( '2006',
'Mar','07')
"Ghulam Farid" wrote:

>
> Hi to all
> i have a table which in which date is storing in three separate fields
> all datatype char. Values storing are like
> 01 in day field Mar in month field and 2005 in year fields
> . now when i try to get data between two dates results are not coming as
> expected.
> i m using following query
> SELECT a.station_desc
> ,sum(b.ttl_appl_visited) Total_Token_Booked
> ,sum(b.ttl_urgent_tokens_today) Urgent_Token
> FROM tbl_Value b
> left JOIN dbo.Daily_Report_Station ON
> dbo.tbl_line_2.Source_ID = dbo.Daily_Report_Station.Station_ID
> where report_day between '15' and '07' and report_month between 'Feb'
> and 'Mar'
> and report_year between '2006' and '2006'
> group by a.station_desc
>
> if i give report day value like report_day between '10' and '20' it
> returns me correct value but when first value is greater no result comes
> as in the query
> Regards,
> Farid
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Hi,
I'd say the problem is how you use between clause.
Between translates into pair of statements >= and <=
So first part of your condition would be
where report_day >= 15 and report_day <= 7.
This condition returns false, hence all following conditions are not parsed
afaik.
try this example
select 'test' where 2 between 2 and 4
select 'test' where 3 between 4 and 2
Also, do you want data between 07.02.2006 and 15.03.2006 or between 07 and
15 day of Feb and Mar in 2006?
HTH
Peter|||yes u r right first condition is making result false so wht would b the
apropriate condition using same data structure. and its true i want
result between 07-02-2005 and 15-03-2005 but result is not coming. any
help
*** Sent via Developersdex http://www.examnotes.net ***|||You'll have to convert the months to integers. Might be a good use of a
computed column or view with a case report_month when 'Jan' then 1 etc.
Then if you want between 15 Feb and 07 Apr, after converting the months to
integers you would write something like:
where (
( month = 2 and day >= 15) or /* handle February 15-28 */
( month > 2 and month < 4 ) or /* Mar or any other months inbetween */
(month = 4 and day <= 7 ) /* handle final month Apr */
)
"Ghulam Farid" wrote:

> yes u r right first condition is making result false so wht would b the
> apropriate condition using same data structure. and its true i want
> result between 07-02-2005 and 15-03-2005 but result is not coming. any
> help
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Stop doing this and use a DATETIME data type. One of your problems is
that you are mimicking a Cobol record, which has fields for the date
components. If you understood the concept of a column -- which is
nohting like a field -- you would not make this mistake.sql

Problem Deploying SSAS Connection cannot be made. Ensure server running

I have a couple of questions. I've gotten Our SSAS Running against SS 2000 where the Server is separate from the Web Server running IIS by installing SSAS on the Web Server and Pointing it to the instance on the Database Server. This works on both mine and my bosses systems and I finally got it working on our internal server (the described situation above, our dev computers have all running on one box).

When I implement this on one of our client configurations that is the same as the first above (DB + SSAS and connected to IIS Server with SSAS as well) I cannot get it to connect and return the data.

The error I get via my Asp.net 2.0 webpage is :

A connection cannot be made. Ensure that the server is running.

I have checked every possible setting between the two sets of servers and I'm about to lose it

Everything seems identical. Do you have any suggestions. I checked the services to see if the SSAS was running on the web server and it is.

Suggestions?

SSAS authenticates using windows authentication. In order to get an ASP.NET application to connect to SSAS, it needs to be running under an account that is in a role in SSAS that has rights to view the cube you want to query.

There are a couple of ways to do this:

For IIS6, you are best to setup an application pool running under a domain account, run your ASP.NET in that application pool and put that domain account in a role in SSAS.

For IIS5 instead of using an application pool you can set the account using the <Identity> tag in the web.config

Note: to SSAS, it will see all requests as coming from a single user so you cannot use role based security with this sort of setup. If you need role based security you would need to maybe look into setting up HTTP authentication.

|||

Ok I'm a bit confused. Sorry.

I've got on Server 1

IIS & Analysis Services

On Server 2

SQL Server 2000 & Analysis Services

As far as I got was installing Analysis Services on the Web Server.

There is an Application Pool that the website is running under but I'm not sure how to setup the Roles for that Pool.

I added user roles under the SSAS on the web server but it's still not working. Sorry for my confusion, I took over this project from another developer and have never worked with this before. Thank you so much for your assistance.

|||So why are there 2 AS servers? and which one has the database you want to connect to?

If you just want your web server to connect to the second server with AS and SQL on it then you don't need AS on the web server, you just need the OLEDB provider for Analysis Services (version 8.0 for AS 2000). The typical problem with web servers is that NTLM credentials will not do 2 hops. What this means is that the client can authenticate to the web server, but the web server cannot pass those credentials along to the second server (for security reasons).

There are actually 2 ways around this you can configure Kerberos authentication (there are a couple of whitepapers from MS on this, they should not be too hard to find). The only other thing you need is to have <Identity Impersonate="True" /> in your web.config.

Using the original method I described, I would actually create a new app pool, just for this application. In the app pool settings I think there is an Identity tab, you would set this to run as a specific domain user. Then on your AS server create a role with the same domain account and give it access to the database/cubes that the web app needs.

|||

I tried setting up Application Pool, didn't work. I'm not sure the client wants Kerberos installed on the machine. I have this working on our internal computers (2 workstations) and our development servers. I'm trying to put it on theirs and having difficulty.

I setup Roles on the AS server (I'd rather not use the same one as on the database server) on the IIS Server (running Windows Server 2003). I can connect to the DataMart and see the data. I can get to the page but it wont' connect from IIS to AS it seems.

Using <Identity...> blows it up says it's not recognized (I'm assuming you need IIS 5 or Kerberos for that to work).

I setup every way I could imagine in the App Pool settings. It defaults as Network. I tried that. Then I tried various user configurations.

So the DB server is on SQL 2000, our test was on SQL 2005.

Thanks for your help. Any more ideas?

|||

If you want to use the AS on the IIS box, then that makes things a bit easier. You only need Kerberos when IIS and AS are on separate machines.

The <Identity ...> tag is an ASP.NET setting and works for both IIS 5 and 6, but it is case sensitive and needs to be under the right section of the web.config file. But if you are using IIS 6 and don't need role based security then you are better off just using the app pool identity.

If you left the App Pool set to Network, you would need to go into the Role Manager for your database and either create a new role or add the "NETWORK" user to an existing role.

It's a lot simpler if IIS and AS are on the same box, the main "trick" is that it is the app pool account that connects to AS and needs to be in an appropriate role. If they are on the same machine you should not have trouble with firewalls and identities not crossing machine boundaries.