January 20, 2011 at 5:12 am
Hi there I have the following query -
SELECT PERS.[PERSON-REF]
,PERSCONT.[CONTACT-DETAILS]
,MIN(PERSCONT.[PRIORITY])AS Priority
FROM dbo.[CORE_CO-PERSON] AS PERS
INNER JOIN
dbo.[CORE_CO-PERSON-CONTACT] AS PERSCONT
ON
PERS.[PERSON-REF] = PERSCONT.[PERSON-REF]
WHEREPERSCONT.[CONTACT-TYPE] = 'TEL'
ANDPERS.[PERSON-REF] = '33252528990007'
GROUP BYPERS.[PERSON-REF], PERSCONT.[CONTACT-DETAILS]
I'm getting two results
PERSON-REF CONTACT-DETAILS Priority
---------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------
33252528990007 01634 830414 10
33252528990007 01634314676 11
(2 row(s) affected)
I only want to pull back one row in this instance as I would only like to pull back the row with the lowest Priority number. In this case 10.
How do I do this as I though the MIN in the SELECT Statement would be enough.
Thanks
January 20, 2011 at 9:30 am
You could use a sub-query that groups on Person-Ref and Priority.
SELECTPERS.[PERSON-REF]
,PERSCONT.[CONTACT-DETAILS]
,PERSCONT.[PRIORITY] AS Priority
FROMdbo.[CORE_CO-PERSON] AS PERS
INNER JOIN dbo.[CORE_CO-PERSON-CONTACT] AS PERSCONT
ON PERS.[PERSON-REF] = PERSCONT.[PERSON-REF]
INNER JOIN (SELECT[PERSON-REF]
, MIN([PRIORITY]) AS [PRIORITY]
FROMdbo.[CORE_CO-PERSON-CONTACT]
GROUP BY [PERSON-REF]) x
ON PERSCONT.[PERSON-REF] = x.[PERSON-REF]
AND PERSCONT.[PRIORITY] = x.[PRIORITY]
WHEREPERSCONT.[CONTACT-TYPE] = 'TEL'
ANDPERS.[PERSON-REF] = '33252528990007'
GROUP BY PERS.[PERSON-REF], PERSCONT.[CONTACT-DETAILS]
_____________________________________________________________________
- Nate
January 26, 2011 at 11:19 am
You could add a Top 1 to your query and order by priority. Assuming you need the details field. It's that field in your query causing you to get two records. Also...Using the Top 1 you can get rid of the group by as well as the MIN() function call.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
May 16, 2011 at 4:41 am
Good Morning,
I have another example here and again am struggling to get my head around just pulling back the minimum line that has the minimum CreatedDateTime.
ExampleSELECT UP.TableRecordUID
,UP.CreatedDate + UP.CreatedTime AS 'CreatedDateTime'
,UP.USERID
FROM DW_UPDATES_D AS UP
WHERE (UP.CreatedDate >= CONVERT(DATETIME,'2011-04-01 00:00:00', 102)
AND UP.CreatedDate < CONVERT(DATETIME, '2012-03-31 00:00:00', 102))
AND UP.FormDescription = 'Service Orders - Jobs'
AND UP.UpdatedText = 'Allocated'
ANDUP.Text = 'Status'
ANDUP.TableRecordUID = '562247'
Gives me two results -
TableRecordUID CreatedDateTime USERID
-------------- ------------------------------------------------------ -----------
562247 2011-03-30 10:20:18.000 219
562247 2011-03-30 16:09:35.000 214
(2 row(s) affected)
I'm only interested in the 562247 2011-03-30 10:20:18.000 219 line as it has an earlier datetime stamp.
How do I just get the one line result back?
As always thank you very much.
May 16, 2011 at 5:35 am
This should help:
SELECT UP.TableRecordUID
,UP.CreatedDate + UP.CreatedTime AS 'CreatedDateTime'
,UP.USERID
FROM DW_UPDATES_D AS UP
WHERE (UP.CreatedDate >= CONVERT(DATETIME,'2011-04-01 00:00:00', 102)
AND UP.CreatedDate < CONVERT(DATETIME, '2012-03-31 00:00:00', 102))
AND UP.FormDescription = 'Service Orders - Jobs'
AND UP.UpdatedText = 'Allocated'
AND UP.Text = 'Status'
AND UP.TableRecordUID = '562247'
AND
UP.CreatedDate + UP.CreatedTime = (SELECT MIN(UP.CreatedDate + UP.CreatedTime)
FROM DW_UPDATES_D
WHERE (UP.CreatedDate >= CONVERT(DATETIME,'2011-04-01 00:00:00', 102)
AND UP.CreatedDate < CONVERT(DATETIME, '2012-03-31 00:00:00', 102))
AND UP.FormDescription = 'Service Orders - Jobs'
AND UP.UpdatedText = 'Allocated'
AND UP.Text = 'Status'
AND UP.TableRecordUID = '562247')
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
May 16, 2011 at 6:25 am
Thanks for the reply -
But I get the following error -
Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.
May 16, 2011 at 6:31 am
sorry about that. i forgot to remove the UP references....
SELECT UP.TableRecordUID
,UP.CreatedDate + UP.CreatedTime AS 'CreatedDateTime'
,UP.USERID
FROM DW_UPDATES_D AS UP
WHERE (UP.CreatedDate >= CONVERT(DATETIME,'2011-04-01 00:00:00', 102)
AND UP.CreatedDate < CONVERT(DATETIME, '2012-03-31 00:00:00', 102))
AND UP.FormDescription = 'Service Orders - Jobs'
AND UP.UpdatedText = 'Allocated'
AND UP.Text = 'Status'
AND UP.TableRecordUID = '562247'
AND
UP.CreatedDate + UP.CreatedTime = (SELECT MIN(CreatedDate + CreatedTime)
FROM DW_UPDATES_D
WHERE (CreatedDate >= CONVERT(DATETIME,'2011-04-01 00:00:00', 102)
AND CreatedDate < CONVERT(DATETIME, '2012-03-31 00:00:00', 102))
AND FormDescription = 'Service Orders - Jobs'
AND UpdatedText = 'Allocated'
AND Text = 'Status'
AND TableRecordUID = '562247')
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
May 16, 2011 at 6:41 am
Thank you very much.
I've run it and the result is what I expect.
I've also studied the code and seen what you've done.
That makes sense to me now.
Many Thanks
Ryan
May 16, 2011 at 6:42 am
Your welcome! Anytime.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
May 16, 2011 at 7:30 am
Here is my finished code. I did tweak what you gave me.
SELECT dbo.DW_SERVICEORDERSJOBS_F.JobNumber AS [Soj.JobNumber]
, dbo.DW_SERVICEORDERSJOBS_F.ServicePriority AS [Soj.ServicePriority]
,CONVERT(datetime, CONVERT(varchar, dbo.DW_SERVICEORDERSJOBS_F.CreatedDate, 104), 104) AS [Soj.CreatedDate]
,DATEPART(YYYY,dbo.DW_SERVICEORDERSJOBS_F.CreatedDate) AS [Soj.CreatedYear]
,DATEPART(WK, dbo.DW_SERVICEORDERSJOBS_F.CreatedDate) AS [Soj.CreatedWeek]
,dbo.DW_SERVICEORDERS_D.ServiceOrderCategory AS [Ser.ServiceOrderCategory]
,dbo.DW_PERSONNEL_D.ShortName AS [Per.ShortName]
,dbo.DW_PERSONNEL_D.InternalDeptName AS [Per.InternalDeptName]
,dbo.DW_CUSTOMERS_D.ParentCustomerShortName AS [Cus.ParentCustomerShortName]
,dbo.DW_CUSTOMERS_D.CustomerCategory AS [Cus.CustomerCategory]
,dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderJobStatus AS [Soj.ServiceOrderJobStatus]
,CASE WHEN ActivityUID IS NULL THEN 'No Appointment' ELSE 'Scheduled Appointment' END AS [Cal.Appointment]
,dbo.DW_CUSTOMERS_D.ShortName AS [Cus.ShortName]
,DATEPART(MM, dbo.DW_SERVICEORDERSJOBS_F.CreatedDate) AS [Ser.CreatedMonth]
,dbo.DW_CUSTOMERS_D.ServiceTerritory AS [Cus.ServiceTerritory]
,USERID.FullName AS 'AllocatorFullName'
FROM dbo.DW_SERVICEORDERS_D
INNER JOIN
dbo.DW_SERVICEORDERSJOBS_F
ON
dbo.DW_SERVICEORDERS_D.ServiceOrderUID = dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderID
INNER JOIN
dbo.DW_CUSTOMERS_D
ON
dbo.DW_SERVICEORDERS_D.CustomerSiteID = dbo.DW_CUSTOMERS_D.CustomerUID
LEFT OUTER JOIN
dbo.DW_APPOINTMENTS_F
INNER JOIN
dbo.DW_ACTIVITIES_D
ON dbo.DW_APPOINTMENTS_F.ActivityID = dbo.DW_ACTIVITIES_D.ActivityUID
ON dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderJobUID = dbo.DW_ACTIVITIES_D.OwnerDocumentID
LEFT OUTER JOIN
dbo.DW_PERSONNEL_D
ON dbo.DW_SERVICEORDERSJOBS_F.AssignedToPersonnelID = dbo.DW_PERSONNEL_D.PersonnelUID
/** In Order to work out the first time an order is set to Allocated.**/
LEFT OUTER JOIN
(
SELECT UP.TableRecordUID
,UP.CreatedDate + UP.CreatedTime 'AllocatedDate'
,UP.USERID AS 'Allocator'
FROM DW_UPDATES_D AS UP
WHERE (UP.CreatedDate >= CONVERT(DATETIME,'2011-04-01 00:00:00', 102)
AND UP.CreatedDate < CONVERT(DATETIME, '2012-03-31 00:00:00', 102))
AND UP.FormDescription = 'Service Orders - Jobs'
AND UP.UpdatedText = 'Allocated'
AND UP.Text = 'Status'
AND UP.CreatedDate + UP.CreatedTime = (SELECT MIN(CreatedDate + CreatedTime)
FROM DW_UPDATES_D
WHERE (CreatedDate >= CONVERT(DATETIME,'2011-04-01 00:00:00', 102)
AND CreatedDate < CONVERT(DATETIME, '2012-03-31 00:00:00', 102))
AND FormDescription = 'Service Orders - Jobs'
AND UpdatedText = 'Allocated'
AND Text = 'Status'
AND UP.TableRecordUID = TableRecordUID)
) AS UP1
ON dbo.DW_SERVICEORDERSJOBS_F.JobNumber = UP1.TableRecordUID
LEFT OUTER JOIN
dbo.DW_USERS_D AS USERID
ONUP1.Allocator = USERID.UserUID
WHERE (dbo.DW_ACTIVITIES_D.OwnerDocumentTypeID = 20
OR dbo.DW_ACTIVITIES_D.OwnerDocumentTypeID IS NULL)
AND (dbo.DW_SERVICEORDERSJOBS_F.CreatedDate >= CONVERT(DATETIME,'2011-04-01 00:00:00', 102)
AND dbo.DW_SERVICEORDERSJOBS_F.CreatedDate < CONVERT(DATETIME, '2012-03-31 00:00:00', 102))
AND (dbo.DW_PERSONNEL_D.PersonnelType = N'Trades Staff')
GROUP BY dbo.DW_SERVICEORDERSJOBS_F.JobNumber
,dbo.DW_SERVICEORDERSJOBS_F.ServicePriority
,CONVERT(datetime, CONVERT(varchar,dbo.DW_SERVICEORDERSJOBS_F.CreatedDate, 104), 104)
,DATEPART(YYYY, dbo.DW_SERVICEORDERSJOBS_F.CreatedDate)
,DATEPART(WK, dbo.DW_SERVICEORDERSJOBS_F.CreatedDate)
,dbo.DW_SERVICEORDERS_D.ServiceOrderCategory
,dbo.DW_PERSONNEL_D.ShortName
,dbo.DW_PERSONNEL_D.InternalDeptName
,dbo.DW_CUSTOMERS_D.ParentCustomerShortName
,dbo.DW_CUSTOMERS_D.CustomerCategory
,dbo.DW_CUSTOMERS_D.ShortName
,CASE WHEN ActivityUID IS NULL THEN 'No Appointment' ELSE 'Scheduled Appointment' END
,dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderJobStatus
,DATEPART(MM, dbo.DW_SERVICEORDERSJOBS_F.CreatedDate)
,dbo.DW_CUSTOMERS_D.ServiceTerritory
,USERID.FullName
ORDER BY dbo.DW_SERVICEORDERSJOBS_F.JobNumber
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply