CODEWITHSUNDEEP
sqlsql-server
prasuangelo
prasuangelo

Reputation: 35

Selecting a date based upon non unique column data in SQL Server

Any suggestion please........ This is where I completely stucked. I have no code to show what I have tried. Cause I couldn't think of a way to figure this out in sql? Somebody suggested me to use pivot (new to the concept...couldn't figure it out). 4 steps of Col5 makes a complete cycle. I have multiple steps processed and need the timing of a complete cycle. 1 step of a cycle may begin before completion of other.

Table A

Col1    Col2     Col3 Col4 Col4     Col5   Col6
 A       Date1     11     12   13     StepA   AA
 A       Date2     11     12   13     StepB   AA 
 A       Date3     11     12   13     StepC   AA
 A       Date4     11     12   13     StepD   AA
 A       dAte1     11     12   13     StepA   AC
 A       dAte2     11     12   13     StepB   AC 
 A       dAte3     11     12   13     StepC   AC
 A       daTe1     111    12   13     StepA   AB <=== Step is not completed    
 A       dAte4     11     12   13     StepD   AC <=== Step completed but other step started already
 A       daTe2     111    12   13     StepB   AB 
 A       daTe3     111    12   13     StepC   AB
 A       daTe4     111    12   13     StepD   AB

Expected OutPut Table (Result):

AA Date1  Date2  Date3  Date4
AC dAte1  dAte2  dAte3  dAte4
AB daTe1  daTe2  daTe3  daTe4

Upvotes: 1

Views: 138

Answers (2)

S.A
S.A

Reputation: 196

The Query:

SELECT * FROM 
    (SELECT [Col1]
      ,[Col2]
      ,[Col3]
      ,[Col4]
      ,[Col41]
      ,[Col5]
      ,[Col6]
    FROM [dbo].[YourTable])p
PIVOT ( MAX(Col2) FOR Col5  IN ([StepA],[StepB],[StepC],[StepD]) ) PVT

The Output:

Col1    Col3    Col4    Col41   Col6    StepA   StepB   StepC   StepD
A       11      12      13      AA      Date1   Date2   Date3   Date4
A       11      12      13      AC      dAte1   dAte2   dAte3   dAte4
A       111     12      13      AB      daTe1   daTe2   daTe3   daTe4

You can choose which columns you require and which columns to remove in your final select.(regarding col3,Col4,Col41)

****Based on the logic explained in the comments*******

/* Provided sample Data */

select * from #Testdata

COL1    Date                        COL2   COL3 COL4 COL5  COL6
QMS14   2013-04-08 17:19:52.8944000 3132    95  70   RFP    GSBE 
QMS14   2013-04-08 17:19:58.1445000 3132    95  70   SR     GSBE 
QMS14   2013-04-08 17:19:58.3164000 3132    95  70   RTP    GSBE 
QMS14   2013-04-08 17:19:58.3164000 3132    95  70   SoRe   GSBE
QMS14   2013-04-08 17:39:30.0501000 3132    95  70   RFP    GSBE 
QMS14   2013-04-08 17:39:30.0501000 3132    95  70   SR     GSBE 
QMS14   2013-04-08 17:39:30.3470000 3132    95  70   RTP    GSBE 
QMS14   2013-04-08 17:39:30.3470000 3132    95  70   SoRe   GSBE

/*Query assuming each Cycle happens in the same minute */

SELECT 
     COL1 
    ,COL2 
    ,COL3 
    ,COL4 
    ,COL6 
    ,[RFP]
    ,[SR]
    ,[RTP]
    ,[SoRe]
FROM (
    SELECT *
        ,DATEPART(MINUTE,DATE) DateMinute
        ,DENSE_RANK() OVER ( PARTITION BY Col1 ORDER BY DATEPART(MINUTE,DATE)) [Rank]   FROM  #Testdata
        )a
    PIVOT ( MAX([DATE]) FOR COL5 IN ([RFP],[SR], [RTP],[SoRe] )) PVT

OUTPUT:

COL1    COL2    COL3 COL4   COL6    RFP                         SR                          RTP                         SoRe
QMS14   3132     95   70   GSBE     2013-04-08 17:19:52.8944000 2013-04-08 17:19:58.1445000 2013-04-08 17:19:58.3164000 2013-04-08 17:19:58.3164000
QMS14   3132     95   70   GSBE     2013-04-08 17:39:30.0501000 2013-04-08 17:39:30.0501000 2013-04-08 17:39:30.3470000 2013-04-08 17:39:30.3470000

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

To me, it looks like the cycles are determined by combinations of four "steps" with the same tag in col6. The data in the example only has one cycle per code, but the comments suggest there might be more than one.

How can we identify which cycle for a given code a record belongs to? Well, one way is to assume that all the cycles with that code are complete and just enumerate the records by col6, col5. This additional cycle sequence number is then used for aggregation. For enumerating the sequences, I further assume that the dates are increasing over time.

Although a pivot can be used in this case, I think the aggregation method might be a bit clearer:

select col6, seqnum,
       MAX(case when col5 = 'StepA' then col2 end),
       MAX(case when col5 = 'StepB' then col2 end),
       MAX(case when col5 = 'StepC' then col2 end),
       MAX(case when col5 = 'StepD' then col2 end)
from (select a.*,
             ROW_NUMBER() over (partition by col6, col5 order by col2) as seqnum
      from a
     ) a
group by col6, seqnum

Upvotes: 0

Related Questions