CODEWITHSUNDEEP
sql
Shane LeBlanc
Shane LeBlanc

Reputation: 2643

Query To Return Multiple Values From A Single Column Based On Value Of Another Column

I have a table with an EntryDate and a ChecklistDay column. They are Date and Integer columns. I want to return two columns, one named StartDate and another EndDate, both getting their values from the EntryDate column. StartDate would be returned from...

SELECT EntryDate AS StartDate FROM TABLE WHERE ChecklistDay = 1

EndDate would be returned from...

SELECT EntryDate AS EndDate FROM TABLE WHERE ChecklistDay = 10

Now I only want to return a row if a value is returned for both StartDate and EndDate, meaning that a ChecklistDay must have both values 1 and 10 in order for it to select the two EntryDates and return a row. What kind of query do I use here?

Upvotes: 1

Views: 2357

Answers (3)

Milen
Milen

Reputation: 8867

Would this help:

Select CASE ChecklistDay WHEN 1 THEN EntryDate ELSE NULL as StartDate,   
CASE CheckListDay WHEN 10 THEN DateAdd(day, ChecklistDay, StartDate) ELSE NULL END as EndDate   
from Table

Upvotes: 0

Aguardientico
Aguardientico

Reputation: 7779

This should run in any ANSI compatible DBMS (Oracle, MySql, Sql Server, Postgresql, Informix, DB2, etc)

SELECT
   CASE ChecklistDay WHEN 1 THEN EntryDate ELSE NULL END as StartDate
 , CASE CheckListDay WHEN 10 THEN EntryDate ELSE NULL END as EndDate
FROM
   TABLE
;

Upvotes: 0

Bill Gregg
Bill Gregg

Reputation: 7147

You can join to the same table twice.

select startDt.EntryDate as StartDate,
       endDt.EntryDate as EndDate
from table startDt
inner join table endDt
on startDt.id = endDt.id
where startDt.ChecklistDay = 1
and endDt.CheckListDay = 10

Upvotes: 2

Related Questions