May 7, 2008 at 3:41 pm
I am trying to get the max effective date from the following query but the way I am doing I need to add a GROUP BY in the WHERE Clause but I keep getting a syntax error:
SELECTDISTINCT Listing.[ConsultantID]
,Listing.ConsultantName
,Listing.[ListType] AS OrderType
,Ord.ListType As ReportListType
,Ord.[TSUserType]
,Ord.[InternalUserName]
--,Ord.[ListTypeDesc]
,Ord.OrderTypeXID
,Ord.OrderStatusXID
,CONVERT(VARCHAR(10),Ord.ORderCreateDate,101) AS OrderCreateDate
,'G'+ CONVERT(VARCHAR(20),Ord.ORderGroupNumber) AS OrderGroupNumber
,Ord.OrderNumber
,ISNULL(Ord.PartOneTotal,0) As PartOneTotal
,ISNULL(Ord.PartTwoTotal,0) AS PartTwoTotal
,ISNULL(Ord.PartThreeTotal,0) as PartThreeTotal
,ISNULL(Ord.Hostcredit,0) AS HostCredit
,ISNULL(Ord.SampleAllowanceAmount,0) AS SampleAllowanceAmount
,ISNULL(Case
WHEN Listing.[ListType] IN ('Client')THEN ord.TSODiscountAmount
END,0)AS 'TSODiscounts'
,ISNULL(Case
WHEN Listing.[ListType] NOT IN ('Client')THEN ord.ConsultantDiscountAmount
END,0)AS 'NONTSODiscounts'
,ISNULL(Ord.ConsultantDiscountAmount,0) AS ConsultantDiscountAmount
,Ord.ShippedDate
,'ShipDate' =
CASE ord.OrderStatusXID
WHEN 32 THEN Convert(nvarchar(10),Ord.ShippedDate,101)
WHEN 16 THEN 'Partial'
WHEN 8 THEN 'Unshipped'
WHEN 128 THEN 'Unshipped'
END
,Ord.ShipToFirstName + ' ' +Ord.ShipToLastName AS ShipToName
,ISNULL(Ord.ShippingTotal,0) AS ShippingTotal
,ISNULL(Ord.OrderTotal,0) AS OrderTotal
,ISNULL(Ord.TaxTotal,0) AS TaxTotal
,Ord.SortOrder
FROM(SELECTDISTINCT con.[ConsultantID]
,Con.FirstName + ' ' + Con.LastName AS ConsultantName
,List.ListType
,MAX(con.EffectiveDate) AS EffectiveDate
FROM[Consultant]
GROUP BY Con.ConsultantID AS Con With (NoLock)
,(SELECT DISTINCT ListType FROM uvw_ListingType With (NoLock) WHERE (ListType = @OrderTypeFilter OR @OrderTypeFilter IS NULL AND ListType <> 'UFO')) AS List
WHERECon.ConsultantID = @ConsultantID) AS Listing
LEFT OUTER JOIN [uvw_OrderListingSummary] AS Ord ON
Listing.[ConsultantID] = Ord.[ConsultantID]
AND listing.listtype = ord.listtype
ANDOrd.[OrderCreateDate] BETWEEN @StartDate AND @EndDate
AND Ord.ListType IS NOT NULL
Order BY ord.SortOrder Asc
or maybe I am going about in the wrong way.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
May 7, 2008 at 9:40 pm
I have not tested whether the query will work but shouldn't the locking hint be moved to the FROM statement rather than the GROUP BY statement.
FROM WITH (NOLOCK)
GROUP BY
Also, there appears to be a missing close parenthesis after the GROUP BY statement. (I did not match all of the parens; just a quick glance.)
Sorry if this is not helpful.
May 8, 2008 at 6:44 am
Ah, I see where the problem might be:
(SELECT DISTINCT con.[ConsultantID]
,Con.FirstName + ' ' + Con.LastName AS ConsultantName
,List.ListType
,MAX(con.EffectiveDate) AS EffectiveDate
,(SELECT DISTINCT ListType FROM uvw_ListingType With (NoLock) WHERE (ListType = @OrderTypeFilter OR @OrderTypeFilter IS NULL AND ListType <> 'UFO')) AS List
FROM [Consultant] AS Con With (NoLock)
WHERE Con.ConsultantID = @ConsultantID
GROUP BY Con.ConsultantID ) AS Listing
This query won't run fast, but it should compile.
Correlated sub-queries will kill your performance. Also, depending on the clustered indexes, statistics, etc., instead of MAX with a GROUP BY, you might try getting TOP(1) with an ORDER BY. In some situations that can be faster. DISTINCT is a crutch that will destroy performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 8, 2008 at 10:32 am
I ended up doing a order by and reversing the direction.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply