Easier way to do this in SQL - 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)

Easier way to do this in SQL

Author

Topic

guyaton
Starting Member

3 Posts

Posted - 2007-07-16 : 12:19:15

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 - 2007-07-16 : 12:50:11

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

guyaton
Starting Member

3 Posts

Posted - 2007-07-16 : 13:28:16

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!

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-16 : 13:37:54

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

guyaton
Starting Member

3 Posts

Posted - 2007-07-16 : 13:49:39

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.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-07-16 : 20:23:37

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).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources