select statement for in inclusion list

Hi All:

Here is a question for you SQL gurus:

I have a table called "accountmovements' registering movements on member
accounts with the following columns (type):

ID (integer) - Primary key
Accountholder (integer)
Datemovement (date)
Amount (decimal)
Text (varchar)

I would like create a select query locating the account holders which do not
have a movement registered before a certain date with a particular text
(Let's say "Dividend" in this example"). In other words, I want to find
candidates for the next payout of dividend. In order to eligible for a
dividend one must not have received any previous dividend payment for the
past 30 days. Somehow I feel that there must be subquery involved in this
because the delimiting factor is not just any movement before certain date
and any movement with a certain text (in that case it would a simple WHERE
.... AND clause, but a criteria whereby only movements after a certain date
are examined for the said text. If one such instance is found for a
particular account holder, that person must be excluded from the resulting
list.

Any advice would be highly appreciated.

Thanks,

TB

Re: select statement for in inclusion list - CORRECTION

Sorry I made a typo:

Instead of:
> I would like create a select query locating the account holders which do
> not have a movement registered before a certain date with a particular
> text

it should be:

> I would like create a select query locating the account holders which do
> not have a movement registered AFTER a certain date with a particular text

After, not before

Thanks in advance for your kind advice.

"TB" wrote in message
news:uswdTXbrFHA.3352@TK2MSFTNGP14.phx.gbl...
> Hi All:
>
> Here is a question for you SQL gurus:
>
> I have a table called "accountmovements' registering movements on member
> accounts with the following columns (type):
>
> ID (integer) - Primary key
> Accountholder (integer)
> Datemovement (date)
> Amount (decimal)
> Text (varchar)
>
> I would like create a select query locating the account holders which do
> not have a movement registered before a certain date with a particular
> text (Let's say "Dividend" in this example"). In other words, I want to
> find candidates for the next payout of dividend. In order to eligible for
> a dividend one must not have received any previous dividend payment for
> the past 30 days. Somehow I feel that there must be subquery involved in
> this because the delimiting factor is not just any movement before certain
> date and any movement with a certain text (in that case it would a simple
> WHERE ... AND clause, but a criteria whereby only movements after a
> certain date are examined for the said text. If one such instance is found
> for a particular account holder, that person must be excluded from the
> resulting list.
>
> Any advice would be highly appreciated.
>
> Thanks,
>
> TB
>

Re: select statement for in inclusion list

TB wrote:
> Hi All:
>
> Here is a question for you SQL gurus:
>
> I have a table called "accountmovements' registering movements on

What database? I will assume SQL Server, but you should always include
database type and version when asking for query help.

> member accounts with the following columns (type):
>
> ID (integer) - Primary key
> Accountholder (integer)
> Datemovement (date)

Do you mean "datetime"? Or is my assumption about SQL Server incorrect?

> Amount (decimal)
> Text (varchar)
>
> I would like create a select query locating the account holders which
> do not have a movement registered before

I know. You meant "after".

> a certain date with a
> particular text (Let's say "Dividend" in this example"). In other
> words, I want to find candidates for the next payout of dividend. In
> order to eligible for a dividend one must not have received any
> previous dividend payment for the past 30 days.

This is getting confusing. Do you want to compare to "a certain date", or do
you wish it to look at the last 30 days?

I'm going to assume the latter.

> Somehow I feel that
> there must be subquery involved in this because the delimiting factor
> is not just any movement before certain date and any movement with a
> certain text (in that case it would a simple WHERE ... AND clause,
> but a criteria whereby only movements after a certain date are
> examined for the said text. If one such instance is found for a
> particular account holder, that person must be excluded from the
> resulting list.
>
Sample data in the form of INSERT statements (www.aspfaq.com/5006) would be
helpful. As well as the results you wish to be retrieved from the sample
data.

Will this work?

--u=unpaid, p=paid
SELECT Accountholder u FROM
accountmovements u left join accountmovements p
ON u.Accountholder = p.Accountholder AND
p.Datemovement >= DATEADD(d,-30,GETDATE())
WHERE u.[Text]='Dividend' AND
p.[Text]='Dividend' AND
p.Accountholder IS NULL

If not, provide some sample data.

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: select statement for in inclusion list

Hello Bob:

As always, you - Bob - are the man on the spot answering my questions
in this news group. Thanks a lot!

First things first:
The subject is an ASP page accessing a MySQL database.
The following is a dump of the table:
# Dumping Table Structure for Movements

#
CREATE TABLE `Accountmovements` (
`ID` int(11) NOT NULL auto_increment,
`Datemovement` datetime default NULL,
`Accountholder` int(11) default '0',
`Amount` double default '0',
`Text` varchar(50) default NULL,
PRIMARY KEY (`ID`),
KEY `ID` (`ID`),
KEY `ID` (`ID`)
) TYPE=MyISAM;
#
# Dumping Data for Accountmovements
#
INSERT INTO `Accountmovements` (ID, Datemovement, ID, Amount, Text)
VALUES (86, '2005-08-15 00:00:00', 21, 28, 'Earnings');
INSERT INTO `Accountmovements` (ID, Datemovement, ID, Amount, Text)
VALUES (87, '2005-08-15 00:00:00', 12, 1, 'Dividend');
INSERT INTO `Accountmovements` (ID, Datemovement, ID, Amount, Text)
VALUES (88, '2005-08-15 00:00:00', 4, 100, 'Earnings');
INSERT INTO `Accountmovements` (ID, Datemovement, ID, Amount, Text)
VALUES (89, '2005-08-15 00:00:00', 14, 45, 'Dividend');
INSERT INTO `Accountmovements` (ID, Datemovement, ID, Amount, Text)
VALUES (90, '2005-08-18 00:00:00', 6, 80.25, 'Earnings');
INSERT INTO `Accountmovements` (ID, Datemovement, ID, Amount, Text)
VALUES (91, '2005-08-18 00:00:00', 14, 12.84, 'Dividend');
INSERT INTO `Accountmovements` (ID, Datemovement, ID, Amount, Text)
VALUES (96, '2005-08-30 00:00:00', 6, -50, 'Payment');
INSERT INTO `Accountmovements` (ID, Datemovement, ID, Amount, Text)
VALUES (97, '2005-08-29 00:00:00', 3, 750, 'Earnings');
INSERT INTO `Accountmovements` (ID, Datemovement, ID, Amount, Text)
VALUES (98, '2005-08-29 00:00:00', 24, 285, 'Earnings');
INSERT INTO `Accountmovements` (ID, Datemovement, ID, Amount, Text)
VALUES (99, '2005-08-29 00:00:00', 12, 150, 'Dividend');
INSERT INTO `Accountmovements` (ID, Datemovement, ID, Amount, Text)
VALUES (100, '2005-08-29 00:00:00', 19, 500, 'Earnings');

In this specific example, the cut off date is 2005/08/20, which means
that only accountholders which have not had any movements with
'Dividend' as Text after August 20th shall be selected. Therefore the
resulting SQL statement should return all the values in the ID column
EXCEPT 12.

Any advice would be greatly appreciated.

TB

Re: select statement for in inclusion list

TB wrote:
> Hello Bob:
>
> As always, you - Bob - are the man on the spot answering my questions
> in this news group. Thanks a lot!
>
> First things first:
> The subject is an ASP page accessing a MySQL database.

Uh-oh. I have no experience with MySQL. I hope you can handle T-SQL syntax.

> In this specific example, the cut off date is 2005/08/20, which means
> that only accountholders which have not had any movements with
> 'Dividend' as Text after August 20th shall be selected. Therefore the
> resulting SQL statement should return all the values in the ID column
> EXCEPT 12.

12 is not a value in the ID column in any row. I assume you mean
"accountholders column", in which case, this query seems to work:

SELECT distinct u.Accountholder FROM
accountmovements u left join accountmovements p
ON u.Accountholder = p.Accountholder AND
p.[Text]='Dividend' AND
p.Datemovement >= '20050820'
WHERE
p.Accountholder IS NULL
ORDER BY u.Accountholder

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: select statement for in inclusion list

Your are right about the confused column names. The INSERT statements
should be like:
INSERT INTO `Accountmovements` (ID, Datemovement, Accountholder, ID,
Amount, Text) VALUES (86, '2005-08-15 00:00:00', 21, 28, 'Earnings');,
(etc, etc)

Adapting to the syntax used for MySQL, I have just run the following
version of your proposal:

SELECT DISTINCT u.Accountholder FROM
accountmovements AS u left join accountmovements AS p
ON u.Accountholder = p.Accountholder AND
p.Text='Dividend' AND
p.Datemovement >= '2005/08/20'
WHERE p.Accountholder IS NULL
ORDER BY u.Accountholder

The result was:
3
4
6
12
14
19
21
24

In other words, ALL the accountholders were returned, including
accountholder 12, who is NOT eligible because he received a dividend on
August 29th.

TB

Re: select statement for in inclusion list

Once again, I typed too fast. The insert statements should be like:

INSERT INTO `Accountmovements` (ID, Datemovement, Accountholder,
Amount, Text) VALUES (86, '2005-08-15 00:00:00', 21, 28, 'Earnings');

Thanks again.

TB

Re: select statement for in inclusion list

TB wrote:
> Your are right about the confused column names. The INSERT statements
> should be like:
> INSERT INTO `Accountmovements` (ID, Datemovement, Accountholder, ID,
> Amount, Text) VALUES (86, '2005-08-15 00:00:00', 21, 28, 'Earnings');,
> (etc, etc)
>
> Adapting to the syntax used for MySQL, I have just run the following
> version of your proposal:
>
> SELECT DISTINCT u.Accountholder FROM
> accountmovements AS u left join accountmovements AS p
> ON u.Accountholder = p.Accountholder AND
> p.Text='Dividend' AND
> p.Datemovement >= '2005/08/20'
> WHERE p.Accountholder IS NULL
> ORDER BY u.Accountholder
>
> The result was:
> 3
> 4
> 6
> 12
> 14
> 19
> 21
> 24
>
> In other words, ALL the accountholders were returned, including
> accountholder 12, who is NOT eligible because he received a dividend
> on August 29th.
>
That's strange. Here are the results I get:
Accountholder
3
4
6
14
19
21
24


Here is the result of select *:
ID Datemovement Accountholder Amount Text
86 8/15/2005 12:00:00.000 AM 21 28.0 Earnings
87 8/15/2005 12:00:00.000 AM 12 1.0 Dividend
88 8/15/2005 12:00:00.000 AM 4 100.0 Earnings
89 8/15/2005 12:00:00.000 AM 14 45.0 Dividend
90 8/18/2005 12:00:00.000 AM 6 80.25 Earnings
91 8/18/2005 12:00:00.000 AM 14 12.84 Dividend
96 8/30/2005 12:00:00.000 AM 6 -50.0 Payment
97 8/29/2005 12:00:00.000 AM 3 750.0 Earnings
98 8/29/2005 12:00:00.000 AM 24 285.0 Earnings
99 8/29/2005 12:00:00.000 AM 12 150.0 Dividend
100 8/29/2005 12:00:00.000 AM 19 500.0 Earnings

I really don't know why the MySQL version is not doing what the T-SQL
version does. I think you're going to have to run this by a MySQL forum or
newsgroup. Maybe a subquery will work:

SELECT DISTINCT u.Accountholder FROM
accountmovements AS u left join
(select Accountholder from accountmovements
WHERE Text='Dividend' AND
Datemovement >= '2005/08/20') AS p
ON u.Accountholder = p.Accountholder
WHERE p.Accountholder IS NULL
ORDER BY u.Accountholder

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: select statement for in inclusion list

Dear Bob:

Your first solution does actually once correctly adapted the syntaxis
to MySQL. Thanks a lot for your help.

TB

Re: select statement for in inclusion list

Hello again:

Two follow-up questions:

1) The weakness of this statement is that it only tests for
accountholders already present in accountmovements. Therefore I would
like to join with another table called "accountholders" so as to
include the users who have no history, i.e. have had no
accountmovements, and therefore qualified as well.

The structure of the table accountholders is
IDuser (int) - Primary Key
Lastname (varchar)
Firstname (varchar)

2) Alongside each qualified accountholder I need to show the date
(Datemovement) of the last time they received dividend. My problem is
that I would to return some kind of text (i.e "no history") whenever an
accountholder is not present in accountmovements, i.e. has had no
movements at all, OR simply has not had any movements with the text
"dividend" in the Text column.

Thanks

TB

Re: select statement for in inclusion list

And one more thing: the table of accountholders also countains a
numeric field (int) called accountbalance, which should test for a
certain minimum amount, i.e an accountholder must hold more than for
example 100 to be qualified.

TB

Re: select statement for in inclusion list

TB wrote:
> And one more thing: the table of accountholders also countains a
> numeric field (int) called accountbalance, which should test for a
> certain minimum amount, i.e an accountholder must hold more than for
> example 100 to be qualified.
>
> TB

Let's see some sample data (in the form of INSERT statements) and desired
results.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: select statement for in inclusion list

Here we go:
CREATE TABLE `accountholders` (
`IDuser` int(11) NOT NULL auto_increment,
`Firstname` varchar(20) default NULL,
`Lastname` varchar(20) default NULL,
`Balance` double(22,2) default '0.00',
PRIMARY KEY (`IDuser`)
) TYPE=MyISAM AUTO_INCREMENT=25 ;

INSERT INTO `accountholders` VALUES (2, 'Tom', 'Baker', 0.00);
INSERT INTO `accountholders` VALUES (3, 'Fellow', 'Citizen', 750.00);
INSERT INTO `accountholders` VALUES (4, 'John', 'Doe', 100.00);
INSERT INTO `accountholders` VALUES (6, 'Trial', 'User', 30.25);
INSERT INTO `accountholders` VALUES (12, 'Paul', 'Admin', 151.00);
INSERT INTO `accountholders` VALUES (14, 'Oscar', 'Trevor', 57.84);
INSERT INTO `accountholders` VALUES (15, 'Joe', 'Poker', 0.00);
INSERT INTO `accountholders` VALUES (16, 'John', 'Bridge', 0.00);
INSERT INTO `accountholders` VALUES (17, 'Jimmy', 'Another', 0.00);
INSERT INTO `accountholders` VALUES (18, 'Carlos', 'Yet Another',
0.00);
INSERT INTO `accountholders` VALUES (19, 'Peter', 'A tester', 500.00);
INSERT INTO `accountholders` VALUES (20, 'testy', 'test', 0.00);
INSERT INTO `accountholders` VALUES (21, 'Peter', 'Check', 28.00);
INSERT INTO `accountholders` VALUES (23, 'Hank', 'Guy', 0.00);
INSERT INTO `accountholders` VALUES (24, 'Paul', 'Testerfield',
285.00);

Thanks,

TB

Re: select statement for in inclusion list

TB wrote:
> Here we go:

I won't be able to get to this until tomorrow, but in the meantime, could
you post the desired results you wish to see from this sample data?

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: select statement for in inclusion list

For the sake of this example the qualifying accountholders must
1) Have a minimum balance of 100 (in accountholders.balance) AND
2) Have no account movements after August 20th with the text 'Dividend'
(in accountmovements.text), or simply no account movements at all (i.e.
not present in the accountmovements table)

The resulting out put should list IDuser (from whatever table you
prefer), Firstname, Lastname and Balance from the table accountholders,
as well as the date of the last account movement with the text
'Dividend'. In case of no previous movements (before the qualifying
cut-off date), the resulting text should be 'No Dividend history'.

Based on the above, the result of the SQL statement should be:

IDuser, Firstname, Lastname, Balance, Datemovement

3, Fellow, Citizen, 750.00, No dividend history
4, John, Doe, 100.00, No dividend history
6, Trial, User, 30.25, No dividend history
14, Oscar, Trevor, 57.84, 2005/08/18
19, Peter, A Tester, 500.00, No dividend history
21, Peter, Check, 28.00, No dividend history
24, Paul, Testerfield, 285.00, No dividend history

Thanks

Re: select statement for in inclusion list

TB wrote:
> For the sake of this example the qualifying accountholders must
> 1) Have a minimum balance of 100 (in accountholders.balance) AND
> 2) Have no account movements after August 20th with the text
> 'Dividend' (in accountmovements.text), or simply no account movements
> at all (i.e. not present in the accountmovements table)
>
> The resulting out put should list IDuser (from whatever table you
> prefer), Firstname, Lastname and Balance from the table
> accountholders, as well as the date of the last account movement with
> the text 'Dividend'. In case of no previous movements (before the
> qualifying cut-off date), the resulting text should be 'No Dividend
> history'.
>
> Based on the above, the result of the SQL statement should be:
>
> IDuser, Firstname, Lastname, Balance, Datemovement
>
> 3, Fellow, Citizen, 750.00, No dividend history
> 4, John, Doe, 100.00, No dividend history
> 6, Trial, User, 30.25, No dividend history
> 14, Oscar, Trevor, 57.84, 2005/08/18
> 19, Peter, A Tester, 500.00, No dividend history
> 21, Peter, Check, 28.00, No dividend history
> 24, Paul, Testerfield, 285.00, No dividend history
>
> Thanks

Hmm, it's hard to reconcile your desired results with your description, but
here's my attempt.

For your first two requirements (no dividend movements after the cutoff date
AND balance >= 100) this statement should produce those results

SELECT distinct
u.Accountholder
,h.Firstname
,h.Lastname
,Balance
,'No dividend history' LastDividendMovement
FROM
accountmovements u left join accountmovements p
ON u.Accountholder = p.Accountholder AND
p.[Text]='Dividend' AND
p.Datemovement >= '20050820'
INNER JOIN Accountholders h
ON u.Accountholder=h.IDUser AND Balance >= 100
WHERE
p.Accountholder IS NULL

3 Fellow Citizen 750.00 No dividend history
4 John Doe 100.00 No dividend history
19 Peter A tester 500.00 No dividend history
24 Paul Testerfield 285.00 No dividend history


This statement fulfills your other requirement (no movements at all):
SELECT distinct
h.IDUser
,h.Firstname
,h.Lastname
,Balance
,'No dividend history' LastDividendMovement
FROM
Accountholders h left join accountmovements m
ON h.IDUser= m.Accountholder
WHERE
m.Accountholder IS NULL
ORDER BY IDUser

However, this results in too many rows:
2 Tom Baker .00 No dividend history
15 Joe Poker .00 No dividend history
16 John Bridge .00 No dividend history
17 Jimmy Another .00 No dividend history
18 Carlos Yet Another .00 No dividend history
20 testy test .00 No dividend history
23 Hank Guy .00 No dividend history

