1

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?

Improve this question
3
  • 1
    Which DBMS you are using? Commented Jun 26, 2013 at 15:49
  • How would one row from the table relate to another row in the same table? Is there some common ID that we can join through? Commented Jun 26, 2013 at 16:19
  • There is a ChecklistEntryID PK, and also a ProjectID FK. I need to find the start date and end date for each project. If an entry for ChecklistDay 1 and 10 are in the table, then that part of the project is complete and I need to count the days it took to complete. Commented Jun 26, 2013 at 16:35

3 Answers 3

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Understood. That's why I listed "table" twice, but aliased it. This is what you wanted. You can join a single table to itself.
I don't see the alias for table in this query. Only aliases for columns. In any case, there is an error in design of this database - here is no bullet proof way to say if two rows are actually a pair of related data.
Elmo Bill is aliasing table as startDt and table as endDt, remember that [as] is optional
If you can't relate the rows, then given 4 start dates and 4 end dates, how can you return only 4 rows? Or would you expect the cross product of each start date with each end date, yielding 16 rows?
@ElmoVanKielmo TABLE was an example of the actual table name, it's not actually named TABLE. I'll correct that in an edit. Also, how could I make the design more efficient? I want to allow a user to enter a particular day that they are at on a project sheet. So if milestone 1 starts on checklistday 1, then when they hit the beginning of checklistday 10 it commences milestone 2, indicating that milestone 1 took the number of days from the date they started on checklistday 1 to 10. Perhaps I should begin another thread, this probably isn't too clear.
|
0

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
;
Improve this answer

2 Comments

He wants to return a result row only for pair of rows, where one has a checklistday = 1 and another checklistday = 10.
You are right @ElmoVanKielmo, I misunderstood the question, then Bill Gregg answer is correct.
0

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
Improve this answer

1 Comment

He wants to return a result row only for pair of rows, where one has a checklistday = 1 and another checklistday = 10. Your answer is clever, but doesn't check if the complementary row exists.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.