MS SQL - Subquery returned more than 1 value

I have a query that has a sub-query in the where clause;

SELECT     TOP (100) PERCENT dbo.Users.ID AS UserId,  
          (SELECT     MAX(Date_Purchased)
              FROM  (SELECT date_purchased
                       FROM dbo.QEliteContacts
                       WHERE contact_id = dbo.Users.ID) AS Date_Purchased_EJA),
					   dbo.Client_Services_Contact_Types.Contact_Type_Acronym
			FROM      dbo.Users INNER JOIN
                      dbo.Users_Info ON dbo.Users.ID = dbo.Users_Info.ID  LEFT OUTER JOIN
                      dbo.Client_Services_Contacts ON dbo.Client_Services_Contacts.contact_id = dbo.Users_Info.ID LEFT OUTER JOIN
                      dbo.Client_Services_Contact_Types ON dbo.Client_Services_Contact_Types.id = dbo.Client_Services_Contacts.ContactTypeID
WHERE     (dbo.Users_Info.Status_Flag = 'A') AND (1 = 1) AND (dbo.Client_Services_Contact_Types.Contact_Type_Acronym = 'hr') AND (	SELECT EJA_Date_Purchased
        FROM  (	SELECT date_purchased AS EJA_Date_Purchased
                FROM dbo.QEliteContacts
                WHERE (contact_id = dbo.Users.ID)) AS t) BETWEEN '2013/01/01' AND '2013/10/09'
ORDER BY UserID

Open in new window


Originally it was;

