Databases
StackOverflow2013
Postsdbo
POUsing DISTINCT on a single column of a multi-column select gets correct results but can get it to order by createon date?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing DISTINCT on a single column of a multi-column select gets correct results but can get it to order by createon date?
    text
    copied!<p>Please review the following query and see if you can help me out.</p> <pre><code>DECLARE @Contacts TABLE ( workgroupid UNIQUEIDENTIFIER, contactid UNIQUEIDENTIFIER, leadname NVARCHAR(130), createdon DATETIME, Marketsource NVARCHAR(150)) INSERT INTO @Contacts (workgroupid, contactid, leadname, createdon, Marketsource) SELECT workgroupid, contactid, Coalesce(FirstName + ' ', ' ') + Coalesce(LastName, ' '), createdon, Marketsource FROM Contacts WHERE iLeadPartnerID IS NOT NULL AND ( CreatedOn BETWEEN @startDate AND @endDate ) AND MarketSource IS NOT NULL AND WorkgroupId = @workgroupID AND Len(MarketSource) &gt; 0 DECLARE @tasks TABLE ( targetid UNIQUEIDENTIFIER, workflow NVARCHAR(100), result NVARCHAR(50), tasktype NVARCHAR(30), completedon DATETIME, userid UNIQUEIDENTIFIER, producer NVARCHAR(50)) INSERT INTO @tasks (targetid, workflow, result, tasktype, completedon, userid, producer) SELECT t.TargetID, t.Workflow_ResultChosen_Label, t.Result, t.TaskType, t.Completed, t.UserID, Coalesce(u.FirstName + ' ', 'Not') + Coalesce(u.LastName, ' Assigned') FROM Tasks t LEFT JOIN Users u ON u.UserID = t.UserID WHERE t.CreatedOn BETWEEN @startDate AND @endDate AND t.TargetID IN (SELECT contactid FROM @Contacts) AND t.TaskType &lt;&gt; '[=] Auto Email' GROUP BY t.TargetID, t.UserID, t.Workflow_ResultChosen_Label, t.Result, t.TaskType, t.Completed, u.FirstName, u.LastName SELECT DISTINCT ( c.Marketsource ) AS [Market Source], t.producer AS [Producer], C.leadname AS [Lead Name], t.TaskType AS [Task], Coalesce (t.workflow, t.result, ' ') AS [Task Result], Dateadd(HOUR, @utcoffset, c.createdon) AS [Received On], Dateadd(HOUR, @utcoffset, ( MIN (t.completedon) )) AS [Completed], (SELECT COUNT(*) FROM Quotes q WITH (NOLOCK) WHERE SaleID IS NULL AND q.ContactID = c.contactid) AS [Pending Quotes], (SELECT COUNT(*) FROM Sales s WITH (NOLOCK) WHERE s.Client_ID = c.contactid) AS [Total Sales] FROM @Contacts c INNER JOIN @tasks t ON t.targetid = c.contactid GROUP BY c.Marketsource, c.createdon, c.contactid, c.leadname, t.producer, t.tasktype, t.workflow, t.result, t.completedon </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload