Possible Duplicate:
SQL Query Help
Duplicate of http://stackoverflow.com/questions/939055/sql-query-help
Two Tables
T_Person – Table 1
CARDNO
168 471 488 247 519 518 331 240 518 386 441 331
T_Cardevent – Table 2
CARDEVENTDATE CARDEVENTTIME
20090225 163932 20090225 164630 20090225 165027 20090225 165137 20090225 165147 20090225 165715 20090225 165749 20090303 162059 20090303 162723 20090303 155029 20090303 155707 20090303 162824
CARDEVENTTIME VALUE IS 6 NUMBERS NOT A 5 NUMBERS, SO NO NEED TO ADD ANYTHING.
I WANT A DATA FROM PARTICULAR DATE TO PARTICULAR DATE FOR THAT CARDNO’s
Query
SELECT T_PERSON.CARDNO, T_CARDEVENT.CARDEVENTDATE, MIN(T_CARDEVENT.CARDEVENTTIME), T_CARDEVENT.CARDEVENTDATE, CASE WHEN MIN (cardeventtime) = MAX(cardeventtime) THEN 'Nodata' ELSE MAX(cardeventtime) END AS OUTTIME FROM T_PERSON LEFT OUTER JOIN T_CARDEVENT ON T_PERSON.CARDNO = T_CARDEVENT.CARDNO WHERE T_CARDEVENT.CARDEVENTDATE BETWEEN '" & sdate & "' AND '" & edate & "' AND T_CARDEVENT.CARDEVENTTIME BETWEEN 030001 AND 030000 GROUP BY T_PERSON.CARDNO, T_CARDEVENT.CARDEVENTDATE ORDER BY CARDNO, CARDEVENTDATE
'" & sdate & "' – From date '" & edate & "' – To date
T_CARDEVENT.CARDEVENTTIME BETWEEN 030001 AND 030000 – This is problem to me because it is taking today 03:00:01 to 03:00:00
I need in time and out time of the particular cardno from this date to this date.
But this time should take 03:00:01 to 03:00:00 means (yesterday 03 am to today 03 am) The Time should change 03:00:01 to 03:00:00 instead of 00:00:01 to 23:59:59
I need the sql query for the above condition. Help me?