InTime and OutTime for the modified date?

Question

Using SQL Server 2000 Table

CARDNO         CARDEVENTDATE       CARDEVENTDATE    CARDEVENTTIME
                                     ADJUSTED 

  0121      20090611         20090610         025050
  0121      20090611         20090611         040000
  0121      20090611         20090611         050000
  0121      20090612         20090611         020000
  0122      20090611         20090611         030001
  0122      20090612         20090611         030000
  0123      20090611         20090611         080000
  0123      20090611         20090611         100000
  0123      20090611         20090611         132449
  0123      20090612         20090611         025959
  0124      20090611         20090610         030000
  0124      20090612         20090612         030001
  0125      20090611         20090611         030002
  0125      20090612         20090612         040000

From the Above table I want to display Intime and Outtime for the Particular Card Event Adjusted Date

Leave the CardEventDate.

I want to Show Cardno, CardeventDateAdjusted, Cardeventtime

Cardno – 121
CardeventDateAdjusted - 20090611
Intime – 040000, Outtime – 020000

Cardno – 121
CardeventDateAdjusted - 20090610
Intime – 025050, Outtime – No

Cardno – 122
CardeventDateAdjusted - 20090611
Intime – 030001, Outtime – 030000

………. So on,

Intime should > 030000 and Outtime should < 030001 according to the cardeventdateadjusted.

In Cardeventtime column, the First value should take Intime and Last value should take Outtime according to Cardno, Cardeventdateadjusted.

Expected Output Like this.

CARDNO           CARDEVENTDATE  INTIME  OUTTIME
                    ADJUSTED   

   0121             20090611        040000  020000
   0121             20090610        025050     -
   0122             20090611        030001  030000
   0123             20090611        080000  025959
   0124             20090610        030000     -
   0124             20090612        030001     -
   0125             20090612        040000     -
   0125             20090611        030002     -

There is Blank column in output time, because there is no Outtime on the Date. So I need Output Like this.

Answer 1

OK, I know it's going to be uggly, but you can do...

SELECT CARDNO, 
       Min(CardEvenDateAdjusted),
       Max(CardEvenDateAdjusted)
  FROM (
        SELECT CARDNO, 
               CARDEVENTDATE, 
               (1000000 * CAST (CARDEVENTDATE AS BIGINT) + 
                          CAST (CARDEVENTTIME AS BIGINT) - 30001) / 1000000 AS CardEvenDateAdjusted, 
               CARDEVENTTIME 
          FROM T_CARDEVENT 
         WHERE (CARDEVENTDATE > 20090601) 
       )
 GROUP 
    BY CARDNO
 ORDER 
    BY CARDNO

Answer 2

Edit: the question's been altered in edit to the point that the long, detailed answer had nothing to do with its current state, so I'm also restarting from scratch.

The request is ambiguous (it's never clearly specified what's intime and what's outtime) and apparently contradictory. For example one request is:

Cardno – 121 CardeventDateAdjusted - 20090610 Intime – 025050, Outtime – No

but another specified point is

Intime should > 030000 and Outtime should < 030001 according to the cardeventdateadjusted.

There's zero info in the question as it now stands about what the "according to" might mean (what arithmetic manipulations it might imply or require). Ignoring this totally-unspecified proviso, you're asking to see 025050 and ALSO asking that it be > 030000 : the laws of arithmetic prohibit that.

I've wasted far too many hours on trying to read your mind and resolve contradictions and ambiguities -- it's getting ridiculous. Unless the next edit produces a well-specified, non-ambiguous, non-contradictory spec, I'm outta here.