Month question

[vp]

Hi all,

I have a table which has claims data..On any particular day I will
have to extract all the record from the previous month.(considering
leap years and months like Feb 28/29 days)

can you please help me...

thanks
vijay

[Tonkuma]

WHERE MONTH(date_column) = MONTH(CURRNT_DATE - 1 MONTH)
AND YEAR(date_column) = YEAR(CURRNT_DATE - 1 MONTH)

[vp]

Hi,

I have to extract records for the previous month from 1st of the
previous month to the last day of the previous month..

thanks..

[Brian Tkatch]

It can be done without using a FUNCTION on the COLUMN.

WHERE date_column BETWEEN
CURRENT DATE - (DAY(CURRENT DATE) - 1) DAYS - 1 MONTH
AND CURRENT DATE - DAY(CURRENT DATE) DAYS

B.

[Tonkuma]

I remembered that you already showed this smart way.

UDB date query
http://groups.google.com/group/comp....fe17ced3?hl=en

[Brian Tkatch]

In that case, a TIMESTAMP was used, so a conversion on the COLUMN was
needed, if done in this method.

B.

[vp]

Hi,

these below cases fail for to get 1st and last day of the month...

db2 "select (date('2006-03-03') - (DAY(CURRENT DATE) - 1) DAYS - 1
MONTH) from sysibm.sysdummy1"

db2 "select (date('2006-04-03') - DAY(CURRENT DATE) DAYS ) from
sysibm.sysdummy1"

only if todays day('03') equals CURRENT day than only the query is
working..

please let me know...

[Brian Tkatch]

If you are not using CURRENT DATE, you must replace both instances:

db2 "select (date('2006-03-03') - (DAY(date('2006-03-03')) - 1) DAYS
- 1 MONTH) from sysibm.sysdummy1"
db2 "select (date('2006-04-03') - DAY(date('2006-04-03')) DAYS ) from
sysibm.sysdummy1"

B.

[--CELKO--]

Easiest way to do this
is with a range table. They usually look like this:
CREATE TABLE ReportRanges
(range_name CHAR(15) NOT NULL PRIMARY KEY,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date));

INSERT INTO ReportRanges
VALUES ('2006-01 Report', '2005-01-01', '2006-02-28 23:59:59.9999');
etc.

SELECT R.range_name, <<you gave no specs!!>> ...
FROM Ploof AS P, ReportRanges AS
WHERE P.ploof_date BETWEEN R.start_date AND COLESCE (R.end_date,
CURRENT_TIMESTAMP)
GROUP BY P.program_name;

The reasons for using a table

1) Portability. Temporal functions vary quite a bit from product.

2) Standards conformance.

3) Control at the day by say level -- exclude holidays, cut off before
midnight, etc.

4) The Calendar table is a more general tool for other temporal
problems. For a small extra effort now, he can solve a lot of problems
in the future.

[Tony Rogerson]

I am glad you took my advice and added a primary key to the table and
made the ranges not locale specific.

Now, if we can only get you to...

a) name columns on the INSERT
b) use ansi 92 join syntax instead of that confusing comma stuff of the
previous standard.

Don't forget to mention the dramatic overhead posed in maintaining a
calendar table across multiple countries.
--CELKO-- wrote:

Easiest way to do this
is with a range table. They usually look like this:
CREATE TABLE ReportRanges
(range_name CHAR(15) NOT NULL PRIMARY KEY,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date));

INSERT INTO ReportRanges
VALUES ('2006-01 Report', '2005-01-01', '2006-02-28 23:59:59.9999');
etc.

SELECT R.range_name, <<you gave no specs!!>> ...
FROM Ploof AS P, ReportRanges AS
WHERE P.ploof_date BETWEEN R.start_date AND COLESCE (R.end_date,
CURRENT_TIMESTAMP)
GROUP BY P.program_name;

The reasons for using a table

1) Portability. Temporal functions vary quite a bit from product.

2) Standards conformance.

3) Control at the day by say level -- exclude holidays, cut off before
midnight, etc.

4) The Calendar table is a more general tool for other temporal
problems. For a small extra effort now, he can solve a lot of problems
in the future.

[--CELKO--]

>> Don't forget to mention the dramatic overhead posed in maintaining a calendar table across multiple countries. <<

a) name columns on the INSERT

Okay, my bad for short code in a newsgroup. Gee, nobody else does
that, do they?

b) use ansi 92 join syntax instead of that confusing comma stuff of the
previous standard.

This is still valid, Standard SQL. Why do you think that the verbose
infixed syntax is preferred? for INNER JOINs?

Unh? I use UTC and the TIMEZONE stuff in SQL-92 to convert to local
times. I have to "fake it" with VIEWs in older products, but that is
all.

[Tony Rogerson]

>>> Okay, my bad for short code in a newsgroup. Gee, nobody else does
that, do they?

Not anybody with professional experience and training, and if you are
doing it lazy hand then undoubtedly you'll be writing it in your
production systems.

I'll have to remember to mention this when you next bash somebody for
using terms incorrectly - fields/columns records/rows; its a dam site
more serious not specifying what columns you are inserting into on the
INSERT - you will get data corruption!

This is still valid, Standard SQL. Why do you think that the verbose infixed syntax is preferred? for INNER JOINs?

Using INNER and CROSS JOIN keywords gives you distinction in the
statement, you can clearly see the join clause, the select clause and
the where clause, you don't have to work out whats a join and whats a
filter in the where clause.

select ...
from yourtable as t
inner join yourothertable as b on b.yourpk = t.yourpk
where t.colsearch = 'asdad'

And not written as I've seen you post it elsewhere to booster your
argument...

select ...
from yourtable as t
inner join
yourothertable as b
on b.yourpk = t.yourpk
where t.colsearch = 'asdad'

Which quite frankly is unreadable.

Don't rely on the comma syntax it might be depreciated in a release of
the standard in the future, things do get depreciated in the standard!
Unh? I use UTC and the TIMEZONE stuff in SQL-92 to convert to local

times. I have to "fake it" with VIEWs in older products, but that is
all

Yes, but you are not using the correct standard formatting though. Most
modern, certainly the major rdbms take the format in the correct
standard form - so use it!

[DougDB]

On 2 Feb 2006 09:08:30 -0800, "Tonkuma" <to*****@jp.ibm.com> wrote:

I remembered that you already showed this smart way.

UDB date query
http://groups.google.com/group/comp....fe17ced3?hl=en