October 10, 2005 at 6:06 pm
hi all, i have a sql coding issue that is a bit confusing.
i'm pulling dates from one table (history) and pulling sales figures from another (tlcinterface) to present date/time for each sale. works fine, except we often "unsell" our sales, then resell them after making corrections. this causes the problem of showing multiple sales when there should only be one since the history table records each sales event, but does not record the "unsold" event (separate application handles this).
code is as follows:
SELECT
CASE GROUPING(userupdated)
WHEN 0 THEN userupdated
WHEN 1 THEN 'Team Totals'
END AS [Team Sheila],
COUNT(firstyearpremium) AS [Sales],
'$' + CONVERT(VARCHAR, CAST(SUM(firstyearpremium) AS MONEY),1) AS [Premium],
'$' + CONVERT(VARCHAR, CAST(MIN(firstyearpremium) AS MONEY),1) AS [Low],
'$' + CONVERT(VARCHAR, CAST(MAX(firstyearpremium) AS MONEY),1) AS [High],
'$' + CONVERT(VARCHAR, CAST(ROUND(SUM(firstyearpremium)/COUNT(firstyearpremium),2) AS MONEY),1) AS [Average]
FROM history
INNER JOIN tlcinterface
ON documentid = leadid
WHERE history.description = 'new status: sold'
/*
AND history.eventdatetime = (SELECT MAX(history.eventdatetime) FROM history h2 WHERE leadid = h2.documentid)
*/
GROUP BY userupdated WITH ROLLUP
ORDER BY SUM(firstyearpremium) DESC, Sales DESC, userupdated
--ISNULL(userupdated, 'zzz')
DISTINCT doesn't help because the history table times differ.
leadid/documentid (same thing) is unique and history is using an eventdatetime stamp.
commented out code would resolve issue but results in the error --> An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
any ideas?
thanks
October 11, 2005 at 1:05 am
The structure of your tables and some example data would realy help, but I'll take a shot at it without.
I think you'll find a subquery will help greatly.
SELECT ...
FROM history
INNER JOIN tlcinterface ON tlcinterface.documentid = history.leadid
INNER JOIN (SELECT MAX(history.eventdatetime) LastEvent, leadid FROM history GROUP BY leadid) LatestSale ON LatestSale.leadid = history.leadid AND LatestSale.LastEvent=history.eventdatetime
WHERE history.description = 'new status: sold'
HTH
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply