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