Cancelling/Counting out rows

[HalfBrian]

Hello everyone,

 

Version: 5.0.24a-community-nt

 

Query (so far):

SELECT COUNT(ApplNo) FROM status WHERE Status='PEND' AND StatusDate>='20070203' AND StatusDate<='20070306' GROUP BY 'ApplNo'

(the StatusDate(s) are added by PHP)

 

Errors: N/A

 

Table:

CREATE TABLE `status` (
  `id` int( NOT NULL auto_increment,
  `ApplNo` varchar(10) default NULL,
  `Status` varchar(10) default NULL,
  `StatusDate` timestamp NULL default NULL,
  `Type` varchar(10) default NULL,
  PRIMARY KEY  (`id`),
  KEY `app` (`ApplNo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin14279

 

Each change in 'Status' is recorded as a new row (so each ApplNo might have multiple rows to it)

 

What the query should do is select the number of rows that are currently pending (that is, Status=PEND but not w/ a future CLOS (as by StatusDate) )

 

Example:

 

ApplNo          Status          Date
01                PEND            01/01
01                CLOS            01/02
02                PEND            01/11

 

That was count as 1 (since 01 is canceled out by the CLOS)

 

Thanks,

Brian

[fenway]

And what does it do? BTW, you should probably have an index on (status_date,applno)

[HalfBrian]

It returns 3 (it should return 2) because it doesn't discount the record w/ a CLOS (it uses a different recordset than the example in the first example)

 

and thanks for the indexing tips

[fenway]

I'm sorry, I don't understand.

[HalfBrian]

To recap:

 

For every row w/ the Status as PEND, it should add one to the count (you can only count an ApplNo once, hence the DISTINCT).  If the status is PEND in one record, but a future record (for the same ApplNo) has the Status as CLOS, it should not add one to the count.  (In other words, I want to count all of the currently pending ApplNo's)

 

My current query does not discount the CLOSed ApplNo's, but I need it too.

 

-Brian

 

P.S. When I return to my computer, I will post the actual sample data I am working with.

[fenway]

I don't see a count anywhere.

[artacus]

You'll have to left join this table back on itself so you can eliminate the ones w/ future close dates

SELECT COUNT(*) 
FROM status AS s1
LEFT JOIN status AS s2 ON s1.ApplNo = s2.ApplNo AND s2.Status = 'CLOS'
WHERE s1.Status='PEND' AND s1.StatusDate BETWEEN '20070203' AND '20070306' 
AND s2.ApplNo IS NULL

[HalfBrian]

Thanks artacus... it works almost perfectly. I forgot to mention that if a record is set back to PENDing (after being closed), it needs to count again.

 

Thanks,

Brian