CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Adam S
Adam S

Reputation: 73

TSQL - Partition BY if in order

I am trying to partition by [Status] after it's sorted by [Status_DateTime] DESC. So only [Status] that are in a row will be partitioned in order to get the earliest Date for it.

/****** Object:  Table [dbo].[Order_Status]    Script Date: 1/13/2017 1:06:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Order_Status](
    [Case_No_ID] [int] NULL,
    [Status] [nvarchar](60) NULL,
    [Status_Datetime] [datetime] NULL,
    [Employee_ID] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Order_Status] ([Case_No_ID], [Status], [Status_Datetime], [Employee_ID]) VALUES     
(223079, N'HP - HOLD FOR PARTS', CAST(N'2015-12-21T16:44:50.000' AS DateTime), 740)
,(223079, N'DP - ORDER CREATED', CAST(N'2015-12-21T16:45:11.000' AS DateTime), 740)
,(223079, N'PP - PRINT PL', CAST(N'2015-12-21T16:47:03.000' AS DateTime), 709)
,(223079, N'PS- PARTS SHIPPED', CAST(N'2015-12-21T17:50:39.000' AS DateTime), 824)
,(223079, N'OS - MACHINE RECEIVED', CAST(N'2015-12-29T09:47:34.000' AS DateTime), 2)
,(223079, N'RP - UNDER INSPECTION', CAST(N'2015-12-30T12:14:07.000' AS DateTime), 168)
,(223079, N'RP - UNDER INSPECTION', CAST(N'2015-12-30T13:41:01.000' AS DateTime), 168)
,(223079, N'RP - UNDER INSPECTION', CAST(N'2016-01-05T14:15:46.000' AS DateTime), 241)
,(223079, N'RP - UNDER INSPECTION', CAST(N'2016-01-06T13:44:18.000' AS DateTime), 13)
,(223079, N'HP - HOLD FOR PARTS', CAST(N'2016-01-07T14:18:59.000' AS DateTime), 710)
,(223079, N'RELEASED FROM HOLD FOR PART', CAST(N'2016-01-11T16:37:49.000' AS DateTime), 710)
,(223079, N'RP - UNDER INSPECTION', CAST(N'2016-01-12T08:58:05.000' AS DateTime), 241)
,(223079, N'HP - HOLD FOR PARTS', CAST(N'2016-01-20T09:50:19.000' AS DateTime), 710)
,(223079, N'HP06 - PARTS SHORTAGE, PLANNER CONFIRM ETA AND TRIGGER EDI', CAST(N'2016-01-25T15:32:19.000' AS DateTime), 755)
,(223079, N'HP06 - PARTS SHORTAGE, PLANNER CONFIRM ETA AND TRIGGER EDI', CAST(N'2016-03-04T15:56:52.000' AS DateTime), 755)
,(223079, N'HP06 - PARTS SHORTAGE, PLANNER CONFIRM ETA AND TRIGGER EDI', CAST(N'2016-05-16T14:57:12.000' AS DateTime), 755)
,(223079, N'HP06 - PARTS SHORTAGE, PLANNER CONFIRM ETA AND TRIGGER EDI', CAST(N'2016-06-14T15:35:14.000' AS DateTime), 755)
,(223079, N'HP06 - PARTS SHORTAGE, PLANNER CONFIRM ETA AND TRIGGER EDI', CAST(N'2016-08-15T15:28:32.000' AS DateTime), 755)
GO

Update: How can I use Order BY before Partition BY? If a certain status is duplicated in a 2 consecutive rows, I am trying get the first datetime, If the status haven't changed but the Status_DateTime did.

For example:

Case_No_ID  Status                                                                                               Status_DateTime         First_Time              Employee_ID
----------- ---------------------------------------------------------------------------------------------------- ----------------------- ----------------------- -----------
223079      HP06 - PARTS SHORTAGE, PLANNER CONFIRM ETA AND TRIGGER EDI                                           2016-08-15 15:28:32.000 2016-01-25 15:32:19.000 755
223079      HP06 - PARTS SHORTAGE, PLANNER CONFIRM ETA AND TRIGGER EDI                                           2016-06-14 15:35:14.000 2016-01-25 15:32:19.000 755
223079      HP06 - PARTS SHORTAGE, PLANNER CONFIRM ETA AND TRIGGER EDI                                           2016-05-16 14:57:12.000 2016-01-25 15:32:19.000 755
223079      HP06 - PARTS SHORTAGE, PLANNER CONFIRM ETA AND TRIGGER EDI                                           2016-03-04 15:56:52.000 2016-01-25 15:32:19.000 755
223079      HP06 - PARTS SHORTAGE, PLANNER CONFIRM ETA AND TRIGGER EDI                                           2016-01-25 15:32:19.000 2016-01-25 15:32:19.000 755

Without affecting the ones that are not in a row for example : 'HP - HOLD FOR PARTS' below

Case_No_ID  Status                                                                                               Status_DateTime         First_Time              Employee_ID
----------- ---------------------------------------------------------------------------------------------------- ----------------------- ----------------------- -----------
    223079      HP - HOLD FOR PARTS                                                                                  2016-01-20 09:50:19.000 2016-01-20 09:50:19.000 710
    223079      RP - UNDER INSPECTION                                                                                2016-01-12 08:58:05.000 2016-01-12 08:58:05.000 241
    223079      RELEASED FROM HOLD FOR PART                                                                          2016-01-11 16:37:49.000 2016-01-11 16:37:49.000 710
    223079      HP - HOLD FOR PARTS                                                                                  2016-01-07 14:18:59.000 2016-01-07 14:18:59.000 710
    223079      RP - UNDER INSPECTION                                                                                2016-01-06 13:44:18.000 2016-01-06 13:44:18.000 13

I tried the following:

Select 
    ROW_NUMBER() Over (order by status_Datetime Desc) rownum
    , Case_No_ID
    , Status
    , Status_DateTime 
    , First_value(Status_DateTime) over (partition by [status] Order by [status] Desc) First_Time
    , Employee_ID
from Order_Status
where Case_No_ID = 223079
order by Status_DateTime desc

It worked fine if the status change was in consecutive order (rows 1 to 5) First time it went to the status was '2016-01-25 15:32:19.000' and since the status didn't change, but If you check First_Time column on row 6 it shows Status_DateTime from row 9, but there was status change between them.

rownum               Case_No_ID  Status                                                                                               Status_DateTime         First_Time              Employee_ID
-------------------- ----------- ---------------------------------------------------------------------------------------------------- ----------------------- ----------------------- -----------
1                    223079      HP06 - PARTS SHORTAGE, PLANNER CONFIRM ETA AND TRIGGER EDI                                           2016-08-15 15:28:32.000 2016-08-15 15:28:32.000 755
2                    223079      HP06 - PARTS SHORTAGE, PLANNER CONFIRM ETA AND TRIGGER EDI                                           2016-06-14 15:35:14.000 2016-08-15 15:28:32.000 755
3                    223079      HP06 - PARTS SHORTAGE, PLANNER CONFIRM ETA AND TRIGGER EDI                                           2016-05-16 14:57:12.000 2016-08-15 15:28:32.000 755
4                    223079      HP06 - PARTS SHORTAGE, PLANNER CONFIRM ETA AND TRIGGER EDI                                           2016-03-04 15:56:52.000 2016-08-15 15:28:32.000 755
5                    223079      HP06 - PARTS SHORTAGE, PLANNER CONFIRM ETA AND TRIGGER EDI                                           2016-01-25 15:32:19.000 2016-08-15 15:28:32.000 755
6                    223079      HP - HOLD FOR PARTS                                                                                  2016-01-20 09:50:19.000 2015-12-21 16:44:50.000 710
7                    223079      RP - UNDER INSPECTION                                                                                2016-01-12 08:58:05.000 2016-01-12 08:58:05.000 241
8                    223079      RELEASED FROM HOLD FOR PART                                                                          2016-01-11 16:37:49.000 2016-01-11 16:37:49.000 710
9                    223079      HP - HOLD FOR PARTS                                                                                  2016-01-07 14:18:59.000 2015-12-21 16:44:50.000 710
10                   223079      RP - UNDER INSPECTION                                                                                2016-01-06 13:44:18.000 2016-01-12 08:58:05.000 13
11                   223079      RP - UNDER INSPECTION                                                                                2016-01-05 14:15:46.000 2016-01-12 08:58:05.000 241
12                   223079      RP - UNDER INSPECTION                                                                                2015-12-30 13:41:01.000 2016-01-12 08:58:05.000 168
13                   223079      RP - UNDER INSPECTION                                                                                2015-12-30 12:14:07.000 2016-01-12 08:58:05.000 168
14                   223079      OS - MACHINE RECEIVED                                                                                2015-12-29 09:47:34.000 2015-12-29 09:47:34.000 2
15                   223079      PS- PARTS SHIPPED                                                                                    2015-12-21 17:50:39.000 2015-12-21 17:50:39.000 824
16                   223079      PP - PRINT PL                                                                                        2015-12-21 16:47:03.000 2015-12-21 16:47:03.000 709
17                   223079      DP - ORDER CREATED                                                                                   2015-12-21 16:45:11.000 2015-12-21 16:45:11.000 740
18                   223079      HP - HOLD FOR PARTS                                                                                  2015-12-21 16:44:50.000 2015-12-21 16:44:50.000 740

Thank you for your help

Upvotes: 1

Views: 127

Answers (2)

James Z
James Z

Reputation: 12317

If I understood you correctly, you need something like this. This uses running total over lag to group the consecutive statuses into same group, and then use the first_value partition by that:

select case_no_id, status, status_datetime, first_value(status_datetime) over (partition by GRP order by status_datetime desc), employee_id
from ( 
    select sum(case when Status = prev_status then 0 else 1 end) over (order by Status_datetime desc) as GRP, * 
    from (
        select *, lag(status) over (order by Status_Datetime desc) as prev_status from Order_Status 
    ) X
) Y
order by Status_Datetime desc

Your example in rextester

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269793

One method just uses lag() to look at the previous status and keep only the ones where it has changed:

select os.*
from (select os.*,
             lag(status) over (partition by Case_No_ID order by Status_Datetime) as prev_status
      from Order_Status os
     ) os
where prev_status is null or prev_status <> status;

Upvotes: 1

Related Questions