SQL query for top and bottom value for a particular date

Question

SQL Server 2000

My Table:

CARDNO  CARDEVENTDATE CARDEVENTTIME
121 20090610 025050
 121    20090611 040000
121 20090611 050000
121 20090611 020000
122 20090611 030001
122 20090611 030000
123 20090611 080000
123 20090611 100000
123 20090611 132449
123 20090611 025959
124 20090610 030000
124 20090612 030001
125 20090611 030002
125 20090612 040000

Cardno is Separate Table Cardeventdate, cardeventtime is separate table

From the above table I want to get Top Time and Bottom Time for the Particular cardeventdate and Cardno

For the 121, 20090611, Top Time is 040000, Bottom Time is 020000 For 123, 20090611, Top Time is 080000, Bottom Time is 025959 … Like this I need.

I used Min (time) and Max (time), But it showing like this.

For CardNo – 121 Cardeventdate – 20090611 Min Time – 020000 Max Time – 040000

I don’t want to get min and Max, I need only top and Bottom (or) First and Last time value of the particular Date and Cardno.

I used this Query

SELECT     RowNumber = IDENTITY (int, 1, 1), CARDNO, CARDEVENTDATE, CARDEVENTTIME INTO #Table1 FROM T_CARDEVENT SELECT     t1.CARDNO, t1.CARDEVENTDATE, t1.CARDEVENTTIME  FROM #Table t1 INNER JOIN (SELECT     RowNumber = MIN(RowNumber), CARDEVENTDATE, CARDNO  FROM  #Table1 t  WHERE      (cardeventdate > 20090601) GROUP BY cardno, cardeventdate  UNION ALL  SELECT     MAX(RowNumber), CARDEVENTDATE, CARDNO FROM #Table1 t WHERE     (cardeventdate > 20090601) GROUP BY cardno, cardeventdate) t2 ON t2.rownumber = t1.rownumber

Output:

ROWNUMBER   CARDNO CARDEVENTDATE CARDEVENTTIME
335 0121            20090611     040000
1099    0121            20090611     050000
1100    0121            20090611     025050
336 0121            20090612     020000
337 0122            20090611     030001
338 0122            20090612     030000
339 0123            20090611     080000
1101    0123            20090611     100000
1102    0123            20090611     132449
340 0123            20090612     025959
341 0124            20090611     030000
342 0124            20090612     030001
343 0125            20090611     030002
344 0125            20090612     040000

So Here Row Number is created for all columns, from that how I have to take First Time and Last Time for the Particular Date.

Expecting Output

CARDNO  CARDEVENTDATE CARDEVENTTIME Expecting
0121    20090611 040000 Top Value
0121    20090611 020000 No Need
0121    20090611 025050 Bottom Value

……… so on

Need Query Help.

Answer 1

Well, unless you have some additional fields to establish an order, this is non deterministic. Given the three values for the 23-04-2009 - how is it that APPLE is the first and ROSE is the last? If the ID and the DATE are the same for all three entries, there's no order defined to filter out "GRAPHE"......

Marc

UPDATE: I expanded on Lieven's idea a bit and got this working in my setup:

DECLARE @TempTable TABLE (RowNumber INT IDENTITY(1,1), 
                          DayNumber INT, 
                          ID VARCHAR(3), DateField DATETIME, Value VARCHAR(32))

INSERT INTO @TempTable(DayNumber, id, datefield, value)
    SELECT DATEPART(DAYOFYEAR, DateField), ID, DateField, Value
    FROM @Table

SELECT * 
FROM @TempTable t
INNER JOIN 
   (SELECT RowNumber = MIN(RowNumber), DayNumber, ID
    FROM @TempTable t
    GROUP BY DayNumber, t.ID
    UNION ALL 
    SELECT MAX(RowNumber), DayNumber, ID
    FROM @TempTable t
    GROUP BY DayNumber, t.ID) t2 
  ON t2.RowNumber = t.RowNumber
GO

