asked
Joining and Grouping data from 3 tables
I have 3 tables >Category CategorySerno | CategoryName 1 One 2 Two 3 Three > Status StatusSerno | Status 1 Active 2 Pending > > Data CatId |Status | Date 1 1 2014-07-26 11:30:09.693 2 2 2014-07-25 17:30:09.693 1 1 2014-07-25 17:30:09.693 1 2 2014-07-25 17:30:09.693 When I join them I get I need the Joining of the latest Date/ Like One Active 2014-07-26 11:30:09.693 Two Inactive 2014-07-25 17:30:09.693 Three Null Null When I am doing a Join and group them It gives me One Active 2014-07-26 11:30:09.693 One Active 2014-07-26 11:30:09.693 One Active 2014-07-26 11:30:09.693 Two Inactive 2014-07-25 17:30:09.693 Three Null Null
select * from ( select c.CategoryName, s.Status, d.Date, rn = row_number() over (partition by c.CategorySerno order by d.Date desc) from Category c left join Data d on c.CategorySerno = d.CatId left join Status s on d.Status = s.StatusSerno ) d where d.rn = 1
This should do it! DECLARE @Category TABLE ( CategorySerno TINYINT , CategoryName VARCHAR(5) ); INSERT INTO @Category VALUES ( 1, 'One' ), ( 2, 'Two' ), ( 3, 'Three' ); DECLARE @Status TABLE ( StatusSerno TINYINT , [Status] VARCHAR(10) ); INSERT INTO @Status VALUES ( 1, 'Active' ), ( 2, 'Pending' ); DECLARE @Data TABLE ( CatId TINYINT , [Status] TINYINT , [Date] DATETIME ) INSERT INTO @Data VALUES ( 1, 1, '2014-07-26 11:30:09.693' ), ( 2, 2, '2014-07-25 17:30:09.693' ), ( 1, 1, '2014-07-25 17:30:09.693' ), ( 1, 2, '2014-07-25 17:30:09.693' ); WITH cteLatestDate AS ( SELECT d.CatId , MAX(d.Date) AS LatestDate FROM @Data d GROUP BY d.CatId ), cteTable1 AS ( SELECT ld.CatId , ld.LatestDate , d.Status FROM cteLatestDate ld INNER JOIN @Data d ON d.CatId = ld.CatId AND d.Date = ld.LatestDate ) SELECT c.CategoryName , s.Status , t1.LatestDate FROM @Category c LEFT OUTER JOIN cteTable1 t1 ON t1.CatId = c.CategorySerno LEFT OUTER JOIN @Status s ON s.StatusSerno = t1.Status;
16 People are following this question.
