SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Easier way to do this in SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

guyaton
Starting Member

3 Posts

Posted - 07/16/2007 :  12:19:15  Show Profile  Reply with Quote
I have this really nasty SQL statement. I have this table that Contains a unique ID to one of 4 tables. There is another column that tells which table to pull the information from.

for example

| SaleType | SaleString |
| 1 | 34 |
| 3 | 98 |

etc and based on the number in "SaleType" column is which table I need to use to get the proper "SaleString" right now I am doing 4 Select Statements and unions to get this done (Code posted below). I am sure this is very inefficient and would like a better way do to it if possible.


SELECT 
e.ClientName as [Organization], e.ClientDept as [Department], 
e.State, a.Rating, f.PartnerName as [Partner], d.ServiceName as [Item], 
c.AnticipatedValue as [Cost], c.ImplementationCost as [Imp], a.FollowupDate as [Followup], 
a.AnticipatedCloseDate as [Closing], b.StaffName as [SecondaryStaff]

FROM ClientPotentialSales as a 
LEFT JOIN GTG_Staff as b on a.SalesPerson2=b.StaffID 
LEFT OUTER JOIN ClientPotentialSaleLineItems as c on a.RecordID=c.RecordID 
LEFT OUTER JOIN Services as d on c.SaleItem=d.ServiceID AND c.SaleType=1
LEFT OUTER JOIN Clients as e on a.ClientID=e.ClientID 
LEFT OUTER JOIN ClientPotentialSalePartner as f on a.PartnerID=f.PartnerID 

WHERE a.SalesPerson=(
SELECT StaffID FROM GTG_Staff WHERE StaffUserName='bcraft') And 
(a.Rating>0 AND a.Rating<6) AND c.SaleType=1

UNION 

SELECT 
e.ClientName as [Organization], e.ClientDept as [Department], 
e.State, a.Rating, f.PartnerName as [Partner], d.SoftwareName as [Item], 
c.AnticipatedValue as [Cost], c.ImplementationCost as [Imp], a.FollowupDate as [Followup], 
a.AnticipatedCloseDate as [Closing], b.StaffName as [SecondaryStaff]

FROM ClientPotentialSales as a 
LEFT JOIN GTG_Staff as b on a.SalesPerson2=b.StaffID 
LEFT OUTER JOIN ClientPotentialSaleLineItems as c on a.RecordID=c.RecordID 
LEFT OUTER JOIN Software as d on c.SaleItem=d.SoftwareID AND c.SaleType=2
LEFT OUTER JOIN Clients as e on a.ClientID=e.ClientID 
LEFT OUTER JOIN ClientPotentialSalePartner as f on a.PartnerID=f.PartnerID 

WHERE a.SalesPerson=(
SELECT StaffID FROM GTG_Staff WHERE StaffUserName='bcraft') And 
(a.Rating>0 AND a.Rating<6) AND c.SaleType=2

UNION

SELECT 
e.ClientName as [Organization], e.ClientDept as [Department], 
e.State, a.Rating, f.PartnerName as [Partner], d.LeadServiceName as [Item], 
c.AnticipatedValue as [Cost], c.ImplementationCost as [Imp], a.FollowupDate as [Followup], 
a.AnticipatedCloseDate as [Closing], b.StaffName as [SecondaryStaff]

FROM ClientPotentialSales as a 
LEFT JOIN GTG_Staff as b on a.SalesPerson2=b.StaffID 
LEFT OUTER JOIN ClientPotentialSaleLineItems as c on a.RecordID=c.RecordID 
LEFT OUTER JOIN ClientPotentialSaleService as d on c.SaleItem=d.LeadServiceID AND c.SaleType=3
LEFT OUTER JOIN Clients as e on a.ClientID=e.ClientID 
LEFT OUTER JOIN ClientPotentialSalePartner as f on a.PartnerID=f.PartnerID 

WHERE a.SalesPerson=(
SELECT StaffID FROM GTG_Staff WHERE StaffUserName='bcraft') And 
(a.Rating>0 AND a.Rating<6) AND c.SaleType=3

UNION

SELECT 
e.ClientName as [Organization], e.ClientDept as [Department], 
e.State, a.Rating, f.PartnerName as [Partner], d.LeadSoftwareName as [Item], 
c.AnticipatedValue as [Cost], c.ImplementationCost as [Imp], a.FollowupDate as [Followup], 
a.AnticipatedCloseDate as [Closing], b.StaffName as [SecondaryStaff]

FROM ClientPotentialSales as a 
LEFT JOIN GTG_Staff as b on a.SalesPerson2=b.StaffID 
LEFT OUTER JOIN ClientPotentialSaleLineItems as c on a.RecordID=c.RecordID 
LEFT OUTER JOIN ClientPotentialSaleSoftware as d on c.SaleItem=d.LeadSoftwareID AND c.SaleType=4
LEFT OUTER JOIN Clients as e on a.ClientID=e.ClientID 
LEFT OUTER JOIN ClientPotentialSalePartner as f on a.PartnerID=f.PartnerID 

WHERE a.SalesPerson=(
SELECT StaffID FROM GTG_Staff WHERE StaffUserName='bcraft') And 
(a.Rating>0 AND a.Rating<6) AND c.SaleType=4

ORDER BY a.Rating DESC, e.ClientName ASC, c.AnticipatedValue DESC