So I'm misinterpreting your description. Let's see, did you mean:
(balance >=100 AND no dividends after 8/19)
OR
(balance >=100 AND no movements at all)
?

No, there are two objections to this interpretation:
1. No holders meet the second requirement (all of the holders with no
movements have 0.00 balances)
2. It would exclude Trial User and oscar Trevor.

I guess you need to clarify your requirements. I do not understand why
holders 6, 14 and 21 are included in the results.

Wait ... Could this be what you mean?
(balance >=100 AND no dividends on or after 8/20)
OR
(balance > 0 AND no "Dividend" movements at all)

If so, this statement gets the second requirement:
SELECT DISTINCT IDuser,
Firstname,
Lastname,
Balance,
'No dividend history'
FROM dbo.accountholders h join
Accountmovements m
ON h.IDUser=m.Accountholder
WHERE Balance > 0 AND [Text] != 'Dividend'

Here are the results:
3 Fellow Citizen 750.00 No dividend history
4 John Doe 100.00 No dividend history
6 Trial User 30.25 No dividend history
19 Peter A tester 500.00 No dividend history
21 Peter Check 28.00 No dividend history
24 Paul Testerfield 285.00 No dividend history


So that gets us 6 and 21

Now, why is user 14 included? Could this be the correct interpretation of
your requirements?

