Board index » delphi » Re: Help with SelectSQL

jackie

Delphi Developer
 

Re: Help with SelectSQL


2003-08-09 01:09:53 PM
delphi131
Sorry about previous formatting. Hoping this works:
when X =2 I want only records for employees that worked for 2 or more
days for a given employee. Hence the included records (that is the ones I
want returned) are as follows:
Excluded Included
DRID EMPID JOBDATE DRID EMPID JOBDATE
6 232 15/07/2003
6 419 15/07/2003
7 403 18/07/2003
7 403 21/07/2003
7 403 22/07/2003
7 403 23/07/2003
7 403 24/07/2003
7 403 25/07/2003
11 243 02/07/2003
11 243 03/07/2003
11 243 04/07/2003
12 48 18/07/2003
12 87 01/07/2003
12 87 02/07/2003
12 131 09/07/2003
12 277 08/07/2003
12 277 09/07/2003
12 277 10/07/2003
12 277 11/07/2003
12 277 14/07/2003
12 277 15/07/2003
12 361 11/07/2003
12 371 01/07/2003
12 371 02/07/2003
12 371 03/07/2003
etc
etc
when X =3 I want only records for employees that worked for 3 or more
days for a given employee. Hence the included records (that is the ones I
want returned) are as follows:
Excluded Included
DRID EMPID JOBDATE DRID EMPID JOBDATE
6 232 15/07/2003
6 419 15/07/2003
7 403 18/07/2003
7 403 21/07/2003
7 403 22/07/2003
7 403 23/07/2003
7 403 24/07/2003
7 403 25/07/2003
11 243 02/07/2003
11 243 03/07/2003
11 243 04/07/2003
12 48 18/07/2003
12 87 01/07/2003
12 87 02/07/2003
12 131 09/07/2003
12 277 08/07/2003
12 277 09/07/2003
12 277 10/07/2003
12 277 11/07/2003
12 277 14/07/2003
12 277 15/07/2003
12 361 11/07/2003
12 371 01/07/2003
12 371 02/07/2003
12 371 03/07/2003
etc
etc
 
 
jackie

Delphi Developer

2003-08-09 01:34:28 PM

Re: Help with SelectSQL

"Anderson Franco" <XXXX@XXXXX.COM>writes
Quote
>Doesn't seem to work for all records! This is my SelectSQL following
 your
>suggestion:
It guess you are comparing bananas with oranges ;-)

>SelectSQL.Add('AND J2.empid = J1.empid)>= '+x );
 >
 >but when x is 3 I get:
 >
 >DRID EMPID JOBDATE
 >6 232 15/07/2003 <------this is wrong as there are only 2
 recordsfor
>drid = 6 !!
 >7 403 18/07/2003
 >7 403 21/07/2003
Ok. You are ordering by DRID and there are only two DRID = 6. But how many
EMPID there are ?
If you want to count DRID you have to change

>SelectSQL.Add('AND J2.drid = J1.drid)>= '+x );
Anderson Franco



In IBConsole I now tried your suggestion like this:
SELECT drid, empid, jobdate
FROM jobmst J1
WHERE jobdate BETWEEN '07/01/2003' AND '07/31/2003'
AND (SELECT COUNT(*) FROM jobmst J2
WHERE J2.jobdate BETWEEN '07/01/2003' AND '07/31/2003'
AND J2.drid = J1.drid)>= 1
ORDER BY drid, empid, jobdate
which gives all the reocrds like this and as I woould expect:
DRID EMPID JOBDATE
6 232 15/07/2003
6 419 15/07/2003
7 403 18/07/2003
7 403 21/07/2003
7 403 22/07/2003
7 403 23/07/2003
7 403 24/07/2003
7 403 25/07/2003
11 243 02/07/2003
11 243 03/07/2003
11 243 04/07/2003
12 48 18/07/2003
12 87 01/07/2003
12 87 02/07/2003
12 131 09/07/2003
12 277 08/07/2003
12 277 09/07/2003
12 277 10/07/2003
12 277 11/07/2003
12 277 14/07/2003
12 277 15/07/2003
12 361 11/07/2003
12 371 01/07/2003
12 371 02/07/2003
12 371 03/07/2003
etc
etc
When I tried
SELECT drid, empid, jobdate
FROM jobmst J1
WHERE jobdate BETWEEN '07/01/2003' AND '07/31/2003'
AND (SELECT COUNT(*) FROM jobmst J2
WHERE J2.jobdate BETWEEN '07/01/2003' AND '07/31/2003'
AND J2.drid = J1.drid)>= 2 ORDER BY drid, empid, jobdate
we get wrong results:
Returned Records But these records should be
DRID EMPID JOBDATE excluded because < 2 days
6 232 15/07/2003 <
6 419 15/07/2003 <
7 403 18/07/2003
7 403 21/07/2003
7 403 22/07/2003
7 403 23/07/2003
7 403 24/07/2003
7 403 25/07/2003
11 243 02/07/2003
11 243 03/07/2003
11 243 04/07/2003
12 48 18/07/2003 <
12 87 01/07/2003
12 87 02/07/2003
12 131 09/07/2003 <
12 277 08/07/2003
12 277 09/07/2003
12 277 10/07/2003
12 277 11/07/2003
12 277 14/07/2003
12 277 15/07/2003
12 361 11/07/2003 <
12 371 01/07/2003
12 371 02/07/2003
12 371 03/07/2003
etc
etc
Hoping this is clear.
jackie.
 
jackie

Delphi Developer

2003-08-11 06:54:44 AM

Re: Help with SelectSQL

Gosh, if I only knew that a couple of days ago!!!! Thanks Paul....
jackie...(a novice)
"Paul Linehan" <XXXX@XXXXX.COM>writes
Quote

XXXX@XXXXX.COM says...

>(P.S. Is there any way in IBConsole to dump the query output? Sure
 would
>mak it easier for me...<g>).

Yes,there is.


Run your query, then in the InteractiveSQL window, choose the menu
option Query and the last option here is Save Output.


Paul...


--

plinehan__AT__yahoo__DOT__com

C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04

p.s. just lacerated a tendon in left hand, so pls
excuse typos and tricky abbrevs - TIA.
 

Craig Stuntz [TeamB]

Delphi Developer

2003-08-11 10:06:45 PM

Re: Help with SelectSQL

jackie writes:
Quote
Hope this makes it a bit clearer to you cos sure as hell I don't
follow it...<g>. Again to make sure that it is clear what I need, when
I say I need to "only return records from when an employee has worked
for any client more than X days in a given month (i.e. when>= X
records for an employee for a given client in the month then show all
records for that employee).
OK, here's the problem. The query we're discussing is returning
records with multiple visits on the same *day.* To get multiple visits
on the same month, drop the date from the fields list and the GROUP BY,
and put the month in the WHERE clause.
-Craig
 
jackie

Delphi Developer

2003-08-12 01:07:12 AM

Re: Help with SelectSQL

"Craig Stuntz [TeamB]" <XXXX@XXXXX.COM>writes
Quote
jackie writes:

>Is this what you mean Craig?
Yes.

>If so, then it is still not right as this only gives the first
 >instance an employee works for a given client in that month
I know. You can not get everything you want in a single query. This
query will return the DRID and the EMPIDs you want. you will then need
to do a second query (or turn both into a single selectable proc) to
get the individual dates worked.

I just tried Franco's suggestion (see his last post above)
SELECT drid, empid, jobdate
FROM jobmst J1
WHERE jobdate BETWEEN '07/01/2003' AND '07/31/2003'
AND (SELECT COUNT(*) FROM jobmst J2
WHERE J2.jobdate BETWEEN '07/01/2003' AND '07/31/2003'
AND J2.drid = J1.drid
AND J2.empid = J1.empid)>= 2
ORDER BY drid, empid, jobdate
and this seems to rpoduce the desired results. Not sure if this is what you
meant by
Quote
.......need
to do a second query (or turn both into a single selectable proc) to
get the individual dates worked.
jackie
 
jackie

Delphi Developer

2003-08-12 01:10:43 AM

Re: Help with SelectSQL

"Anderson Franco" <XXXX@XXXXX.COM>writes
Quote
>When I tried
 >
 >SELECT drid, empid, jobdate
 >FROM jobmst J1
 >WHERE jobdate BETWEEN '07/01/2003' AND '07/31/2003'
 >AND (SELECT COUNT(*) FROM jobmst J2
 >WHERE J2.jobdate BETWEEN '07/01/2003' AND '07/31/2003'
 >AND J2.drid = J1.drid)>= 2 ORDER BY drid, empid, jobdate
 >
 >we get wrong results:
 >
 >Returned Records But these records should be
 >DRID EMPID JOBDATE excluded because < 2 days
 >6 232 15/07/2003 <
 >6 419 15/07/2003 <
This records are right for me. You are asking for *DRID>= 2* in the date
interval.

WHERE J2.jobdate BETWEEN '07/01/2003' AND '07/31/2003'
AND J2.drid = J1.drid)>= 2

>7 403 18/07/2003
 >7 403 21/07/2003
 >7 403 22/07/2003
 >7 403 23/07/2003
 >7 403 24/07/2003
 >7 403 25/07/2003
 >11 243 02/07/2003
 >11 243 03/07/2003
 >11 243 04/07/2003
 >12 48 18/07/2003 <
 >12 87 01/07/2003
 >12 87 02/07/2003
 >12 131 09/07/2003 <
 >12 277 08/07/2003
 >12 277 09/07/2003
 >12 277 10/07/2003
 >12 277 11/07/2003
 >12 277 14/07/2003
 >12 277 15/07/2003
 >12 361 11/07/2003 <
 >12 371 01/07/2003
 >12 371 02/07/2003
 >12 371 03/07/2003
 >etc
 >etc
This records are right for me. You are asking for DRID>= 2 in the date
interval.
Perhaps you want ask for (DRID and EMPID)>= 2 in the date interval. So
you
should try:

SELECT drid, empid, jobdate
FROM jobmst J1
WHERE jobdate BETWEEN '07/01/2003' AND '07/31/2003'
AND (SELECT COUNT(*) FROM jobmst J2
WHERE J2.jobdate BETWEEN '07/01/2003' AND '07/31/2003'
AND J2.drid = J1.drid
AND J2.empid = J1.empid)>= 2
ORDER BY drid, empid, jobdate

Hello Franco, yes this looks like it gives the right results for me now....I
will test this in more detail after I get some urgently needed
sleep.....<g>.
Thank you for your persistence with my problem.
jackie
 
Craig Stuntz [TeamB]

Delphi Developer

2003-08-12 01:29:59 AM

Re: Help with SelectSQL

jackie writes:
Quote
and this seems to rpoduce the desired results. Not sure if this is
what you meant by

>.......need
 >to do a second query (or turn both into a single selectable proc) to
 >get the individual dates worked.
 It's similar. This query gives you a correlated subquery, as I
mentioned in the first reply in this thread.
The method I suggested also uses two queries, however the second
(outer) will only be run for matching records, so performance may be
better.
-Craig
--
Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
Delphi/InterBase Weblog : delphi.weblogs.com
Please read and follow Borland's rules for the user of their
news server: info.borland.com/newsgroups/guide.html
 
jackie

Delphi Developer

2003-08-12 07:03:31 AM

Re: Help with SelectSQL

Quote
>and this seems to rpoduce the desired results. Not sure if this is
 >what you meant by
 >
 >>.......need
 >>to do a second query (or turn both into a single selectable proc) to
 >>get the individual dates worked.
It's similar. This query gives you a correlated subquery, as I
mentioned in the first reply in this thread.
Yes I do recall you having mentioned this. Franco's idea does however seem
to produce the results. Is there somewhere where I can read up on what a
correlated subquery is?
Quote

The method I suggested also uses two queries, however the second
(outer) will only be run for matching records, so performance may be
better.

Thanks for the time that you have spent on my problem, it is greatly
appreciated.
jackie....(with so much to learn)
 
jackie

Delphi Developer

2003-08-12 07:05:17 AM

Re: Help with SelectSQL

Your query works Franco and I just wanted to again thank you for the time
that you have spent on my problem, it is very much appreciated.
jackie....(now a little wiser)
 
Craig Stuntz [TeamB]

Delphi Developer

2003-08-12 09:27:08 PM

Re: Help with SelectSQL

