SQL Server sum over partition by

Question

I have a fact table and I am using sum over partition by on the column vacant. I want to sum vacant when vacant is above 0. I have the following query:

SELECT 
    f.[D_Building_Id],
    f.[D_Entity_Id],
    f.[Date],
    f.[D_Contract_Id],
    f.[Lead],
    f.[Vacant],
    CASE 
        WHEN f.Vacant <> '0' 
            THEN SUM(f.Vacant) OVER (PARTITION BY f.D_Entity_Id, d_con.Contractnr ORDER BY f.Date ASC) 
        ELSE 0 
    END AS Vacant_Leadtime
FROM 
    [fact] f  
LEFT JOIN 
    d_contract d_con ON d_con._Id = f.D_Contract_Id
WHERE 
    1 = 1

However I have the issue that when a new vacancy appears on the same combination of D_building_Id and D_Entity_Id as the previous vacancy that the sum continues with the previous one. But what I want that it starts a new sum when and does not continue with the previous sum over partition by.

See example date below as an example that you can use:

CREATE TABLE VacancyData 
(
    D_Building_id INT,
    D_Entity_Id INT,
    Date DATE,
    D_Contract_Id INT NULL,
    Lead INT NULL,
    Vacant INT,
    Vacant_Leadtime INT
)

INSERT INTO VacancyData (D_Building_id, D_Entity_Id, Date, D_Contract_Id, Lead, Vacant, Vacant_Leadtime) 
VALUES
(13492, 23406, '2024-10-24', NULL, NULL, 1, 148),
(13492, 23406, '2024-10-25', NULL, NULL, 1, 149),
(13492, 23406, '2024-10-26', NULL, NULL, 1, 150),
(13492, 23406, '2024-10-27', NULL, NULL, 1, 151),
(13492, 23406, '2024-10-28', NULL, NULL, 1, 152),
(13492, 23406, '2024-10-29', NULL, NULL, 1, 153),
(13492, 23406, '2024-10-30', NULL, NULL, 1, 154),
(13492, 23406, '2024-10-31', NULL, NULL, 1, 155),
(13492, 23406, '2024-11-01', NULL, NULL, 1, 156),
(13492, 23406, '2024-11-02', NULL, NULL, 1, 157),
(13492, 23406, '2024-11-03', NULL, NULL, 1, 158),
(13492, 23406, '2024-11-04', NULL, NULL, 1, 159),
(13492, 23406, '2024-11-05', NULL, NULL, 1, 160),
(13492, 23406, '2024-11-06', 7115,  0   ,1, 0),
(13492, 23406, '2024-11-07', 7126,  1   ,0, 0),
(13492, 23406, '2024-11-08', 7126,  2   ,0, 0),
(13492, 23406, '2024-11-09', 7126,  3   ,0, 0),
(13492, 23406, '2024-11-10', 7126,  4   ,0, 0),
(13492, 23406, '2024-11-11', 7126,  5   ,0, 0),
(13492, 23406, '2024-11-12', 7126,  6   ,0, 0),
(13492, 23406, '2024-11-14', 7126,  8   ,0, 0),
(13492, 23406, '2024-11-19', 7126,  13  ,0, 0),
(13492, 23406, '2024-11-20', 7126,  14  ,0, 0),
(13492, 23406, '2024-11-21', 7126,  15  ,0, 0),
(13492, 23406, '2024-11-25', 7126,  19  ,0, 0),
(13492, 23406, '2024-11-26', 7126,  20  ,0, 0),
(13492, 23406, '2024-11-27', 7126,  21  ,0, 0),
(13492, 23406, '2024-11-28', 7126,  22  ,0, 0),
(13492, 23406, '2024-11-29', 7126,  23  ,0, 0),
(13492, 23406, '2024-11-30', 7126,  24  ,0, 0),
(13492, 23406, '2024-12-01', 7126,  25  ,0, 0),
(13492, 23406, '2024-12-02', 7126,  26  ,0, 0),
(13492, 23406, '2024-12-06', 7126,  30  ,0, 0),
(13492, 23406, '2024-12-07', 7126,  31  ,0, 0),
(13492, 23406, '2024-12-08', 7126,  32  ,0, 0),
(13492, 23406, '2024-12-09', 7126,  33  ,0, 0),
(13492, 23406, '2024-12-10', 7126,  34  ,0, 0),
(13492, 23406, '2024-12-11', 7126,  35  ,0, 0),
(13492, 23406, '2024-12-12', 7126,  36  ,0, 0),
(13492, 23406, '2024-12-13', 7126,  37  ,0, 0),
(13492, 23406, '2024-12-14', 7126,  38  ,0, 0),
(13492, 23406, '2024-12-15', 7126,  39  ,0, 0),
(13492, 23406, '2024-12-16', 7126,  40  ,0, 0),
(13492, 23406, '2024-12-17', 7126,  41  ,0, 0),
(13492, 23406, '2024-12-18', 7126,  42  ,0, 0),
(13492, 23406, '2024-12-19', 7126,  43  ,0, 0),
(13492, 23406, '2024-12-20', NULL, NULL, 1, 161),
(13492, 23406, '2024-12-21', NULL, NULL, 1, 162),
(13492, 23406, '2024-12-22', NULL, NULL, 1, 163),
(13492, 23406, '2024-12-23', NULL, NULL, 1, 164),
(13492, 23406, '2024-12-24', NULL, NULL, 1, 165),
(13492, 23406, '2024-12-30', NULL, NULL, 1, 166),
(13492, 23406, '2025-01-01', NULL, NULL, 2, 167);

So the issue is that on 2024-12-20 the Vacant_Leadtime is 161, as it continues the sum of 2024-11-05. However, what I would like to see is that on the date 2024-12-20 and onwards the Vacant_Leadtime is 1 and starts a new sum. Meaning that I get the following output:

CREATE TABLE VacancyDataResult 
(
    D_Building_id INT,
    D_Entity_Id INT,
    Date DATE,
    D_Contract_Id INT NULL,
    Lead INT NULL,
    Vacant INT,
    Vacant_Leadtime INT
)

INSERT INTO VacancyDataResult (D_Building_id, D_Entity_Id, Date, D_Contract_Id, Lead, Vacant, Vacant_Leadtime) 
VALUES
(13492, 23406, '2024-10-24', NULL, NULL, 1, 148),
(13492, 23406, '2024-10-25', NULL, NULL, 1, 149),
(13492, 23406, '2024-10-26', NULL, NULL, 1, 150),
(13492, 23406, '2024-10-27', NULL, NULL, 1, 151),
(13492, 23406, '2024-10-28', NULL, NULL, 1, 152),
(13492, 23406, '2024-10-29', NULL, NULL, 1, 153),
(13492, 23406, '2024-10-30', NULL, NULL, 1, 154),
(13492, 23406, '2024-10-31', NULL, NULL, 1, 155),
(13492, 23406, '2024-11-01', NULL, NULL, 1, 156),
(13492, 23406, '2024-11-02', NULL, NULL, 1, 157),
(13492, 23406, '2024-11-03', NULL, NULL, 1, 158),
(13492, 23406, '2024-11-04', NULL, NULL, 1, 159),
(13492, 23406, '2024-11-05', NULL, NULL, 1, 160),
(13492, 23406, '2024-11-06', 7115,  0   ,1, 0),
(13492, 23406, '2024-11-07', 7126,  1   ,0, 0),
(13492, 23406, '2024-11-08', 7126,  2   ,0, 0),
(13492, 23406, '2024-11-09', 7126,  3   ,0, 0),
(13492, 23406, '2024-11-10', 7126,  4   ,0, 0),
(13492, 23406, '2024-11-11', 7126,  5   ,0, 0),
(13492, 23406, '2024-11-12', 7126,  6   ,0, 0),
(13492, 23406, '2024-11-14', 7126,  8   ,0, 0),
(13492, 23406, '2024-11-19', 7126,  13  ,0, 0),
(13492, 23406, '2024-11-20', 7126,  14  ,0, 0),
(13492, 23406, '2024-11-21', 7126,  15  ,0, 0),
(13492, 23406, '2024-11-25', 7126,  19  ,0, 0),
(13492, 23406, '2024-11-26', 7126,  20  ,0, 0),
(13492, 23406, '2024-11-27', 7126,  21  ,0, 0),
(13492, 23406, '2024-11-28', 7126,  22  ,0, 0),
(13492, 23406, '2024-11-29', 7126,  23  ,0, 0),
(13492, 23406, '2024-11-30', 7126,  24  ,0, 0),
(13492, 23406, '2024-12-01', 7126,  25  ,0, 0),
(13492, 23406, '2024-12-02', 7126,  26  ,0, 0),
(13492, 23406, '2024-12-06', 7126,  30  ,0, 0),
(13492, 23406, '2024-12-07', 7126,  31  ,0, 0),
(13492, 23406, '2024-12-08', 7126,  32  ,0, 0),
(13492, 23406, '2024-12-09', 7126,  33  ,0, 0),
(13492, 23406, '2024-12-10', 7126,  34  ,0, 0),
(13492, 23406, '2024-12-11', 7126,  35  ,0, 0),
(13492, 23406, '2024-12-12', 7126,  36  ,0, 0),
(13492, 23406, '2024-12-13', 7126,  37  ,0, 0),
(13492, 23406, '2024-12-14', 7126,  38  ,0, 0),
(13492, 23406, '2024-12-15', 7126,  39  ,0, 0),
(13492, 23406, '2024-12-16', 7126,  40  ,0, 0),
(13492, 23406, '2024-12-17', 7126,  41  ,0, 0),
(13492, 23406, '2024-12-18', 7126,  42  ,0, 0),
(13492, 23406, '2024-12-19', 7126,  43  ,0, 0),
(13492, 23406, '2024-12-20', NULL, NULL, 1, 1),
(13492, 23406, '2024-12-21', NULL, NULL, 1, 2),
(13492, 23406, '2024-12-22', NULL, NULL, 1, 3),
(13492, 23406, '2024-12-23', NULL, NULL, 1, 4),
(13492, 23406, '2024-12-24', NULL, NULL, 1, 5),
(13492, 23406, '2024-12-30', NULL, NULL, 1, 6),
(13492, 23406, '2025-01-01', NULL, NULL, 2, 7);

