Solved

Select to distinguish a value?

Posted on 2011-02-13
4
375 Views
Last Modified: 2012-05-11
I want to one line that is something like ths:

Model    Received_date   Shipped_Date
Ford       12 Jan 10            15 Jan 10

The problem is that both dates are "events" in the same table so I get

Model    Event_TYPE     Event_Date
Ford       Received          12 Jan 10
Ford       Shipped            15 Jan 10

Is there a way to select
Event_TYPE (Received)
Event_Date (Received)

then select

Event_type (Shipped)
Event_Date (Shipped)

Does this make sense?
0
Comment
Question by:swjtx99
  • 2
4 Comments
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 34885545
you can try this

SELECT Model, MIN(CASE WHEN Event_TYPE = 'Received' THEN Event_Date END) AS Received_Date,
MIN(CASE WHEN Event_TYPE = 'Shipped' THEN Event_Date END) AS Shipped_Date
FROM TableName
GROUP BY Model

Open in new window


or

SELECT T.Model, T.Event_Date AS Received_Date, S.Event_Date AS Shipped_Date
FROM TableName T
INNER JOIN (SELECT Model, Event_Date FROM TableName WHERE Event_TYPE = 'Shipped') S
ON T.Model = S.Model
WHERE T.Event_TYPE = 'Received'

Open in new window

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 34885941
sql 2005 onwards...

select model,received,shipped
 from yourtable as x
 pivot (max(event_date) for event_type in ('Received','Shipped')) as pvt
 order by 1

pre sql 2005

select model
     ,  max(case event_type when 'received' then event_date end) as Received
       ,  max(case event_type when 'shipped' then event_date end) as Shipped
from yourtable
group by model
order by 1
0
 

Author Comment

by:swjtx99
ID: 34893957
sorry for the delay in answering. Got knocked off.

Thanks for the help!
0
 

Author Closing Comment

by:swjtx99
ID: 34893958
Many thanks,
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now