Link to home
Start Free TrialLog in
Avatar of thayduck
thayduckFlag for United States of America

asked on

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
Avatar of _agx_
_agx_
Flag of United States of America image
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

Avatar of thayduck
thayduck
Flag of United States of America image

ASKER

SQL 2008 R2

Does not matter which record if they have the same date because all I want is that date.
Avatar of _agx_
_agx_
Flag of United States of America image
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

Avatar of thayduck
thayduck
Flag of United States of America image

ASKER

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.
Avatar of _agx_
_agx_
Flag of United States of America image
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

Avatar of thayduck
thayduck
Flag of United States of America image

ASKER

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.
Avatar of _agx_
_agx_
Flag of United States of America image
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

Avatar of _agx_
_agx_
Flag of United States of America image
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

Avatar of thayduck
thayduck
Flag of United States of America image

ASKER

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 '
Avatar of _agx_
_agx_
Flag of United States of America image
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

Avatar of thayduck
thayduck
Flag of United States of America image

ASKER

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.
Avatar of thayduck
thayduck
Flag of United States of America image

ASKER

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  '
Avatar of thayduck
thayduck
Flag of United States of America image

ASKER

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
Avatar of thayduck
thayduck
Flag of United States of America image

ASKER

Those  are the results from the above sub query.
Avatar of _agx_
_agx_
Flag of United States of America image
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?
Avatar of thayduck
thayduck
Flag of United States of America image

ASKER

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.
Avatar of _agx_
_agx_
Flag of United States of America image
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.
Avatar of _agx_
_agx_
Flag of United States of America image
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

Avatar of _agx_
_agx_
Flag of United States of America image
Where L1.GROUP_ID = 1950188 and L1.Dispute_Amt = 440
Note, I removed the Group_ID, Dispute_Amt filter for testing.
Avatar of thayduck
thayduck
Flag of United States of America image

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of _agx_
_agx_
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of thayduck
thayduck
Flag of United States of America image

ASKER

Thanks for your help and sticking with it.

Both of your solutions worked.

;WITH groupData
AS

**PLUS**

The one I awarded the points to.
Avatar of _agx_
_agx_
Flag of United States of America image
You're welcome.  If you do find a better way to do it, do post back. I'd be interested in seeing other options :)