Can't Figure out the 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

SQL Server 2005 Forums

Transact-SQL (2005)

Can't Figure out the query

Author

Topic

furrelkt
Starting Member

49 Posts

Posted - 2008-02-08 : 12:27:14

I would like some help with this query please. I know it's probably easy, but i can't figure it out.
I have this:

 
SELECT  audit_proj_id pid 
        ,audit_log_type log_type
        ,audit_proj_status status
        ,audit_proj_unique_name project_id
        ,audit_user_last_name + ', ' + audit_user_first_name resource
        ,audit_changed date_changed
FROM auditFPS

The results are:


pid	log_type	status	project_id	resource	date_changed
5004304	OLD	Confirmed	39IMP-IMA-BSPPN	Giarratani, Ruthann	2008-02-08 11:45:28.300
5004304	NEW	Complete	39IMP-IMA-BSPPN	Giarratani, Ruthann	2008-02-08 11:45:28.300

How would i go about designing the query so that the results are:


pid	project_id	old	new	resource	date_changed
5004304	39IMP-IMA-BSPPN	Confirmed  Complete	Giarratani, Ruthann	2008-02-08 11:45:28.300

I can get this by using a UNION, But of course the status is not filled in.

pid	project_id	old	new	resource	date_changed
5004304	39IMP-IMA-BSPPN			Giarratani, Ruthann	2008-02-08 11:45:28.300

Any help would really be appreciated.
Thanks

Keri

~"I can accept failure, but I can't accept not trying." - Michael Jordan ~

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 12:36:25

Try this:-

;
With Your_CTE (PID,LogType,Status,ProjectID,Resource,DateChanged) AS
(
SELECT  audit_proj_id 
        ,audit_log_type 
        ,audit_proj_status
        ,audit_proj_unique_name 
        ,audit_user_last_name + ', ' + audit_user_first_name 
        ,audit_changed 
FROM auditFPS 
)

SELECT t1.PID,t1.ProjectID,
t1.Status AS Old,
t2.Status AS New,
t1.Resource,
t1.DateChanged
FROM Your_CTE t1
INNER JOIN Your_CTE t2
ON t1.PID=t2.PID
AND t1.ProjectID=t2.ProjectID
AND t1.LogType='OLD'
AND t2.LogType='NEW'

furrelkt
Starting Member

49 Posts

Posted - 2008-02-08 : 13:18:55

Thank you so much for your help. Now i see how to do this. I also was able to figure this out by your example by using the derived table...I am just learning the CTE.


SELECT  a.audit_proj_id pid 
        ,a.audit_fiserv_proj_status old
        ,b.audit_fiserv_proj_status new
        ,a.audit_proj_unique_name project_id
        ,a.audit_user_last_name + ', ' + a.audit_user_first_name resource
        ,a.audit_changed date_changed
FROM auditFPS a
INNER JOIN (select audit_proj_id 
        ,audit_fiserv_proj_status 
        ,audit_proj_unique_name
        ,audit_user_last_name + ', ' + audit_user_first_name resource
        ,audit_changed 
        from auditFPS where audit_log_type = 'NEW'
        ) b ON a.audit_proj_id = b.audit_proj_id AND a.audit_proj_unique_name = b.audit_proj_unique_name
  WHERE a.audit_log_type = 'OLD'

So i will get the same results using the derived table.

Makes Sense to me now. Thanks again, i really appreciate it.

Keri

~"I can accept failure, but I can't accept not trying." - Michael Jordan ~

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 13:21:46

You are welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources