August 5, 2015 at 8:45 am
Hello,
I'm very green when it comes to SQL so forgive me if this is something that should be common knowledge.
I have a query that I'm filtering using Customer ID, CustomerID = '12345', even though I need the query to filter that data, I don't need to see that column in my results. I tried removing it from my Select Distinct group but I'm guessing it needs to be there or the filter won't work(like I said, very green). Is there something that I can add to hide this column?
Thanks in advance.
SELECT DISTINCT
RG.ResNumber,
ResWithSupp.SupplierID,
ResWithSupp.ServiceType,
RG.CustomerID,
RG.ResStatus,
RG.DepDate,
RG.BookDate,
TotalSupplierSalePrice = ResWithSupp.TotalSupplierSalePrice
FROM ResGeneral RG
INNER JOIN AccCompanies on RG.CompanyID = AccCompanies.CompanyID
LEFT JOIN
(
SELECT
RI.ResNumber, RI.SupplierID, RI.SupplierName, RI.ServiceType, RI.CreatedBy,
Qty = SUM(Qty),
TotalSupplierSalePrice = SUM(SupplierSalePrice)
FROM
(
SELECT DISTINCT RI.ResNumber, RI.SupplierID, RI.SupplierName, RI.ServiceSeq, RI.ServiceType, RI.CreatedBy,
Qty = (Qty),
SupplierSalePrice = ((QuotePrice + QuotePriceTax + QuotePriceGST) * (Qty))
FROM ResItinerary RIWITH(NOLOCK)
LEFT JOIN ResGeneral RG
ON RI.ResNumber = RG.ResNumber
WHERE
Deleted <> 'Y'
AND BookDate BETWEEN '2014-01-01' AND GETDATE()
--AND RI.SupplierID LIKE ' Parameters!SupplierID.Value '
--AND RI.ServiceID LIKE ' Parameters!ServiceID.Value '
GROUP BY
RI.ResNumber, RI.SupplierID, RI.SupplierName, RI.ServiceSeq, RI.ServiceType, RI.CreatedBy,
Qty,
/* SupplierSalePrice */ ((QuotePrice + QuotePriceTax + QuotePriceGST) * (Qty))
) AS RI
GROUP BY RI.ResNumber, RI.SupplierID, RI.SupplierName, RI.ServiceType, RI.CreatedBy
) ResWithSupp
ON RG.ResNumber = ResWithSupp.ResNumber
LEFT JOIN
(
SELECT RI.ResNumber, dbo.fn_AggConcat(RI.Description + ', ') AS PromoDescription, dbo.fn_AggConcat(RI.ServiceID + ', ') AS PromoCode
FROM ResItinerary RI WITH(NOLOCK)
WHERE RI.Deleted <> 'Y' AND RI.ServiceType = 'PRO'
GROUP BY RI.ResNumber
) PRO
ON PRO.ResNumber = RG.ResNumber
WHERE 1 =1
AND BookDate >= '2014-01-01' AND BookDate < DATEADD(d, 1, '2015-08-02')--GETDATE()
and RG.ResStatus = 'A' and RG.CustomerID = '03866796'
and TotalSupplierSalePrice < '0'
and ResWithSupp.ServiceType not in ('PMA','CAR','TRF','PRO','CNV','PKG')
and ResWithSupp.SupplierID in ('','US-NYCEGT','US-NYCEGTIN')
ORDER BY ResWithSupp.SupplierID, RG.ResNumber
August 5, 2015 at 8:52 am
The columns used in the filter don't need to be in the SELECT column list.
However, the results of a query could change if you remove a column from the column list when using DISTINCT. This won't be the case as the value should be the same for all the rows.
One additional point is that using DISTINCT might be masking problems that might cause poor performance or wrong results. Whenever you use it, be sure to be able to justify it.
August 5, 2015 at 8:58 am
Thanks,
For some reason when I initially did that I got an error, I tried it again and it seems to be working fine now, maybe I left a comma in.
August 5, 2015 at 9:15 am
You also seem not to be using this part:
LEFT JOIN
(
SELECT RI.ResNumber,
dbo.fn_AggConcat(RI.Description + ', ') AS PromoDescription,
dbo.fn_AggConcat(RI.ServiceID + ', ') AS PromoCode
FROM ResItinerary RI WITH(NOLOCK)
WHERE RI.Deleted <> 'Y' AND RI.ServiceType = 'PRO'
GROUP BY RI.ResNumber
) PRO ON PRO.ResNumber = RG.ResNumber
It's only adding work.
Your LEFT JOINs are being ultimately converted to INNER JOINs and some conditions can be moved to the subqueries to filter as soon as possible.
SELECT DISTINCT
RG.ResNumber,
ResWithSupp.SupplierID,
ResWithSupp.ServiceType,
RG.CustomerID,
RG.ResStatus,
RG.DepDate,
RG.BookDate,
TotalSupplierSalePrice = ResWithSupp.TotalSupplierSalePrice
FROM ResGeneral RG
INNER JOIN AccCompanies on RG.CompanyID = AccCompanies.CompanyID
INNER JOIN
(
SELECT
RI.ResNumber,
RI.SupplierID,
RI.SupplierName,
RI.ServiceType,
RI.CreatedBy,
Qty = SUM(Qty),
TotalSupplierSalePrice = SUM(SupplierSalePrice)
FROM
(
SELECT DISTINCT --Is distinct really needed?
RI.ResNumber,
RI.SupplierID,
RI.SupplierName,
RI.ServiceSeq,
RI.ServiceType,
RI.CreatedBy,
Qty = (Qty),
SupplierSalePrice = ((QuotePrice + QuotePriceTax + QuotePriceGST) * (Qty))
FROM ResItinerary RI
JOIN ResGeneral RG ON RI.ResNumber = RG.ResNumber
WHERE Deleted <> 'Y'
AND BookDate BETWEEN '2014-01-01' AND GETDATE() -- This condition is different from the one below
--AND RI.SupplierID LIKE ' Parameters!SupplierID.Value '
--AND RI.ServiceID LIKE ' Parameters!ServiceID.Value '
AND RI.ServiceType not in ('PMA','CAR','TRF','PRO','CNV','PKG')
AND RI.SupplierID in ('','US-NYCEGT','US-NYCEGTIN')
) AS RI
GROUP BY RI.ResNumber, RI.SupplierID, RI.SupplierName, RI.ServiceType, RI.CreatedBy
HAVING SUM(SupplierSalePrice) < 0
) ResWithSupp ON RG.ResNumber = ResWithSupp.ResNumber
WHERE 1 =1
AND RG.BookDate >= '2014-01-01' AND RG.BookDate < DATEADD(d, 1, '2015-08-02')
and RG.ResStatus = 'A'
and RG.CustomerID = '03866796'
ORDER BY ResWithSupp.SupplierID, RG.ResNumber
The 2 subqueries might be combined as one if you don't have any duplicate rows that need to be eliminated by the distinct (you shouldn't have with a correct design).
SELECT DISTINCT
RG.ResNumber,
ResWithSupp.SupplierID,
ResWithSupp.ServiceType,
RG.CustomerID,
RG.ResStatus,
RG.DepDate,
RG.BookDate,
TotalSupplierSalePrice = ResWithSupp.TotalSupplierSalePrice
FROM ResGeneral RG
INNER JOIN AccCompanies on RG.CompanyID = AccCompanies.CompanyID
INNER JOIN
(
SELECT
RI.ResNumber,
RI.SupplierID,
RI.ServiceType,
TotalSupplierSalePrice = SUM((QuotePrice + QuotePriceTax + QuotePriceGST) * (Qty))
FROM ResItinerary RI
JOIN ResGeneral RG ON RI.ResNumber = RG.ResNumber
WHERE Deleted <> 'Y'
AND BookDate BETWEEN '2014-01-01' AND GETDATE() -- This condition is different from the one below
--AND RI.SupplierID LIKE ' Parameters!SupplierID.Value '
--AND RI.ServiceID LIKE ' Parameters!ServiceID.Value '
AND RI.ServiceType not in ('PMA','CAR','TRF','PRO','CNV','PKG')
AND RI.SupplierID in ('','US-NYCEGT','US-NYCEGTIN')
GROUP BY RI.ResNumber, RI.SupplierID, RI.SupplierName, RI.ServiceType, RI.CreatedBy
HAVING SUM((QuotePrice + QuotePriceTax + QuotePriceGST) * (Qty)) < 0
) ResWithSupp ON RG.ResNumber = ResWithSupp.ResNumber
WHERE 1 =1
AND RG.BookDate >= '2014-01-01' AND RG.BookDate < DATEADD(d, 1, '2015-08-02')
and RG.ResStatus = 'A'
and RG.CustomerID = '03866796'
ORDER BY ResWithSupp.SupplierID, RG.ResNumber
This is completely untested, but it might give you an idea of all the additional work that you're doing.
August 5, 2015 at 10:27 am
Thanks,
I had tried that as well and again got an error but I'll give it another look.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply