SQL Server record chained

I’m working on SQL Server 2012
I have tickets with this information

Ticket No| Date | Closed
T001     | D1   | 
T002     | D2   |
T003     | D3   | Yes
T004     | D4   | Yes 
T005     | D5   |
T006     | D6   | Yes

I would like to group them by “case”, the first ticket after a closing isopenning the “case” and all the following ones until the closing are part of the “case”

  • Convert specific BigInt to DateTime in T-SQL
  • Unable to connect to SQL database - C#, VS2012, SQL Server 2012
  • Does the order of columns matter in a group by clause?
  • How to force a SQL Server 2008 database to go Offline
  • Get everything after and before certain character in SQL Server
  • Select records with multiple row criteria in sql server
  • Case No | Ticket No| Date | Closed
    C001    | T001     | D1   | 
    C001    | T002     | D2   |
    C001    | T003     | D3   | Yes
    C002    | T004     | D4   | Yes 
    C003    | T005     | D5   |
    C003    | T006     | D6   | Yes
    

    It’s been hours i’ve been trying (using lag, rank, etc) but i can’t find a way to do it. I an’t find a way to generate the Case No as wanted
    Do you have an idea ? Thanks a lot

    Here is the query :

    SELECT
        CONCAT('C' , ID_TICKET) As ID_CASE_TEMP
        ,ID_TICKET
        ,DT_TICKET
        ,IIF(IsNull(LAG(IsNull(LB_CLOSING,'No')) OVER (ORDER BY DT_TICKET),'Yes') = 'Yes','Yes','No') As LB_OPENNING
        ,IsNull(LB_CLOSING,'No') As LB_CLOSING
        ,LAG(ID_TICKET) OVER (ORDER BY DT_TICKET) As PREVIOUS_TICKET
        ,LAG(DT_TICKET) OVER (ORDER BY DT_TICKET) As PREVIOUS_TICKET_DATE
        ,LAG(IsNull(LB_CLOSING,'No')) OVER (ORDER BY DT_TICKET) As PREVIOUS_TICKET_CLOSING
        ,ROW_NUMBER() OVER (ORDER BY DT_TICKET) As CD_ROW_NUMBER
    FROM 
        TICKETS 
    

    One Solution collect form web for “SQL Server record chained”

    Here is one way to do it using LAG window function

    SELECT 'C' + RIGHT(Concat('00', 1+Sum(CASE WHEN prev_val = 'Yes' THEN 1 ELSE 0 END)
                                            OVER (ORDER BY [date])), 3) AS [Case No], 
           [Ticket No],
           [Date],
           [Closed]
    FROM   (SELECT *, Lag(closed) OVER( ORDER BY [date]) AS prev_val 
            FROM   table1) a 
    
    • Live Demo
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.