Capture dates as first date and subsequent dates as secondary dates

I have asked before how to store a date as the previous date and then subsequent dates as the new date.  The query works as I expected it.  However, I now notice that the dates I am storing are renewal dates after the initial order date.  I need to start with the first order date and then the renewals as the new dates/previous dates after that.

Here is the query I use to capture the order data and renewal data associated with that orderid.  The OrderDateTime is the original date, The renewal date "DateTime" are subsequent to the order date.  

SELECT     TOP (100) PERCENT dbo.OeOrders.OrderID, dbo.OeOrders.VisitID, dbo.OeOrders.Category, dbo.OeOrders.ProviderID, dbo.OeOrders.OrderDateTime, 
                      dbo.OeOrders.OrderNumber, dbo.OeOrders.OrderedProcedure, dbo.OeOrders.OrderedProcedureMnemonic, dbo.OeOrderEdits.EditSeqID, 
                      dbo.OeOrderEdits.Ack, dbo.OeOrderEdits.DateTime, dbo.OeOrderEdits.Event, dbo.OeOrders.Status, dbo.BarVisits.AccountNumber, 
                      dbo.BarVisits.RoomID, dbo.BarVisits.RoomLocation
FROM         dbo.BarVisits RIGHT OUTER JOIN
                      dbo.OeOrders ON dbo.BarVisits.VisitID = dbo.OeOrders.VisitID LEFT OUTER JOIN
                      dbo.OeOrderEdits ON dbo.OeOrders.OrderID = dbo.OeOrderEdits.OrderID
WHERE     (dbo.OeOrders.OrderedProcedure = '1250100') AND (dbo.OeOrders.OrderDateTime > '2015-06-30') AND 
                      (dbo.OeOrderEdits.Event LIKE '%order renewed by%') AND (dbo.OeOrders.VisitID = 'A10000444689')
ORDER BY dbo.OeOrders.VisitID, dbo.OeOrders.OrderDateTime, dbo.OeOrderEdits.DateTime

Open in new window



I have attached an Excel workbook "Renewals" with two tabs: Renewals and Renewals by Rank.  The OrderID 2285056.001 has an OrderDateTime of 7/3 at 1:38.  That would be the first Previous Date.  The first renewal date is 7/3 at 7:30.  This would be the New Date for the first previous date.  Then it will be the next Previous Date for the next Renewal.

The second query takes the initial query and ranks the data.

SELECT     T .VisitID, T .DateTime, T2.DateTime AS NewDt, T .AccountNumber, T .OrderDateTime, T .ProviderID, rank() OVER (partition BY T .VisitID, 
                      T .DateTime
ORDER BY T2.DateTime) AS TRank, T .OrderID, T .RoomID
FROM         dbo.vw_Restraint_Renewals AS T LEFT OUTER JOIN
                      dbo.vw_Restraint_Renewals AS T2 ON T .VisitID = T2.VisitID AND T .DateTime < T2.DateTime
where T.VisitID='A10000444689'

Open in new window


The second tab has the results of this query.

What is the syntax to collect the Previous and and New Dates showing the original order date as the first previous date?

Thanks

Glen
Renewals.xlsx
GPSPOWAsked:
Who is Participating?
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.

PortletPaulfreelancerCommented:
samplesample dataexpectedexpected resultIf I have interpreted this correctly, you want the earliest [DateTime] value for each orderid, then beside that any other later [dateTime] rows as the [new dt]

The following query is not tested at all, but I think it will do what you are asking for
;WITH cte
AS (SELECT  *
          , ROW_NUMBER() OVER (PARTITION BY orderid
            ORDER BY [DateTime] ASC) AS rn
      FROM Renewals
)
SELECT
      cte.orderid
    , cte.[DateTime]
    , xtra.[DateTime] AS new_dt
    , cte.VisitID
    , cte.AccountNumber
    , cte.OrderDateTime
    , cte.ProviderID
    , ROW_NUMBER() OVER (PARTITION BY xtra.orderid
                        ORDER BY xtra.[DateTime] ASC) AS TRank
    , cte.OrderID
    , cte.RoomID
FROM cte
LEFT OUTER JOIN cte AS xtra
      ON cte.orderid = xtra.orderid
      AND xtra.rn > 1 /* ([DateTime] rows after the earliest) */
WHERE cte.rn = 1      /* (the earliest [DateTime] rows) */
;

Open in new window

{+edit}
Just as an observation but [DateTime] isn't a great choice of column name as it is also a keyword used in TSQL. Very confusing!
0
PortletPaulfreelancerCommented:
sqlfiddle started to work & test result an be seen below
    CREATE TABLE Renewals
        (  [VisitID] varchar(20)
         , [OrderID] int
         , [OrderDateTime] datetime, [DateTime] datetime
         , [OrderNumber] varchar(20), [OrderedProcedure] int, [OrderedProcedureMnemonic] varchar(20)
         , [EditSeqID] int, [Ack] varchar(1), [Category] varchar(3)
         , [ProviderID] varchar(5), [Event] varchar(22), [Status] varchar(4)
         , [AccountNumber] varchar(12), [RoomID] int, [RoomLocation] varchar(8))
    ;
        
    INSERT INTO Renewals
        ([VisitID], [OrderID], [OrderDateTime], [DateTime], [OrderNumber], [OrderedProcedure], [OrderedProcedureMnemonic], [EditSeqID], [Ack], [Category], [ProviderID], [Event], [Status], [AccountNumber], [RoomID], [RoomLocation])
    VALUES
        ('A10000444689', 2284263.002, '2015-07-02 00:45:00', '2015-07-02 18:32:00', '0702-0101', 1250100, 'RESTRAINT', 6, 'Y', 'NUR', 'HADSO', 'order renewed by HADSO', 'CANC', 'A00000281936', 115, 'MED/SURG'),
        ('A10000444689', 2285056.001, '2015-07-03 01:38:00', '2015-07-03 07:30:00', '0703-0401', 1250100, 'RESTRAINT', 6, 'Y', 'NUR', 'HADSO', 'order renewed by HADSO', 'CANC', 'A00000281936', 115, 'MED/SURG'),
        ('A10000444689', 2285056.001, '2015-07-03 01:38:00', '2015-07-04 01:37:00', '0703-0401', 1250100, 'RESTRAINT', 7, 'Y', 'NUR', 'HADSO', 'order renewed by HADSO', 'CANC', 'A00000281936', 115, 'MED/SURG'),
        ('A10000444689', 2285056.001, '2015-07-03 01:38:00', '2015-07-04 10:46:00', '0703-0401', 1250100, 'RESTRAINT', 8, 'Y', 'NUR', 'HADSO', 'order renewed by HADSO', 'CANC', 'A00000281936', 115, 'MED/SURG'),
        ('A10000444689', 2285056.001, '2015-07-03 01:38:00', '2015-07-06 00:29:00', '0703-0401', 1250100, 'RESTRAINT', 11, 'Y', 'NUR', 'HADSO', 'order renewed by HADSO', 'CANC', 'A00000281936', 115, 'MED/SURG'),
        ('A10000444689', 2285056.001, '2015-07-03 01:38:00', '2015-07-06 05:45:00', '0703-0401', 1250100, 'RESTRAINT', 12, 'Y', 'NUR', 'HADSO', 'order renewed by HADSO', 'CANC', 'A00000281936', 115, 'MED/SURG')
    ;
    
**Query 1**:

    ;WITH cte
    AS (SELECT  *
              , ROW_NUMBER() OVER (PARTITION BY orderid
                ORDER BY [DateTime] ASC) AS rn
          FROM Renewals
    )
    SELECT
          cte.orderid
        , cte.[DateTime]
        , xtra.[DateTime] AS new_dt
        , cte.VisitID
        , cte.AccountNumber
        , cte.OrderDateTime
        , cte.ProviderID
        , ROW_NUMBER() OVER (PARTITION BY xtra.orderid
                            ORDER BY xtra.[DateTime] ASC) AS TRank
        , cte.OrderID
        , cte.RoomID
    FROM cte
    LEFT OUTER JOIN cte AS xtra
          ON cte.orderid = xtra.orderid
          AND xtra.rn > 1 /* ([DateTime] rows after the earliest) */
    WHERE cte.rn = 1      /* (the earliest [DateTime] rows) */
    

