Query twice on the same column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query twice on the same column

Author

Topic

Lenham
Starting Member

6 Posts

Posted - 2004-09-14 : 00:41:20

I have a database which includes a table called ‘Job_status_log’ each change in a jobs status creates an entry which includes the ‘Job_No’ ‘Date’ and ‘Status_Code’.

I need to run a query that shows the days elapsed between say ordered(status code 2) and Completed(status Code 5). Date calculations I am fine with.

My problem is how do I create a SELECT statement to display both from the same Table and Column for each job?

I am sorry if I have missed a simple solution for this, but I just can’t think of one at the moment.

Thanking you for any assistance

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-14 : 01:22:20

You can do it with inner joins:

SELECT A.Job_No, A.Date, A.Status_Code, B.Date, DateDiff(d, A.Date, B.Date)
FROM Job_Status_Log A INNER JOIN Job_Status_log B ON A.Job_No = B.Job_No AND A.Status_Code = 2 AND B.Status_Code = 5

Lenham
Starting Member

6 Posts

Posted - 2004-09-15 : 18:51:02

Hi Timmy,

Thanks for your response, unfortunately perhaps I did not explain my problem fully.
the Job_Status_log table that I need to query is like this.

Job_No--Logged_Date---------------------Status_Code

1-------12/09/2003 11:42:34 a.m.--------2 (ordered)
2-------17/09/2003 10:24:06 a.m.--------2 (ordered)
3-------23/09/2003 4:26:33 p.m.---------2 (ordered)
2-------23/06/2004 5:34:23 p.m.---------3 (printed)
1-------25/09/2003 9:36:02 a.m.---------3 (printed)
1-------27/09/2003 8:38:22 a.m.---------5 (completed)
4-------17/10/2003 3:39:57 p.m.---------2 (ordered)
3-------29/09/2003 8:28:02 a.m.---------5 (completed)

I am looking to produce a result set something like this.

Job_No--Date_Ordered----Date_Completed--Days_Taken

1-------12/09/2003------27/09/2003------15
3-------23/09/2003------29/09/2003------6

Job No 2 would not be listed (or have NULL in the Date_Completed) as it does not have a status of 5 in the log.

As you can see in the results, I need the same column (Logged_date) output twice depending upon the status_code.

Any assistance much appreciated.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-15 : 19:23:43

Job 2 is not output by my query either.
The output is:
1 2003-09-12 11:42:34.000 2 2003-09-27 08:38:22.000 15
3 2003-09-23 16:26:33.000 2 2003-09-29 08:28:02.000 6
Just change the column names and positioning and you've got it.

Lenham
Starting Member

6 Posts

Posted - 2004-09-16 : 16:51:39

Thanks Timmy,

I really appreciated the help, I now understand (well at least that bit)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources