CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
MAK
MAK

Reputation: 7270

SQL Server 2008 R2: Get date wise records using multiple CTE

I have the following table:

Table : TblLocation

CREATE TABLE TblLocation
(
    ColDate DATE,
    ColTime time,
    Colvalue VARCHAR(50)
);

INSERT INTO TblLocation VALUES('2018-01-01','01:10:11','Location1');
INSERT INTO TblLocation VALUES('2018-01-01','02:10:11','Location2');
INSERT INTO TblLocation VALUES('2018-01-01','03:10:11','Location3');
INSERT INTO TblLocation VALUES('2018-01-01','11:10:11','Location4');

INSERT INTO TblLocation VALUES('2018-01-02','01:10:11','Location1');
INSERT INTO TblLocation VALUES('2018-01-02','02:10:11','Location2');
INSERT INTO TblLocation VALUES('2018-01-02','03:10:11','Location2');
INSERT INTO TblLocation VALUES('2018-01-02','01:10:11','Location3');
INSERT INTO TblLocation VALUES('2018-01-02','03:15:11','Location4');

INSERT INTO TblLocation VALUES('2018-01-03','02:10:11','Location1');
INSERT INTO TblLocation VALUES('2018-01-03','02:50:11','Location1');
INSERT INTO TblLocation VALUES('2018-01-03','03:10:11','Location1');

Note: I want to find the first, Mid and Last Location of for each date.

Expected Output:

ColDate         FirstLocation   MidLocation     LastLocation
-------------------------------------------------------------
2018-01-01      Location1       Location3       Location4
2018-01-02      Location1       Location2       Location4
2018-01-03      Location1       NULL            Location1

Output explaination:

  1. FirstLocation - This should be based on ORDER by date and time and will be FIRST time of that date.

  2. MidLocation - This should NOT be equal to FirstLocation and LastLocation but which is present in between location which is more in count or any in tie scenario. If nothing between then NULL as showed in 3rd row.

  3. LastLocation - This should be based on ORDER by date and time and will be LAST time of that date.

My Try:

WITH CTEMain AS
(
    SELECT ColDate,ColTime, ColValue, ROW_NUMBER() OVER(PARTITION BY ColDate ORDER BY ColDate,ColTime ASC) Rn 
    FROM dbo.TblLocation
),
CTEMinMax AS
(
    SELECT MIN(Rn) AS MinRn,MAX(Rn) AS MaxRn
    FROM CTEMain
),
CX AS 
(
    SELECT c1.ColDate,c1.Colvalue AS FirstLocation,
       c2.Colvalue AS LastLocation
    FROM CTEMain c1 
    INNER JOIN CTEMinMax cmin ON c1.Rn = cmin.MinRn
    INNER JOIN CTEMain c2 ON 1 = 1
    INNER JOIN CTEMinMax cmax ON c2.Rn = cmax.MaxRn
), 
CXX AS
( 
    SELECT TOP 1 ROW_NUMBER() OVER(PARTITION BY Colvalue ORDER BY Rn DESC) Rnk,ColValue,Rn
    FROM CTEMain c1
    INNER JOIN CX x ON c1.colvalue <> x.FirstLocation AND c1.colvalue <> x.LastLocation
    GROUP BY ColValue,Rn
    ORDER BY Rnk DESC
)
SELECT ColDate,CX.FirstLocation,(SELECT ColValue FROM CXX) AS MidName, cx.LastLocation
FROM CX

Output:

ColDate     FirstLocation   MidName     LastLocation
-----------------------------------------------------
2018-01-01  Location1       Location2   Location4
2018-01-02  Location1       Location2   Location4
2018-01-03  Location1       Location2   Location4

Upvotes: 1

Views: 72

Answers (2)

paparazzo
paparazzo

Reputation: 45106

Close 

declare @t TABLE (ColDate DATE, ColTime time, Colvalue VARCHAR(50));
INSERT INTO @t VALUES
('2018-01-01','01:10:11','Location1'),
('2018-01-01','02:10:11','Location2'),
('2018-01-01','03:10:11','Location3'),
('2018-01-01','11:10:11','Location4'),

('2018-01-02','01:10:11','Location1'),
('2018-01-02','02:10:11','Location2'),
('2018-01-02','03:10:11','Location2'),
('2018-01-02','01:10:11','Location3'),
('2018-01-02','03:15:11','Location4'),

('2018-01-03','02:10:11','Location1'),
('2018-01-03','02:50:11','Location1'),
('2018-01-03','03:10:11','Location1');

--select * from @t order by ColDate, ColTime

with cte as 
( select * 
       , row_number() over (partition by ColDate order by ColTime asc)  as tasc
       , row_number() over (partition by ColDate order by ColTime desc) as tdsc
  from @t 
) 

select distinct
       cteMin.ColDate, cteMin.ColTime
     , cteMin.Colvalue as [cteMin.Colvalue]
     , cteMid.Colvalue as [cteMid.Colvalue]
     , cteMax.Colvalue as [cteMax.Colvalue]
from cte cteMin 
join cte cteMax
  on cteMin.ColDate = cteMax.ColDate 
 and cteMin.tasc = 1 
 and cteMax.tdsc = 1
left join cte cteMid 
  on cteMid.ColDate = cteMin.ColDate 
 and cteMid.tasc > 1
 and cteMid.tdsc = 2
 and cteMid.Colvalue <> cteMin.Colvalue 
 and cteMid.Colvalue <> cteMax.Colvalue

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

I would use correlated subquery with apply operator :

select distinct t.coldate, t1.*, 
       ( select top 1 ColValue
         from TblLocation
         where (coldate = t.coldate and 
                ColValue <> t1.FirstLocation and ColValue <> t2.LastLocation)
         order by count(*) over() desc
       ) as MidName, t2.*
from TblLocation t cross apply 
     (select top 1 ColValue as FirstLocation 
      from TblLocation
      where coldate = t.coldate
      order by coltime
     ) t1 cross apply 
     (select top 1 ColValue as LastLocation
      from TblLocation
      where coldate = t.coldate
      order by coltime desc
     ) t2;

Upvotes: 1

Related Questions