**[Results][2]**:
    | orderid |               DateTime |                 new_dt |      VisitID | AccountNumber |          OrderDateTime | ProviderID | TRank | OrderID | RoomID |
    |---------|------------------------|------------------------|--------------|---------------|------------------------|------------|-------|---------|--------|
    | 2284263 | July, 02 2015 18:32:00 |                 (null) | A10000444689 |  A00000281936 | July, 02 2015 00:45:00 |      HADSO |     1 | 2284263 |    115 |
    | 2285056 | July, 03 2015 07:30:00 | July, 04 2015 01:37:00 | A10000444689 |  A00000281936 | July, 03 2015 01:38:00 |      HADSO |     1 | 2285056 |    115 |
    | 2285056 | July, 03 2015 07:30:00 | July, 04 2015 10:46:00 | A10000444689 |  A00000281936 | July, 03 2015 01:38:00 |      HADSO |     2 | 2285056 |    115 |
    | 2285056 | July, 03 2015 07:30:00 | July, 06 2015 00:29:00 | A10000444689 |  A00000281936 | July, 03 2015 01:38:00 |      HADSO |     3 | 2285056 |    115 |
    | 2285056 | July, 03 2015 07:30:00 | July, 06 2015 05:45:00 | A10000444689 |  A00000281936 | July, 03 2015 01:38:00 |      HADSO |     4 | 2285056 |    115 |

  [1]: http://sqlfiddle.com/#!3/db639/2

Open in new window

0
GPSPOWAuthor Commented:
Paul,

For OrderID 2285056 the first DateTime should be the OrderDate on 7/3 at 1:38.  After that the DateTime will be the subsequent DateTime's.  I am trying to capture the interval of time from when the order was placed until the first subsequent event.  The intervals for each subsequent event.

For example, the first subsequent event is approximately 6 hours after the OrderDate, 7/3 1:38 until 7/3 7:30.
The second event is approximately 18 hours after the first event ( 7/3 7:30 until 7/4 1:37).  And so on until the NewDate is a null.

So my problem is capturing the OrderDateTime as the first "DateTime".

I know that DateTime is not a very good name for a field.  However, I did not design the database.  That is what it is called.

Thanks

Glen
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

GPSPOWAuthor Commented:
Declare @VID varchar (50);
Declare @NDT datetime;
Declare @AN varchar (50);
Declare @PDT datetime;
Declare @OID varchar(50);
Declare @cnt int;
Declare @rcnt int =1;
if OBJECT_ID(N'tempdb..#OID') is not null
		Begin
			drop table #OID
		End
create table #OID (
VisitID varchar (50),
DateTime datetime,
AccountNumber varchar (50),
OrderDateTime datetime,
OrderID varchar (50));
insert into #OID
SELECT     VisitID, DateTime,  AccountNumber, OrderDateTime,  OrderID
FROM         dbo.vw_Restraint_Renewals_by_Rank
WHERE     (TRank = 1) 
select @cnt  = COUNT (*) 
from #OID
select @cnt as CT, @rcnt as RT
set @OID=''

Open in new window


I have started to create a loop to capture the information I need.  There are 46 records in the query with about 13 different OrderID's.

I think my solution would be that everytime the loop encounters a @OID <> current record #OID.OrderID store the OrderDateTime as the @PDT and the DateTime as the @NDT.  When the next records @OID= current record's #OID.OrderID, then set the @PDT to the @NDT before changing the @NDT value.

I have not been able to figure out how to loop through the #OID temp table to create my output.

Please advise.

Thanks

Glen
0
PortletPaulfreelancerCommented:
There is no need to use a loop.

Did you attempt to use my suggestion? Did it not work?
Could you tell me what it is doing that is incorrect?

