Find The First Date When A Group Of Records In A Table Change

I need to read Table1 and find the date 12/04/2017, which is in the first record in the group of records with a Cust# of A631, Date Code of 28 and Status Code of M.
Also would need to find date 03/01/2017 since that is first record of group  A722      33          M      etc...
 
Table1:

   Cust         DATE                DateCode     Status
    A631       12/01/2017          25                 C
    A631       12/01/2017          25                 D
    A631       12/02/2017          26                 I
    A631       12/03/2017          27                 J
    A631       12/04/2017          28                M
    A631       12/04/2017          28                M  
    A631       12/05/2017          28                M
    A631       12/06/2017          28                M
    A631       03/25/2018          28                M
    A721       02/13/2018          29                K
    A721       03/10/2018          30                J
    A722       03/01/2017          33               M
    A722       03/03/2017          33               M
thayduckProgrammer AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
Which DBMS?

the first record in the group

If multiple records have the same date, which record should be returned and why?  Example:

    A631       12/04/2017          28                M
    A631       12/04/2017          28                M  

Open in new window

0
thayduckProgrammer AnalystAuthor Commented:
SQL 2008 R2

Does not matter which record if they have the same date because all I want is that date.
0
_agx_Commented:
Try something like this

Demo Setup
create table #YourTable
(
Cust varchar(100)
, [Date] date
, DateCode int
, [Status] char(1)
)

insert into #YourTable (Cust, [Date], DateCode, [Status])
values 
( 'A631','12/01/2017',25,'C')
, ('A631','12/01/2017',25,'D')
, ('A631','12/02/2017',26,'I')
, ('A631','12/03/2017',27,'J')
, ('A631','12/04/2017',28,'M')
, ('A631','12/04/2017',28,'M')
, ('A631','12/05/2017',28,'M')
, ('A631','12/06/2017',28,'M')
, ('A631','03/25/2018',28,'M')
, ('A721','02/13/2018',29,'K')
, ('A721','03/10/2018',30,'J')
, ('A722','03/01/2017',33,'M')
, ('A722','03/03/2017',33,'M')
; 

Open in new window


Query
;WITH data
AS
(
	SELECT Cust, [Date], DateCode, [Status]
		, ROW_NUMBER() OVER (PARTITION BY Cust ORDER BY [Date] ASC) AS Row
	FROM  #YourTable
	WHERE [Status] = 'M'
)
SELECT * 
FROM   data
WHERE  row = 1

Open in new window

0
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

thayduckProgrammer AnalystAuthor Commented:
You have to disregard the status code. It could be any letter, I just happened to put the same code M in the records your retrieved.
Sorry about that.
0
_agx_Commented:
EDIT:  It's okay.  I'm just not sure how you're defining 1st record then. If it's not based on a specific StatusCode (M) and/or DateCode (28), why  12/04/2017 instead of say:

A631       12/06/2017          28                M

---------------------------------------------------------------------
If you just want earliest date per "Cust", try this.  

;WITH data
AS
(
	SELECT Cust, [Date], DateCode, [Status]
		, ROW_NUMBER() OVER (PARTITION BY Cust ORDER BY [Date] ASC) AS Row
	FROM  #YourTable
)
SELECT * 
FROM   data
WHERE  row = 1
ORDER BY Cust

Open in new window

0
thayduckProgrammer AnalystAuthor Commented:
I guess I am not explaining correctly.

I need to find :
  A631       12/04/2017          28                M

Now, the only way (I can see) to find it is to find the last record with that customer with the latest date,
    A631       03/25/2018          28                M

Then, using the Customer#, Date Code and Status from this record, go back into the table and find the first record with that
Customer#, Date Code and Status which would be:

 A631       12/04/2017          28                M

I will then get the date from this record and use it in the report.
0
_agx_Commented:
Ohh... so whatever the latest record is for each Cust, find the earliest date with the same DateCode and Status?  Try something like this:

;WITH latest
AS
(
	-- Get latest record per CUST
	SELECT Cust, [Date], DateCode, [Status]
		, ROW_NUMBER() OVER (PARTITION BY Cust ORDER BY [Date] DESC) AS Row
	FROM  #YourTable
)
, earliest AS
(
	-- Get earliest record with SAME Cust, DateCode and Status
	SELECT e.*
			, ROW_NUMBER() OVER (PARTITION BY e.Cust ORDER BY e.[Date] ASC) AS DateRow
	FROM   latest e INNER JOIN latest l 
			ON e.Cust = l.Cust
			AND e.DateCode = l.DateCode
			AND e.[Status] = l.[Status]
	WHERE  l.Row = 1
	AND    e.Row <> l.Row
)
SELECT * 
FROM   earliest
WHERE  DateRow = 1
ORDER BY Cust

Open in new window


Results from sample data:

Cust	Date	DateCode	Status	Row	DateRow
A631	2017-12-04	28	M	4	1
A722	2017-03-01	33	M	2	1

Open in new window

0
_agx_Commented:
You could also do something like this.


;WITH data
AS
(
	-- Get latest record per CUST
	SELECT Cust
                      , [Date]
                      , DateCode
                      , [Status]
                      , (SELECT MIN([Date]) FROM #YourTable e 
                         WHERE e.Cust = l.Cust
                         AND e.DateCode = l.DateCode
                         AND e.[Status] = l.[Status]
                         AND e.[Date] < l.[Date]
                      ) AS EarliestDate
                      , ROW_NUMBER() OVER (PARTITION BY Cust ORDER BY [Date] DESC) AS Row
	FROM  #YourTable l
)
SELECT * 
FROM   data
WHERE  Row = 1

Open in new window


Sample Data Results

Cust	Date	DateCode	Status	EarliestDate	Row
A631	2018-03-25	28	M	2017-12-04	1
A721	2018-03-10	30	J	NULL	1
A722	2017-03-03	33	M	2017-03-01	1

Open in new window

0
thayduckProgrammer AnalystAuthor Commented:
Below code is working like I asked, Thanks.
Now, is there some way to have this code only return the record where MyRow = 1 ?
I basically want to call this code within another stored Procedure, returning only MyRow = 1 record.

This code is altered a little since it will be called within a open query accessing Oracle database..
Also, Group_ID replaced Customer number in the processing.

'SELECT L1.GROUP_ID,L1.MODIFIED_ON,L1.DISPUTE_AMT,L1.SALESID,L1.CONTACT_ID,L1.RESOLVER,L1.PCODE,L1.STATUS,L1.SALESAREA,L1.CLOSEDATE,L1.PAYDATE,
                       (SELECT MIN(L2.MODIFIED_ON) FROM GPCOMP1.GPPROBLOG L2
                         WHERE
                                      L2.GROUP_ID = 1950188
                                     
                         AND   L2.SALESID = L1.SALESID
                         AND   L2.CONTACT_ID = L1.CONTACT_ID
                                     AND   L2.RESOLVER = L1.RESOLVER
                         AND   L2.PCODE = L1.PCODE
                         AND   L2.STATUS = L1.STATUS
                         AND   L2.SALESAREA = L1.SALESAREA
                                     AND   L2.CLOSEDATE IS NULL
                                     AND   L2.PAYDATE IS NULL
                                     AND   L2.MODIFIED_ON < L1.MODIFIED_ON                   
                                                           
                      ) AS EarliestDate,
    ROW_NUMBER() OVER (PARTITION BY L1.GROUP_ID ORDER BY L1.MODIFIED_ON DESC) AS MyRow
                       
      FROM GPCOMP1.GPPROBLOG L1
    Where L1.GROUP_ID = 1950188 and L1.CLOSEDATE IS NULL and L1.PAYDATE IS NULL order by myrow '
0
_agx_Commented:
I'm not very familiar with Oracle. Does the Oracle/open query support CTE's, like in the example? If you wrap the query in a CTE, then you can filter the results on the generated "MyRow" values:

;WITH data
AS
(
	/* your query here */
)
SELECT * 
FROM   data
WHERE  MyRow = 1

Open in new window

0
thayduckProgrammer AnalystAuthor Commented:
I need to run the query as a sub query within another stored procedure, returning only 1 record, which would be the record with a 1.
0
thayduckProgrammer AnalystAuthor Commented:
This worked and gave me record 1.


 'SELECT * From (SELECT L1.GROUP_ID,L1.MODIFIED_ON,L1.DISPUTE_AMT,L1.SALESID,L1.CONTACT_ID,L1.RESOLVER,L1.PCODE,L1.STATUS,L1.SALESAREA,L1.CLOSEDATE,L1.PAYDATE,
                       (SELECT MIN(L2.MODIFIED_ON) FROM GPCOMP1.GPPROBLOG L2
                         WHERE
                                      L2.GROUP_ID = 1950188
                                     
                         AND   L2.SALESID = L1.SALESID
                         AND   L2.CONTACT_ID = L1.CONTACT_ID
                                     AND   L2.RESOLVER = L1.RESOLVER
                         AND   L2.PCODE = L1.PCODE
                         AND   L2.STATUS = L1.STATUS
                         AND   L2.SALESAREA = L1.SALESAREA
                                     AND   L2.CLOSEDATE IS NULL
                                     AND   L2.PAYDATE IS NULL
                                     AND   L2.MODIFIED_ON < L1.MODIFIED_ON                   
                                                           
                      ) AS EarliestDate,
    ROW_NUMBER() OVER (PARTITION BY L1.GROUP_ID ORDER BY L1.MODIFIED_ON DESC) AS MyRow
                       
      FROM GPCOMP1.GPPROBLOG L1
    Where L1.GROUP_ID = 1950188 and L1.CLOSEDATE IS NULL and L1.PAYDATE IS NULL order by myrow) where MYROW = 1  '
0
thayduckProgrammer AnalystAuthor Commented:
Still having issues. does not get correct record. The Group_ID is another name for Cust#. Also had to add the Dispute_Amt.
But when I run using only Group_ID 1950188, I am looking for date Earliest Date of 2017/12/21 which I am not getting.
Below is actual code I am executing.

 (SELECT L1.CREATED_ON, L1.Dispute_Amt,L1.SALESID,L1.STATUS,      
                       (SELECT MIN(L2.CREATED_ON) FROM GPCOMP1.GPPROBLOG L2
                         WHERE L2.GROUP_ID = 1950188                                                       
                         AND   L2.SALESID = L1.SALESID                                    
                                     AND   L2.STATUS = L1.STATUS
                                     AND   L2.Dispute_Amt = L1.Dispute_Amt                                    
                                     AND   L2.CREATED_ON < L1.CREATED_ON ) AS EarliestDate,

                        ROW_NUMBER() OVER (PARTITION BY L1.GROUP_ID ORDER BY L1.CREATED_ON) AS MyRow



                                  FROM GPCOMP1.GPPROBLOG L1 Where L1.GROUP_ID = 1950188 and L1.Dispute_Amt = 440)   '

















CREATED_ON         DISPUTE_AMT      SALESID      STATUS      EARLIESTDATE                         MYROW
2016-07-07 11:14:14.0000000      440      FRCOLLMB      D        NULL                                            1
2017-02-10 15:13:00.0000000      440      CSDIB              D        NULL                                              2
2017-02-10 15:15:23.0000000      440      CSDIB              D       2017-02-10 15:13:00.0000000  3
2017-04-26 13:06:14.0000000      440      MARKAM      D        NULL                                            4
2017-04-26 13:11:58.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      5
2017-06-08 11:17:37.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      6
2017-06-08 16:06:50.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      7
2017-06-09 11:55:50.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      8
2017-06-12 13:47:27.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      9
2017-06-21 10:41:34.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      10
2017-07-10 08:17:26.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      11
2017-07-10 14:36:33.0000000      440      MARKSD      D      NULL      12
2017-07-10 14:46:17.0000000      440      MARKSD      D      2017-07-10 14:36:33.0000000      13
2017-07-11 14:02:34.0000000      440      MARKSD      D      2017-07-10 14:36:33.0000000      14
2017-07-12 15:18:19.0000000      440      MARKSD      D      2017-07-10 14:36:33.0000000      15
2017-07-12 15:19:48.0000000      440      MARKSD      D      2017-07-10 14:36:33.0000000      16
2017-07-13 14:03:30.0000000      440      MARKSD      D      2017-07-10 14:36:33.0000000      17
2017-07-17 15:50:02.0000000      440      MARKSD      D      2017-07-10 14:36:33.0000000      18
2017-08-01 08:38:23.0000000      440      MARKSD      C      NULL      19
2017-08-14 08:34:38.0000000      440      MARKSD      C      2017-08-01 08:38:23.0000000      20
2017-08-14 11:48:27.0000000      440      MARKAM      C      NULL      21
2017-12-21 09:15:54.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      22
2017-12-21 14:06:42.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      23
2017-12-21 14:07:02.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      24
2017-12-21 14:07:47.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      25
2017-12-21 15:43:12.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      26
2017-12-21 15:43:12.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      27
2018-01-10 14:22:44.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      28
2018-01-12 07:45:29.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      29
2018-01-12 07:46:14.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      30
2018-02-01 14:53:32.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      31
2018-02-01 15:34:20.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      32
2018-02-07 15:43:23.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      33
2018-02-15 16:08:58.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      34
2018-02-16 11:05:07.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      35
2018-02-19 08:25:04.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      36
2018-02-19 08:27:00.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      37
2018-03-02 14:55:09.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      38
2018-03-16 16:11:06.0000000      440      MARKAM      D      2017-04-26 13:06:14.0000000      39
0
thayduckProgrammer AnalystAuthor Commented:
Those  are the results from the above sub query.
0
_agx_Commented:
Sorry, I misread the post.

But when I run using only Group_ID 1950188, I am looking for date Earliest Date of 2017/12/21 which I am not getting.

"2017/12/21" should be the earliest date for which record? The current query only looks at records having the same (SalesID, Status, Dispute_Amt, etc), so the result for

        440      MARKAM      C      

may be different than  the result for

      440      MARKAM      D      

Please update the sample with the result(s) you're expecting and why?
0
thayduckProgrammer AnalystAuthor Commented:
0
thayduckProgrammer AnalystAuthor Commented:
Want to capture the date in red when I execute the Sub Query. That is the last date that the Sales ID or Status has changed for the  Group ID and Disputes Amount   group.
0
_agx_Commented:
Ohh....That's a lot more complicated.  It can't be done with a subquery, least not easily, because it's more than just finding the earliest change. You'd need to know the last two changes to get the dates you want.  Have to think about that...

Current Value
Examine records with the same values
Examine prior record with different values.
0
_agx_Commented:
This produces the correct results using the sample data. Though like I said, it's more complicated so performance may not be that great.

;WITH groupData
AS
(
    -- Sort by most recent per group + dispute 
    SELECT Created_On, Group_ID, Dispute_Amt, SalesID, Status
        ,  ROW_NUMBER() OVER (PARTITION BY Group_ID, Dispute_Amt ORDER BY Dispute_Amt, CREATED_ON DESC) AS GroupRow
    FROM   GPCOMP1.GPPROBLOG
)
SELECT  mx.Created_On, mx.Group_Id, mx.Dispute_Amt, mx.SalesID, mx.Status
        -- find date AFTER latest change
        , (SELECT Created_On
           FROM   groupData chg
           WHERE  chg.Group_Id = mx.Group_Id
           AND    chg.Dispute_Amt = mx.Dispute_Amt
           AND    chg.GroupRow = change.ChangeRow - 1
        ) AS EarliestDate
FROM    groupData mx CROSS APPLY
        (
            -- find latest change record by group + dispute 
            SELECT MIN(GroupRow)
            FROM   groupData chg
            WHERE  chg.Group_Id = mx.Group_Id
            AND    chg.Dispute_Amt = mx.Dispute_Amt
            AND (  chg.SalesID <> mx.SalesID OR chg.Status <> mx.Status )    
        ) change(ChangeRow)
WHERE mx.GroupRow = 1

Open in new window


Result:
Created_On	Group_Id	Dispute_Amt	SalesID	Status	EarliestDate
2017-06-29 13:01:54.000	1908093	9000	MARKSD	D	2017-06-29 13:01:54.000
2017-08-08 16:37:41.000	1908093	12500	MARKSD	D	2017-07-28 16:38:48.000
2018-03-16 16:11:06.000	1950188	440	MARKAM	D	2017-12-21 14:06:42.000

Open in new window

0
_agx_Commented:
Where L1.GROUP_ID = 1950188 and L1.Dispute_Amt = 440
Note, I removed the Group_ID, Dispute_Amt filter for testing.
0
thayduckProgrammer AnalystAuthor Commented:
So far so good except for 2 issues.

If there was no Sales ID or Status change within a Group ID/Dispute Amount grouping like below, the date coming back from your calculations is NULL.
It should be the first Created On date in that group which is 2010-12-30.

Created_On       Group_ID            Dispute_Amt             SalesID           Status
2010-12-30          1102498                -61.93                    FRCOLLMB          C
2010-12-31          1102498                -61.93                    FRCOLLMB          C
2016-02-03          1102498                -61.93                    FRCOLLMB          C
2016-02-27          1102498                -61.93                    FRCOLLMB          C
2016-02-27          1102498                -61.93                    FRCOLLMB          C
2016-12-03          1102498                -61.93                    FRCOLLMB          C

If there is only 1 record within a Group ID/Dispute Amount grouping like below, the date coming back from your calculations is NULL.
It should be the  Created On date in that group which is 2010-08-27 .          

Created_On       Group_ID            Dispute_Amt           SalesID              Status
2010-08-27          918079                 -73.85                    FRCOLLMB           C
0
_agx_Commented:
This gets the job done, but ... I'm sure there's a better way to do it.

Result:
Created_On	Group_Id	Dispute_Amt	SalesID	Status	(No column name)
2016-12-03 00:00:00.000	1102498	-61.93	FRCOLLMB	C	2010-12-30 00:00:00.000
2017-06-29 13:01:54.000	1908093	9000	MARKSD	D	2017-06-22 17:04:39.000
2017-08-08 16:37:41.000	1908093	12500	MARKSD	D	2017-07-28 15:48:42.000
2018-03-16 16:11:06.000	1950188	440	MARKAM	D	2017-12-21 09:15:54.000

Open in new window


;WITH detail
AS
(
    -- Sort by most recent per group + dispute 
    SELECT Created_On, Group_ID, Dispute_Amt, SalesID, Status
        ,  ROW_NUMBER() OVER (PARTITION BY Group_ID, Dispute_Amt ORDER BY Dispute_Amt, CREATED_ON DESC) AS GroupRow
    FROM   GPCOMP1.GPPROBLOG
)
, lastTwoChanges AS
(
    -- Find records for last two changes
	SELECT  chg.Group_Id, chg.Dispute_Amt
			, MAX( CASE WHEN chg.SalesID = mx.SalesID AND chg.Status = mx.Status THEN chg.GroupRow ELSE NULL END ) AS EarliestRow
			, MIN( CASE WHEN chg.SalesID <> mx.SalesID OR chg.Status <> mx.Status THEN chg.GroupRow ELSE NULL END ) AS ChangeRow
	FROM detail mx LEFT JOIN detail chg
				ON chg.Group_Id = mx.Group_Id
				AND  chg.Dispute_Amt = mx.Dispute_Amt
				AND  chg.GroupRow > mx.GroupRow  
    WHERE  mx.GroupRow = 1        
    GROUP BY chg.Group_Id, chg.Dispute_Amt
)
SELECT  mx.Created_On, mx.Group_Id, mx.Dispute_Amt, mx.SalesID, mx.Status
        , ( SELECT TOP 1 Created_On
            FROM   detail d INNER JOIN lastTwoChanges e 
					ON e.Group_Id = d.Group_Id
					AND e.Dispute_Amt = d.Dispute_Amt
					AND d.GroupRow IN (e.EarliestRow, e.ChangeRow)
            WHERE  e.Group_Id = mx.Group_Id
            AND    e.Dispute_Amt = mx.Dispute_Amt
            ORDER BY Created_ON DESC
      )      
FROM  detail mx
WHERE mx.GroupRow = 1
ORDER BY mx.Group_Id, mx.Dispute_Amt, mx.Created_On

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thayduckProgrammer AnalystAuthor Commented:
Thanks for your help and sticking with it.

Both of your solutions worked.

;WITH groupData
AS

**PLUS**

The one I awarded the points to.
0
_agx_Commented:
You're welcome.  If you do find a better way to do it, do post back. I'd be interested in seeing other options :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.