combining into a single row - 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 2000 Forums

Transact-SQL (2000)

combining into a single row

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-01-31 : 20:18:09

[code]

item_id emp_id entry_date description initial_amount other
1 1090554 09/27/04 MA STARTED
2 1090554 09/29/04 MA COMPLETED 5000.00 55000
34 2388601 04/15/02 MA STARTED
35 2388601 04/24/02 MA COMPLETED 22000.00 122000
36 2388601 10/09/02 MA STARTED
37 2388601 10/16/02 MA COMPLETED 39000.00 139000
38 2388601 04/08/03 MA STARTED
39 2388601 04/11/03 MA COMPLETED 50000.00 150000
40 2388601 10/08/03 MA STARTED
41 2388601 10/08/03 MA STARTED
42 2388601 10/15/03 MA COMPLETED 63000.00 163000
43 2388601 04/19/04 MA STARTED
44 2388601 04/23/04 MA COMPLETED 63000.00 163000
45 2388601 10/04/04 MA STARTED
46 2388601 10/06/04 MA COMPLETED 66000.00 166000[/code]

Here I need to write a query where I need to combine this different columns to a single row

of the table tbl_emp_category with the columns

emp_id ordered_date receieved_date amount advances

Here the conditions are as follows:-

If the description is 'MA STARTED' then get the entry_date as ordered_date in the table tbl_emp_category
IF the description is 'MA COMPLETED then get the entry_date as receieved_date in the table tbl_emp_category
and the intial_amount will be the amount and the other will be the advances in the table tbl_emp_category

one more thing we need take care is the multiple emp_ids.In that case we need to get the pair with the one with max entry_date.

please help me with the query

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-31 : 20:21:52

[code]
SELECT e1.emp_id, e1.entry_date AS ordered_date, e2.entry_date AS received_date, e2.initial_amount as amount, e2.other AS advances
FROM tbl_emp_category e1
INNER JOIN tbl_emp_category e2
ON e1.emp_id = e2.emp_id
[/code]

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-01 : 03:04:40

Here but would u get the max of the entry_date for that emp_id

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-01 : 12:19:02

You can use GROUP BY with MAX to get that. If you want a more detailed solution, please post your table with CREATE TABLE statement, and sample data with INSERT INTO statements.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-01 : 15:28:22

CREATE TABLE action_results
(
[item_id] [int] NOT NULL ,
[emp_id] [varchar] (20),
[entry_date] [datetime],
[description] [varchar] (50),
[intitial_amount] [money],
[other] [money]
)
ON [PRIMARY]

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (1,1090554,09/27/04,MA STARTED,MARKETINTE,NULL,NULL)

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (2,1090554,09/29/04,MA COMPLETED,MARKETINTE,5000.00,55000)

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (34,2388601,04/15/02,MA STARTED,MARKETINTE,NULL,NULL)

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (35,2388601,04/24/02,MA COMPLETED,MARKETINTE,22000.00,122000)

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (36,2388601,10/09/02,MA STARTED,MARKETINTE,NULL,NULL)

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (37,2388601,10/16/02,MA COMPLETED,MARKETINTE,39000.00,139000)

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (38,2388601,04/08/03,MA STARTED,MARKETINTE,NULL,NULL)

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (39,2388601,04/11/03,MA COMPLETED,MARKETINTE,50000.00,150000)

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (41,2388601,10/08/03,MA STARTED,MARKETINTE,NULL,NULL)

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (42,2388601,10/15/03,MA COMPLETED,MARKETINTE,63000.00,163000)

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (45,2388601,10/04/04,MA STARTED,MARKETINTE,NULL,NULL)

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (46,2388601,10/06/04,MA COMPLETED,MARKETINTE,66000.00,166000)

Here is the set of data and Now I want to get the results into the table tbl_emp_category from action_results
as mentioned

If the description is 'MA STARTED' then get the entry_date as ordered_date in the table tbl_emp_category
IF the description is 'MA COMPLETED' then get the entry_date as receieved_date in the table tbl_emp_category
and the intial_amount will be the amount and the other will be the advances in the table tbl_emp_category

one more thing we need take care is the multiple emp_ids.In that case we need to get the pair with the one with max entry_date.

please help me with the query

