Thread: Need your help to display correct data by date

Hi Everybody,

I want to calculate Total of Quantity and GROUP BY CarID.
My problem quite difficult to me, and currently I don't know how to do, please help to show me the solutions for this.
In my database have some tables: TBL_CAR_TX, TBL_CAR, TBL_PERSON

table: TBL_CAR_TX
------------------
select * from TBL_CAR_TX
where TranDate >= '2011-06-09' and Trandate <= '2011-06-20'

TranID, AccID, CarID, TranDate, Type, Quantity, .....
--------------------------------------------------------
0 2563 BMW 2011-06-09 H -15 (1)
1 2563 BMW 2011-06-20 R 15
2 2563 BMW 2011-06-20 H 20
3 0055 TOY 2011-06-12 H -10 (2)
4 0055 TOY 2011-06-20 R 10
...
(H): Hold, (R): Release
if we change the condition at WHERE stmt:
select * from TBL_CAR_TX
where TranDate >= '2011-06-10' and Trandate <= '2011-06-19'
==> all the record was disappeared ==> I can't calculate because don't display data.

Look at the table TBL_CAR_TX, we can see that a person borrow car 'BMW' from '2011-06-09' and until '2011-06-20' he returned it. So If I do in SQL Server 2008, how can I keep the record (1) & (2) in the range date from '2011-06-10' to '2011-06-19' to calculate. If can not, how can I do it in java programming ? Any suggest.

table TBL_CAR:
MarketID, CarID, Name, Size, ...
---------------------------
GER BMW
JPA TOY

Table TBL_PERSON:
AccID, Name, Age, DOB, ...
-------------------------
2563 Robert
0055 Mike

Thanks you very much for your help

All the dates stored in DB as Date type. Could you pls guide me more clear about compare date in this situation ?