calculate status based on other records

I am trying to retrieve the order status of a record based on the existence
of other records, but I am having difficulty. Below is a sample of what I'm
trying to accomplish. Any help is appreciated.
To determine if an order event is returnable, I need to find if there are
any return order records for which the order event is the parent. Given the
table and data defined below, the desk is not returnable, but the hutch is
returnable. I would like to return a record set of all order events that
provides the id, type, description, and whether it is returnable, but I have
been unable to create such a query. I could also create a new field for
flag its return status, but there are several other similar situations and I
'd
prefer not to have that many extra fields (although not sure which approach
would work best).
create table [dbo].[events] (
[id] [int] NOT NULL,
[type] [varchar] (20) NOT NULL,
[description] [varchar] (50) NOT NULL,
[parent] [int] NULL
)
insert into events (id, type, description, parent) values (1, 'order',
'desk', null)
insert into events (id, type, description, parent) values (2, 'order',
'hutch', null)
insert into events (id, type, description, parent) values (3, 'return
order', 'desk', 1)"John" <someone@.nowhere.com> wrote in message
news:O0oXLkLEFHA.3976@.tk2msftngp13.phx.gbl...
> I am trying to retrieve the order status of a record based on the
existence
> of other records, but I am having difficulty. Below is a sample of what
I'm
> trying to accomplish. Any help is appreciated.
> To determine if an order event is returnable, I need to find if there are
> any return order records for which the order event is the parent. Given
the
> table and data defined below, the desk is not returnable, but the hutch is
> returnable. I would like to return a record set of all order events that
> provides the id, type, description, and whether it is returnable, but I
have
> been unable to create such a query. I could also create a new field for
> flag its return status, but there are several other similar situations and
I'd
> prefer not to have that many extra fields (although not sure which
approach
> would work best).
> create table [dbo].[events] (
> [id] [int] NOT NULL,
> [type] [varchar] (20) NOT NULL,
> [description] [varchar] (50) NOT NULL,
> [parent] [int] NULL
> )
> insert into events (id, type, description, parent) values (1, 'order',
> 'desk', null)
> insert into events (id, type, description, parent) values (2, 'order',
> 'hutch', null)
> insert into events (id, type, description, parent) values (3, 'return
> order', 'desk', 1)
>
Hello -
If I understand correctly, the desk is not returnable because it has already
been returned. So, something like this should give you a list of all the
returnable items:
SELECT id
FROM events
WHERE NOT EXISTS (SELECT id FROM events e2 WHERE e2.parent=events.id AND
e2.type='return order')
Regards,
Simon|||"Simon Shearn" <simon@.antispam.aardvarks> wrote in message
news:420d8e46$0$38040$bed64819@.news.gradwell.net...
> "John" <someone@.nowhere.com> wrote in message
> news:O0oXLkLEFHA.3976@.tk2msftngp13.phx.gbl...
> existence
> I'm
> the
> have
> I'd
> approach
> Hello -
> If I understand correctly, the desk is not returnable because it has
> already
> been returned. So, something like this should give you a list of all the
> returnable items:
> SELECT id
> FROM events
> WHERE NOT EXISTS (SELECT id FROM events e2 WHERE e2.parent=events.id AND
> e2.type='return order')
> Regards,
> Simon
>
Thanks for you response Simon.
It is not exactly what I was looking for. I don't simply want a list of
returnable items. I want a list of all orders (whether returned or not) and
their returnable status. So for the data above, I'd like the following data
returned
id type description returnable
1 'order' 'desk' no
2 'order' 'hutch' yes|||On Sat, 12 Feb 2005 07:50:30 -0500, John wrote:
(snip)
>It is not exactly what I was looking for. I don't simply want a list of
>returnable items. I want a list of all orders (whether returned or not) an
d
>their returnable status. So for the data above, I'd like the following dat
a
>returned
>id type description returnable
>1 'order' 'desk' no
>2 'order' 'hutch' yes
Hi John,
SELECT id, type, description,
CASE WHEN EXISTS (SELECT id
FROM events AS e2
WHERE e2.parent = events.id
AND e2.type='return order')
THEN 'no'
ELSE 'yes'
END AS returnable
FROM events
WHERE type = 'order'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

calculate percentage

I have a report in which there is a column callled "Login Status".The values in the Login Status column can be 'Available','Successful' and 'Error'.
I am able to get the count for each options.
For example i have a table that has 10 rows.
I am able to get number of rows that have Login Status ="Available" and so on.I am getting this using Running Total Fields.
Now i want to calculate the percentage of each options.
For example there are 10 rows.
Available count=5
Successful count=3
Error=2
So Available Percent=Available Count*100/Total.
How can i achieve this calculation?
i am placing all Running Total Field in report footer
Thank you very much in advance
Regards
JigneshAdd a formula for each porcentage calculation and place them in report footer section

To get the total of records You can use the function RecordCount|||Also, read up on the PercentOf functions, which may be useful if you are grouping on status.sql