AND (	SELECT MAX(EJA_Date_Purchased) AS EJA_Date_Purchased

Open in new window


but it returned bad data.  The data it should return is;

date_purchased   ID        User ID  Acronym
2012/11/01        197369      345221       bill

With the Max() it returned;

2013/01/18        197366  231151     hr
2013/05/01        197369  345221     hr

Without the max() I get;

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


I'm trying to return a record that has a date_purchased withing a range and the contact_type_acronym = 'hr'

The subquery/view is;

SELECT     bd.date_purchased, bd.ID, csc.contact_id, dbo.Client_Services_Contact_Types.Contact_Type_Acronym
FROM         dbo.Board_Details AS bd LEFT OUTER JOIN
                      dbo.Client_Services_Contacts AS csc ON csc.service_id = bd.ID LEFT OUTER JOIN
                      dbo.Client_Services_Contact_Types ON dbo.Client_Services_Contact_Types.id = csc.ContactTypeID
WHERE     (bd.ad_type LIKE '%Elite%'

Open in new window



I can see where the max() will cause incorrect data to be returned, but I need to get any record withing the range.
lantervAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
""
Without the max() I get;

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
""

Of course because MAX will generate only one row in that subquery and you must find the appropriate expression or function to replace that MAX so you have only 1row returned by the query. So in other words you need a TOP 1, or a SUM, or AVG, or...whatever fits your business logic.
0
Daniel WilsonCommented:
Please try this.  I've moved a subselect from the WHERE clause to an INNER JOIN, making the multiple records returned by the subselect syntactically correct.

SELECT     TOP (100) PERCENT dbo.Users.ID AS UserId,  
          (SELECT     MAX(Date_Purchased)
              FROM  (SELECT date_purchased
                       FROM dbo.QEliteContacts
                       WHERE contact_id = dbo.Users.ID) AS Date_Purchased_EJA),
					   dbo.Client_Services_Contact_Types.Contact_Type_Acronym
			FROM      dbo.Users INNER JOIN
                      dbo.Users_Info ON dbo.Users.ID = dbo.Users_Info.ID  LEFT OUTER JOIN
                      dbo.Client_Services_Contacts ON dbo.Client_Services_Contacts.contact_id = dbo.Users_Info.ID LEFT OUTER JOIN
                      dbo.Client_Services_Contact_Types ON dbo.Client_Services_Contact_Types.id = dbo.Client_Services_Contacts.ContactTypeID
		      Inner Join (	SELECT contact_id, EJA_Date_Purchased	SELECT date_purchased AS EJA_Date_Purchased
					FROM dbo.QEliteContacts) D on D.contact_id = dbo.Users.ID And D.EJA_Date_Purchased  BETWEEN '2013/01/01' AND '2013/10/09'
WHERE     (dbo.Users_Info.Status_Flag = 'A') AND (1 = 1) AND (dbo.Client_Services_Contact_Types.Contact_Type_Acronym = 'hr') 
              
ORDER BY UserID

Open in new window

0
lantervAuthor Commented:
I'm having trouble with the syntax.

Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ')'.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Daniel WilsonCommented:
Without the tables to work on, I'm at a bit of a disadvantage.  I really think the query can be simplified, however.

SELECT     TOP (100) PERCENT U.ID AS UserId,  Max(Q.Date_Purchased) as Date_Purchased_EJA

From dbo.Users U INNER JOIN dbo.QEliteContacts Q on U.ID = Q.contact_id INNER JOIN
                      dbo.Users_Info ON U.ID = dbo.Users_Info.ID  LEFT OUTER JOIN LEFT OUTER JOIN
                      dbo.Client_Services_Contacts ON dbo.Client_Services_Contacts.contact_id = dbo.Users_Info.ID LEFT OUTER JOIN
                      dbo.Client_Services_Contact_Types ON dbo.Client_Services_Contact_Types.id = dbo.Client_Services_Contacts.ContactTypeID
WHERE     (dbo.Users_Info.Status_Flag = 'A') AND (IsNull(dbo.Client_Services_Contact_Types.Contact_Type_Acronym,'hr) = 'hr') 
		AND Q.Date_Purchased  BETWEEN '2013/01/01' AND '2013/10/09'

Open in new window

0
lantervAuthor Commented:
I think I need to explain further.  I'm building the query string dynamically.  A sample query string created from my CF page would be;

I think I need to explain further.  I'm trying to add a select to a view so I can select users that have a date_purchased in a range and a contact acronym of 'hr'.

The dynamic query string created by my CF page would be;

select distinct(cl.userid) from View_CallListFields as cl where 0 = 0 AND ( 1=1 and ( ( cl.Contact_Type_Acronym = 'ha' ) ) and ( cl.Date_Purchased_EJA >= '2013/01/01' AND cl.Date_Purchased_EJA <= '2013/10/09' ) ) order by cl.userid

The entire View_CallListFields is;

SELECT     TOP (100) PERCENT dbo.Users.ID AS UserId, dbo.Users_Info.Home_Area_Code, dbo.Users_Info.Work_Area_Code, dbo.Users_Info.State AS HomeState, 
                      (CASE WHEN (dbo.Users_Info.Address1 = '' OR
                      dbo.Users_Info.Address1 IS NULL) THEN 0 ELSE 1 END) AS has_home_address, (CASE WHEN dbo.users.ID NOT IN
                          (SELECT     UserID
                            FROM          Users_Client_Companies) THEN 1 ELSE 0 END) AS Eliminate_TSI_Clients, (CASE WHEN dbo.users.ID IN
                          (SELECT     individual_number
                            FROM          qUsersPresented_ES) THEN 1 ELSE 0 END) AS Presented_ES, (CASE WHEN dbo.users.ID IN
                          (SELECT     individual_number
                            FROM          qUsersPlaced_ES
                            WHERE      isnull(date_of_placement, '') > '') THEN 1 ELSE 0 END) AS Placed_ES, (CASE WHEN dbo.users.ID IN
                          (SELECT     individual_number
                            FROM          qUsersPresented_CS) THEN 1 ELSE 0 END) AS Presented_CS, (CASE WHEN dbo.users.ID IN
                          (SELECT     individual_number
                            FROM          qUsersPlaced_CS
                            WHERE      isnull(date_of_placement, '') > '') THEN 1 ELSE 0 END) AS Placed_CS, (CASE WHEN dbo.users.ID IN
                          (SELECT     individual_number
                            FROM          QEliteAdCandidatesPanel
                            WHERE      isnull(date_of_placement, '') > '') THEN 1 ELSE 0 END) AS Placed_EJA, (CASE WHEN dbo.users.ID IN
                          (SELECT     individual_number
                            FROM          QEliteAdCandidatesPanel) THEN 1 ELSE 0 END) AS Presented_EJA, (CASE WHEN dbo.users.id IN
                          (SELECT     ID
                            FROM          qUsers_TTMember) THEN 1 ELSE 0 END) AS TT_Member, (CASE WHEN dbo.users.id IN
                          (SELECT     ID
                            FROM          qUsers_HasHomeEmail) THEN 1 ELSE 0 END) AS Has_Home_Email, (CASE WHEN dbo.users.id IN
                          (SELECT     ID
                            FROM          qUsers_HasWorkEmail) THEN 1 ELSE 0 END) AS Has_Work_Email, dbo.Companies.Sales_Level, dbo.Users_Salary.ISIPCT AS Individual_Letter_Per, 
                      dbo.Users_Salary.ISCPCT AS Corporate_Letter_Per, (CASE WHEN (dbo.Users_Info.Basic_JobAd = 1 OR
                      dbo.Users_Info.Custom_JobAd = 1 OR
                      dbo.Users_Info.Elite_JobAd = 1 OR
                      dbo.Users_Info.EJA_Candidate = 1) THEN 1 ELSE 0 END) AS job_board, (CASE WHEN dbo.Users_Info.Current_Company_Number IN
                          (SELECT     company_id
                            FROM          Companies_Adv) THEN 1 ELSE 0 END) AS Comp_Adv, (CASE WHEN dbo.Users_Info.Current_Company_Number IN
                          (SELECT     company_id
                            FROM          Companies_BJA) THEN 1 ELSE 0 END) AS Comp_BJA, (CASE WHEN dbo.Users_Info.Current_Company_Number IN
                          (SELECT     company_id
                            FROM          Companies_CJA) THEN 1 ELSE 0 END) AS Comp_CJA, (CASE WHEN dbo.Users_Info.Current_Company_Number IN
                          (SELECT     company_id
                            FROM          Companies_CS) THEN 1 ELSE 0 END) AS Comp_CS, (CASE WHEN dbo.Users_Info.Current_Company_Number IN
                          (SELECT     company_id
                            FROM          Companies_EJA) THEN 1 ELSE 0 END) AS Comp_EJA, (CASE WHEN dbo.Users_Info.Current_Company_Number IN
                          (SELECT     company_id
                            FROM          Companies_ES) THEN 1 ELSE 0 END) AS Comp_ES, (CASE WHEN dbo.Users_Info.Current_Company_Number IN
                          (SELECT     company_id
                            FROM          Companies_Intern) THEN 1 ELSE 0 END) AS Comp_Intern, (CASE WHEN dbo.Users_Info.Current_Company_Number IN
                          (SELECT     company_id
                            FROM          Companies_OrgAssess) THEN 1 ELSE 0 END) AS Comp_OrgAssess, (CASE WHEN dbo.Users_Info.Current_Company_Number IN
                          (SELECT     company_id
                            FROM          Companies_SB) THEN 1 ELSE 0 END) AS Comp_SB,
                         
                          (SELECT     MAX(EJA_Date_Purchased) AS EJA_Date_Purchased
                            FROM          (SELECT     date_purchased AS EJA_Date_Purchased
                                                    FROM          dbo.QEliteContacts
                                                    WHERE      (contact_id = dbo.Users.ID)) AS t) AS Date_Purchased_EJA, 
                      
                      dbo.Users_Salary.ISTEC1 AS Technical_Federal_Per, 
                      dbo.Users_Salary.ISTEC2 AS Technical_StateLocal_Per, dbo.Users_Salary.ISTEC3 AS Technical_InternationalForeign_Per, 
                      dbo.Users_Salary.ISTEC4 AS Technical_Economics_Per, dbo.Users_Salary.ISTEC5 AS Technical_Employment_Per, dbo.Users_Info.Title_Number, 
                      dbo.Users_Info.Year_Started_in_Tax, dbo.Users_Info.JD_year, dbo.Users_Info.Honors_JD, dbo.Users_Info.Top_25_JD_Flag, dbo.Users_Info.Past_Public_Flag, 
                      dbo.Users_Info.Past_Corp_Flag, dbo.Users_Info.Past_Law_Flag, dbo.Users_Info.Bird_Dog, dbo.Users_Info.Contract_Candidate, 
                      dbo.Users_Info.Current_Company_Number, dbo.Companies.Name, dbo.Users_Info.Corporate_Officer, dbo.Users_Salary.ISSALC, dbo.Users_Info.Country, 
                      dbo.Users_Info.Country_Phone_Prefix, dbo.Users_Info.Tier_Tracking, dbo.Users_Info.Market_Plan, dbo.Companies_Sic_Codes.Sic_Code, dbo.Users_Salary.relocate, 
                      dbo.Users_Salary.relocateUS, dbo.Users_Salary.other_language, dbo.Users_Salary.Korean, dbo.Users_Salary.Russian, dbo.Users_Salary.Japanese, 
                      dbo.Users_Salary.Arabic, dbo.Users_Salary.Italian, dbo.Users_Salary.Malaysian, dbo.Users_Salary.French, dbo.Users_Salary.Cantonese, 
                      dbo.Users_Salary.German, dbo.Users_Salary.Mandarin, dbo.Users_Salary.Spanish, dbo.Users_Communications.Search_Number, 
                      dbo.Users_Communications.Comment, dbo.Users_Communications.Communication_Type, dbo.Users_Communications.Date_Entered, dbo.Users_Salary.ISUDTD, 
                      dbo.Users_Salary.ISFUN1 AS Function_Planning_Per, dbo.Users_Salary.ISFUN2 AS Function_Compliance_Prep_Per, 
                      dbo.Users_Salary.ISFUN3 AS Function_Compliance_Review_Per, dbo.Users_Salary.ISFUN4 AS Function_FAS109_Per, 
                      dbo.Users_Salary.ISFUN5 AS Function_AuditAppl_Per, dbo.Users_Salary.ISFUN6 AS Function_Litigate_Per, dbo.Users_Salary.ISFUN7 AS Function_MA_Per, 
                      dbo.Users_Info.Individual_Type, dbo.Users_Info.Position_3_Tier, Admin_Users_1.AS400_Initials AS current_consultantIN, 
                      Admin_Users_1.AS400_Initials AS original_recruiterIN, dbo.Users_Info.Specialty, dbo.Users_Info.Date_Updated_From_Web, dbo.Users_Info.Marital_Status, 
                      dbo.Users_Info.Off_Limits_Flag, dbo.Users_Info.Resume_In_House, dbo.Users_Info.Manual_File, dbo.Users_Info.TEI_Flag, dbo.Users_Info.Email_Unsubscribe, 
                      dbo.Users_Info.No_Email_Tsi, dbo.Users_Info.No_Email_Tt, dbo.Users_Info.Diversity_Flag, dbo.Users_Info.TD_Campus_Outreach_Flag, dbo.Users_Info.Prof_Flag, 
                      dbo.Users_Info.Press_Release_Flag, dbo.Users_Info.SB_Client, dbo.Users_Info.SB_Participant, dbo.Users_Info.Custom_JobAd, dbo.Users_Info.Basic_JobAd, 
                      dbo.Users_Info.CS_Client, dbo.Users_Info.CS_Participant, dbo.Users_Salary.ISBON1, dbo.Users_Salary.ISBON2, dbo.Users_Salary.ISBON3, 
                      dbo.Users_Salary.ISBON4, dbo.Users_Info.Other_Software, dbo.Users_Info.Other_Relocation, dbo.Users_Info.Other_Language AS Expr2, 
                      dbo.Users_Info.Other_Education, dbo.Users_Info.Original_Recruiter, dbo.Users_Info.Current_Consultant, dbo.Notes.Note, dbo.Users_Info.Mr_Mrs_Ms_Miss, 
                      dbo.Users_Info.Function_Planning, dbo.Users_Info.Function_Compliance_Prep, dbo.Users_Info.Function_Compliance_Review, dbo.Users_Info.Function_FAS109, 
                      dbo.Users_Info.Function_AuditAppl, dbo.Users_Info.Function_Litigate, dbo.Users_Info.Function_MA, dbo.Users_Info.Technical_Federal, 
                      dbo.Users_Info.Technical_StateLocal, dbo.Users_Info.Technical_InternationalForeign, dbo.Users_Info.Technical_Economics, dbo.Users_Info.Technical_Employment, 
                      dbo.Users_Info.Individual_Letter, dbo.Users_Info.Corporate_Letter, dbo.Users_Info.ZipCode, dbo.Users_Info.City, dbo.Users_Info.Market_Sender_Consultant, 
                      dbo.Users_Info.Operations_Admin_ID, dbo.Companies.Corporate_structure, dbo.Companies.Client_Company, dbo.Companies.State AS WorkState, dbo.Users.SCDATE, 
                      dbo.Users_Info.TEI_Chair, dbo.Users.EMAIL_HOME, dbo.Users.EMAIL_WORK, dbo.Users_Info.Send_Christmas_Card, dbo.Users_Info.ES_Client, 
                      dbo.Users_Info.Elite_JobAd, dbo.Users_Info.EJA_Candidate, dbo.Users_Info.CS_Interest, dbo.Users_Info.Date_Entered AS Created_Date, 
                      dbo.Users_Communications.Admin_Users_ID AS CommAdminID, dbo.Users_Info.No_Email_Tf, SC_Client_Companies.Service_Type_ID AS Client_Company_Service, 
                      SC_Dev_Companies.Service_Type_ID AS Developing_Company_Service, dbo.Users.Joined_From, dbo.Users_Info.prof_accounting, 
                      dbo.Users_Info.prof_accounting_grad, dbo.Users_Info.prof_accounting_undergrad, dbo.Users_Info.prof_law, dbo.Users_Info.prof_department_head, 
                      dbo.Users_Info.prof_career_placement, dbo.Users_Info.Cell_Phone, dbo.Users_Info.Cell_Area_Code, 
                      dbo.Companies_Programs_Maps.ProgramTypeID AS School_Program_Type, dbo.Users_Info.prof_areas_of_tax, dbo.Users_Info.Contract_Salary_W2_Local, 
                      dbo.Users_Info.Contract_Salary_W2_Travel, dbo.Users_Info.Contract_Salary_1099_Local, dbo.Users_Info.Contract_Salary_1099_Travel, 
                      dbo.Companies.Actual_Sales_Per_Million, ES_CRM.AdminID AS ES_Lead, CS_CRM.AdminID AS CS_Lead, SB_CRM.AdminID AS SB_Lead, 
                      EJA_CRM.AdminID AS EJA_Lead, dbo.Users_Info.Prof_Emeritus, dbo.Users_Info.Prof_Adjunct_Flag, dbo.Companies.Year_End, 
                      dbo.Users_Site_Visits.site_id AS visit_site_id, dbo.Users_Site_Visits.visit_date, dbo.Companies.Publicly_Held, dbo.Companies.Privately_Held, 
                      dbo.Client_Services_Contact_Types.Contact_Type_Acronym, dbo.Users_Info.Do_Not_Mail_To_Work, dbo.Users_Info.Do_Not_Mail_To_Home
FROM         dbo.Companies LEFT OUTER JOIN
                      dbo.Companies_Programs_Maps ON dbo.Companies.ID = dbo.Companies_Programs_Maps.CompanyID RIGHT OUTER JOIN
                      dbo.Users INNER JOIN
                      dbo.Users_Info ON dbo.Users.ID = dbo.Users_Info.ID LEFT OUTER JOIN
                      dbo.Sales_Center_CRM_Companies AS SC_Dev_Companies ON dbo.Users_Info.Current_Company_Number = SC_Dev_Companies.CompanyID AND 
                      SC_Dev_Companies.CompanyTypeID = 2 LEFT OUTER JOIN
                      dbo.Sales_Center_CRM_Companies AS SC_Client_Companies ON dbo.Users_Info.Current_Company_Number = SC_Client_Companies.CompanyID AND 
                      SC_Client_Companies.CompanyTypeID = 1 LEFT OUTER JOIN
                      dbo.Notes ON dbo.Users.ID = dbo.Notes.Individual_Number ON dbo.Companies.ID = dbo.Users_Info.Current_Company_Number LEFT OUTER JOIN
                      dbo.Admin_Users AS Admin_Users_1 ON dbo.Users_Info.Original_Recruiter = Admin_Users_1.adminID LEFT OUTER JOIN
                      dbo.Companies_Sic_Codes ON dbo.Companies.ID = dbo.Companies_Sic_Codes.Company_Number LEFT OUTER JOIN
                      dbo.Users_Salary ON dbo.Users.ID = dbo.Users_Salary.UserID LEFT OUTER JOIN
                      dbo.Users_Communications ON dbo.Users.ID = dbo.Users_Communications.Users_id AND dbo.Users_Communications.Communication_Type <> 'M' AND 
                      dbo.Users_Communications.Communication_Type <> 'E' LEFT OUTER JOIN
                      dbo.Sales_Center_CRM_Companies AS ES_CRM ON ES_CRM.CompanyID = dbo.Users_Info.Current_Company_Number AND 
                      ES_CRM.Service_Type_ID = 1 LEFT OUTER JOIN
                      dbo.Sales_Center_CRM_Companies AS CS_CRM ON ES_CRM.CompanyID = dbo.Users_Info.Current_Company_Number AND 
                      CS_CRM.Service_Type_ID = 3 LEFT OUTER JOIN
                      dbo.Sales_Center_CRM_Companies AS SB_CRM ON SB_CRM.CompanyID = dbo.Users_Info.Current_Company_Number AND 
                      SB_CRM.Service_Type_ID = 4 LEFT OUTER JOIN
                      dbo.Sales_Center_CRM_Companies AS EJA_CRM ON EJA_CRM.CompanyID = dbo.Users_Info.Current_Company_Number AND 
                      EJA_CRM.Service_Type_ID = 12 LEFT OUTER JOIN
                      dbo.Users_Site_Visits ON dbo.Users_Site_Visits.user_id = dbo.Users.ID LEFT OUTER JOIN
                      dbo.Client_Services_Contacts ON dbo.Client_Services_Contacts.contact_id = dbo.Users_Info.ID LEFT OUTER JOIN
                      dbo.Client_Services_Contact_Types ON dbo.Client_Services_Contact_Types.id = dbo.Client_Services_Contacts.ContactTypeID
WHERE     (dbo.Users_Info.Status_Flag = 'A')

Open in new window



I'm trying to add the select to get the most recent date_purchased for any user that has a contact acronym of 'hr'.

The QEliteContacts is;

SELECT     bd.date_purchased, bd.ID, csc.contact_id, dbo.Client_Services_Contact_Types.Contact_Type_Acronym
FROM         dbo.Board_Details AS bd LEFT OUTER JOIN
                      dbo.Client_Services_Contacts AS csc ON csc.service_id = bd.ID LEFT OUTER JOIN
                      dbo.Client_Services_Contact_Types ON dbo.Client_Services_Contact_Types.id = csc.ContactTypeID
WHERE     (bd.ad_type LIKE '%Elite%')

Open in new window

0
coreconceptsCommented:
Hi Laterv,

Can you try running this for me and let me know what you get:

With ClientContracts As

(
SELECT

dbo.Users.ID AS UserId  
,  dbo.Client_Services_Contact_Types.Contact_Type_Acronym As Type_Acronym
, MAX(Date_Purchased) OVER(PARTITION BY dbo.Users.ID ORDER BY Date_Purchased DESC) As Date_Purchased_EJA
FROM  dbo.Users 
INNER JOIN dbo.Users_Info 
	ON dbo.Users.ID = dbo.Users_Info.ID  
LEFT OUTER JOIN dbo.Client_Services_Contacts 
	ON dbo.Client_Services_Contacts.contact_id = dbo.Users_Info.ID 
LEFT OUTER JOIN dbo.Client_Services_Contact_Types 
	ON dbo.Client_Services_Contact_Types.id = dbo.Client_Services_Contacts.ContactTypeID
WHERE  (dbo.Users_Info.Status_Flag = 'A')
AND (dbo.Client_Services_Contact_Types.Contact_Type_Acronym = 'hr') 

)
SELECT UserID 
, Type_Acronym
, Date_Purchased_EJA 
FROM ClientContracts
	WHERE Date_Purchased_EJA >= '2013/01/01' 
			AND Date_Purchased_EJA < '2013/10/10'

Open in new window


Should return almost the same thing, if it does... I would add something to make the windowing function deterministic.. right now it's looking at your USER ID and returning the newest date, but if there's a "tie" we want to add something like an order number or contract number to the ORDER BY
0
lantervAuthor Commented:
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'ORDER'.
0
PortletPaulfreelancerCommented:
Incorrect syntax near the keyword 'ORDER'.

regret to say you can't use the ORDER BY, only: MAX(...) over (PARTITION BY ...)
0
PortletPaulfreelancerCommented:
I would be avoiding the use of that view as much as possible (for performance)
- it's overkill for this:
SELECT DISTINCT
        (cl.userid)
FROM View_CallListFields AS cl
WHERE 0 = 0
        AND (1 = 1
        AND ((cl.Contact_Type_Acronym = 'ha'))
        AND (cl.Date_Purchased_EJA >= '2013/01/01'
        AND cl.Date_Purchased_EJA <= '2013/10/09'))
ORDER BY
        cl.userid

Open in new window

that query above translates into the following:
SELECT DISTINCT /* use distinct only if really needed */
        U.userid
FROM dbo.Users as U
INNER JOIN dbo.Users_Info AS UI
        ON U.ID = UI.ID
INNER JOIN dbo.Client_Services_Contacts AS CSC
        ON UI.ID = CSC.contact_id
INNER JOIN dbo.Client_Services_Contact_Types AS CSCT
        ON CSC.ContactTypeID = CSCT.id
INNER JOIN (
                SELECT
                        contact_id
                      , MAX(date_purchased) AS Date_Purchased_EJA
                FROM dbo.QEliteContacts
                GROUP BY
                        contact_id
           ) AS QEC
        ON U.ID = QEC.contact_id
WHERE 0 = 0
        AND (1 = 1
        AND ((CSCT.Contact_Type_Acronym = 'ha'))
        AND (QEC.Date_Purchased_EJA >= '2013/01/01'
        AND QEC.Date_Purchased_EJA <= '2013/10/09'))
ORDER BY
        U.userid
;

Open in new window

{+ edit} note left joins are not used as they are not required here to meet the where clause conditions
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lawrence BarnesCommented:
I took the first post on this thread and moved the where to an inner join (as portletpaul suggested) for this result.

If this doesn't work for you we could setup a shared screen session as its hard to work on it without the table structure or query hints.
SELECT TOP (100) PERCENT 
    dbo.Users.ID AS UserId
    , q.EJA_Date_Purchased
    , dbo.Client_Services_Contact_Types.Contact_Type_Acronym
FROM dbo.Users 
INNER JOIN dbo.Users_Info ON dbo.Users.ID = dbo.Users_Info.ID
INNER JOIN ( SELECT q.Contact_ID, MAX(q.Date_Purchased) as EJA_Date_Purchased FROM dbo.QEliteContacts q WHERE q.Date_Purchased BETWEEN '2013/01/01' AND '2013/10/09' GROUP BY q.Contact_ID ) maxQ ON dbo.Users.ID = maxQ.Contact_ID
LEFT OUTER JOIN dbo.Client_Services_Contacts ON dbo.Client_Services_Contacts.contact_id = dbo.Users_Info.ID 
LEFT OUTER JOIN dbo.Client_Services_Contact_Types ON dbo.Client_Services_Contact_Types.id = dbo.Client_Services_Contacts.ContactTypeID
WHERE (dbo.Users_Info.Status_Flag = 'A') 
    AND (1 = 1) 
    AND (dbo.Client_Services_Contact_Types.Contact_Type_Acronym = 'hr') 
ORDER BY UserID

Open in new window

0
PortletPaulfreelancerCommented:
You do not need left joins here because of this:

AND (dbo.Client_Services_Contact_Types.Contact_Type_Acronym = 'hr')
the purpose of a left join would be to allow a NULL to be returned from dbo.Client_Services_Contact_Types

then as soon as you filter for: Contact_Type_Acronym = 'hr', NULLs are ignored
so why use a left join?

& as you don't need a left join on that table, you also don't need it on Client_Services_Contacts because it is part of the join chain from user to Contact_Type_Acronym = 'hr'

------------------
regarding TOP 100 PERCENT..

My understanding is that practice arose as a way to force views to be sorted (back in sql 2k/2005), since then really there is no point in it:
the optimizer recognizes that TOP 100 PERCENT qualifies all rows and does not need to be computed at all.  It gets removed from the query plan, and there is no other reason to do an intermediate sorting operation.  As such, the output isn't returned in any particular order.
from MSDN : TOP 100 Percent ORDER BY Considered Harmful.
0
lantervAuthor Commented:
I didn't want to just join the data to an already complex view.  But that's what I had to do to get this project finished.  It just seemed there was a better way.  It now generates some 200k more rows than before and they are pared down using the DISTINCT function.  So be it.  Thanks anyway.
0
PortletPaulfreelancerCommented:
>>I didn't want to just join the data to an already complex view.
agreed, it is too complex for that need.

>>It just seemed there was a better way.
also agreed, and hoped that had been identified.

did you try the second code block of http:#a39564902 ?
0
PortletPaulfreelancerCommented:
Thanks for the grading, cheers, Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.