Search code examples
sqldynamics-crm

SQL Group By last created record per quarter and org

I have 3 tables in my database - Quarter, Organization, History and I'm using Dynamics CRM to store my data.

Quarter columns :

  • quarterId (guid)
  • quarter (picklist)
  • year (picklist)

Organization columns:

  • organizationId (guid)

History columns:

  • historyId (guid)
  • calc (float)
  • cert (picklist)
  • organization (organizationId)
  • quarter (quarterId)
  • create-date (datetime)

I'd like to construct the following SQL query: Retrieve all History records per Quarter and Organization whose create-date value is last created.

For example in History table:

| guid | organization | cert | calc |     create-date     | quarter
| 1    | org1         | 2    | 2.5  | 2021-07-06 00:00:00 | Q3 2021
| 2    | org2         | 2    | NULL | 2021-07-25 00:00:00 | Q3 2021
| 3    | org3         | 3    | NULL | 2021-07-25 00:00:00 | Q3 2021
| 4    | org1         | 3    | NULL | 2021-08-01 00:00:00 | Q3 2021
| 5    | org1         | 3    | NULL | 2021-08-01 00:00:00 | Q3 2021
| 6    | org1         | 3    | NULL | 2021-09-15 00:00:00 | Q3 2021
| 7    | org1         | 2    | 2.25 | 2021-11-18 00:00:00 | Q4 2021

Query should return the following records: (minus the guid column)

| guid | organization | cert | calc |      create-date    | quarter | year
| 2    | org2         | 2    | NULL | 2021-07-25 00:00:00 | Q3      | 2021
| 3    | org3         | 3    | NULL | 2021-07-25 00:00:00 | Q3      | 2021
| 6    | org1         | 3    | NULL | 2021-09-15 00:00:00 | Q3      | 2021
| 7    | org1         | 2    | 2.25 | 2021-11-18 00:00:00 | Q4      | 2021

My query :

SELECT     
    QTR.new_year AS [year], 
    CASE 
        WHEN QTR.new_quarter = 100000000 THEN 'Q1' 
        WHEN QTR.new_quarter = 100000001 THEN 'Q2' 
        WHEN QTR.new_quarter = 100000002 THEN 'Q3' 
        ELSE 'Q4' 
    END AS [quarter], 
    /*  FORMAT(HISTORY.new_calc, 'N2') AS [calc], 
        HISTORY.new_cert AS [cert], */
    MAX(HISTORY.new_create_date) AS [create-date], 
    ORG.new_name AS [org]
FROM            
    dbo.new_quarterBase AS QTR
INNER JOIN 
    dbo.new_historyBase AS HISTORY ON QTR.new_quarterId = HISTORY.new_quarter 
INNER JOIN 
    dbo.new_organizationBase AS ORG ON HISTORY.new_organization = ORG.new_organizationId 
GROUP BY 
    QTR.new_quarter, QTR.new_year, ORG.new_name

The query works fine, but I'd like the query to also return the commented-out columns and I don't want them to be in the GROUP BY clause.

How can I solve this problem?

Solution

  • I'd like to construct the following SQL query: Retrieve all History records per Quarter and Organization whose create-date value is last created.

    I don't know wha the underlying database is for Dynamics CRM, but the way to do this in SQL uses row_number() or rank():

    SELECT qh.*
FROM (SELECT q.new_year AS [year], 
             (CASE WHEN q.new_quarter = 100000000 THEN 'Q1' 
                   WHEN q.new_quarter = 100000001 THEN 'Q2' 
                   WHEN q.new_quarter = 100000002 THEN 'Q3' 
                   ELSE 'Q4' 
              END) AS [quarter], 
             FORMAT(h.new_calc, 'N2') AS [calc], 
             h.new_cert AS [cert],
             h.new_create_date, 
             o.new_name AS org,
             RANK() OVER (PARTITION BY q.new_quarter, q.new_year, o.new_name ORDER BY h.new_create_date DESC) as seqnum
      FROM dbo.new_quarterBase q JOIN 
           dbo.new_historyBase h
           ON q.new_quarterId = h.new_quarter JOIN
           dbo.new_organizationBase o
           ON h.new_organization = o.new_organizationId 
     ) qh
WHERE seqnum = 1;

    The difference between row_number() and rank() is that the latter returns all history records in a quarter if there are ties for the most recent. The former returns only one.