CODEWITHSUNDEEP
sqlgrouping
Olivier
Olivier

Reputation: 27

SQL Grouping based on validity

I have the following table, let's call it tbl_costcenters, with the following dummy entries:

ID PosName CostcenterCode ValidFrom ValidUntil
1  test1   111            1.1.2019  1.6.2019
2  test1   111            1.6.2019  1.9.2019
3  test1   222            1.9.2019  1.6.2020

and i would have the following result:

PosName  ValidFrom    ValidUntil   CostcenterCode
test1    1.1.2019     1.9.2019     111
test1    1.9.2019     1.6.2020     222

This is very simplified. The real table contains much more cols. I need to group them based on the costcentercode and get a validity that englobes the two first entries of my example, returning the validfrom from record ID 1 and the validuntil from record ID 2.

Sorry i did not really know for what to search. I think that the answer is easy for somebody that is strong in SQL.

The answer should work for both, SQL Server and for Oracle.

Thank you for your help.

Upvotes: 1

Views: 91

Answers (3)

Olivier
Olivier

Reputation: 27

The definitve solution for me was:

select posname, min(validfrom), 
case 
    when 
        max(case when validuntil is null then 1 ELSE 0 END) = 0
    then max(validuntil)
end
from tbl_costcenters pos 
group by posname, costcentercode;

Thank you all.

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

This seems simple aggregation :

select PosName, 
       min(ValidFrom) as ValidFrom, 
       (case when max(ValidUntil) > min(ValidFrom) then max(ValidUntil) end) as ValidUntil, 
       CostcenterCode
from tbl_costcenters t
group by PosName, CostcenterCode;

Upvotes: 1

GMB
GMB

Reputation: 222502

I suspect that you want to group togethers records whose date overlap, while keeping those that don't overlap separated (although this is not showing in your sample data).

If so, we could use some gaps-and-island techniques here. One option uses window functions to build groups of adjacent records:

select 
    postName, 
    min(validFrom) validFrom,
    max(validUntil) validUntil
    costCenter
from (
    select
        t.*,
        sum(case when validFrom <= lagValidUntil then 0 else 1 end)
            over(partition by posName, costCenter order by validFrom) grp
    from (
        select
            t.*,
            lag(validUntil) 
                over(partition by posName, costCenter order by validFrom) lagValidUntil
        from mytable t
    ) t
) t
group by postName, costCenter, grp
order by postName, validFrom

Upvotes: 0

Related Questions