Re: TRICKY SQL

From: Steve Kass (skass_at_drew.edu)
Date: 08/27/04 

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
>
>
>
>

Relevant Pages

  • Re: TRICKY SQL
    ... for this query, and you can write ... each id, and itf for example ... Steve Kass ... Drew University ...
    (microsoft.public.sqlserver.server)
  • Re: How to filter out the items between the delimiter ";"?
    ... Steve Kass ... Drew University ... >If I delete it the record may not have 2 or more delimiters after each ... >Can somebody help me how to build this query? ...
    (microsoft.public.sqlserver.programming)
  • Re: Getting comma separated values in a column
    ... Steve Kass ... Drew University ... Jake wrote: ... >I need to create a query that will return individual values like so... ...
    (microsoft.public.sqlserver.programming)
  • Re: Query question. Can be very simple. Please take a look...
    ... > Steve Kass ... > Drew University ... > TomTom wrote: ... >>the query is not an issue and the number of query can be more than one if ...
    (microsoft.public.sqlserver.programming)
  • Re: Excel -> SQL Server
    ... Steve Kass wrote: ... > If I create D:\format.xls with a sheet named actions, ... I cut and pasted that statement directly from Query Analyser. ... just after I've run the command in QA and I try to open the xl ...
    (microsoft.public.sqlserver.programming)