jeudi 16 juin 2016

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

Aucun commentaire:

Enregistrer un commentaire