Re: [Emerald] SQL query

Josh Hillman ( (no email) )
Tue, 8 Feb 2000 08:47:48 -0500

From: Network Operation Centre <don@world-link.com.au>
> Can somebody help with this query. I want to do this query but I also want
> to pull out the maExpireDate's as well but can not work out how to add it.
>
> select Login, FirstName, LastName, PhoneHome, PhoneWork, AccountType from
> SubAccounts where AccountType='Unlimited' and customerid in (it goes here
> but dunno how to add it)

SELECT Login,
Name=sa.Firstname+' '+sa.Lastname,
Phone=CASE
WHEN sa.PhoneHome =''
THEN sa.PhoneWork
WHEN sa.PhoneHome !='' and sa.PhoneWork !=''
THEN convert(varchar(35), sa.PhoneHome+' / '+sa.PhoneWork)
ELSE ma.PhoneHome
END,
AccountType,
ExpireDate=convert(varchar(10), ma.maExpireDate, 101)
FROM Subaccounts sa, MasterAccounts ma
WHERE sa.CustomerID = ma.CustomerID
AND sa.AccountType='Unlimited'
/* remove the last line if you don't want it to be specific to that account
*/

> And would anyone have this in a Crystal Report at all :-) or the
following
> for Crystal Report but instead of using accounttype= what it does can it
be
> changed to accounttype=*
>
> select Login, FirstName, LastName, PhoneHome, PhoneWork, AccountType,
> LastModifyDate, TimeLeft from SubAccounts where AccountType='casual 100'
and
> TimeLeft<120

Just replace the 'Unlimited' with 'casual 100' and add
AND sa.TimeLeft < 120

Josh

For more information about this list (including removal) go to:
http://www.iea-software.com/support/maillists/liststart