Checking repeating data using dates

[essjay_d12]

Hi,

I have a lot of visitors (storing each one in db) and each visitor has a date field (date of visit). What I want to work out is how many of visitors in december (month based on system date - got this working) have also visited before? in the last year etc

I can do it using a view - but i need to be producing the value for these visitors in one statement. if i create a view then do select statement at same time it gives me invalid character message!

any ideas?

cheers,

d

[chazzy]

when you create a view you're creating a database object, something that's alive forever.

you can select off of that view if you like.

[essjay_d12]

I have the following sql command

select COUNT(COUNT(*)) from ACTIONS a
WHERE a.DATE_ENTERED BETWEEN '01-DEC-06' AND '01-SEP-07'
AND a.COMPANY_NAME IN

(select f.COMPANY_NAME from ACTIONS f, ACTION_TYPE_LOV a
WHERE f.DATE_ENTERED BETWEEN '01-SEP-07' AND '01-DEC-07'
AND a.ACTION_SCORE = 'Y'
AND f.INPUT_TYPE = a.ACTION_NAME
GROUP BY f.COMPANY_NAME)
GROUP BY a.COMPANY_NAME
ORDER BY a.COMPANY_NAME;

I also want to display the row count for the sub-query..... so i have 2 row counts. Can this be done?

Cheers

d

[chazzy]

Ok, so you want both the count of the individual company as well as the total sum?

I just did something very similar to this, and it was just so much easier to count it on the application side, rather than using SQL. I constructed a TreeMap to store the data then when displaying it kept a tally, put that total in the map and just displayed the map. It came out quite beautiful.