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.
| Author |
Topic |
|
udaymahajan
Starting Member
17 Posts |
 Posted - 2008-03-27 : 08:14:45
|
| My tables and data as followsAB_Corporate_ProjectFields are : ab_crp_id , cust_name , owner_rep_idData in table AB_Corporate_Project is as followsab_crp_id cust_name owner_rep_id1 harry 32 msas 2AB_Plant_ProjectFields are : ab_plant_id , ab_name , owner_rep_idData in Table AB_Plant_Projectab_plant_id ab_name owner_rep_id1 abc 12 def 2Other_ProjectFields are : other_proj_id,ot_name, owner_rep_idData in table Other_Projectother_proj_id ot_name owner_rep_id1 xyz 22 cdf 3Owner_RepFields are : owner_rep_id,owner_nameData in Table Owner_Repowner_rep_id owner_name1 henry2 hologa3 tmwDaily_Time_EntryFields are: dl_id,dt_id,project_type,project_id,time_stData in table Daily_Time_Entrydl_id dt_id project_type project_id time_st1 03/23/08 AB Corporate 1 1.202 03/23/08 AB Corporate 2 3.253 03/21/08 AB Corporate 1 2.254 03/23/08 AB Plant 1 4.355 03/23/08 AB Plant 2 4.506 03/23/08 Other Project 1 3.247 03/23/08 Other Project 2 4.35I want to show records as per date from Daily_Time_Entry table and detail data to be displayed as followsdl_id dt_id project_type project_id time_st exp1 exp2 exp3 owner_name1 03/23/08 AB Corporate 1 1.20 3 -- -- tmw 2 03/23/08 AB Corporate 2 3.25 2 -- -- hologa3 03/21/08 AB Corporate 1 2.25 3 -- -- tmw4 03/23/08 AB Plant 1 4.35 -- 1 -- henry5 03/23/08 AB Plant 2 4.50 -- 2 -- hologa6 03/23/08 Other Project 1 3.24 -- -- 2 hologa7 03/23/08 Other Project 2 4.35 -- -- 3 tmwAlso in project_type AB Corporate for AB_Corporate_Project, AB Plant for AB_Plant_Project , Other Project for Other_Project I write query for this is as followsSelect dl. dl_id,dl.dt_id,dl.project_type,dl. project_id,dl.time_st,ac.owner_rep_id as exp1,ab. owner_rep_id as exp2,op. owner_rep_id as exp3,ow. owner_nameFrom Daily_Time_Entry dl left outer joinAB_Corporate_Project ac on dl. project_id = ac. ab_crp_id and dl. project_type=’ AB Corporate’ left outer join AB_Plant_Project ab on dl. project_id =ab. ab_plant_id and dl. project_type=’ AB Plant’ left outer join Other_Project op on dl. project_id = op. other_proj_id and dl. project_type=’ Other Project’ inner join Owner_Rep ow on(ow. owner_rep_id = ac. owner_rep_id) or(ow. owner_rep_id = ab. owner_rep_id)or(ow. owner_rep_id = op.owner_rep_id)So how can I write query to show output as follows or any other way or how to create Sql cursors for to show outputPlz help to solve this query to show output as specified. uday |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
|
|
|
|
|
|
|
how to use joins to solve this sql query