jackie writes:
Quote
Yes I do recall you having mentioned this. Franco's idea does however
seem to produce the results. Is there somewhere where I can read up
on what a correlated subquery is?
Corrolated simply means that the subquery is run once for each record
in the "main" query. (As opposed to a non-correlated subquery, which
is run only once, period.)
The method I suggested is somewhere in between -- the "second" query
is run once for each *matching* record.
-Craig
--
Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
Delphi/InterBase Weblog : delphi.weblogs.com
IB 6 versions prior to 6.0.1.6 are pre-release and may corrupt
your DBs! Open Editon users, get 6.0.1.6 from mers.com
 
jackie

Delphi Developer

2003-10-16 09:05:48 AM

Re: Help with SelectSQL

I have a table with a field containing fees-paid-to-date to hired
contractors. To display a given set of contractor payments (i.e. for certain
"where" conditions) in ASC or DESC order, currently I set the SelectSQL to
"SELECT * FROM contractors where ....... ORDER BY ASC" or "SELECT * FROM
contractors where ....... ORDER BY DESC". However, I also need to show all
contractors that have yet to be employeed (i.e. fees-paid-to-date is null)
at the end of the returned list of contractors and would appreciate some
help with this.
jackie
 
Wayne Niddery [TeamB]

Delphi Developer

2003-10-17 01:36:11 AM

Re: Help with SelectSQL

jackie writes:
Quote
I have a table with a field containing fees-paid-to-date to hired
contractors. To display a given set of contractor payments (i.e. for
certain "where" conditions) in ASC or DESC order, currently I set the
SelectSQL to "SELECT * FROM contractors where ....... ORDER BY ASC"
or "SELECT * FROM contractors where ....... ORDER BY DESC". However,
I also need to show all contractors that have yet to be employeed
(i.e. fees-paid-to-date is null) at the end of the returned list of
contractors and would appreciate some help with this.
Since this is a question about SQL, it really belongs in
borland.public.interbase.sql, however...
Need clarification:
- is all the data being selected from a single table or are their joins?
- are you ordering by the fees-paid-to-date column or other columns?
In general, nulls will sort to the end if such a column is included in the
sort. You can also specify Ascending or Descending on a column by column
basis.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
"It is error alone which needs the support of government. Truth can
stand by itself." - Thomas Jefferson
 
jackie

Delphi Developer

2003-10-17 11:07:40 AM

Re: Help with SelectSQL

Quote
Since this is a question about SQL, it really belongs in
borland.public.interbase.sql, however...

Sorry.....I'll subscribe to the group and will note it for next time as I
often need help with SQLs.
Quote
Need clarification:
- is all the data being selected from a single table or are their joins?
- are you ordering by the fees-paid-to-date column or other columns?
There are joins to several other tables but I tried to keep it simple in my
post as I find it difficult to explain things otherwise. The order will be
by fees-paid-to-date.
Quote

In general, nulls will sort to the end if such a column is included in the
sort. You can also specify Ascending or Descending on a column by column
basis.
Yes I want the nulls (i.e. contractors yet to be employeed hence
fees-paid-to-date is null) to be listed at the end FOR BOTH the ASC and DESC
sorts and this is what I am unsure about doing.
Thanks for your reply Wayne.
jackie
 
Wayne Niddery [TeamB]

Delphi Developer

2003-10-18 11:03:27 AM

Re: Help with SelectSQL

jackie writes:
Quote

Yes I want the nulls (i.e. contractors yet to be employeed hence
fees-paid-to-date is null) to be listed at the end FOR BOTH the ASC
and DESC sorts and this is what I am unsure about doing.
Yes, nulls will appear at the end for either.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
"It is error alone which needs the support of government. Truth can
stand by itself." - Thomas Jefferson
 
jackie

Delphi Developer

2003-10-18 12:12:13 PM

Re: Help with SelectSQL

ThanksWayne for clarifying this.
"Wayne Niddery [TeamB]" <XXXX@XXXXX.COM>writes
Quote
jackie writes:
>
 >Yes I want the nulls (i.e. contractors yet to be employeed hence
 >fees-paid-to-date is null) to be listed at the end FOR BOTH the ASC
 >and DESC sorts and this is what I am unsure about doing.
Yes, nulls will appear at the end for either.

--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
"It is error alone which needs the support of government. Truth can
stand by itself." - Thomas Jefferson