OraFAQ Forum: SQL & PL/SQL » Date calculation

Home » SQL & PL/SQL » SQL & PL/SQL » Date calculation

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Date calculation [message #562044]

Fri, 27 July 2012 06:41

musclebreast
Messages: 24
Registered: July 2012

Junior Member

Hello,

I hope you've got an idea. OK, I've got the following table:

ID.................Date...........................number...period.....termination
434473 ......2012-04-18 00:00:00.000...............6.......month(s)...end of month
443080 ......2012-02-26 00:00:00.000...............36......month(s).....end of quarter

Now it's getting complicated. Date is the start date of a contract.

I need to calculate a date when the contract can be terminated and must compare it with the current date. Lets take the first row. In column "termination" is the termination date. A contract can be terminated at "end of month" (I need to check the last day of a month. In order to get the real termination date we need to subtract the column "number".

exapmle 30.01.2012 - 7 month = 30.06. 2011

The thing I can do it, but my logic works only if the date is in the same year. I've been thinking alot...sql is not important first....Have you got a theoretical solution? I really struggle with the differnet years.

Kind regards,

Lara

Report message to a moderator

Re: Date calculation [message #562046 is a reply to message #562044]

Fri, 27 July 2012 06:45

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

ADD_MONTHS & LAST_DAY functions might be what you need.

SQL> select add_months(sysdate, -9) result from dual;

RESULT
----------
27.10.2011

SQL> select last_day(sysdate) last_day from dual;

LAST_DAY
----------
31.07.2012

SQL>

Report message to a moderator

Re: Date calculation [message #562047 is a reply to message #562046]

Fri, 27 July 2012 06:57

musclebreast
Messages: 24
Registered: July 2012

Junior Member

Hello,

all this I can do..maybe it's not that good described. Actually I generate the last days of 2012 (trouble is I do it only for the current year, that's why I struggle with past years.) and subtract the month. Then I compare the result with the current date...works fine, but only within one year.

example.

current date 02.01. 2012
termnation date 30.01. 2012 here I subtract 3 month = 30.10. 2011

The trouble...I never get a match, because I only generate the last days of the curtrent year. if the termination date would be the 3ß.06.2012...everthing would be fine and my sql works...I need a theoretical solution at first...then i can try to create a sql..

kind regards,

lara

Report message to a moderator

Re: Date calculation [message #562048 is a reply to message #562047]

Fri, 27 July 2012 07:05

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

I think you need to show us what you've actually coded. I see no reason why the year should be a problem.

Report message to a moderator

Re: Date calculation [message #562049 is a reply to message #562048]

Fri, 27 July 2012 07:13

musclebreast
Messages: 24
Registered: July 2012

Junior Member

Hopefully it's not too much...in my case the year is the big problem..at least for me:

;With QuarterTable(EOQDate)
AS
(SELECT DATEADD(qq,1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) -1
UNION ALL
SELECT DATEADD(qq,1,EOQDate)
FROM QuarterTable
WHERE DATEADD(qq,1,EOQDate) <= DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
), HalfTable1(EOQDate)
AS
(SELECT DATEADD(qq,2,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) -1
UNION ALL
SELECT DATEADD(qq,2,EOQDate)
FROM HalfTable1
WHERE DATEADD(qq,2,EOQDate) <= DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
),YearTable1(EOQDate)
AS
(SELECT DATEADD(qq,4,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) -1
UNION ALL
SELECT DATEADD(qq,4,EOQDate)
FROM YearTable1
WHERE DATEADD(qq,4,EOQDate) <= DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
)

SELECT *

FROM

(

SELECT

tester.ID,
tester.Effective_Date,
tester.EndDate,
EOQDate,

CASE
WHEN tester.Period = 'day(s)' THEN tester.Number WHEN tester.Period = 'week(s)' THEN tester.Number*7 WHEN tester.Period = 'month(s)' THEN tester.Number*30 END as days,

tester.End_Spec,

CASE
WHEN tester.Period = 'day(s)' THEN convert(varchar,DATEADD(day,-tester.Number,EOQDate-30),103) WHEN tester.Period = 'week(s)' THEN convert(varchar,DATEADD(day,-30-tester.Number*7,EOQDate),103) WHEN tester.Period = 'month(s)' THEN convert(varchar,DATEADD(day,-30-tester.Number*30,EOQDate),103) END as Termination_Date

FROM

(SELECT *

FROM

(SELECT A1.ID,
MAX(CASE
WHEN AttrID = '4' THEN Valdate END) as Effective_Date,

MAX(Case
WHEN AttrID = '5' THEN Valdate END) as Enddate,
MAX(Case
WHEN AttrID = '7' THEN ValInt END) as Number,
MAX(Case
WHEN AttrID = '10' THEN ValStr END) as Period,
MAX(Case
WHEN AttrID = '11' THEN ValStr END) as End_Spec

FROM dbo.llattrdata A1, dbo.dtree A2
Where A2.DataID = A1.ID AND A1.DefID = '434818' AND A2.Subtype ='144' AND (A1.AttrID = '4' OR A1.AttrID = '7' OR A1.AttrID = '10' OR A1.AttrID = '11' OR A1.AttrID = '5')
GROUP BY
A1.ID
) as tester1

where tester1.Enddate IS NULL AND tester1.Number IS NOT NULL AND tester1.Period IS NOT NULL AND tester1.End_Spec IS NOT NULL AND tester1.End_Spec <> 'see description') as tester

CROSS JOIN HalfTable1

WHERE tester.End_Spec = 'end of half-year'

UNION ALL

SELECT

tester.ID,
tester.Effective_Date,
tester.EndDate,
EOQDate,

CASE
WHEN tester.Period = 'day(s)' THEN tester.Number WHEN tester.Period = 'week(s)' THEN tester.Number*7 WHEN tester.Period = 'month(s)' THEN tester.Number*30 END as days,

tester.End_Spec,

CASE
WHEN tester.Period = 'day(s)' THEN convert(varchar,DATEADD(day,-tester.Number,EOQDate-30),103) WHEN tester.Period = 'week(s)' THEN convert(varchar,DATEADD(day,-30-tester.Number*7,EOQDate),103) WHEN tester.Period = 'month(s)' THEN convert(varchar,DATEADD(day,-30-tester.Number*30,EOQDate),103) END as Termination_Date

FROM

(SELECT *

FROM

(SELECT A1.ID,
MAX(CASE
WHEN AttrID = '4' THEN Valdate END) as Effective_Date,

MAX(Case
WHEN AttrID = '5' THEN Valdate END) as Enddate,
MAX(Case
WHEN AttrID = '7' THEN ValInt END) as Number,
MAX(Case
WHEN AttrID = '10' THEN ValStr END) as Period,
MAX(Case
WHEN AttrID = '11' THEN ValStr END) as End_Spec

FROM dbo.llattrdata A1, dbo.dtree A2
Where A2.DataID = A1.ID AND A1.DefID = '434818' AND A2.Subtype ='144' AND (A1.AttrID = '4' OR A1.AttrID = '7' OR A1.AttrID = '10' OR A1.AttrID = '11' OR A1.AttrID = '5')
GROUP BY
A1.ID
) as tester1

where tester1.Enddate IS NULL AND tester1.Number IS NOT NULL AND tester1.Period IS NOT NULL AND tester1.End_Spec IS NOT NULL AND tester1.End_Spec <> 'see description') as tester

CROSS JOIN QuarterTable

WHERE tester.End_Spec = 'end of quarter'

UNION ALL

SELECT

tester.ID,
tester.Effective_Date,
tester.EndDate,
EOQDate,

CASE
WHEN tester.Period = 'day(s)' THEN tester.Number WHEN tester.Period = 'week(s)' THEN tester.Number*7 WHEN tester.Period = 'month(s)' THEN tester.Number*30 END as days,

tester.End_Spec,

CASE
WHEN tester.Period = 'day(s)' THEN convert(varchar,DATEADD(day,-tester.Number,EOQDate-30),103) WHEN tester.Period = 'week(s)' THEN convert(varchar,DATEADD(day,-30-tester.Number*7,EOQDate),103) WHEN tester.Period = 'month(s)' THEN convert(varchar,DATEADD(day,-30-tester.Number*30,EOQDate),103) END as Termination_Date

FROM

(SELECT *

FROM

(SELECT A1.ID,
MAX(CASE
WHEN AttrID = '4' THEN Valdate END) as Effective_Date,

MAX(Case
WHEN AttrID = '5' THEN Valdate END) as Enddate,
MAX(Case
WHEN AttrID = '7' THEN ValInt END) as Number,
MAX(Case
WHEN AttrID = '10' THEN ValStr END) as Period,
MAX(Case
WHEN AttrID = '11' THEN ValStr END) as End_Spec

FROM dbo.llattrdata A1, dbo.dtree A2
Where A2.DataID = A1.ID AND A1.DefID = '434818' AND A2.Subtype ='144' AND (A1.AttrID = '4' OR A1.AttrID = '7' OR A1.AttrID = '10' OR A1.AttrID = '11' OR A1.AttrID = '5')
GROUP BY
A1.ID
) as tester1

where tester1.Enddate IS NULL AND tester1.Number IS NOT NULL AND tester1.Period IS NOT NULL AND tester1.End_Spec IS NOT NULL AND tester1.End_Spec <> 'see description') as tester

CROSS JOIN YearTable1

WHERE tester.End_Spec = 'end of year'

) t

WHERE convert(varchar, Termination_Date, 103) = convert(varchar, getdate(), 103)

That's why I tried to narrow donw the problem and described it...how I filter the information is not important for you

Report message to a moderator

Re: Date calculation [message #562050 is a reply to message #562049]

Fri, 27 July 2012 07:34

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

That, I'm afraid, is completely unreadable
Please read How to use [code] tags and make your code easier to read?
and repost the code, formatted, in code tags, as described in that link.

In addition you need to specify which bit of that sql is doing the date conversion/comparison that is causing you an issue.
We do not have your tables, data or inputs so it's hard to tell what does what.
Really you need to post a Test case - create table statements and insert statements for data, then we'll be able to work with your tables and data to recreate the problem.

Report message to a moderator

Re: Date calculation [message #562051 is a reply to message #562050]

Fri, 27 July 2012 07:44

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Are you sure this is oracle code?
The use of convert looks wrong and dateadd is not an oracle function.

Report message to a moderator

Re: Date calculation [message #562052 is a reply to message #562051]

Fri, 27 July 2012 07:51

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Quote:

<snip>
FROM dbo.llattrdata A1, dbo.dtree A2

The above would imply SQL Server.

Report message to a moderator

Re: Date calculation [message #562055 is a reply to message #562052]

Fri, 27 July 2012 08:08

musclebreast
Messages: 24
Registered: July 2012

Junior Member



"""Here I generate the last day of each quarter, year, half year of the current year.""" 



;With QuarterTable(EOQDate)
 AS
 (SELECT DATEADD(qq,1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) -1
 UNION ALL
 SELECT DATEADD(qq,1,EOQDate)
 FROM QuarterTable
 WHERE DATEADD(qq,1,EOQDate) <= DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
 ), HalfTable1(EOQDate)
 AS
 (SELECT DATEADD(qq,2,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) -1
 UNION ALL
 SELECT DATEADD(qq,2,EOQDate)
 FROM HalfTable1
 WHERE DATEADD(qq,2,EOQDate) <= DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
 ),YearTable1(EOQDate)
 AS
 (SELECT DATEADD(qq,4,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) -1
 UNION ALL
 SELECT DATEADD(qq,4,EOQDate)
 FROM YearTable1
 WHERE DATEADD(qq,4,EOQDate) <= DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
 )
 

SELECT *
 
FROM
 
(
 

SELECT 

tester.ID,
 tester.Effective_Date,
 tester.EndDate,
 EOQDate,
 



CASE 
WHEN tester.Period = 'day(s)' THEN tester.Number WHEN tester.Period = 'week(s)' THEN tester.Number*7 WHEN tester.Period = 'month(s)' THEN tester.Number*30 END as days,
 
tester.End_Spec,
 

CASE 
WHEN tester.Period = 'day(s)' THEN convert(varchar,DATEADD(day,-tester.Number,EOQDate-30),103) WHEN tester.Period = 'week(s)' THEN convert(varchar,DATEADD(day,-30-tester.Number*7,EOQDate),103) WHEN tester.Period = 'month(s)' THEN convert(varchar,DATEADD(day,-30-tester.Number*30,EOQDate),103) END as Termination_Date
 


FROM 

(SELECT * 

FROM
 
(SELECT A1.ID,
 MAX(CASE 
WHEN AttrID = '4' THEN Valdate END) as Effective_Date, 

MAX(Case
 WHEN AttrID = '5' THEN Valdate END) as Enddate,
 MAX(Case
 WHEN AttrID = '7' THEN ValInt END) as Number, 
MAX(Case
 WHEN AttrID = '10' THEN ValStr END) as Period, 
MAX(Case
 WHEN AttrID = '11' THEN ValStr END) as End_Spec
 


FROM dbo.llattrdata A1, dbo.dtree A2
 Where A2.DataID = A1.ID AND A1.DefID = '434818' AND A2.Subtype ='144' AND (A1.AttrID = '4' OR A1.AttrID = '7' OR A1.AttrID = '10' OR A1.AttrID = '11' OR A1.AttrID = '5') 
GROUP BY
 A1.ID
 ) as tester1
 
where tester1.Enddate IS NULL AND tester1.Number IS NOT NULL AND tester1.Period IS NOT NULL AND tester1.End_Spec IS NOT NULL AND tester1.End_Spec <> 'see description') as tester
 
CROSS JOIN HalfTable1
 
WHERE tester.End_Spec = 'end of half-year' 


UNION ALL
 

SELECT 

tester.ID,
 tester.Effective_Date,
 tester.EndDate,
 EOQDate,
 

CASE 
WHEN tester.Period = 'day(s)' THEN tester.Number WHEN tester.Period = 'week(s)' THEN tester.Number*7 WHEN tester.Period = 'month(s)' THEN tester.Number*30 END as days,
 

tester.End_Spec,
 
CASE 
WHEN tester.Period = 'day(s)' THEN convert(varchar,DATEADD(day,-tester.Number,EOQDate-30),103) WHEN tester.Period = 'week(s)' THEN convert(varchar,DATEADD(day,-30-tester.Number*7,EOQDate),103) WHEN tester.Period = 'month(s)' THEN convert(varchar,DATEADD(day,-30-tester.Number*30,EOQDate),103) END as Termination_Date
 





FROM 

(SELECT * 

FROM
 
(SELECT A1.ID,
 MAX(CASE 
WHEN AttrID = '4' THEN Valdate END) as Effective_Date, 

MAX(Case
 WHEN AttrID = '5' THEN Valdate END) as Enddate,
 MAX(Case
 WHEN AttrID = '7' THEN ValInt END) as Number, 
MAX(Case
 WHEN AttrID = '10' THEN ValStr END) as Period, 
MAX(Case
 WHEN AttrID = '11' THEN ValStr END) as End_Spec
 


FROM dbo.llattrdata A1, dbo.dtree A2
 Where A2.DataID = A1.ID AND A1.DefID = '434818' AND A2.Subtype ='144' AND (A1.AttrID = '4' OR A1.AttrID = '7' OR A1.AttrID = '10' OR A1.AttrID = '11' OR A1.AttrID = '5') 
GROUP BY
 A1.ID
 ) as tester1
 
where tester1.Enddate IS NULL AND tester1.Number IS NOT NULL AND tester1.Period IS NOT NULL AND tester1.End_Spec IS NOT NULL AND tester1.End_Spec <> 'see description') as tester
 



CROSS JOIN QuarterTable
 
WHERE tester.End_Spec = 'end of quarter' 





UNION ALL
 

SELECT 

tester.ID,
 tester.Effective_Date,
 tester.EndDate,
 EOQDate,
 

CASE 
WHEN tester.Period = 'day(s)' THEN tester.Number WHEN tester.Period = 'week(s)' THEN tester.Number*7 WHEN tester.Period = 'month(s)' THEN tester.Number*30 END as days,
 

tester.End_Spec,
 
CASE 
WHEN tester.Period = 'day(s)' THEN convert(varchar,DATEADD(day,-tester.Number,EOQDate-30),103) WHEN tester.Period = 'week(s)' THEN convert(varchar,DATEADD(day,-30-tester.Number*7,EOQDate),103) WHEN tester.Period = 'month(s)' THEN convert(varchar,DATEADD(day,-30-tester.Number*30,EOQDate),103) END as Termination_Date
 





FROM 

(SELECT * 

FROM
 
(SELECT A1.ID,
 MAX(CASE 
WHEN AttrID = '4' THEN Valdate END) as Effective_Date, 

MAX(Case
 WHEN AttrID = '5' THEN Valdate END) as Enddate,
 MAX(Case
 WHEN AttrID = '7' THEN ValInt END) as Number, 
MAX(Case
 WHEN AttrID = '10' THEN ValStr END) as Period, 
MAX(Case
 WHEN AttrID = '11' THEN ValStr END) as End_Spec
 


FROM dbo.llattrdata A1, dbo.dtree A2
 Where A2.DataID = A1.ID AND A1.DefID = '434818' AND A2.Subtype ='144' AND (A1.AttrID = '4' OR A1.AttrID = '7' OR A1.AttrID = '10' OR A1.AttrID = '11' OR A1.AttrID = '5') 
GROUP BY
 A1.ID
 ) as tester1
 
where tester1.Enddate IS NULL AND tester1.Number IS NOT NULL AND tester1.Period IS NOT NULL AND tester1.End_Spec IS NOT NULL AND tester1.End_Spec <> 'see description') as tester
 



CROSS JOIN YearTable1
 
WHERE tester.End_Spec = 'end of year' 

) t
 
WHERE convert(varchar, Termination_Date, 103) = convert(varchar, getdate(), 103)

Hi,

actually I use it in SQL server but I think about it to use and then write it in oracle...if it's mor easy..pleasy ignore sql server functions....more or less only three party are important:

;With QuarterTable(EOQDate)
 AS
 (SELECT DATEADD(qq,1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) -1
 UNION ALL
 SELECT DATEADD(qq,1,EOQDate)
 FROM QuarterTable
 WHERE DATEADD(qq,1,EOQDate) <= DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
 ), HalfTable1(EOQDate)
 AS
 (SELECT DATEADD(qq,2,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) -1
 UNION ALL
 SELECT DATEADD(qq,2,EOQDate)
 FROM HalfTable1
 WHERE DATEADD(qq,2,EOQDate) <= DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
 ),YearTable1(EOQDate)
 AS
 (SELECT DATEADD(qq,4,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) -1
 UNION ALL
 SELECT DATEADD(qq,4,EOQDate)
 FROM YearTable1
 WHERE DATEADD(qq,4,EOQDate) <= DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
 )

here I generate the tables with the last day...

here I do a join with the calculated dates (last day of a quarter) with my found entries of documents.

CROSS JOIN QuarterTable
 
WHERE tester.End_Spec = 'end of quarter'

here I go through the table of documents with the calculated day and compare it with the current date.

WHERE convert(varchar, Termination_Date, 103) = convert(varchar, getdate(), 103)

As I tried to explain...my problem is that I can do it only within one year..it never works when the calculated date is in the past...forgeht the sql..just read my exapmle ..i need only a theoretically ideal..then i can try to develop a sql..is my problem more clear now? Smile

Kind regards

Report message to a moderator

Re: Date calculation [message #562059 is a reply to message #562055]

Fri, 27 July 2012 09:12

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This is unreadable.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Also always post your Oracle version, with 4 decimals.

Regards
Michel

Report message to a moderator

Re: Date calculation [message #562071 is a reply to message #562059]

Fri, 27 July 2012 15:06

Bill B
Messages: 1971
Registered: December 2004

Senior Member

in oracle you simply subtract the first date from the second and you get the number of days, hours, minutes, and seconds between the two. Explain in plain words what you want to do. Your code makes no sense.

Report message to a moderator

Re: Date calculation [message #562083 is a reply to message #562071]

Fri, 27 July 2012 16:33

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

Are you using oracle, or something else?
Cause it appears to be something else.
And if it is then you're asking for help in the wrong place.
The theoretical way to do it in oracle is different to the theoretical way to do it in sql server.

Report message to a moderator

Previous Topic:	Dynamic procedure name with record type i/p parameter
Next Topic:	Project

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Aug 21 21:16:56 CDT 2025