SQL query sample

Question

Hi, my table is look like this order.. I would like count the TermID's with these conditions; If it's possible also want to get resultset with these headers

- TermID must have close or open status
- TermID Status must turn to Open (close to open)
- Status Date (open status) must be bigger date than close status date

with my regards,

ID        TermID Status Date
103990107 103641 Close 28/05/2010
104000600 103641 Open 31/05/2010
103980976 106458 Close 26/05/2010
103990045 106458 Open 27/05/2010
103939537 106475 Open 18/05/2010
103908130 117220 Open 13/05/2010
103929653 117220 Open 13/05/2010
103999017 117360 Open 31/05/2010
103834814 117402 Open 05/05/2010
103993973 117420 Open 28/05/2010
103849795 119377 Close 05/05/2010
103955266 119377 Close 21/05/2010
103995229 119377 Close 29/05/2010
103979873 119704 Open 25/05/2010
103832225 119767 Open 04/05/2010
103925429 123229 Close 15/05/2010
103986986 123932 Open 27/05/2010

Answer 1

I think you're asking two questions here:

---

1. How do I count all TermIDs?

Answer: You use the count(...) aggregate function in SQL.

Example:

SELECT count(TermID) from <table>

This will count all TermIDs in your table.

---

2. How do I update my records to have a new status / status date?

Answer: Use the UPDATE statement:

UPDATE <table>
SET Status = 'Open', <column name> = <value>, ...
WHERE Staus = 'Closed'

Note: You're question seems vague, so the above may not work well. Please tailor to your specific use cases.

Answer 2

Try this one. It will work in oracle

SELECT TermID, COUNT(*) CNT FROM
 (
 SELECT ID,TermID,Status, Date,
 LEAD(Status,1,NULL) OVER (PARTITION BY TermID ORDER BY Date DESC) NEXT_STATUS,
 FROM MYTABLE
 ) 
WHERE Status = 'Close' AND NEXT_STATUS = 'Open';