SQL help: each person's last check 
Author Message
 SQL help: each person's last check

I am having trouble coding something in SQL that could be done with an
index and relation, but I want to learn SQL.

For each person's timecard, I need to find the last check they
received.  Here is my attempt and the resulting error. Are there any
other ways to do this in one SQL statement?

 SELECT bill1.BPID, keck1.kcheckno, keck1.kchckdate;
   FROM ITBILL bill1, ITKECK keck1;
   INTO CURSOR QWAZ;
   WHERE bill1.BPID = keck1.kPID;
    AND KCHCKDATE IN ( SELECT MAX(keck2.KCHCKDATE);
      FROM ITKECK keck2;
      WHERE keck2.KPID = bill1.BPID );

*  FPDos2.6 Error: #1814, SQL queries of this type are not supported at
present
*  VFP5 Error: #1814, SQL: queries of this type are not supported.

* ITKECK.DBF - paychecks
* KCHECKNO - number
* KCHCKDATE - date
* KPID - Personal ID
*
* ITBILL.DBF - time cards
* BPDI - Personal ID



Mon, 11 Oct 1999 03:00:00 GMT  
 SQL help: each person's last check


: I am having trouble coding something in SQL that could be done with an
: index and relation, but I want to learn SQL.
:
: For each person's timecard, I need to find the last check they
: received.  Here is my attempt and the resulting error. Are there any
: other ways to do this in one SQL statement?
:
:  SELECT bill1.BPID, keck1.kcheckno, keck1.kchckdate;
:    FROM ITBILL bill1, ITKECK keck1;
:    INTO CURSOR QWAZ;
:    WHERE bill1.BPID = keck1.kPID;
:     AND KCHCKDATE IN ( SELECT MAX(keck2.KCHCKDATE);
:       FROM ITKECK keck2;
:       WHERE keck2.KPID = bill1.BPID );
:
: *  FPDos2.6 Error: #1814, SQL queries of this type are not supported at
: present
: *  VFP5 Error: #1814, SQL: queries of this type are not supported.
:
: * ITKECK.DBF - paychecks
: * KCHECKNO - number
: * KCHCKDATE - date
: * KPID - Personal ID
: *
: * ITBILL.DBF - time cards
: * BPDI - Personal ID
:
:

Try this instead:

SELECT bill1.BPID, keck1.kcheckno, MAX(keck1.kchckdate);
   FROM ITBILL bill1, ITKECK keck1;
     INTO CURSOR QWAZ;
 WHERE bill1.BPID = keck1.kPID;
  GROUP BY bill1.bpid

Regards,

Steven E. Kalbach



Mon, 11 Oct 1999 03:00:00 GMT  
 SQL help: each person's last check

Quote:
>> SELECT bill1.BPID, keck1.kcheckno, MAX(keck1.kchckdate) ;

   FROM ITBILL bill1, ITKECK keck1;
     INTO CURSOR QWAZ;
 WHERE bill1.BPID = keck1.kPID;
  GROUP BY bill1.bpid <<

Steve,
That query will not return the correct  kcheckno, except by pure chance.
  -Anders

--

04/27/97 00:41
---------
Using: OUI PRO 1.5.0.2 from http://www.dvorak.com



Wed, 13 Oct 1999 03:00:00 GMT  
 SQL help: each person's last check



: >> SELECT bill1.BPID, keck1.kcheckno, MAX(keck1.kchckdate) ;
:    FROM ITBILL bill1, ITKECK keck1;
:      INTO CURSOR QWAZ;
:  WHERE bill1.BPID = keck1.kPID;
:   GROUP BY bill1.bpid <<
:
: Steve,
: That query will not return the correct  kcheckno, except by pure chance.
:   -Anders
:
: --

: 04/27/97 00:41
: ---------
: Using: OUI PRO 1.5.0.2 from http://www.dvorak.com
:
:
:

Your right!

Regards,



Wed, 13 Oct 1999 03:00:00 GMT  
 SQL help: each person's last check