(balance >=100 AND no dividends on or after 8/20)
OR
(balance > 0 AND no "Dividend" movements at all)
OR
(balance > 0 AND at least one 'Dividend movement prior to 8/20)
? Let's see. Here's the statement for the final requirement:
SELECT DISTINCT IDuser,
Firstname,
Lastname,
Balance,
CAST(LastDividend AS varchar(11))
FROM dbo.accountholders h join (
SELECT Accountholder, MAX(Datemovement) LastDividend
FROM Accountmovements
WHERE [Text] = 'Dividend'
GROUP BY Accountholder
HAVING MAX(Datemovement) < '20050820'
) AS m
ON h.IDUser=m.Accountholder
WHERE Balance > 0

which results in:
14 Oscar Trevor 57.84 Aug 18 2005


Hmm, this seems to do it. let's put them all together in a UNION query:

SELECT distinct
u.Accountholder Accountholder
,h.Firstname
,h.Lastname
,Balance
,'No dividend history' LastDividendMovement
FROM
accountmovements u left join accountmovements p
ON u.Accountholder = p.Accountholder AND
p.[Text]='Dividend' AND
p.Datemovement >= '20050820'
INNER JOIN Accountholders h
ON u.Accountholder=h.IDUser AND Balance >= 100
WHERE
p.Accountholder IS NULL
UNION
SELECT DISTINCT IDuser,
Firstname,
Lastname,
Balance,
CAST(LastDividend AS varchar(11))
FROM dbo.accountholders h join (
SELECT Accountholder, MAX(Datemovement) LastDividend
FROM Accountmovements
WHERE [Text] = 'Dividend'
GROUP BY Accountholder
HAVING MAX(Datemovement) < '20050820'
) AS m
ON h.IDUser=m.Accountholder
WHERE Balance > 0
UNION
SELECT DISTINCT IDuser,
Firstname,
Lastname,
Balance,
'No dividend history'
FROM dbo.accountholders h join
Accountmovements m
ON h.IDUser=m.Accountholder
WHERE Balance > 0 AND [Text] != 'Dividend'
ORDER BY Accountholder

Running this results in:
3 Fellow Citizen 750.00 No dividend history
4 John Doe 100.00 No dividend history
6 Trial User 30.25 No dividend history
14 Oscar Trevor 57.84 Aug 18 2005
19 Peter A tester 500.00 No dividend history
21 Peter Check 28.00 No dividend history
24 Paul Testerfield 285.00 No dividend history

which does fit your intended results ...


Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: select statement for in inclusion list

Once again, thanks for a very thorough answer.

As for my requirements you are correct that they are:
(balance >=3D100 AND no dividends on or after 8/20)

But
(balance > 0 AND no "Dividend" movements at all)
is not correct, because the qualifying accountholders must ALWAYS have
a balance of 100

What I don=B4t know is whether it is sufficient to simply stick to the
first one condition or whether a combined one is necessary like this:
(balance >=3D100 AND no dividends on or after 8/20)
OR
(balance >=3D 100 AND no "Dividend" movements at all)

Shouldn=B4t be necessary, huh?

The last one:
(balance > 0 AND at least one 'Dividend movement prior to 8/20)
is not correct either, which is why you are RIGHT when you suggest that
user/account holder 14 should NOT be included in the final result
because he doesn=B4t satisfy the balance condition (his account balance
is 57.84)=20

Does that simply the final SQL statement?

TB

Re: select statement for in inclusion list

TB wrote:
> Once again, thanks for a very thorough answer.
>
> As for my requirements you are correct that they are:
> (balance >=100 AND no dividends on or after 8/20)
>
> But
> (balance > 0 AND no "Dividend" movements at all)
> is not correct, because the qualifying accountholders must ALWAYS have
> a balance of 100
>
> What I don´t know is whether it is sufficient to simply stick to the
> first one condition or whether a combined one is necessary like this:
> (balance >=100 AND no dividends on or after 8/20)
> OR
> (balance >= 100 AND no "Dividend" movements at all)
>
> Shouldn´t be necessary, huh?

I don't know. You're the only one who can answer that (or perhaps your
client should be involved in answering the question). I've shown you the sql
statements that handle each requirement. As you see, each statement returns
a different set of results. It's up to you to decide which one to use (or if
you should use both in a union query).

>
> The last one:
> (balance > 0 AND at least one 'Dividend movement prior to 8/20)
> is not correct either, which is why you are RIGHT when you suggest
> that user/account holder 14 should NOT be included in the final result
> because he doesn´t satisfy the balance condition (his account balance
> is 57.84)
>
> Does that simply the final SQL statement?
>
I don't know. What if holder 14 had a balance of 157.84. Wouldn't you need
to have that final statement to show his last dividend movement?

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: select statement for in inclusion list

Yes. If accountholder 14 has a balance of 157.84, he would be eligible
because he would fulfill the condition:
(balance >=100 AND no dividends on or after 8/20)

TB

Re: select statement for in inclusion list

TB wrote:
> Yes. If accountholder 14 has a balance of 157.84, he would be eligible
> because he would fulfill the condition:
> (balance >=100 AND no dividends on or after 8/20)
>
> TB
Right, but that still means that you need the final sql statement to get his
last dividend movement date, right?

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: select statement for in inclusion list

TB wrote:
> Yes. If accountholder 14 has a balance of 157.84, he would be eligible
> because he would fulfill the condition:
> (balance >=100 AND no dividends on or after 8/20)
>
> TB
Oh wait! So it's no longer a requirement to get the last dividend movement
date?
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: select statement for in inclusion list

Yes, that right. But please remember that with the current data,
account holder 14 does not hold an account balance of at least 100 (it
was just a supposition in response to your question), so he should not
be included. In fact account holders 6 and 21 should not be included
either due to insufficient account balances.

So to summarize (hope to get right this time):

The condition to qualify is:
balance >=100 AND no dividends on or after 8/20
Desired result:

IDuser, Firstname, Lastname, Balance, Datemovement

3, Fellow, Citizen, 750.00, No dividend history
4, John, Doe, 100.00, No dividend history
19, Peter, A Tester, 500.00, No dividend history
24, Paul, Testerfield, 285.00, No dividend history

If the accountbalance of account holder 14 is 157.84, the desired
result would be:

IDuser, Firstname, Lastname, Balance, Datemovement

3, Fellow, Citizen, 750.00, No dividend history
4, John, Doe, 100.00, No dividend history
14, Oscar, Trevor, 157.84, 2005/08/18
19, Peter, A Tester, 500.00, No dividend history
24, Paul, Testerfield, 285.00, No dividend history

Thanks

Re: select statement for in inclusion list

TB wrote:
> Yes, that right. But please remember that with the current data,
> account holder 14 does not hold an account balance of at least 100 (it
> was just a supposition in response to your question), so he should not
> be included. In fact account holders 6 and 21 should not be included
> either due to insufficient account balances.
>
> So to summarize (hope to get right this time):
>
> The condition to qualify is:
> balance >=100 AND no dividends on or after 8/20
> Desired result:
>
> IDuser, Firstname, Lastname, Balance, Datemovement
>
> 3, Fellow, Citizen, 750.00, No dividend history
> 4, John, Doe, 100.00, No dividend history
> 19, Peter, A Tester, 500.00, No dividend history
> 24, Paul, Testerfield, 285.00, No dividend history
>
> If the accountbalance of account holder 14 is 157.84, the desired
> result would be:
>
> IDuser, Firstname, Lastname, Balance, Datemovement
>
> 3, Fellow, Citizen, 750.00, No dividend history
> 4, John, Doe, 100.00, No dividend history
> 14, Oscar, Trevor, 157.84, 2005/08/18
> 19, Peter, A Tester, 500.00, No dividend history
> 24, Paul, Testerfield, 285.00, No dividend history
>
> Thanks
So you're all set then, right? You should be able to modify the sql
statements I gave you to provide this result, right?

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: select statement for in inclusion list

After running around a lot, mostly due to my inability to explain
myself clearly enough, this statement of yours (slightly adapted to
MySQL) does work:

SELECT DISTINCT u.accountholder, h.Firstname, h.Lastname, h.Balance,
'No dividend history' AS LastDividendMovement
FROM Accountmovements AS u
LEFT JOIN Accountmovements AS p ON u.Accountholder = p.Accountholder
AND p.Text = 'Dividend'
AND p.Datemovement >= '2005/08/20'
INNER JOIN accountholders AS h ON u.Accountholder = h.IDUser
AND h.Balance >=100
WHERE p.Accountholder IS NULL

Before changing the Balance of account holder 14, it correctly returns:

accountholder Firstname Lastname Balance LastDividendMovement
4 John Doe 100 No dividend history
3 Fellow Citizen 750 No dividend history
24 Paul Testerfield 285 No dividend history
19 Phillip A tester 500 No dividend history

However, after changing the balance of account holder 14 to 157.84 the
statement returns:

accountholder Firstname Lastname Balance LastDividendMovement
4 John Doe 100 No dividend history
14 Orni ORNI Tradal 157,84 No dividend history
3 Fellow Citizen 750 No dividend history
24 Paul Testerfield 285 No dividend history
19 Phillip A tester 500 No dividend history

Which is RIGHT, except that for account holder 14 it should say
"2005/08/18" (never mind about the date format) instead of "No dividend
history" because this account holder did receive dividend on August
18th - before the cut-off date.

Any suggestions?

TB

Re: select statement for in inclusion list

TB wrote:
> After running around a lot, mostly due to my inability to explain
> myself clearly enough, this statement of yours (slightly adapted to
> MySQL) does work:
>
> SELECT DISTINCT u.accountholder, h.Firstname, h.Lastname, h.Balance,
> 'No dividend history' AS LastDividendMovement
> FROM Accountmovements AS u
> LEFT JOIN Accountmovements AS p ON u.Accountholder = p.Accountholder
> AND p.Text = 'Dividend'
> AND p.Datemovement >= '2005/08/20'
> INNER JOIN accountholders AS h ON u.Accountholder = h.IDUser
> AND h.Balance >=100
> WHERE p.Accountholder IS NULL
>
> Before changing the Balance of account holder 14, it correctly
> returns:
>
> accountholder Firstname Lastname Balance LastDividendMovement
> 4 John Doe 100 No dividend history
> 3 Fellow Citizen 750 No dividend history
> 24 Paul Testerfield 285 No dividend history
> 19 Phillip A tester 500 No dividend history
>
> However, after changing the balance of account holder 14 to 157.84 the
> statement returns:
>
> accountholder Firstname Lastname Balance LastDividendMovement
> 4 John Doe 100 No dividend history
> 14 Orni ORNI Tradal 157,84 No dividend history
> 3 Fellow Citizen 750 No dividend history
> 24 Paul Testerfield 285 No dividend history
> 19 Phillip A tester 500 No dividend history
>
> Which is RIGHT, except that for account holder 14 it should say
> "2005/08/18" (never mind about the date format) instead of "No
> dividend history" because this account holder did receive dividend on
> August 18th - before the cut-off date.
>
> Any suggestions?
>
Before I spend any more time on this, I want you to clarify your
requirements. They now seem to be:
All holders with balance >= 100 with no "dividend" activity after 8/20
If they have no "dividend" movements at all, display "no dividend history"
otherwise, display date of last "dividend" movement.

Is this correct?

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: select statement for in inclusion list

Yes. Exactly.

Re: select statement for in inclusion list

Bob Barrows [MVP] wrote:
> TB wrote:
>> After running around a lot, mostly due to my inability to explain
>> myself clearly enough, this statement of yours (slightly adapted to
>> MySQL) does work:
>>
>> SELECT DISTINCT u.accountholder, h.Firstname, h.Lastname, h.Balance,
>> 'No dividend history' AS LastDividendMovement
>> FROM Accountmovements AS u
>> LEFT JOIN Accountmovements AS p ON u.Accountholder = p.Accountholder
>> AND p.Text = 'Dividend'
>> AND p.Datemovement >= '2005/08/20'
>> INNER JOIN accountholders AS h ON u.Accountholder = h.IDUser
>> AND h.Balance >=100
>> WHERE p.Accountholder IS NULL
>>
>> Before changing the Balance of account holder 14, it correctly
>> returns:
>>
>> accountholder Firstname Lastname Balance LastDividendMovement
>> 4 John Doe 100 No dividend history
>> 3 Fellow Citizen 750 No dividend history
>> 24 Paul Testerfield 285 No dividend history
>> 19 Phillip A tester 500 No dividend history
>>
>> However, after changing the balance of account holder 14 to 157.84
>> the statement returns:
>>
>> accountholder Firstname Lastname Balance LastDividendMovement
>> 4 John Doe 100 No dividend history
>> 14 Orni ORNI Tradal 157,84 No dividend history
>> 3 Fellow Citizen 750 No dividend history
>> 24 Paul Testerfield 285 No dividend history
>> 19 Phillip A tester 500 No dividend history
>>
>> Which is RIGHT, except that for account holder 14 it should say
>> "2005/08/18" (never mind about the date format) instead of "No
>> dividend history" because this account holder did receive dividend on
>> August 18th - before the cut-off date.
>>
>> Any suggestions?
>>
> Before I spend any more time on this, I want you to clarify your
> requirements. They now seem to be:
> All holders with balance >= 100 with no "dividend" activity after 8/20
> If they have no "dividend" movements at all, display "no dividend
> history" otherwise, display date of last "dividend" movement.
>
> Is this correct?
>
This seems to do it:

SELECT
IDUser,
Firstname,
Lastname,
Balance,
COALESCE(CONVERT(varchar(11),LastDividendMovement),
'No dividend history') LastDividendMovement
FROM AccountHolders h LEFT JOIN (
SELECT
Accountholder,
MAX(Datemovement) LastDividendMovement
FROM dbo.Accountmovements
WHERE [Text]='Dividend'
GROUP BY Accountholder) d
ON h.IDUser= d.Accountholder
WHERE Balance >=100
AND COALESCE(LastDividendMovement,'19000101') < '20050820'
ORDER BY IDUser

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: select statement for in inclusion list

I have a problem with adapting to MySQL this part of your statement:
COALESCE(CONVERT(varchar(11),LastDividendMovement),
'No dividend history') LastDividendMovement

By "CONVERT", do you mean "CAST"?

Re: select statement for in inclusion list

TB wrote:
> I have a problem with adapting to MySQL this part of your statement:
> COALESCE(CONVERT(varchar(11),LastDividendMovement),
> 'No dividend history') LastDividendMovement
>
> By "CONVERT", do you mean "CAST"?
Yes. In T-SQL, the same datatype must be returned by each conditional part
of the CASE or COALESCE statement.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: select statement for in inclusion list

OK. I would also like to know the meaning of:

AND COALESCE(LastDividendMovement,'19000101') < '20050820'

TB

Re: select statement for in inclusion list

Also:

GROUP BY Accountholder) d

The final "d", is that an alias for the entire join, or
dbo.accountmovements?. In the latter case shouldn=B4t be placed next to
the table, i.e:
FROM dbo.Accountmovements as d

Re: select statement for in inclusion list

TB wrote:
> OK. I would also like to know the meaning of:
>
> AND COALESCE(LastDividendMovement,'19000101') < '20050820'
>
> TB
COALESCE is a function that returns the first non-null argument. So, if
LastDividendMovement contains Null, '19000101' is returned.
It is equivalent to:
AND CASE WHEN LastDividendMovement Is Null THEN
'19000101' ELSE LastDividendMovement END < '20050820'

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: select statement for in inclusion list

TB wrote:
> Also:
>
> GROUP BY Accountholder) d
>
> The final "d", is that an alias for the entire join, or
> dbo.accountmovements?. In the latter case shouldn´t be placed next to
> the table, i.e:
> FROM dbo.Accountmovements as d
No. The alias applies to the virtual table returned by the subquery, so the
alias has to be after the parenthesis that delimits the subquery. Think of
it like this:

FROM accountholders h left join
(...) d on ...
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: select statement for in inclusion list

TB wrote:
> Also:
>
> GROUP BY Accountholder) d
>
> The final "d", is that an alias for the entire join, or
> dbo.accountmovements?. In the latter case shouldn´t be placed next to
> the table, i.e:
> FROM dbo.Accountmovements as d

Another way to think about this is by creating a view (MySQL supports views,
correct?) from the subquery, which you should probably do if you need to use
this subquery in more than one query.

Like this:

CREATE VIEW LastDivMovePerHolder AS
SELECT
Accountholder,
MAX(Datemovement) LastDividendMovement
FROM dbo.Accountmovements
WHERE [Text]='Dividend'
GROUP BY Accountholder


Run the script, then, change the sql I gave you to:

SELECT
IDUser,
Firstname,
Lastname,
Balance,
COALESCE(CONVERT(varchar(11),LastDividendMovement),
'No dividend history') LastDividendMovement
FROM AccountHolders h LEFT JOIN LastDivMovePerHolder d
ON h.IDUser= d.Accountholder
WHERE Balance >=100
AND COALESCE(LastDividendMovement,'19000101') < '20050820'
ORDER BY IDUser

Does that help?

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: select statement for in inclusion list

I am beginning to get a grip on this, but I have to consider the
following: For your formula to work a zero account balance must be
stored as Null instead of 0.00. I have always been told to avoid Null
values, so as a routine I normally set a default value to all fields
when designing a database. In this case I set the default value as 0.00
for Balance.

Do you recommend changing that to Null, and subsequently change all the
current zero balance values to Null, or do have another way getting to
the same result checking for 0.00 instead of Null??

Re: select statement for in inclusion list

TB wrote:
> I am beginning to get a grip on this, but I have to consider the
> following: For your formula to work a zero account balance must be
> stored as Null instead of 0.00.

?
Why would that be the case? I'm not checking for Null or zero balances
anywhere in the sql I provided...

> I have always been told to avoid Null
> values, so as a routine I normally set a default value to all fields
> when designing a database. In this case I set the default value as
> 0.00 for Balance.
>
> Do you recommend changing that to Null, and subsequently change all
> the current zero balance values to Null, or do have another way
> getting to the same result checking for 0.00 instead of Null??

I don't understand what you are getting at. Nothing in the sql I provided
would be affected by the use of either Null or zero in the balance column
....

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: select statement for in inclusion list

Bob, you have provided with me with so much information now that I need
to digest it, because I am certain your suggestions are perfectly valid
- all I need is to properly understand them. So because I will not want
to bother you anymore, I am going to take a breather, save this entire
thread, read it again and hopefully be able to adapt it MySQL which
supposedly follows standard ANSI conventions most of the way.

Thank you so much for your help. Whenever I get it to work, I will send
you a message (I think I have your email address sniffed out - You work
at NyPro, right?) to let you know. There is even a slight chance that
the project will migrate to MS SQL Server, enabling me to use your SQL
directly.

Cheers,

TB

Re: select statement for in inclusion list

TB wrote:
>
> Thank you so much for your help. Whenever I get it to work, I will
> send you a message (I think I have your email address sniffed out -
> You work at NyPro, right?) to let you know.
>

Frankly, I do not remember inviting you to either "sniff out" or post my
personal information in a public newsgroup.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: select statement for in inclusion list

Your place of work is listed on a publicly available page on the
Microsoft web site and I have revealed nothing else.

If you feel that I have offended you, I apologize deeply.

Once again, thank you for your valuable help.

TB

Re: select statement for in inclusion list

"Bob Barrows [MVP]" wrote in message
news:eIBztmSuFHA.3068@TK2MSFTNGP14.phx.gbl...
> TB wrote:
>>
>> Thank you so much for your help. Whenever I get it to work, I will
>> send you a message (I think I have your email address sniffed out -
>> You work at NyPro, right?) to let you know.
>>
>
> Frankly, I do not remember inviting you to either "sniff out" or post my
> personal information in a public newsgroup.

That's the price of fame Bob.
Just be glad that you don't have the paparazzi after you. :-)

Re: select statement for in inclusion list

TB wrote:
> Your place of work is listed on a publicly available page on the
> Microsoft web site and I have revealed nothing else.

True, just seeing it in a newsgroup where some spider could pick it up ...
>
> If you feel that I have offended you, I apologize deeply.

