| Author |
 Topic  |
|
|
guyaton
Starting Member
3 Posts |
 Posted - 07/16/2007 : 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 - 07/16/2007 : 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
|
Edited by - X002548 on 07/16/2007 12:51:09 |
 |
|
|
guyaton
Starting Member
3 Posts |
Posted - 07/16/2007 : 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 |
|
|
guyaton
Starting Member
3 Posts |
Posted - 07/16/2007 : 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
934 Posts |
Posted - 07/16/2007 : 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).
|
|
|
| |
Topic |
|
|
|
Easier way to do this in SQL
