Help with a query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Help with a query

Author

Topic

vaddi
Posting Yak Master

145 Posts

Posted - 2007-04-13 : 01:51:46

I have a table in sql server 2000 in the following way:

Projet_ID Date_Type Delivery_Date Delivered_Date

1001 Start date 01/11/2006
1001 End Date 02/11/2006 03/11/2006.

I need a query where i can get project_ID , start date as 01/11/2006 ( as shown in delivery_date ) , end date 02/11/2006 as estimateddeliverydate and delivered_date 03/11/2006 as actual enddate.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-13 : 02:06:49

[code]
select Projet_ID,
start_date = max(case when date_type = 'Start date' then delivery_date end),
end_date = max(case when date_type = 'End Date' then delivery_date end),
delivered_date = max(case when date_type = '~~Start~~End Date' then delivered_date_date end)
from yourtable
group by Projet_ID
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-13 : 02:12:09

[code]-- Prepare sample data
DECLARE @Sample TABLE (ProjectID INT, DateType VARCHAR(20), DeliveryDate DATETIME, DeliveredDate DATETIME)

INSERT @Sample
SELECT 1001, 'Start date', '01/11/2006', NULL UNION ALL
SELECT 1001, 'End Date', '02/11/2006', '03/11/2006'

-- Show the expected output
SELECT ProjectID,
MAX(CASE
WHEN DateType = 'Start date' THEN DeliveryDate
ELSE NULL
END) AS StartDate,
MAX(CASE
WHEN DateType = 'End date' THEN DeliveryDate
ELSE NULL
END) AS EndDate,
MAX(CASE
WHEN DateType = 'End date' THEN DeliveredDate
ELSE NULL
END) AS ActualEndDate
FROM @Sample
GROUP BY ProjectID
ORDER BY ProjectID[/code]

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-13 : 03:24:51

You become very slow Peso

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-13 : 03:33:36

quote:
Originally posted by madhivanan

You become very slow Peso

Madhivanan

Failing to plan is Planning to fail

He must have seen the training video

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-13 : 08:23:11

But I am accurate

Check your DeliveredDate. It should be DateType "End date". Right?

Peter Larsson
Helsingborg, Sweden

vaddi
Posting Yak Master

145 Posts

Posted - 2007-04-13 : 09:12:35

Thank guys. I need another small addition to the above.

I have got another table

Project_ID TL FL PM
1001 AA BB CC
1002 DD EE FF.

I need to connect this table and the above table with the project_ID.
The project_Id in both the tables do not have any PK and FK relationship.

I am trying to get the output which looks like

Project_ID , TL , FL , PM ( from this table) , start_date , estimateddeliverydate , actual enddate ( from the previous table).

I am trying to link them using left outer join , but I am getting an error , TL , FL , PM don't contain an aggregate function or group by clause.

How can I go about this.

Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-13 : 09:19:20

Is this a new question?
PLEASE POST A NEW TOPIC...

SELECT		nt.Project_ID,
		nt.TL,
		nt.FL,
		nt.PM,
		x.StartDate,
		x.EndDate,
		x.ActualEndDate
FROM		NewTable AS nt
INNER JOIN	(
			SELECT		ProjectID,
					MAX(CASE
						WHEN DateType = 'Start date' THEN DeliveryDate
						ELSE NULL
					END) AS StartDate,
					MAX(CASE
						WHEN DateType = 'End date' THEN DeliveryDate
						ELSE NULL
					END) AS EndDate,
					MAX(CASE
						WHEN DateType = 'End date' THEN DeliveredDate
						ELSE NULL
					END) AS ActualEndDate
			FROM		@Sample
			GROUP BY	ProjectID
		) AS x ON x.ProjectID = nt.Project_ID
ORDER BY	nt.Project_ID

Peter Larsson
Helsingborg, Sweden

EDIT: Exercise over people. Nothing more to see. Move on...

vaddi
Posting Yak Master

145 Posts

Posted - 2007-04-13 : 10:28:27

Hi Peso,

Its the same query.

When I executed the query , you have given I was getting only the first table resutls i.e project_id , TL , FL and PM.
I am not getting the dates.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-13 : 10:30:19

quote:
Originally posted by Peso

But I am accurate
Check your DeliveredDate. It should be DateType "End date". Right?

Peter Larsson
Helsingborg, Sweden

vaddi
Posting Yak Master

145 Posts

Posted - 2007-04-13 : 12:06:25

Hi Peso,

I have got it working. Thanks a lot for the help.

I have added X.startdate , X.enddate, X.actualenddate in the main query.

Thanks once again.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-13 : 12:09:21

I know you could fix it!

Peter Larsson
Helsingborg, Sweden

vaddi
Posting Yak Master

145 Posts

Posted - 2007-04-13 : 12:10:27

Only the last 1% is what I have done

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources