CODEWITHSUNDEEP
sqloracle-database
django-unchained
django-unchained

Reputation: 844

Get Value for Last Month

I've a table:

| Month      | End Date             |
| 06/01/2019 | 07/02/2019 12:35:00  |
| 07/01/2019 | 08/03/2019 2.33.20   |

I want to show Start Date of next month as the very next second of End Date of Last Month.

Result:

| Month      | End Date             | Start Date          |
| 06/01/2019 | 07/02/2019 12:35:00  | NULL                |
| 07/01/2019 | 08/03/2019 2.33.20   | 07/02/2019 12:35:01 |
| 08/01/2019 | 09/03/2019 2.33.07   | 08/02/2019 2:33:21  |

....so on

Can anyone help?

Upvotes: 0

Views: 113

Answers (3)

Popeye
Popeye

Reputation: 35900

I have tried the query without using the analytical function. Adding different answer for the users!!

SELECT T1.month,
       T1.end_date,
       T2.END_DATE + INTERVAL '1' SECOND AS START_DATE
FROM   my_table t1
LEFT JOIN my_table T2 ON T2.MONTH = ADD_MONTHS(T1.MONTH,-1)
ORDER BY T1.MONTH

db<>fiddle demo

Cheers!!

Upvotes: 1

Eray Balkanli
Eray Balkanli

Reputation: 7960

MT0 provided the Oracle syntax already. I am not familiar with Oracle but here is SQL Server syntax I would use for this purpose, maybe useful for someone else.:

CREATE TABLE #temp (mnt DATE, enddate DATETIME)

INSERT INTO #temp VALUES ('2019-06-01','2019-07-02 12:35:00'),('2019-07-01','2019-08-03 12:35:00')

;WITH cte AS (
   SELECT *,rn=ROW_NUMBER() OVER(ORDER BY mnt ASC)
   FROM #temp 
)
SELECT cte.mnt,cte.enddate, startdate=DATEADD(SECOND,1,cte2.enddate) 
FROM cte 
LEFT outer JOIN cte cte2 ON cte2.rn = cte.rn-1

Oh, I didn't know LAG is available on SQL Server, happy to learn something new. IT also works:

SELECT mnt,
       enddate, 
       DATEADD(SECOND,1,LAG( enddate ) OVER ( ORDER BY enddate )) AS start_date
FROM   #temp

Upvotes: 1

MT0
MT0

Reputation: 167774

Use the LAG analytic function:

Oracle Setup:

CREATE TABLE table_name ( Month, End_Date ) AS
SELECT DATE '2019-06-01', DATE '2019-07-02' + INTERVAL '12:35:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT DATE '2019-07-01', DATE '2019-08-03' + INTERVAL '02:33:20' HOUR TO SECOND FROM DUAL UNION ALL
SELECT DATE '2019-08-01', DATE '2019-09-03' + INTERVAL '02:33:07' HOUR TO SECOND FROM DUAL

Query:

SELECT month,
       end_date,
       LAG( end_date ) OVER ( ORDER BY end_date ) + INTERVAL '1' SECOND
         AS start_date
FROM   table_name

Output:

MONTH               | END_DATE            | START_DATE         
:------------------ | :------------------ | :------------------
2019-06-01 00:00:00 | 2019-07-02 12:35:00 | null               
2019-07-01 00:00:00 | 2019-08-03 02:33:20 | 2019-07-02 12:35:01
2019-08-01 00:00:00 | 2019-09-03 02:33:07 | 2019-08-03 02:33:21

db<>fiddle here

Upvotes: 2

Related Questions