If you notice, that's really bad. My SQL is still a work in progress and if you have any better options, I am all ears!

Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 07/16/2007 :  12:50:11  Show Profile  Reply with Quote
We might be abl to help better if you post sample data, DDL, and expected results based on the sample data. Read the hint link in my sig, but I don't know why this wouldn't work


   SELECT  e.ClientName					        AS [OrganizatiON]
		 , e.ClientDept					AS [Department]
		 , e.State
		 , a.Rating
		 , f.PartnerName				AS [Partner]
		 , d.ServiceName				AS [Item]
		 , c.AnticipatedValue			        AS [Cost]
		 , c.ImplementatiONCost			        AS [Imp]
		 , a.FollowupDate				AS [Followup]
		 , a.AnticipatedCloseDate		        AS [Closing]
		 , b.StaffName					AS [SecONdaryStaff]
     FROM  ClientPotentialSales			                AS a 
LEFT JOIN  GTG_Staff					        AS b 
	   ON  a.SalesPersON2=b.StaffID 
LEFT JOIN  ClientPotentialSaleLineItems                         AS c 
	   ON  a.RecordID=c.RecordID 
LEFT JOIN  Services						AS d 
	   ON  c.SaleItem=d.ServiceID AND c.SaleType=1
LEFT JOIN  Clients						AS e 
	   ON  a.ClientID=e.ClientID 
LEFT JOIN  ClientPotentialSalePartner	AS f 
	   ON  a.PartnerID=f.PartnerID 
    WHERE  EXISTS (SELECT * FROM GTG_Staff g 
				    WHERE StaffUserName='bcraft'
					  AND a.SalesPerson = g.StaffID) 
      AND  a.Rating BETWEEN 0 AND 6 
	  AND  c.SaleType IN (1,2,3,4,)




Brett

8-)

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

Add yourself!
http://www.frappr.com/sqlteam




Edited by - X002548 on 07/16/2007 12:51:09
Go to Top of Page

guyaton
Starting Member

3 Posts

Posted - 07/16/2007 :  13:28:16  Show Profile  Reply with Quote
I ran the query, but it only got the [Item] for the First Data Table (Services), the rest were NULL. Here is more information for you as requested.

I would post the DDL of my table...if I knew how to get it. As I posted earlier, I am fairly new to SQL (only 1 month on the job) and so I'm not too sure about much of it. Unfortunately my SQL all self learned (with a few bad habits I'm sure) because there's no one here to teach it to me so I apologize for sounding like a total n00b, but I practically am :)


The main table that I am pulling this from looks like this:

ClientPotentialSaleLineItems
RecordID, int and primary key
SaleType, int <--- This which database we want to go to (1 = Services, 2 = Software, 3 = ClientPotentialSaleService, 4 = ClientPotentialSaleSoftware )
SaleItem, int <--- This is the unique key for that particular table (yes there are duplicates since all tables start at 1).
a bunch of others we don't need

(ex)
| 1 | 2 | 21 |
| 2 | 1 | 1 |
etc


Services
ServiceID int and primary key
ServiceName nvarchar(75)

(ex)
| 1 | MrSID Convertion |
| 2 | GPS Training |


Software
SoftwareID int and primary key
SoftwareName[i] nvarchar(75)
[i]a bunch of others we don't need


(ex)
| 1 | MapTouch |
| 2 | LivingDoc |


ClientPotentialSaleService
[i]LeadServiceID
int and primary key
[i]LeadServiceName[i] nvarchar(50)

(ex)
| 1 | Additional Software Training: On-Site|
| 2 | Additional Software Training: Remote|


ClientPotentialSaleSoftware
[i]LeadSoftwareID
int and primary key
[i]LeadSoftwareName[i] nvarchar(50)

(ex)
| 1 | ArcGIS |
| 2 | GIStrac |


Thanks again!
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/16/2007 :  13:37:54  Show Profile  Reply with Quote
Let me ask...aren't all the queries exactly the same except for the SalesType predicate?


Am I missing something else?



Brett

8-)

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

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

guyaton
Starting Member

3 Posts

Posted - 07/16/2007 :  13:49:39  Show Profile  Reply with Quote
No, if you look at the alias for "d", that changes in each one.
For example, in the first one:

d.ServiceName as [Item],
LEFT OUTER JOIN Services as d on c.SaleItem=d.ServiceID AND c.SaleType=1

versus the second one:
d.SoftwareName as [Item],
LEFT OUTER JOIN Software as d on c.SaleItem=d.SoftwareID AND c.SaleType=2

and the 3rd and 4th ones are slightly different too.


Thanks again.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

939 Posts

Posted - 07/16/2007 :  20:23:37  Show Profile  Reply with Quote
There are ways of rewriting it but you'd have to test them for performance & equality (pretty easy).
If it were me I would have an view/inline table of all the 'D' stuff and do your join to that, sort of like Brett suggests.

select blah
from
ClientPotentialSales as a
LEFT JOIN GTG_Staff as b on a.SalesPerson2=b.StaffID
LEFT OUTER JOIN ClientPotentialSaleLineItems as c on a.RecordID=c.RecordID
inner JOIN
(
select ServiceID, ServiceName, 1 as SaleType from Services union all
select SoftwareID, SoftwareName, 2 as SaleType from Software union all
select LeadServiceID, LeadServiceName, 3 as SaleType from ClientPotentialSaleService union all
select LeadSoftwareID, LeadSoftwareName, 4 as SaleType from ClientPotentialSaleSoftware
) d
on c.SaleItem=d.SoftwareID AND c.SaleType=d.saleType
LEFT OUTER JOIN Clients as e on a.ClientID=e.ClientID
LEFT OUTER JOIN ClientPotentialSalePartner as f on a.PartnerID=f.PartnerID

WHERE a.SalesPerson=(
SELECT StaffID FROM GTG_Staff WHERE StaffUserName='bcraft') And
(a.Rating>0 AND a.Rating<6)
ORDER BY a.Rating DESC, e.ClientName ASC, c.AnticipatedValue DESC


(I have not tested this of course but you get the idea).
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000