I got this result from the small sample of data, does it not match your expected result?

    | orderid |               DateTime |                 new_dt |      VisitID | AccountNumber |          OrderDateTime | ProviderID | TRank | OrderID | RoomID |
    |---------|------------------------|------------------------|--------------|---------------|------------------------|------------|-------|---------|--------|
    | 2284263 | July, 02 2015 18:32:00 |                 (null) | A10000444689 |  A00000281936 | July, 02 2015 00:45:00 |      HADSO |     1 | 2284263 |    115 |
    | 2285056 | July, 03 2015 07:30:00 | July, 04 2015 01:37:00 | A10000444689 |  A00000281936 | July, 03 2015 01:38:00 |      HADSO |     1 | 2285056 |    115 |
    | 2285056 | July, 03 2015 07:30:00 | July, 04 2015 10:46:00 | A10000444689 |  A00000281936 | July, 03 2015 01:38:00 |      HADSO |     2 | 2285056 |    115 |
    | 2285056 | July, 03 2015 07:30:00 | July, 06 2015 00:29:00 | A10000444689 |  A00000281936 | July, 03 2015 01:38:00 |      HADSO |     3 | 2285056 |    115 |
    | 2285056 | July, 03 2015 07:30:00 | July, 06 2015 05:45:00 | A10000444689 |  A00000281936 | July, 03 2015 01:38:00 |      HADSO |     4 | 2285056 |    115 |

Open in new window

If possible please provide more examples, as I have a little trouble understanding your descriptions