: For each person's timecard, I need to find the last check they
: received.  Here is my attempt and the resulting error. Are there any
: other ways to do this in one SQL statement?
:
:  SELECT bill1.BPID, keck1.kcheckno, keck1.kchckdate;
:    FROM ITBILL bill1, ITKECK keck1;
:    INTO CURSOR QWAZ;
:    WHERE bill1.BPID = keck1.kPID;
:     AND KCHCKDATE IN ( SELECT MAX(keck2.KCHCKDATE);
:       FROM ITKECK keck2;
:       WHERE keck2.KPID = bill1.BPID );
:
: *  FPDos2.6 Error: #1814, SQL queries of this type are not supported
at : present
: *  VFP5 Error: #1814, SQL: queries of this type are not supported.
:
: * ITKECK.DBF - paychecks
: * KCHECKNO - number
: * KCHCKDATE - date
: * KPID - Personal ID
: *
: * ITBILL.DBF - time cards
: * BPDI - Personal ID

Carl,
kpid and bpid are the same. So
 SELECT bill1.BPID, keck1.kcheckno, keck1.kchckdate;
  FROM ITBILL bill1, ITKECK keck1;
  WHERE bill1.BPID = keck1.kPID ....
contains a superfluous join.

The problem with this type of query is not to get the last date for each
person, or ID, but to get the righ checknumber. Consider this table:
 create cursor checks (id c(1), checkno c(2), date d)
 set date ymd
 set mark to '-'
 insert into checks values ('a', '10', {97-1-1}) *
 insert into checks values ('a', '11', {96-1-1})
 insert into checks values ('a', '09', {96-4-1})
 insert into checks values ('a', '12', {96-4-1})
 insert into checks values ('b', '20', {97-1-1}) *
 insert into checks values ('b', '21', {96-1-1})
 insert into checks values ('b', '29', {96-4-1})
 insert into checks values ('b', '22', {96-4-1})
   The last date for 'a' and 'b' are marked with *
  select id, checkno, max(date) from checks group by id
 This is what you get:
   a 12 1997-01-01
   b 22 1997-01-10
 Wrong checknumbers!
Some SQL implementations do not allow you to have Select fields that are
not also in the group by field list.  The error can be masked by
circumstance. If the last date is also always the last in physical order
we would by chance get the right answer.

You can use a correlated subquery:

 SELECT kpid, kcheckno, kcheckdate FROM Itkeck k1
  WHERE kcheckdate= ;
  SELECT (SELECT MAX(kcheckdate) ;
  FROM itkeck k2 ;
  WHERE k1.kpid=k2.kpid)

If you do have to check that itcheckkpid exists in ItBill, you can use
EXISTS

 SELECT kpid, kcheckno, kcheckdate ;
    FROM Itkeck k1
        WHERE kcheckdate = ;
          (SELECT MAX(kcheckdate) ;
              FROM itkeck k2 ;
                  WHERE k1.kpid=k2.kpid )
        AND EXISTS;
          (SELECT * FROM itbill it ;
              WHERE it.bpid=k1.kpid)
    INTO CURSOR qwaz

However, that is usually a slow and expensive query in VFP 5.
Splitting this query into two steps makes it very much faster in FoxPro.

  select kpid, max(kcheckdate) as max_kcheckdate ;
      from itkeck ;
        group by kpid ;
        into cursor q1

  select * from itkeck k1 where exists ;
      (select * from q1 ;
           where q1.kpid=k1.kpid and q1.max_kcheckdate=k1.kcheckdate) ;
   into cursor qwaz

This will also return all cases where a person has received more than 1
check  on the same last day.

-Anders

--

04/27/97 00:41
---------
Using: OUI PRO 1.5.0.2 from http://www.dvorak.com



Wed, 13 Oct 1999 03:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. 'Last Modified Date' in file header

2. Please check my SQL and help

3. This is my last project with FoxPro / I'd like to retire too

4. SQL transactions last too long

5. help with programming 'help'

6. 'Whats This' Help in Grids - HELP

7. Checking for directory's existence

8. check box - where's the cursor

9. ******Check out this Web 'Zine*******

10. Don't change check box value with enter key

11. spell check doesn't work

12. Problem with 'Insert into' SQL command

 

 
Powered by phpBB® Forum Software