Home » SQL & PL/SQL » SQL & PL/SQL » How to find the counts for records inserted 30 minutes before
How to find the counts for records inserted 30 minutes before [message #318311] Tue, 06 May 2008 08:19 Go to next message
VEDDETA
Messages: 54
Registered: May 2008
Member

Hi,

How can I get the count for records which has been inserted 2hrs ago but less than 24 hrs before?


CREATE TABLE DATA (
  
  Visitor_Id         Varchar2(10),
  time1      TIMESTAMP
  
);


My query is something like a)get me the count of the visitor who
visited 30 minutes ago.
b) Get me the count of all the visitors who visited more than 1 day ago



Re: How to find the counts for records inserted 30 minutes before [message #318312 is a reply to message #318311] Tue, 06 May 2008 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post your query.

Clue: use a WHERE clause to restrict the rows you COUNT to those WHERE the time from now is BETWEEN an INTERVAL of 2 hours and an INTERVAL of 24 hours.

I don't see the purpose of these 30 minutes from your question.

Regards
Michel
Re: How to find the counts for records inserted 30 minutes before [message #318318 is a reply to message #318312] Tue, 06 May 2008 08:45 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Hi!
Thanks for your instant reply.I have tried to find out the count of
a) all visitors who visited today 30 minutes ago
b) who visited in between 30 to 1 hr
c) who visited in between 2-24 hrs
d) who visited more than 1 day ago...

Below is the query.But I am not getting the proper count as expected.




SELECT Visitor_Id,
count(case when (  time1 > SYSDATE - 30/1440)  then  1   end) "Less than 30 min",
         count(case when  (time1 > SYSDATE - 60/1440 and time1  < SYSDATE - 29/1440 ) then 1 end) "30 -60 Min",
         count(case when  (time1 > SYSDATE - 1440/1440 AND time1 < SYSDATE - 119/1440 ) then 1 end) "2 - 24 Hour"
 FROM  data GROUP BY Visitor_Id

Re: How to find the counts for records inserted 30 minutes before [message #318322 is a reply to message #318312] Tue, 06 May 2008 08:49 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Correction :
* b) Give the count of customers who bisited in between 30 min -60 min

[Updated on: Tue, 06 May 2008 08:51]

Report message to a moderator

Re: How to find the counts for records inserted 30 minutes before [message #318344 is a reply to message #318318] Tue, 06 May 2008 09:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ There is you use a timestamp the resolution of the data is 1/1000000 second, you count up to the minute, so you surely miss some data.

2/ There is a hole for those that are between 1 hour and 2 hours but maybe you don't care.

For timestamp use SYSTIMESTAMP and not SYSDATE and use INTERVAL datatype to make your operations.

Regards
Michel
Re: How to find the counts for records inserted 30 minutes before [message #318349 is a reply to message #318344] Tue, 06 May 2008 09:35 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Thank you very much Michel for pointing out the error.I am really concerned about it. The reason why I told that I was not getting correct count.Can you give some example with sql code?

Regards,
Veddeta

[Updated on: Tue, 06 May 2008 09:37]

Report message to a moderator

Re: How to find the counts for records inserted 30 minutes before [message #318350 is a reply to message #318349] Tue, 06 May 2008 09:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First line:
TIME1 > SYSTIMESTAMP - INTERVAL '30' MINUTE

Regards
Michel
Re: How to find the counts for records inserted 30 minutes before [message #318359 is a reply to message #318311] Tue, 06 May 2008 09:59 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Thank you very much Michel. Thanks for your help on this issue.


For third line:

count(case when  (TIME1 > SYSTIMESTAMP- INTERVAL ‘1440’ MINUTE AND TIME1  < SYSDATE – INTERVAL ‘120’ MINUTE ) then 1 end) "2 TO 24 HRS"


Here, I want count from 120 minutes to 1440 (24 hrs).Is that correct?

Re: How to find the counts for records inserted 30 minutes before [message #318361 is a reply to message #318359] Tue, 06 May 2008 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think the first one is >= or the second one <= but this is a detail.
Read the link I posted (under INTERVAL) you'll see you can use HOUR or DAY... as unit, easier to read than '120' MINUTE or '1440' MINUTE.

Take care you have "smart" quotes (‘ and ’) and not standard ones ('), so it will not work.

Regards
Michel

[Updated on: Tue, 06 May 2008 10:15]

Report message to a moderator

Re: How to find the counts for records inserted 30 minutes before [message #318370 is a reply to message #318311] Tue, 06 May 2008 11:01 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
I am very much thankful to you for guiding me! Thanks for your excellent support Michel.

I am herewith publisheing the SQL. Please do let me know for any concern if any.Thanks once again!

SELECT   Visitor_Id,
         COUNT(CASE 
                 WHEN (Time1 > sysTimesTamp - INTERVAL '60' MINUTE) THEN 1
               END) "< 1",
         COUNT(CASE 
                 WHEN Time1 > sysTimesTamp - INTERVAL '2' HOUR
                      AND Time1 <= sysTimesTamp - INTERVAL '1' HOUR THEN 1
               END) "1 TO 2",
         COUNT(CASE 
                 WHEN Time1 >= sysTimesTamp - INTERVAL '24' HOUR
                      AND Time1 <= sysTimesTamp - INTERVAL '2' HOUR THEN 1
               END) "2 TO 24",
         COUNT(CASE 
                 WHEN (Time1 < sysTimesTamp - INTERVAL '1' DAY) THEN 1
               END) "> 1 Day"
FROM     Date_Table
GROUP BY Visitor_Id
/




First line: Give me count of all customer who visted 60 minutes before
Second line: Give me count of all customer who visted in between 1 and 2 hr (Includes visitor from the 60th minute but less than 120 min)
...

Fourth line: Give me count of all customer who visited more than 1 day back.
Re: How to find the counts for records inserted 30 minutes before [message #318373 is a reply to message #318370] Tue, 06 May 2008 11:16 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Previous Topic: sql query to check if passed value is number or character
Next Topic: Removing duplicates ?
Goto Forum:
  


Current Time: Sat Dec 10 08:57:26 CST 2016

Total time taken to generate the page: 0.13153 seconds