CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2012
mohan111
mohan111

Reputation: 8865

How to get data based on Case condition and MAX Date

I have some data:

Declare @table table (RID VARCHAR(10),
                      CommType INT,
                      CommunicationType INT,
                      VALUE VARCHAR(20),
                      lastDate Datetime)

INSERT INTO @table (RID, CommType, CommunicationType, VALUE, lastDate) 
VALUES 
('00WAAS', 3, 0, 'mohan@gmail', '2012-06-15 15:23:49.653'),
('00WAAS', 3, 1, 'manasa@gmail', '2015-08-15 15:23:49.653'),
('00WAAS', 3, 2, 'mother@gmail', '2014-09-15 15:23:49.653'),
('00WAAS', 3, 2, 'father@gmail', '2016-01-15 15:23:49.653'),
('00WAAS', 3, 0, 'hello@gmail', '2013-01-15 15:23:49.653')

My query:

SELECT 
    TT.RID,
    COALESCE(Homemail, BusinessMail, OtherMail) Mail  
FROM 
    (SELECT
         RID, MAX(Homemail) Homemail,
         MAX(BusinessMail) BusinessMail,
         MAX(OtherMail) OtherMail 
     FROM 
         (SELECT
              RID, 
              CASE 
                 WHEN CommType = 3 AND CommunicationType = 0 THEN VALUE 
              END AS Homemail,
              CASE 
                 WHEN CommType = 3 AND CommunicationType = 1 THEN VALUE 
              END AS BusinessMail,
              CASE 
                 WHEN CommType = 3 AND CommunicationType = 2 THEN VALUE 
              END AS OtherMail,
              lastDate
          FROM
              @table) T
      GROUP BY RID) TT

What I'm expecting

Here I need to get result if CommType = 3 and CommunicationType = 0 then related value based on latest date and if data is not available for CommType = 3 and CommunicationType = 0

then I need to get data of CommunicationType = 1 related value based on latest date and if there is no data for CommunicationType = 1

then CommunicationType = 2 based on latest date of that CommunicationTypes.

Here I have tried Case condition ,MAX and Coalesce

If combination data is present in CommunicationType = 0 is present get CommunicationType = 0 based on latest date

If combination data is not present in CommunicationType = 0 then get CommunicationType = 1 based on latest date

If combination data is not present in CommunicationType = 1 then get CommunicationType = 2 based on latest date

Upvotes: 0

Views: 11115

Answers (2)

David Rushton
David Rushton

Reputation: 5030

I'm not entirely sure I've understood the requirement. But I think you want:

  1. One record returned for each RID.
  2. The returned record should have a CommType of 3.
  3. If there is more than one record with a CommType 3 you want the record with the lowest CommunicationType.
  4. If there is still more than one record you want the one with the most recent lastDate.

This query uses the windowed function ROW_NUMBER to rank the available records, within a subquery. PARTITION BY ensures each RID is ranked sepearatly. The outer query returns all records with a rank of 1.

Query

SELECT
    r.*
FROM
    (           
        /* For each RID We want the lowest communication type with 
         * the most recent last date.
         */
        SELECT
            ROW_NUMBER() OVER (PARTITION BY RID ORDER BY CommunicationType, lastDate DESC) AS rn,
            *
        FROM
            @table
        WHERE
            CommType = 3
    ) AS r
WHERE
    r.rn = 1
;

Next Steps

This query is ok but could be better. For example what would happen if two records had a matching CommType, CommunicationType and lastDate? Reading up on the differences between ROW_NUMBER, RANK, DENSE_RANK and NTILE will help you figure out your options here.

Upvotes: 1

sagi
sagi

Reputation: 40491

If I understood you correctly, use ROW_NUMBER() :

SELECT tt.RID,COALESCE(tt.Homemail,tt.businessMail,tt.OtherMail) 
FROM(
    select s.RID,
        MAX(CASE WHEN s.CommType = 3 AND s.CommunicationType = 0 THEN s.VALUE END) AS Homemail,
        MAX(CASE WHEN s.CommType = 3 AND s.CommunicationType = 1 THEN s.VALUE END) AS BusinessMail,
        MAX(CASE WHEN s.CommType = 3 AND s.CommunicationType = 2 THEN s.VALUE END) AS OtherMail
     from (SELECT t.*,ROW_NUMBER() OVER(PARTITION BY t.rid,t.communicationType ORDER BY t.lastDate DESC)
           FROM @table t
           WHERE t.commType = 3) s
    WHERE s.rnk = 1
    GROUP BY s.rid) tt

Upvotes: 0

Related Questions