count with more than 1

Question

count with more than 1

foxup (Programmer)

(OP)

14 Jun 17 19:53

Hi,

A long time since I've asked for help but for some reason, this Query is racking my brain.

MY.DBF looks like this

PSRV, PSRVHOS, PLOCASOF, PLOCTO

3601 62619 01/01/2017 31/01/2017
3631 62619 01/01/2017 31/01/2017
35XD 62619 01/01/2017
35JY 62619 01/01/2017
3400 62620 01/02/2017 31/03/2017
3701 62620 01/02/2017 31/07/2017
3641 62620 01/03/2017 31/07/2017
3401 62620 01/03/2017 31/07/2017
35JY 62620 01/04/2017 31/07/2017
35XD 62620 01/04/2017 31/07/2017
3601 62621 01/02/2017 28/01/2017
3631 62621 01/02/2017 28/01/2017
35XD 62621 01/02/2017
35JY 62621 01/02/2017
etc.
I would need to find out the "distint psrvphos's" that have only the psrv=35XD and psrv=35JY <only them 2 left> with an empty(dlocto), so in this case, I would get as a result:
62619
62621

any help please?

thank you so much,
FOXUP!

TamarGranor (Programmer) · Answer 1 · 2017-06-14 20:28:00

Try this:

CODE

SELECT PSRVHOS ;
   FROM My ;
   WHERE PSRVHOS IN ;
       (SELECT PSRVHOS FROM My MyA ;
          WHERE MyA.PSRV = "35XD" ;
            AND EMPTY(MyA.dlocto)) ;
     AND PSRVHOS IN ;
       (SELECT PSRVHOS FROM My MyB ;
          WHERE MyB.PSRV = "35JY" ;
            AND EMPTY(MyB.dlocto))

Tamar

Olaf Doschke (Programmer) · Answer 2 · 2017-06-14 20:34:18

Well, rows of interest have either psrv=35XD OR psrv=35JY (notice the boolean expression here is OR, not AND - opposite to what one says about the records having 35XD AND 35JY)

You can't check conditions about two records in a where clause as it only acts on each single record. It has no memory of what it already saw. So to have conditions about two records, you either need a group by - eg count records with either one value and see if that's two, or you need a join, you could have the unusual join condition for two records of the self-joined table to have psrv=35XD in one and psrv=35JY in a joined record with both same PSRVHOS and empty PLOCTO.

Anyway, that's the technical analysis of your problem, I get no real idea what the conditions are. You say only them, but 62619 and 62621 also have other PSRV values, so you contradict your own condition.

You only can get a solution, when you're absolutely clear, what conditions must hold true.

Another question I couldn't answer from what you specify: Is it important to also look at records with non-empty PLOCTO, is it important, that other rows have a non-empty date in there?

If that is not the case, you have very general filter conditions already shrinking down the rows you only need to look at and blend out any other rows from further checking, which is one major approach to a solution, shrink the data to really interested rows only, but only skip all other rows, if nothing of them is important for the final selection, including just their existence or count.

One query, that will also just output 62619 and 62621 is the following, but it's totally vague, if this is what you really need:

CODE

SELECT t1.PSRVHOS FROM my.dbf as t1 inner join my.dbf as t2 ;
    ON t1.PSRVHOS = t2.PSRVHOS and t1.PSRV='35XD' and t2.PSRV='35JY' and EMPTY(t1.PLOCTO) and EMPTY(t2.PLOCTO)

To show how different I could interpret your conditions, this also would just pick out 62619 and 62621:

CODE

SELECT PSRVHOS FROM my.dbf ;
WHERE PSRV in ('35XD','35JY') AND EMPTY(PLOCTO)
GROUP BY PSRVHOS ;
HAVING COUNT(*)=2

Both of these solutions would not care about any other records besides those with 35XD or 35JY and records with a PLOCTO date, therefore my questions.

Bye, Olaf.

foxup (Programmer) · Answer 3 · 2017-06-14 20:36:44

your query doesn't return any results so there must be something wrong as I mentionned there are at least those 2 above. :(
any ideas to why in results in zero records? :(

thanks,
foxup

foxup (Programmer) · Answer 4 · 2017-06-14 20:42:02

Hi Olaf,

Only this must be met as true
-> empty (dlocto) of 35XD and empty (dlocto) of 35JY <only those 2 must be empty> for grouping it by psrvphos

foxup (Programmer) · Answer 5 · 2017-06-14 20:49:06

here is the data broken up for easier reading:

3601 62619 01/01/2017 31/01/2017
3631 62619 01/01/2017 31/01/2017
35XD 62619 01/01/2017
35JY 62619 01/01/2017

3400 62620 01/02/2017 31/03/2017
3701 62620 01/02/2017 31/07/2017
3641 62620 01/03/2017 31/07/2017
3401 62620 01/03/2017 31/07/2017
35JY 62620 01/04/2017 31/07/2017
35XD 62620 01/04/2017 31/07/2017

3601 62621 01/02/2017 28/01/2017
3631 62621 01/02/2017 28/01/2017
35XD 62621 01/02/2017
35JY 62621 01/02/2017

I want as a resulted query:
62619
62621

because those are the only two "psrvphos" that have '35XD' and '35JY' with an "empty(dlocto) and that are also the only 2 left in that particular (group by) psrvphos"

I hope that helps. :)

thanks inadvance,
FOXUP

Olaf Doschke (Programmer) · Answer 6 · 2017-06-14 21:08:38

>and that are also the only 2 left

What do you mean with left here? left over? From what? Does "left over" mean having empty PLOCTO? Do any records with non-empty PLOCTO play any role?
You have some meaning of data in your mind, that does not get to us. PLOCTO - I guess that could mean PLOC TO, but have no idea what PLOC would mean.

Besides that, it seems you can't even copy&paste correctly:

CODE

CREATE CURSOR my (PSRV C(4), PSRVHOS I, PLOCASOF D, PLOCTO D)

SET DATE DMY
SET MARK TO "/"

INSERT INTO my VALUES ("3601", 62619, CTOD('01/01/2017'), CTOD('31/01/2017'))
INSERT INTO my VALUES ("3631", 62619, CTOD('01/01/2017'), CTOD('31/01/2017'))
INSERT INTO my VALUES ("35XD", 62619, CTOD('01/01/2017'), CTOD(''))
INSERT INTO my VALUES ("35JY", 62619, CTOD('01/01/2017'), CTOD(''))

INSERT INTO my VALUES ("3400", 62620, CTOD('01/02/2017'), CTOD('31/03/2017'))
INSERT INTO my VALUES ("3701", 62620, CTOD('01/02/2017'), CTOD('31/07/2017'))
INSERT INTO my VALUES ("3641", 62620, CTOD('01/03/2017'), CTOD('31/07/2017'))
INSERT INTO my VALUES ("2401", 62620, CTOD('01/03/2017'), CTOD('31/07/2017'))
INSERT INTO my VALUES ("35JY", 62620, CTOD('01/04/2017'), CTOD('31/07/2017'))
INSERT INTO my VALUES ("35XD", 62620, CTOD('01/04/2017'), CTOD('31/07/2017'))

INSERT INTO my VALUES ("3601", 62621, CTOD('01/01/2017'), CTOD('28/01/2017'))
INSERT INTO my VALUES ("3631", 62621, CTOD('01/01/2017'), CTOD('28/01/2017'))
INSERT INTO my VALUES ("35XD", 62621, CTOD('01/01/2017'), CTOD(''))
INSERT INTO my VALUES ("35JY", 62621, CTOD('01/01/2017'), CTOD(''))

SELECT t1.PSRVHOS FROM my as t1 inner join my as t2 ;
    ON t1.PSRVHOS = t2.PSRVHOS and t1.PSRV='35XD' and t2.PSRV='35JY' and EMPTY(t1.PLOCTO) and EMPTY(t2.PLOCTO) ;
    INTO CURSOR crsResult1
    
SELECT PSRVHOS FROM my ;
   WHERE PSRV in ('35XD','35JY') AND EMPTY(PLOCTO);
   GROUP BY PSRVHOS ;
   HAVING COUNT(*)=2 ;
   INTO CURSOR crsResult2

Both query results are what you wanted:

Both queries are so different, that it's very likely they both don't work out in the bigger data set and you need some third solution.

Bye, Olaf.

foxup (Programmer) · Answer 7 · 2017-06-15 16:13:56

hi,

thank you. managed with this:

SELECT PSRVHOS FROM my ;
WHERE PSRV in ('35XD','35JY') AND EMPTY(PLOCTO);
GROUP BY PSRVHOS ;
HAVING COUNT(*)=2 ;
INTO CURSOR crsResult2

thank you,
FOXUP

TamarGranor (Programmer) · Answer 8 · 2017-06-15 20:27:23

Seems to me that query only works if each PSRV value can appear only once. If there can be two records for a PSRVHOS where both have, say, '35XD', and you need both values, you'll get the wrong answer.

Tamar

Olaf Doschke (Programmer) · Answer 9 · 2017-06-16 06:35:08

Yes, it has that risc. It could only be 2, if you have 2 times 35XD and no 35JY. Uniqueness of these values is assumed and could be enforced by a candidate index on eg PSRV+bintoc(PSRVHOS) - depends on the real field types. Sinde the data meaning is obscured, as I already complained about, and we even don't know types, I have no idea about how assure that query is what's needed. I also said so.

You have to know what you want, nobody can tell that to you.

Bye, Olaf.

Come Join Us!

Posting Guidelines

Jobs

count with more than 1