CODEWITHSUNDEEP
sql-server
rainbow12
rainbow12

Reputation: 477

Group Values between date time range

I have a table that looks like this:

enter image description here

And I want to group the data order by the daydifference=1, the rows where the daydifference is bigger than 1, will be grouped between the days that have difference=1. So this will be like this:

enter image description here

So I tried this way:

SELECT 
    InicialData
    ,FinalDate
    FROM #ValueAnalysis
    WHERE WorkDays=1
    GROUP BY InicialData, FinalDate

And now I want to join the orders where the daydifference is bigger than 1. Can someone help me please?

Upvotes: 1

Views: 75

Answers (1)

gofr1
gofr1

Reputation: 15997

If you have SQL Server 2017 or newer, you can use STRING_AGG. For example like this:

SELECT va.InitialDate, 
       va.FinalDate, 
       STRING_AGG(cc.Order_,',') WITHIN GROUP (ORDER BY cc.Order_ ASC) as Order_1
FROM ValueAnalysis va 
OUTER APPLY (
    SELECT Order_
    FROM ValueAnalysis 
    WHERE va.InitialDate = InitialDate or va.FinalDate = FinalDate
) as cc
WHERE DayDiff = 1
GROUP BY va.InitialDate, va.FinalDate

Output:

| InitialDate |  FinalDate | Order_1 |
|-------------|------------|---------|
|  2020-01-02 | 2020-01-02 |   21,23 |
|  2020-01-03 | 2020-01-03 |      22 |
|  2020-01-04 | 2020-01-04 |   23,24 |

EDIT#1

If you need generate new table and include dates that are not in original dataset, then you can use recursive CTE:

;WITH cte AS (
    SELECT MIN(InitialDate) InitialDate, 
           MAX(FinalDate) FinalDate, 
    FROM ValueAnalysis
    UNION ALL
    SELECT DATEADD(day,1,InitialDate)
           FinalDate
    FROM cte
    WHERE InitialDate < FinalDate
)

SELECT c.InitialDate InitialDate, 
       c.InitialDate FinalDate, 
       STRING_AGG(va.Order_,',') WITHIN GROUP (ORDER BY va.Order_ ASC) as Order_1
FROM cte c 
LEFT JOIN ValueAnalysis va
    ON va.InitialDate = c.InitialDate or va.FinalDate = c.InitialDate
GROUP BY c.InitialDate, c.FinalDate
OPTION (MAXRECURSION 100)

Upvotes: 1

Related Questions