combining to a single row

[SqlHunter]

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


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

 

[dbomrrsm]

look at case statement, concatenation, max(date) and group by

[blue]DBomrrsm[/blue]

 

[SqlHunter]

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]

 

[Pattycake245]

I am assuming that if you want the max entry date to be used per emp_id then the amounts included with that are ok. Something like this:

insert into tbl_emp_category
select emp_id, ordered_date, received_date, init_amt, other
from
(select emp_id,
max(case when [description] = 'ma started' then entry_date end) ordered_date,
max(case when [description] = 'ma completed' then entry_date end) received_date,
max(intitial_amount) init_amt, max(other) other
from action_results
group by emp_id)as results
group by emp_id, ordered_date, received_date, init_amt, other
order by emp_id

Tim

 

[SqlHunter]

Here the amount got is not right,The rest works fine....

Even I tried writing something like this ....still there is issues....

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