I'm basically creating a temp table with additional info - an artificial "RowNumber" to create some order, and the "DayNumber" to get dates grouped by date only (without time).

Seems to work ok for me - does it work for you, too?

Answer 2

I havn't tried it, but maybe something like this might do it:

SELECT TOP(1) FROM Table
WHERE Date='Some-date'
AND Id=Some-Id
ORDER BY Date ASC
UNION
SELECT TOP(1) FROM Table
WHERE Date='Some-date'
AND Id=Some-Id
ORDER BY Date Desc

Answer 3

to cheat you can add an autonumber feild AUtoID ID DATE VALUE 1 001 23:04:2009 APPLE 2 001 23:04:2009 GRAPHE 3 001 23:04:2009 ROSE 4 001 24:04:2009 BERRY 5 001 24:04:2009 TIFFANY 6 001 24:04:2009 ORGANE 7 001 24:04:2009 SILVER

You can then do min and max against it

I usually use insert into a tempory table which is defined with the ID, technically the insertion order isn't garenteed going in.

Answer 4

Jash, if you execute this script, does it give you the results you'd expect?

CREATE TABLE #T_Cardevent (CARDNO VARCHAR(3), CARDEVENTDATE VARCHAR(8), CARDEVENTTIME VARCHAR(8))

INSERT INTO #T_Cardevent VALUES ('121', '20090610', '025050')
INSERT INTO #T_Cardevent VALUES ('121', '20090611', '040000')
INSERT INTO #T_Cardevent VALUES ('121', '20090611', '050000')
INSERT INTO #T_Cardevent VALUES ('121', '20090611', '020000')
INSERT INTO #T_Cardevent VALUES ('122', '20090611', '030001')
INSERT INTO #T_Cardevent VALUES ('122', '20090611', '030000')
INSERT INTO #T_Cardevent VALUES ('123', '20090611', '080000')
INSERT INTO #T_Cardevent VALUES ('123', '20090611', '100000')
INSERT INTO #T_Cardevent VALUES ('123', '20090611', '132449')
INSERT INTO #T_Cardevent VALUES ('123', '20090611', '025959')
INSERT INTO #T_Cardevent VALUES ('124', '20090610', '030000')
INSERT INTO #T_Cardevent VALUES ('124', '20090612', '030001')
INSERT INTO #T_Cardevent VALUES ('125', '20090611', '030002')
INSERT INTO #T_Cardevent VALUES ('125', '20090612', '040000')

SELECT     
  RowNumber = IDENTITY (int, 1, 1)
  , CARDNO
  , CARDEVENTDATE
  , CARDEVENTTIME 
INTO #Table
FROM #T_CARDEVENT 

SELECT t1.CARDNO, t1.CARDEVENTDATE, t1.CARDEVENTTIME  
FROM #Table t1 
     INNER JOIN (
        SELECT RowNumber = MIN(RowNumber), CARDEVENTDATE, CARDNO 
        FROM #Table t  
        GROUP BY cardno, cardeventdate  
        UNION ALL SELECT MAX(RowNumber), CARDEVENTDATE, CARDNO 
        FROM #Table t 
        GROUP BY cardno, cardeventdate) t2 ON t2.rownumber = t1.rownumber
ORDER BY 1, 2, 3

DROP TABLE #Table
DROP TABLE #T_Cardevent

Answer 5

Here's the solution using inline views:

with 
tempFirst as (
    select id, date, value, 
     row_number() over (partition by id, date order by date asc) as rownum1
    from table1
),
tempLast as ( 
    select *, 
     row_number() over (partition by id, date order by rownum1 desc) as rownum2 
    from tempFirst
)
select id, date, value from tempFirst where rownum1 = 1
union
select id, date, value from tempLast where rownum2 = 1

I have tested the output, which is:

ID  DATE       VALUE
001 2009-04-23 APPLE
001 2009-04-23 ROSE
001 2009-04-24 BERRY
001 2009-04-24 SILVER