CREATE TABLE tbl_emp_category

(
[emp_id] [varchar] (20),
[ordered_date] [datetime],
[received_date] [datetime],
[amount] [money],
[advances] [money]
)
ON [PRIMARY]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-01 : 15:33:02

Please fix your insert into statements as they do not work. Make sure you try out the code that you post in Query Analyzer on a test box before posting so that we can begin working on a solution for you without having to figure out what is wrong.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-01 : 19:55:32

The scripts are

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (1,'1090554','09/27/04','MA STARTED','MARKETINTE',NULL,NULL)

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (2,'1090554','09/29/04','MA COMPLETED','MARKETINTE',5000.00,55000)

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (34,'2388601','04/15/02','MA STARTED','MARKETINTE',NULL,NULL)

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (35,'2388601','04/24/02','MA COMPLETED','MARKETINTE',22000.00,122000)

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (36,'2388601','10/09/02','MA STARTED','MARKETINTE',NULL,NULL)

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (37,'2388601','10/16/02','MA COMPLETED','MARKETINTE',39000.00,139000)

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (38,'2388601','04/08/03','MA' STARTED','MARKETINTE',NULL,NULL)

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (39,'2388601','04/11/03','MA COMPLETED','MARKETINTE',50000.00,150000)

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (41,'2388601','10/08/03','MA STARTED','MARKETINTE',NULL,NULL)

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (42,'2388601','10/15/03','MA COMPLETED','MARKETINTE',63000.00,163000)

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (45,'2388601','10/04/04','MA STARTED','MARKETINTE',NULL,NULL)

insert into action_results (item_id,emp_id,entry_date,description,intitial_amount,other)
values (46,'2388601','10/06/04','MA COMPLETED','MARKETINTE',66000.00,166000)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-02 : 12:39:18

Script still does not work. I take it you didn't try it out in Query Analyzer before posting. Copy your CREATE TABLE statement and your INSERT INTO statements into Query Analyzer and try running them. They don't work!

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-03 : 20:50:47

I tried with this query also still there was issues.Please tduggan can check why the query is not working properly.The problem is with the amount where we are trying to get the 'amount' attached with the decription='MA COMPLETED' and the having the max date for a emp_id

Select distinct
emp_id,
ordered_date = (select max(entry_date) from action_results where [description] = 'MA STARTED' and emp_id =a.emp_id),
received_date = (select max(entry_date) from action_results where [description] = 'MA COMPLETED' and emp_id =a.emp_id),
amount=
(
select intial_amount
from action_results t1
join (select distinct emp_id,max(date) as date from action_results where [description] = 'MA COMPLETED' group by emp_id)t2 on t1.emp_id =t2.emp_id and t1.date =t2.date and t1.[description] = 'MA COMPLETED'

)

from action_results a

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-03 : 22:37:13

I'll give you a clue
There is a rogue ' and the insert list doesnt match the select list, basically 1 into 2 doesnt go

Do what Tara asked you to do, by trying it in QA. I found your errors within 30 seconds!

Why? Because QA told me what the errors in the statement are or it was highlighted to me
Read up on INSERT in BOL

Andy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-04 : 12:25:41

As soon as you fix up the INSERT INTO statements, then we can begin working on a solution for you. We shouldn't have to debug your sample data script. That is up to the poster. If we were being paid for these solutions, then it'd be a different story.

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-04 : 17:36:49

I hope this query will help u..sorry for the wrong query...........PLease help me to figure the query...

CREATE TABLE action_results
(
[item_id] [int] NOT NULL ,
[emp_id] [varchar] (20),
[entry_date] [datetime],
[description] [varchar] (50),
[Attached_by] varchar(50),
[intitial_amount] [money],
[other] [money]
)

insert into action_results (item_id,emp_id,entry_date,description,Attached_by,intitial_amount,other)
values (1,'1090554','09/27/04','MA STARTED','MARKETINTE',NULL,NULL)

insert into action_results (item_id,emp_id,entry_date,description,Attached_by,intitial_amount,other)
values (2,'1090554','09/29/04','MA COMPLETED','MARKETINTE',5000.00,55000)

insert into action_results (item_id,emp_id,entry_date,description,Attached_by,intitial_amount,other)
values (3,'1090554','02/27/05','MA STARTED','MARKETINTE',NULL,NULL)

insert into action_results (item_id,emp_id,entry_date,description,Attached_by,intitial_amount,other)
values (4,'1090554','03/29/05','MA COMPLETED','MARKETINTE',2000.00,500)

insert into action_results (item_id,emp_id,entry_date,description,Attached_by,intitial_amount,other)
values (4,'1090589','09/27/04','MA STARTED','MARKETINTE',NULL,NULL)

insert into action_results (item_id,emp_id,entry_date,description,Attached_by,intitial_amount,other)
values (5,'1090589','09/29/04','MA STARTED','MARKETINTE',NULL,NULL)

--- Here if u check for emp_id 1090589 it doesn't have a 'MA COMPLETED' so no records needed of this type

insert into action_results (item_id,emp_id,entry_date,description,Attached_by,intitial_amount,other)
values (6,'1090600','09/29/04','TITER','MARKETINTE',5000.00,55000)

insert into action_results (item_id,emp_id,entry_date,description,Attached_by,intitial_amount,other)
values (7,'1090600','09/29/04','ESC','MARKETINTE',5000.00,55000)

insert into action_results (item_id,emp_id,entry_date,description,Attached_by,intitial_amount,other)
values (8,'1090600','09/29/04','MA COMPLETED','MARKETINTE',200.00,50)

--Here if u check for emp_id 1090600 there is no 'MA STARTED' So in that case the ordered date will be the same as the receievd_date(date obtained from 'MA COMPLETED'

insert into action_results (item_id,emp_id,entry_date,description,Attached_by,intitial_amount,other)
values (9,'2388601','04/15/02','MA COMPLETED','MARKETINTE',NULL,NULL)

insert into action_results (item_id,emp_id,entry_date,description,Attached_by,intitial_amount,other)
values (10,'2388601','04/24/02','MA COMPLETED','MARKETINTE',22000.00,122000)

insert into action_results (item_id,emp_id,entry_date,description,Attached_by,intitial_amount,other)
values (11,'2388601','10/09/02','MA STARTED','MARKETINTE',NULL,NULL)

insert into action_results (item_id,emp_id,entry_date,description,Attached_by,intitial_amount,other)
values (37,'2388601','10/16/02','MA COMPLETED','MARKETINTE',39000.00,139000)

insert into action_results (item_id,emp_id,entry_date,description,Attached_by,intitial_amount,other)
values (38,'2388601','04/08/03','MA STARTED','MARKETINTE',NULL,NULL)

insert into action_results (item_id,emp_id,entry_date,description,Attached_by,intitial_amount,other)
values (39,'2388601','04/11/03','MA COMPLETED','MARKETINTE',50000.00,150000)

insert into action_results (item_id,emp_id,entry_date,description,Attached_by,intitial_amount,other)
values (41,'2388601','10/08/03','MA STARTED','MARKETINTE',NULL,NULL)

insert into action_results (item_id,emp_id,entry_date,description,Attached_by,intitial_amount,other)
values (42,'2388601','10/15/03','MA COMPLETED','MARKETINTE',63000.00,163000)

insert into action_results (item_id,emp_id,entry_date,description,Attached_by,intitial_amount,other)
values (45,'2388601','10/04/04','MA STARTED','MARKETINTE',NULL,NULL)

insert into action_results (item_id,emp_id,entry_date,description,Attached_by,intitial_amount,other)
values (46,'2388601','10/06/04','MA COMPLETED','MARKETINTE',66000.00,166000)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-04 : 19:42:31

SqlLearner, the problem with your table structure is there is no way to associate the "pairs" of records. How can we know which MA Started goes with which MA Completed?

But assuming that the Started item_id is always 1 greater than the Completed item_id you could do this
I have no idea if the results are correct.
Warning ! this is ugly:


Select	a.emp_id
	,ordered_date = a.entry_date
	,received_date = b.entry_date
	,Amount = b.intitial_amount
	,advances = b.other
from action_results a
JOIN action_results b 
		ON a.item_ID = b.item_ID + 1
		AND a.emp_id = b.emp_id
Where	a.description = 'MA Started'
AND	a.entry_date = 
		(
		Select max(z.entry_date) 
		from action_results z
		JOIN action_results y
				ON z.item_ID = y.item_ID + 1
				AND z.emp_id = y.emp_id
		Where	z.description = 'MA Started'
		AND 	z.emp_id = a.emp_id
		)

Be One with the Optimizer
TG

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-04 : 22:03:19

WE should get the records with 'MA Started' and 'MA completed' with the max entry_date for each emp_id

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-04 : 22:11:15

and sorry one more thing the item_id is an auto generated number

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-04 : 22:38:10

quote:
WE should get the records with 'MA Started' and 'MA completed' with the max entry_date for each emp_id

That is impossible with your current structure. For any given emp_id's max entry_date, you're going to get either an [ma Started] OR an [ma completed].

If an [ma started] record is the max entry_date for an employee, how do you know which [ma completed] record goes with it? If an [ma completed] record is the max entry_date, how do you know which [ma started] record goes with it? As I said before you haven't said how to associate records as "pairs".

It looks like the only way to tell which records should be paired up is by the order you inserted them in your table which is totaly unreliable.

Be One with the Optimizer
TG

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-04 : 22:45:01

The criteria is to get the date as ordered_date as for 'MA-Started' which have the latest date out of the MA-Started for an emp_id and similar is the case with the 'MA completed'......

PLus the additional thing is for the amount get the intial_amount and other from latest 'Ma-Coompleted' one for each emp_id

and

--- Here if u check for emp_id 1090589 it doesn't have a 'MA COMPLETED' so no records needed of this type needs to be pulled...

--Here if u check for emp_id 1090600 there is no 'MA STARTED' So in that case the ordered date will be the same as the receievd_date(date obtained from 'MA COMPLETED'

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-04 : 22:47:25

I tried something like this....but still....haven't got the full solution..........

select distinct
emp_id,
item_id1 = (select max(item_id) from action_results where [description] = 'MA STARTED' and emp_id =a.emp_id),
item_id2= (select max(item_id) from action_results where [description] = 'MA COMPLETED' and emp_id =a.emp_id)
into #tempdb
from action_results a WHERE a.description in ('CMA STARTED','CMA COMPLETED')

--select * from #tempdb

select z.emp_id,
ordered_date =(select date from action_results b where b.item_id = z.item_id1),
received_date =(select date from action_results c where c.item_id = z.item_id2),
amount =(select amount from action_results c where c.item_id = z.item_id2),
other = (select other from action_results c where c.item_id = z.item_id2)
FROM #tempdb z where z.item_id2 IS NOT NULL

drop table #tempdb

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-04 : 23:41:32

How's this? If it's not what you want please post what the results should look like. (not just your criterial description)


Select	emp_id
	,isNull(max(ordered_date),max(received_date)) ordered_date
	,max(received_date) received_date
	,Amount = (Select max(intitial_amount) from action_results where emp_id = ar.emp_id and entry_date = max(received_date) and description = 'ma completed')
	,advances = (Select max(other) from action_results where emp_id = ar.emp_id and entry_date = max(received_date) and description = 'ma completed')
From	(
	Select	emp_id
		,max(entry_date) as ordered_date
		,convert(datetime,null) as received_date
	from	action_results
	where	description = 'MA Started'
	Group by emp_id
	
	UNION ALL
	
	Select	emp_id
		,convert(datetime,null) as ordered_date
		,max(entry_date) as received_date
	from	action_results
	where	description = 'MA Completed'
	Group by emp_id
	) as ar
group by ar.emp_id 
having	max(received_date) is not NULL

Be One with the Optimizer
TG

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-07 : 17:52:44

emp_id ordered_date received_date amount advances
-----------------------------------------------------------------------------
1090554 '02/27/05' '03/29/05' 2000.00 500
1090600 '09/29/04' '09/29/04' 200.00 50
2388601 '10/04/04' '10/06/04' 66000 166000

This is the result Iam expecting and it will work fine incase where the received date has the max amount but if the amount is max in some other item_id then it takes that recordset...

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-07 : 19:20:41

My criteria is the
"amount and advances should be from the latest recieved date of each emp_id of description 'MA COMPLETED'"

But what happens when I run the above query is it brings the the max(amount) of each emp_id

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources