Re: TRICKY SQL
From: Steve Kass (skass_at_drew.edu)
Date: 08/27/04
- Next message: Andy Ball: "Re: SQL Server For Linux"
- Previous message: Khor: "DB backup job for DB maintenance plan failed"
- In reply to: MS User: "TRICKY SQL"
- Next in thread: Hugo Kornelis: "Re: TRICKY SQL"
- Reply: Hugo Kornelis: "Re: TRICKY SQL"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 26 Aug 2004 23:45:10 -0400
MS
If you are certain that move types A and E exist for each id, and also
certain that the move_dates for A and E are the earliest and latest
dates for the id, respectively, then the move type column is irrelevant
for this query, and you can write
SELECT
id,
min(move_date) as A_move_date,
max(move_date) as E_move_date
from YourTable
group by id
If you need to explicitly choose the A and E rows, here is an
alternative to Hugo's suggestion:
SELECT
id,
max(case when move_type = 'A' then move_date end) AS A_move_date,
max(case when move_type = 'E' then move_date end) AS E_move_date
FROM YourTable
GROUP BY id
It's probably a little less readable, but more compact. Hugo's query
handles the possibility that there is more than one move_type E row for
each id (but only one move_type A row, I think), and itf for example
(id, move_type) is constrained to be unique, it may be sufficient to write
SELECT
A.id,
A.move_date AS A_move_date,
(
SELECT move_date
FROM YourTable AS E
WHERE E.id = A.id
AND E.move_type = 'E'
) AS E_move_date
FROM YourTable AS A
WHERE A.move_type = 'A'
Steve Kass
Drew University
MS User wrote:
>SQL 2K
>
>
>Sample data
>
>
>id move_type move_date
>-----------------------------
>1 A 07/01/2004
>1 C 07/02/2004
>1 E 07/05/2004
>
>2 A 07/05/2004
>2 B 07/05/2004
>2 C 07/06/2004
>2 D 07/08/2004
>2 E 07/08/2004
>
>1 A 07/07/2004
>1 E 07/10/2004
>
>For all ID's, the 'move_type' starts with type 'A' and ends with 'E', it may
>or maynot have 'move_type' like 'B', 'C' and 'D' in between and
>move_date will be changing for each move_type for an ID (Min for move_type
>'A' and max for move_type 'E').
>
>Same ID may repeat another set ,as in the example (ID = 1).
>
>
>I am interested only on move_type 'A' and 'E' for each set,
>
>Expected output
>
>id A_move_date E_move_date
>1 07/01/2004 07/05/2004
>2 07/05/2004 07/08/2004
>1 07/07/2004 07/10/2004
>
>Any thoughts/ideas will be highly appreciated.
>
>
>Thanks
>MS
>
>
>
>
- Next message: Andy Ball: "Re: SQL Server For Linux"
- Previous message: Khor: "DB backup job for DB maintenance plan failed"
- In reply to: MS User: "TRICKY SQL"
- Next in thread: Hugo Kornelis: "Re: TRICKY SQL"
- Reply: Hugo Kornelis: "Re: TRICKY SQL"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
