none
Row with MAX(creat_date) Grouped by DealID

    Question

  • I have a data set that i would like to group by deal id and max(date_created) and the userid. This data is from a change log of assigned users in a crm. I would like to get a list of DealId's, the date it was changed last and the name of the person it was changed to. The data currently is resulting in ever change including the dealid, dates created and the usernames. 

    Select D.DealID,date_created, U.user_name
    	FROM P_Deals D
    	left join v_Events E on D.DealID = E.dealId 
    	left join contracts_cstm CC on cast(D.DealID as varchar(5)) = CC.id_c
    	left join leads L on CC.lead_id_c = L.id
    	left join leads_cstm LC on L.id = LC.id_c
    	left join leads_audit LA on L.id = LA.parent_id
    	left join users U on LA.after_value_string = U.id
    	WHERE  (E.PAComplete != '' or E.PAComplete is not null)
    	--and D.DealID = '2937' or D.DealID = '3012'
    	and (E.Funded is null or E.Funded = '') and D.Status not in('dropped', 'Redocumented', 'hold', 'prefunded') and LA.field_name = 'assigned_user_id' and user_name is not null
    )
    DealID date_created                         user_name
    2312 2011-04-19 13:21:44.000 danwo
    2312 2010-09-23 13:17:29.000 mtera
    2457 2010-12-29 13:48:13.000 dsilver
    2457 2009-08-12 14:03:55.000 dsilver
    2555 2008-11-20 14:21:26.000 mblan
    2555 2011-09-27 13:27:50.000 amoga
    2590 2011-09-02 20:57:00.000 damico
    2591 2011-09-06 13:41:15.000 damico
    2591 2011-09-13 13:35:15.000 damico
    2591 2011-09-09 21:31:09.000 jleer
    2594 2010-10-04 12:46:48.000 danwo
    2595 2010-10-04 12:46:48.000 danwo
    2618 2011-10-24 18:04:07.000 amoga

    lundi 26 mars 2012 21:36

Réponses

  • Try:

    ;with cte as (Select D.DealID,date_created, U.user_name, row_number() over (partition by D.DealID order by date_created DESC) as Rn FROM P_Deals D left join v_Events E on D.DealID = E.dealId left join contracts_cstm CC on cast(D.DealID as varchar(5)) = CC.id_c left join leads L on CC.lead_id_c = L.id left join leads_cstm LC on L.id = LC.id_c left join leads_audit LA on L.id = LA.parent_id left join users U on LA.after_value_string = U.id WHERE (E.PAComplete != '' or E.PAComplete is not null) --and D.DealID = '2937' or D.DealID = '3012' and (E.Funded is null or E.Funded = '') and D.Status not in('dropped', 'Redocumented', 'hold', 'prefunded')

    and LA.field_name = 'assigned_user_id' and user_name is not null )) -- I suggest to add alias to every field select * from cte where Rn = 1 -- last Date_Created row



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    lundi 26 mars 2012 21:54

Toutes les réponses

  • Try:

    ;with cte as (Select D.DealID,date_created, U.user_name, row_number() over (partition by D.DealID order by date_created DESC) as Rn FROM P_Deals D left join v_Events E on D.DealID = E.dealId left join contracts_cstm CC on cast(D.DealID as varchar(5)) = CC.id_c left join leads L on CC.lead_id_c = L.id left join leads_cstm LC on L.id = LC.id_c left join leads_audit LA on L.id = LA.parent_id left join users U on LA.after_value_string = U.id WHERE (E.PAComplete != '' or E.PAComplete is not null) --and D.DealID = '2937' or D.DealID = '3012' and (E.Funded is null or E.Funded = '') and D.Status not in('dropped', 'Redocumented', 'hold', 'prefunded')

    and LA.field_name = 'assigned_user_id' and user_name is not null )) -- I suggest to add alias to every field select * from cte where Rn = 1 -- last Date_Created row



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    lundi 26 mars 2012 21:54
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema arE. If you know how, follow ISO-11179 data element naming conventions AND formatting rulES. Temporal data should use ISO-8601 formatS. Code should be in Standard SQL AS much AS possible AND not local dialect. 

    This is minimal polite behavior on a SQL forum. 

    What I cannot imagine is a schema where you to use more LEFT OUTER JOINs than the entire payroll for General Motor. Did you actually pt a “v_” prefix on a VIEW name? Surely not!  The only thing worse is that you seem to be mixing audit data with the tables in the audit; talk to your lawyer. I see a “field_name” which sounds like meta data. But it is worse than that; field is a file system term and it is nothing like a column in a table. 

    Why do you think that “id” is a precise, clear data element name? How about the “status” of nothing in particular? Oh, SQL uses <> and not the old != from C. Will your dialect work? Yes, but it tells us that you are not an SQL yet. 

    Why are you checking for a NULL user name? Why are you casting in so many places? Think about this awful code: 

    WHERE (E.pa_complete_something <> '' 
             OR E.pa_complete_something IS NOT NULL)

    First of all, we have idea if this is a flag, a date or what. But a competent SQL programmer works on his DDL and would not allow a NULL and /or an empty string so you do not waste time checking it, screwing up the optimizer, etc.

    SQL programmers have more power than BASIC, so instead of
    (D.deal_id = '2937' OR D.deal_id = '3012')

    we write:
    D.deal_id IN ('2937', '3012')

    Would you like to try again, with enough information? 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    lundi 26 mars 2012 23:33
  • Hi,

    Can you please post your expected output.

    Sri Krishna


    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    mardi 27 mars 2012 05:00
  • Any progress?

    Kalman Toth SQL SERVER & BI TRAINING

    dimanche 1 avril 2012 20:51