I am unsure on how to approach this issue, so if anybody has some suggestions that would be really helpful.

Please edit your question, and show clearly the sample input data, along with the current and expected output. Do not include images, use text. — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 3 at 8:17
SSMS is just an IDE (like) application that is used with several products such as (but not limited to) SQL Server, Azure SQL Edge and Azure Synapse. SSMS is likely irrelevant to the question here. What product are you actually using for your data engine? Tagging [ssms] for your data engine is like tagging [visual-studio] for your programming language; it doesn't actually really tell us anything. — Thom A
– Thom A ♦, Commented Mar 3 at 9:29
Column leegstand doesn't seem to exist - did you perhaps translate it but not update all references? — Dale K
– Dale K, Commented Mar 3 at 18:44

ValNik · Accepted Answer · 2025-03-03 11:57:44Z

2

Your Contract_ID have sequence (by date)

(null),(7115),(7126),(null)

Calculation on (PARTITION BY f.D_Entity_Id, d_con.Contractnr ORDER BY f.Date) AS Vacant_Leadtime combines first and next (all) null's to one.

If you still want to distinguish between these sets of rows, mark the transitions as breaks (islands), count the island number, and add the island number to the partition.

See example.
I will use D_Contract_Id instead of Contractnr.

with gaps as(
 select *
    ,sum(isGap)over(partition by D_Building_Id,D_Entity_Id  order by Date) gapN
 from(
  select *
    ,case when coalesce(lag(D_Contract_Id)over(partition by D_Building_Id,D_Entity_Id   order by Date),0)
        =coalesce(D_Contract_Id,0) then 0
     else 1
     end isGap
  from VacancyData f
 )x
)
SELECT f.[D_Building_Id],f.[D_Entity_Id],f.[Date]
      ,f.[D_Contract_Id],f.[Lead],f.[Vacant]
      ,CASE WHEN f.Vacant <> '0' THEN 
          SUM(f.Vacant) OVER (PARTITION BY f.D_Entity_Id, f.D_Contract_Id,gapN ORDER BY f.Date ASC) 
       ELSE 0 
       END AS Vacant_Leadtime
      ,isGap,gapN
  FROM [gaps] f  
  WHERE 1=1
order by date

Fiddle

edited Mar 3 at 11:57

answered Mar 3 at 11:52

ValNik

7,8581 gold badge8 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Frans Mar 3 at 12:31

This was indeed what I was looking for!

Tim Biegeleisen · Accepted Answer · 2025-03-03 07:46:36Z

0

I think that your intention was to sum the CASE expression:

SUM(CASE WHEN f.Vacant <> '0' THEN f.Vacant ELSE 0 END)
    OVER (PARTITION BY f.D_Entity_Id, d_con.Contractnr ORDER BY f.Date) AS Vacant_Leadtime

Full updated query:

SELECT f.[D_Building_Id],
       f.[D_Entity_Id],
       f.[Date],
       f.[D_Contract_Id],
       f.[Lead],
       f.[Vacant],
SUM(CASE WHEN f.Vacant <> '0' THEN f.Vacant ELSE 0 END)
    OVER (PARTITION BY f.D_Entity_Id, d_con.Contractnr ORDER BY f.Date) AS Vacant_Leadtime
FROM [fact] f  
LEFT JOIN d_contract d_con
    ON d_con._Id = f.D_Contract_Id;

answered Mar 3 at 7:46

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

1 Comment

Frans Mar 3 at 8:11

Hi Tim, thank for your suggestion. However the same issue still arises. When the date is ad 2024-12-20 it continues with the previous sum of 2024-11-05. What I am looking for is that on 2024-12-20 the Vacant_Leadtime starts again with a new sum of 1.

Collectives™ on Stack Overflow

SQL Server sum over partition by

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related