how to solve this sql 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)

how to solve this sql query

Author

Topic

udaymahajan
Starting Member

17 Posts

Posted - 2008-03-26 : 15:05:43

AB_Corporate_Project
Fields are : ab_crp_id , cust_name , owner_rep_id

Data in table AB_Corporate_Project is as follows

ab_crp_id cust_name owner_rep_id
1 harry 3
2 msas 2

AB_Plant_Project
Fields are : ab_plant_id , ab_name , owner_rep_id

Data in Table AB_Plant_Project

ab_plant_id ab_name owner_rep_id
1 abc 1
2 def 2

Other_Project
Fields are : other_proj_id,ot_name, owner_rep_id

Data in table Other_Project

other_proj_id ot_name owner_rep_id
1 xyz 2
2 cdf 3

Owner_Rep

Fields are : owner_rep_id,owner_name

Data in Table Owner_Rep

owner_rep_id owner_name
1 henry
2 hologa
3 tmw

Daily_Time_Entry
Fields are: dl_id,dt_id,project_type,project_id,time_st
Data in table Daily_Time_Entry

dl_id dt_id project_type project_id time_st
1 03/23/08 AB Corporate 1 1.20
2 03/23/08 AB Corporate 2 3.25
3 03/21/08 AB Corporate 1 2.25
4 03/23/08 AB Plant 1 4.35
5 03/23/08 AB Plant 2 4.50
6 03/23/08 Other Project 1 3.24
7 03/23/08 Other Project 2 4.35

I want to show records as per date from Daily_Time_Entry table and detail data to be displayed as follows

dl_id dt_id project_type project_id time_st owner_rep_id owner_name
1 03/23/08 AB Corporate 1 1.20 3 tmw
2 03/23/08 AB Corporate 2 3.25 2 hologa
3 03/21/08 AB Corporate 1 2.25 3 tmw
4 03/23/08 AB Plant 1 4.35 1 henry
5 03/23/08 AB Plant 2 4.50 2 hologa
6 03/23/08 Other Project 1 3.24 2 hologa
7 03/23/08 Other Project 2 4.35 3 tmw

Also 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 follows

Select dl. dl_id,dl.dt_id,dl.project_type,dl. project_id,dl.time_st,ac. owner_rep_id,ab. owner_rep_id,ow. owner_rep_id,ow. owner_name
From Daily_Time_Entry dl left outer join
AB_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 output
Plz help to solve this query to show output as specified.

uday

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-03-26 : 16:00:52

Wow a simple question explained as confusingly as possible... :)

Let me know if this is what you want


Select a.dl_id,a.dt_id,a.project_type,a.project_id,a.time_st,c.owner_rep_id,c.owner_name
From
	Daily_Time a
Inner Join
	(
		Select ab_plant_id as ID, ab_name as [name], owner_rep_id as RepID
		from
			AB_Plant_Project
		union 
		Select ab_crp_id, cust_name, owner_rep_id
		From
			AB_Corporate_Project
		Union
		Select other_proj_id, ot_name, owner_rep_id
		from
			Other_Project
	) b
on              a.Project_ID = b.ID
Inner Join
	Owner_Rep c
on		b.repId = c.owner_rep_id

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-03-27 : 10:14:34

uday,

I see you've re-posted this question on another thread. If this solution didn't work for you please explain what specific problem you are having.

If you want us to be able to test your query and see the output, please re-post your table definitions and data in the form of DDL and DML so we can run your code without having to hand-type everything.

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-27 : 15:05:36

[code]SELECT dte.dl_id,dte.dt_id,dte.project_type,dte.project_id dte.time_st,o.owner_rep_id,o.owner_name
FROM Daily_Time_Entry dte
INNER JOIN (SELECT ab_crp_id AS ProjID,
cust_name AS CustName,
owner_rep_id AS OwnerID,
'Corporate' AS Type
FROM AB_Corporate_Project
UNION ALL
SELECT ab_plant_id AS ProjID,
ab_name AS CustName,
owner_rep_id AS OwnerID,
'Plant' AS Type
FROM AB_Plant_Project
UNION ALL
SELECT other_proj_id AS ProjID,
ot_name AS CustName,
owner_rep_id AS OwnerID,
'Other Project' AS Type
FROM Other_Project)t
ON t.ProjID=dte.project_id
AND t.Type=dte.project_type
INNER JOIN Owner_Rep o
ON o.owner_rep_id=t.OwnerID[/code]

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-03-27 : 16:02:09

Visaka, there was a syntax error in my original post but it has been corrected (I left off a join command on accident). Your query however will list duplicate postings due to your Union All syntax vs's Union, which I do not believe is wanted in this scenerio assuming that the query is supposed to return all Reps associated with any type of project as illustrated by the original post.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-27 : 16:06:11

I'm also taking type field inside derived table and joining onto that. So i think it will return only a single record for each project based on its type.

sayed
Starting Member

4 Posts

Posted - 2008-04-15 : 03:55:35

A VIEW HAS BEEN DEFINED AS FOLLOW

CREATRE VIEW vwSales OrderDetail
AS
SELECT oh.SalesOrderID,TerritoryId,TotalDue,orderId,productId
FROM Sales.SalesOrderHeader oh JOIN Sales.SalesOrderDetail od
ON oh.SalesOrderID od.SalesOrderID

THE FOLLOWING UPDATE COMMAND GIVES AN ERROR WHEN YOU UPDATE THE ORDERQTY AND TERRITORYID ATTRIBUTE

UPDATE vwSalesOrderDetail
SET OrderQty = 2, TerritoryID = 4
FROM vwSalesOrderDetail
WHERE SalesOrderID = 43659

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 03:58:51

Sayed, don't hijack other topics.

You already have posted your question here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100975

E 12°55'05.25"
N 56°04'39.16"

sayed
Starting Member

4 Posts

Posted - 2008-04-15 : 04:03:31

DISPLAY THE DETAILS OF ALL THE CURRENCIES THAT CONTAIN THE WORD 'NEW' AND 'DOLLAR'IN THEIR NAME.THESE WORD CAN BE INCLUDED IN ANY ORDER .IN ADDITION YOU NEED TO MAKE SURE THAT THE QUERY DOES NOT TAKE TIME TO EXECUTE
PLEASE GIVE QUEARY SOLUTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 04:13:55

Sayed, don't hijack other topics.
If you have a question of your own, please post a new topic.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-15 : 04:22:14

quote:
Originally posted by sayed

DISPLAY THE DETAILS OF ALL THE CURRENCIES THAT CONTAIN THE WORD 'NEW' AND 'DOLLAR'IN THEIR NAME.THESE WORD CAN BE INCLUDED IN ANY ORDER .IN ADDITION YOU NEED TO MAKE SURE THAT THE QUERY DOES NOT TAKE TIME TO EXECUTE
PLEASE GIVE QUEARY SOLUTION

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources