CODEWITHSUNDEEP
sqlt-sql
daniel charlesworth
daniel charlesworth

Reputation: 23

SQL - Summary of transactions - Calculate start end time of transactions based on column

I have a SQL table which holds transactions of products and cumulative total of packed products each minute from a machine.

I'm trying to summarize this table (in a sql query) into product runs.

Example Table

Example Output - Summary

Create table and insert data....

CREATE TABLE [dbo].[Test](
    [ID] [nchar](10) NULL,
    [Product] [varchar](20) NULL,
    [datetime] [datetime] NULL,
    [Cumulative_Packed] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Test] ([ID], [Product], [datetime], [Cumulative_Packed]) VALUES (N'1         ', N'Item1', CAST(N'2019-11-14T14:15:00.000' AS DateTime), 10)
INSERT [dbo].[Test] ([ID], [Product], [datetime], [Cumulative_Packed]) VALUES (N'2         ', N'Item1', CAST(N'2019-11-14T14:16:00.000' AS DateTime), 22)
INSERT [dbo].[Test] ([ID], [Product], [datetime], [Cumulative_Packed]) VALUES (N'3         ', N'Item1', CAST(N'2019-11-14T14:17:00.000' AS DateTime), 35)
INSERT [dbo].[Test] ([ID], [Product], [datetime], [Cumulative_Packed]) VALUES (N'4         ', N'Item1', CAST(N'2019-11-14T14:18:00.000' AS DateTime), 40)
INSERT [dbo].[Test] ([ID], [Product], [datetime], [Cumulative_Packed]) VALUES (N'5         ', N'Item1', CAST(N'2019-11-14T14:19:00.000' AS DateTime), 40)
INSERT [dbo].[Test] ([ID], [Product], [datetime], [Cumulative_Packed]) VALUES (N'6         ', N'Item1', CAST(N'2019-11-14T14:20:00.000' AS DateTime), 40)
INSERT [dbo].[Test] ([ID], [Product], [datetime], [Cumulative_Packed]) VALUES (N'7         ', N'Item1', CAST(N'2019-11-14T14:21:00.000' AS DateTime), 0)
INSERT [dbo].[Test] ([ID], [Product], [datetime], [Cumulative_Packed]) VALUES (N'8         ', N'Item1', CAST(N'2019-11-14T14:22:00.000' AS DateTime), 0)
INSERT [dbo].[Test] ([ID], [Product], [datetime], [Cumulative_Packed]) VALUES (N'9         ', N'Item1', CAST(N'2019-11-14T14:23:00.000' AS DateTime), 0)
INSERT [dbo].[Test] ([ID], [Product], [datetime], [Cumulative_Packed]) VALUES (N'10        ', N'Item2', CAST(N'2019-11-14T14:24:00.000' AS DateTime), 12)
INSERT [dbo].[Test] ([ID], [Product], [datetime], [Cumulative_Packed]) VALUES (N'11        ', N'Item2', CAST(N'2019-11-14T14:25:00.000' AS DateTime), 18)
INSERT [dbo].[Test] ([ID], [Product], [datetime], [Cumulative_Packed]) VALUES (N'12        ', N'Item2', CAST(N'2019-11-14T14:26:00.000' AS DateTime), 18)
INSERT [dbo].[Test] ([ID], [Product], [datetime], [Cumulative_Packed]) VALUES (N'13        ', N'Item2', CAST(N'2019-11-14T14:27:00.000' AS DateTime), 22)
INSERT [dbo].[Test] ([ID], [Product], [datetime], [Cumulative_Packed]) VALUES (N'14        ', N'Item2', CAST(N'2019-11-14T14:28:00.000' AS DateTime), 22)
INSERT [dbo].[Test] ([ID], [Product], [datetime], [Cumulative_Packed]) VALUES (N'15        ', N'Item1', CAST(N'2019-11-14T14:29:00.000' AS DateTime), 0)
INSERT [dbo].[Test] ([ID], [Product], [datetime], [Cumulative_Packed]) VALUES (N'16        ', N'Item1', CAST(N'2019-11-14T14:30:00.000' AS DateTime), 7)
INSERT [dbo].[Test] ([ID], [Product], [datetime], [Cumulative_Packed]) VALUES (N'17        ', N'Item1', CAST(N'2019-11-14T14:31:00.000' AS DateTime), 30)
INSERT [dbo].[Test] ([ID], [Product], [datetime], [Cumulative_Packed]) VALUES (N'18        ', N'Item1', CAST(N'2019-11-14T14:32:00.000' AS DateTime), 38)
INSERT [dbo].[Test] ([ID], [Product], [datetime], [Cumulative_Packed]) VALUES (N'19        ', N'Item1', CAST(N'2019-11-14T14:33:00.000' AS DateTime), 38)
INSERT [dbo].[Test] ([ID], [Product], [datetime], [Cumulative_Packed]) VALUES (N'20        ', N'Item1', CAST(N'2019-11-14T14:34:00.000' AS DateTime), 38)
INSERT [dbo].[Test] ([ID], [Product], [datetime], [Cumulative_Packed]) VALUES (N'21        ', N'Item1', CAST(N'2019-11-14T14:35:00.000' AS DateTime), 38)

Upvotes: 0

Views: 603

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

I think you want lead() and lag() to identify the starts and stops. For your data:

select row_number() over (order by min(datetime)) as run_order,
       product, min(datetime), max(datetime),
       max(cumulative_packed)
from (select t.*,
             sum(case when prev_cp = 0 or prev_cp is null then 1 else 0 end) over (partition by product order by datetime) as rungrp
      from (select t.*,
                   lag(cumulative_packed) over (partition by product order by datetime) as prev_cp,
                   lead(cumulative_packed) over (partition by product order by datetime) as next_cp
             from t
            ) t
       where (prev_cp = 0 or prev_cp is null) or              -- is start
             (next_cp = cp or next_cp = 0 or next_cp is null) -- is stop
      ) t
group by rungrp, product
order by rungrp, product, min(datetime);

EDIT:

Your data seems to require duplicate removal. From what I follow, you just need the first time the value changes. This can be handled with one additional layer of subqueries:

select row_number() over (order by min(datetime)) as run_order,
       product, min(datetime), max(datetime),
       max(cumulative_packed)
from (select t.*,
             sum(case when prev_cp = 0 or prev_cp is null then 1 else 0 end) over (partition by product order by datetime) as rungrp
      from (select t.*,
                   lag(cumulative_packed) over (partition by product order by datetime) as prev_cp,
                   lead(cumulative_packed) over (partition by product order by datetime) as next_cp
             from (select t.*,
                          lag(cumulative_packed) over (partition by product order by datetime) as first_prev_cp
                   from t
                  ) t
             where first_prev_cp is null or first_prev_cp <> cumulative_packed
            ) t
       where (prev_cp = 0 or prev_cp is null) or              -- is start
             (next_cp = cp or next_cp = 0 or next_cp is null) -- is stop
      ) t
group by rungrp, product
order by rungrp, product, min(datetime);

Upvotes: 1

Related Questions