--
by the way
I am Australian. If I see a date "7/3" my mind immediately translates that as the 7th of March. I must stop and re-translate it to July 3 (according to Wikipedia less than 10% of the world's population use m/d/yyyy).

It is good practice to get into the habit of using yyyy-mm-dd when communicating dates here.

The same is true of times, 1:38 could be 01:38 or 13:38
0
PortletPaulfreelancerCommented:
one other question.

Are you actually using SQL Server 2008? (if not, which version do you use?)

If you have SQL Server 2012 or later that would be useful for this problem
see: LEAD() and LAG()
0
GPSPOWAuthor Commented:
SQL 2008
0
PortletPaulfreelancerCommented:
OK, thanks. LEAD/LAG are not available. Pity, but that's fine
Could you now answer my other questions?
Did you attempt to use my suggestion? Did it not work?
Could you tell me what it is doing that is incorrect?

I got this result from the small sample of data, does it not match your expected result?
0
GPSPOWAuthor Commented:
Paul,

How can we make the OrderDateTime show as the first DateTime (7/3/15 @ 1:38) and the first DateTime show as the first NewDate (7/3/15 7:30)?  After the first record, the previous NewDate becomes the DateTime and the new DateTime becomes newDate.
The second outputted record would be DateTime = 7/3/15 7:30 and the NewDate would be 7/4/15 1:37.


 I think it would be easier to keep it straight if we change the resulting field name DateTime to PrevDate.


Thanks

Glen
0
PortletPaulfreelancerCommented:
Would you answer my questions please:

Q: Did you try my suggested query?
Q: Were the results of that query correct or incorrect?

If incorrect, please supply both the incorrect result and the expected result.

(if possible please keep any dates in yyyy-mm-dd format as this permits me to get that data into tables if I need it)

Let me explain what my suggested query does:

First I place the data plus a calculated row number [rn] into a "common table expressio" (CTE) this is a bit like a temporary table. The row number calculation restarts for each unique xtra.orderid  and the numbering starts at 1 for the oldest xtra.[DateTime] value

          , ROW_NUMBER() OVER (PARTITION BY orderid
            ORDER BY [DateTime] ASC) AS rn
 
So, we can now identify rows that have the oldest xtra.[DateTime] value, when that row number is 1

AND  we have that we can determine the "next" rows(s) when that row number is > 1

So. It is going to be very helpful to know if you have tried my suggestion and if it worked or not. Once I know this I wil be able to offer further assistance if needed.

By the way, you can also run my query at sqlfiddle, here: http://sqlfiddle.com/#!3/db639/2
0
GPSPOWAuthor Commented:
Here is the original record data and the expected result.  I reformatted the dates to d/m/y h:m
Renewals.xlsx
0
PortletPaulfreelancerCommented:
this is a new expected result
Expected Result:							
Record#	OrderID		VisitID		ProviderID	PrevDate	NewDate		AccountNumber	Comment
1	2285056.001	A10000444689	HADSO		3/7/2015 1:38	3/7/2015 7:30	A00000281936	PrevDate=OrderDateTime
2	2285056.001	A10000444689	HADSO		3/7/2015 7:30	4/7/2015 1:37	A00000281936	PrevDate=Prior Record New Date
3	2285056.001	A10000444689	HADSO		4/7/2015 1:37	4/7/2015 10:46	A00000281936	PrevDate=Prior Record New Date
4	2285056.001	A10000444689	HADSO		4/7/2015 10:46	6/7/2015 0:29	A00000281936	PrevDate=Prior Record New Date
5	2285056.001	A10000444689	HADSO		6/7/2015 0:29	6/7/2015 5:45	A00000281936	PrevDate=Prior Record New Date

Open in new window

Here is a result by query:
| Record |     orderid |      visitid | providerid |         PrevDate |          NewDate |
|--------|-------------|--------------|------------|------------------|------------------|
|      1 | 2285056.001 | A10000444689 |      HADSO | 2015-03-07 01:38 | 2015-03-07 07:30 |
|      2 | 2285056.001 | A10000444689 |      HADSO | 2015-03-07 07:30 | 2015-04-07 01:37 |
|      3 | 2285056.001 | A10000444689 |      HADSO | 2015-04-07 01:37 | 2015-04-07 10:46 |
|      4 | 2285056.001 | A10000444689 |      HADSO | 2015-04-07 10:46 | 2015-06-07 00:29 |
|      5 | 2285056.001 | A10000444689 |      HADSO | 2015-06-07 00:29 | 2015-06-07 05:45 |

Open in new window

This is the query:
;WITH cte
AS (SELECT  *
          , ROW_NUMBER() OVER (PARTITION BY orderid
            ORDER BY [DateTime] ASC) AS rn
      FROM Renewals
)
SELECT
      cte.Record
    , cte.orderid
    , cte.visitid
    , cte.providerid
    , convert(varchar(16),
               case when cte.rn = 1 then cte.OrderDateTime else nxt.[DateTime] end
             ,121) as PrevDate
    , convert(varchar(16), cte.[DateTime] ,121) as NewDate
  FROM cte
LEFT OUTER JOIN cte AS nxt
      ON cte.orderid = nxt.orderid
      AND cte.rn = nxt.rn + 1
order by PrevDate  ;

Open in new window

Data:
CREATE TABLE Renewals

    ([Record] int, [OrderID] varchar(50), [VisitID] varchar(12), [ProviderID] varchar(5), [OrderDateTime] datetime, [DateTime] datetime, [AccountNumber] varchar(12))
;
    
INSERT INTO Renewals
    ([Record], [OrderID], [VisitID], [ProviderID], [OrderDateTime], [DateTime], [AccountNumber])
VALUES
    (1, '2285056.001', 'A10000444689', 'HADSO', '2015-03-07 01:38:00', '2015-03-07 07:30:00', 'A00000281936'),
    (2, '2285056.001', 'A10000444689', 'HADSO', '2015-03-07 01:38:00', '2015-04-07 01:37:00', 'A00000281936'),
    (3, '2285056.001', 'A10000444689', 'HADSO', '2015-03-07 01:38:00', '2015-04-07 10:46:00', 'A00000281936'),
    (4, '2285056.001', 'A10000444689', 'HADSO', '2015-03-07 01:38:00', '2015-06-07 00:29:00', 'A00000281936'),
    (5, '2285056.001', 'A10000444689', 'HADSO', '2015-03-07 01:38:00', '2015-06-07 05:45:00', 'A00000281936')

;

Open in new window

also see: http://sqlfiddle.com/#!3/0558a0/1
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
GPSPOWAuthor Commented:
Paul,

I was able to adapt your solution to the full data table and it worked exactly like I hoped.

I will need to read up on the "cte" option.

Thank you for all your help.

Glen
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.