OK, it's forgotten. I was just starting to feel a little uncomfortable, as
if I had acquired a stalker. I've heard "stories", you know?

>
> Once again, thank you for your valuable help.
>
You're welcome.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: select statement for in inclusion list

Raymond D'Anjou wrote:
> "Bob Barrows [MVP]" wrote in message
> news:eIBztmSuFHA.3068@TK2MSFTNGP14.phx.gbl...
>> TB wrote:
>>>
>>> Thank you so much for your help. Whenever I get it to work, I will
>>> send you a message (I think I have your email address sniffed out -
>>> You work at NyPro, right?) to let you know.
>>>
>>
>> Frankly, I do not remember inviting you to either "sniff out" or
>> post my personal information in a public newsgroup.
>
> That's the price of fame Bob.
> Just be glad that you don't have the paparazzi after you. :-)

All it would take is one look and they'd turn tail before snapping a single
picture (they like the beautiful people, no?) ;-)

Bob
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: select statement for in inclusion list

"Bob Barrows [MVP]" wrote in message
news:ujc0NOVuFHA.3104@TK2MSFTNGP10.phx.gbl...
> Raymond D'Anjou wrote:
>> "Bob Barrows [MVP]" wrote in message
>> news:eIBztmSuFHA.3068@TK2MSFTNGP14.phx.gbl...
>>> TB wrote:
>>>>
>>>> Thank you so much for your help. Whenever I get it to work, I will
>>>> send you a message (I think I have your email address sniffed out -
>>>> You work at NyPro, right?) to let you know.
>>>>
>>>
>>> Frankly, I do not remember inviting you to either "sniff out" or
>>> post my personal information in a public newsgroup.
>>
>> That's the price of fame Bob.
>> Just be glad that you don't have the paparazzi after you. :-)
>
> All it would take is one look and they'd turn tail before snapping a
> single
> picture (they like the beautiful people, no?) ;-)

I wouldn't feel safe if I were you Bob.
They also take pictures of Prince Charles, Courtney Love and Michael
Jackson.
....apologies to all fans and citizens of the British commonwealth. ;-)
(I came very close to posting a link here to your photo on the Microsoft MVP
site).

Re: select statement for in inclusion list

Raymond D'Anjou wrote:
>>> That's the price of fame Bob.
>>> Just be glad that you don't have the paparazzi after you. :-)
>>
>> All it would take is one look and they'd turn tail before snapping a
>> single
>> picture (they like the beautiful people, no?) ;-)
>
> I wouldn't feel safe if I were you Bob.
> They also take pictures of Prince Charles, Courtney Love and Michael
> Jackson.

Hmmm - I guess I should have added the "rich" keyword as well, another
description that definitely does not apply to me :-)

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.