Solved

Select to distinguish a value?

Posted on 2011-02-13
4
384 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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 to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

809 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