help in writing a query ( Insert Query)

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)

help in writing a query ( Insert Query)

Author

Topic

kneekill
Yak Posting Veteran

76 Posts

Posted - 2008-02-12 : 07:02:55

Hi

need help in writing a query for the follow scenario

Input Table A

each ProcessingID can have any number of StatusID s and the same StatusID can repeat for one given ProcessingID.

example ProcessingID : 100 has four rows in the table ,3 rows have statusID as 1 and 1 row has status ID as 4

Desired Output Table B

example : ProcessingID - 100 has 3 dates for StatusID - 1 then the highest date of the three dates needs to be inserted into Table B. the same need to be done with each and every processingID and statusIDs corresponding to them

Waiting For ur replies

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-12 : 07:07:19

Can you provide table structure of your tables with output you want? Things are not clear in your post.

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-12 : 07:09:42

Select ProcessingID,
max(case when statusid=1 then statusdate end) as status1date,
max(case when statusid=2 then statusdate end) as status2date,
max(case when statusid=3 then statusdate end) as status3date,
max(case when statusid=4 then statusdate end) as status4date
from table
group by ProcessingID

Madhivanan

Failing to plan is Planning to fail

kneekill
Yak Posting Veteran

76 Posts

Posted - 2008-02-12 : 07:13:18

quote:
Originally posted by sunil

Can you provide table structure of your tables with output you want? Things are not clear in your post.

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

The table structure are posted in the image files attached...(i guess u are not able to see the image..)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-12 : 07:15:08

we can. We were expecting the table structure in DDL statement and sample data in insert into table . . . statement.

KH
[spoiler]Time is always against us[/spoiler]

kneekill
Yak Posting Veteran

76 Posts

Posted - 2008-02-12 : 07:34:03

quote:
Originally posted by madhivanan

Select ProcessingID,
max(case when statusid=1 then statusdate end) as status1date,
max(case when statusid=2 then statusdate end) as status2date,
max(case when statusid=3 then statusdate end) as status3date,
max(case when statusid=4 then statusdate end) as status4date
from table
group by ProcessingID

Madhivanan

Failing to plan is Planning to fail

Thanks a lot madhivanan..it worked like charm thanks a lot for the help....

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-12 : 07:36:41

Oh! Seems problem with my IE

. Could not see image.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-12 : 07:37:41

quote:
Originally posted by kneekill

quote:
Originally posted by madhivanan

Select ProcessingID,
max(case when statusid=1 then statusdate end) as status1date,
max(case when statusid=2 then statusdate end) as status2date,
max(case when statusid=3 then statusdate end) as status3date,
max(case when statusid=4 then statusdate end) as status4date
from table
group by ProcessingID

Madhivanan

Failing to plan is Planning to fail

Thanks a lot madhivanan..it worked like charm thanks a lot for the help....

You are welcome

Read about Cross-tab reports in sql server help file for more informations

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-12 : 07:38:42

quote:
Originally posted by sunil

Oh! Seems problem with my IE. Could not see image.

Which version of IE?

Madhivanan

Failing to plan is Planning to fail

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-12 : 07:44:52

quote:
Originally posted by madhivanan

quote:
Originally posted by sunil

Oh! Seems problem with my IE. Could not see image.

Which version of IE?

Madhivanan

Failing to plan is Planning to fail

IE 6 SP2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources