June 18, 2010 at 1:36 am
Hi,
I have a table with DateTimeIn, DateTimeOut, TotalAmount columns.
I want to get the following records:
1) total records and sum(totalamount) where DateTimeOut is between '01-feb-2010 00:00:00' and '02-feb-2010 00:00:00'
2) total records where DateTimeOut is between '01-feb-2010 00:00:00' and '02-feb-2010 00:00:00' and DateDiff bewteen DateTimeIn and DateTimeOut is less that 60min.
Currently I used 2 cursors:
DECLARE CURSOR_TXN_EXCEPTION for 1)
DECLARE CURSOR_TXN_EXCEPTION2 for 2)
but seems like slow result, possible to combine 1) & 2) and just declare 1 cursor?
June 18, 2010 at 1:50 am
Absolutely.
Since 2 is just a subset of 1 , try something like ...
Sum(case when datediff(mn,DateTimeIn, DateTimeOut) < 60 then 1 else 0 end)
Additionally , you may find that you dont need the cursor at all if you code is made a bit smarter.
What are you doing with the result is the cursor loop ?
June 18, 2010 at 2:10 am
Dave Ballantyne (6/18/2010)
Absolutely.Since 2 is just a subset of 1 , try something like ...
Sum(case when datediff(mn,DateTimeIn, DateTimeOut) < 60 then 1 else 0 end)Additionally , you may find that you dont need the cursor at all if you code is made a bit smarter.
What are you doing with the result is the cursor loop ?
In case I need to select DateTimeOut between '01-feb-2010' and '28-feb-2010'.
I need get records with '01-feb-2010' and insert to a temp table, and '02-feb-2010' and so on
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply