 |
|
| Computers Forum Index » Computer - Databases - IBM DB2 » date range column results... |
|
Page 1 of 1 |
|
| Author |
Message |
| Frank Swarbrick... |
 Posted: Wed Oct 14, 2009 3:47 am |
|
|
|
Guest
|
Am I going down the right path, here?
select coalesce(max(r1), 'N') as r0_90
, coalesce(max(r2), 'N') as r91_180
, coalesce(max(r3), 'N') as r181_365
, coalesce(max(r4), 'N') as r366_730
from (
select case when filing_date <= current date - 0 DAYS
and filing_date >= current date - 90 DAYS
then 'Y'
end as r1
, case when filing_date <= current date - 91 DAYS
and filing_date >= current date - 180 DAYS
then 'Y'
end as r2
, case when filing_date <= current date - 181 DAYS
and filing_date >= current date - 365 DAYS
then 'Y'
end as r3
, case when filing_date <= current date - 366 DAYS
then 'Y'
end as r4
from (
select filing_date
from sarfiling
where cust_nbr = ?
and filing_date >= current date - 730 days
) as t
);
This returns a row with the four columns set to either 'Y' or 'N' depending
on whether or not there are any "SAR filings" for a particular customer
within each range of dates.
So, for example, if the specified customer had a SAR filing yesterday and
one 300 days ago, but no others, then the query returns:
R0_90 R91_180 R181_365 R366_730
----- ------- -------- --------
Y N Y N
1 record(s) selected.
It works. Seems to me to be a bit convoluted, but I can't think of how it
can be simplified.
Here's the DDF, in case that's of use.
CREATE TABLE SARFILING (
CUST_NBR CHARACTER (9) NOT NULL
, FILING_DATE DATE NOT NULL
) ;
CREATE INDEX SARFILINGIDX ON SARFILING (CUST_NBR ASC, FILING_DATE ASC);
Thanks!
--
Frank Swarbrick
Applications Architect - Mainframe Applications Development
FirstBank Data Corporation - Lakewood, CO USA
P: 303-235-1403 |
|
|
| Back to top |
|
|
|
| Tonkuma... |
| Posted: Wed Oct 14, 2009 5:45 am |
|
|
|
Guest
|
It will be not necessary to use nested table expressions.
And, you can use BETWEEN predicate.
(Though, access path may be same as your original query.)
Like this:
SELECT COALESCE(
MAX(case
when filing_date BETWEEN current date - 90 DAYS
AND current date - 0 DAYS
then 'Y'
end)
, 'N') as r0_90
, COALESCE(
MAX(case
when filing_date BETWEEN current date - 180 DAYS
AND current date - 91 DAYS
then 'Y'
end)
, 'N') as r91_180
, COALESCE(
MAX(case
when filing_date BETWEEN current date - 365 DAYS
AND current date - 181 DAYS
then 'Y'
end)
, 'N') as r181_365
, COALESCE(
MAX(case
when filing_date <= current date - 366 DAYS
then 'Y'
end)
, 'N') as r366_730
FROM sarfiling
WHERE cust_nbr = ?
AND filing_date >= current date - 730 days
;
But, using a nested table expression may be easier to understand the
query.
select coalesce(max(r1), 'N') as r0_90
, coalesce(max(r2), 'N') as r91_180
, coalesce(max(r3), 'N') as r181_365
, coalesce(max(r4), 'N') as r366_730
from (
select case when filing_date BETWEEN current date - 90 DAYS
AND current date - 0 DAYS
then 'Y'
end as r1
, case when filing_date BETWEEN current date - 180 DAYS
AND current date - 91 DAYS
then 'Y'
end as r2
, case when filing_date BETWEEN current date - 365 DAYS
AND current date - 181 DAYS
then 'Y'
end as r3
, case when filing_date <= current date - 366 DAYS
then 'Y'
end as r4
FROM sarfiling
WHERE cust_nbr = ?
AND filing_date >= current date - 730 days
) as t
; |
|
|
| Back to top |
|
|
|
| Frank Swarbrick... |
| Posted: Thu Oct 15, 2009 10:27 pm |
|
|
|
Guest
|
Looking at it I don't see any reason not to use your first example.
Thanks.
Once thing that I wasn't expecting is that, at least when embedded in a
Cobol program, I get an SQLSTATE of '01003' pretty much always. This means
"Null values were eliminated from the argument of an aggregate function.".
SQLCODE is still 0.
Here is a comment from the MAX() function: "The function is applied to the
set of values derived from the argument values by the elimination of null
values."
In any case, I assume that I simply need to expect this and pretty much
ignore it, yes?
I'm considering just doing this instead:
SELECT MAX(case
when filing_date BETWEEN current date - 90 DAYS
AND current date - 0 DAYS
then 'Y' else 'N'
end) as r0_90
, MAX(case
when filing_date BETWEEN current date - 180 DAYS
AND current date - 91 DAYS
then 'Y' else 'N'
end) as r91_180
, MAX(case
when filing_date BETWEEN current date - 365 DAYS
AND current date - 181 DAYS
then 'Y' else 'N'
end) as r181_365
, MAX(case
when filing_date <= current date - 366 DAYS
then 'Y' else 'N'
end) as r366_730
FROM sarfiling
WHERE cust_nbr = ?
AND filing_date >= current date - 730 days
This gives the same result and eliminates the '01003' warning, but also
relies on the "coincidence" that 'Y' is "greater" than 'N'.
Any thoughts in preferring one over the other? Or is there a third, even
better, way?
Thanks,
Frank
--
Frank Swarbrick
Applications Architect - Mainframe Applications Development
FirstBank Data Corporation - Lakewood, CO USA
P: 303-235-1403
n 10/13/2009 at 11:45 PM, in message
<11fac9b6-9948-412d-ad98-7885a048b9ab at (no spam) h40g2000prf.googlegroups.com>,
Tonkuma<tonkuma at (no spam) fiberbit.net> wrote:
Quote: It will be not necessary to use nested table expressions.
And, you can use BETWEEN predicate.
(Though, access path may be same as your original query.)
Like this:
SELECT COALESCE(
MAX(case
when filing_date BETWEEN current date - 90 DAYS
AND current date - 0 DAYS
then 'Y'
end)
, 'N') as r0_90
, COALESCE(
MAX(case
when filing_date BETWEEN current date - 180 DAYS
AND current date - 91 DAYS
then 'Y'
end)
, 'N') as r91_180
, COALESCE(
MAX(case
when filing_date BETWEEN current date - 365 DAYS
AND current date - 181 DAYS
then 'Y'
end)
, 'N') as r181_365
, COALESCE(
MAX(case
when filing_date <= current date - 366 DAYS
then 'Y'
end)
, 'N') as r366_730
FROM sarfiling
WHERE cust_nbr = ?
AND filing_date >= current date - 730 days
;
But, using a nested table expression may be easier to understand the
query.
select coalesce(max(r1), 'N') as r0_90
, coalesce(max(r2), 'N') as r91_180
, coalesce(max(r3), 'N') as r181_365
, coalesce(max(r4), 'N') as r366_730
from (
select case when filing_date BETWEEN current date - 90 DAYS
AND current date - 0 DAYS
then 'Y'
end as r1
, case when filing_date BETWEEN current date - 180 DAYS
AND current date - 91 DAYS
then 'Y'
end as r2
, case when filing_date BETWEEN current date - 365 DAYS
AND current date - 181 DAYS
then 'Y'
end as r3
, case when filing_date <= current date - 366 DAYS
then 'Y'
end as r4
FROM sarfiling
WHERE cust_nbr = ?
AND filing_date >= current date - 730 days
) as t
; |
|
|
| Back to top |
|
|
|
| Tonkuma... |
| Posted: Thu Oct 15, 2009 11:31 pm |
|
|
|
Guest
|
Your second query is better than others without considering warning
sqlstate.
Because, it eliminates nest of functions and the query get simple.
Here is (a little tricky?)another query calculating the date
difference at once.
SELECT
MAX(case
when date_diff BETWEEN 0 AND 90 then
'Y'
else 'N'
end ) as r0_90
, MAX(case
when date_diff BETWEEN 91 AND 180 then
'Y'
else 'N'
end ) as r91_180
, MAX(case
when date_diff BETWEEN 181 AND 365 then
'Y'
else 'N'
end ) as r181_365
, MAX(case
when date_diff >= 366 then
'Y'
else 'N'
end ) as r366_730
FROM (SELECT TIMESTAMPDIFF(16, CHAR( TIMESTAMP(current date)
- TIMESTAMP(filing_date )
)
) AS date_diff
FROM sarfiling
WHERE cust_nbr = ?
AND filing_date >= current date - 730 days
) AS s
; |
|
|
| Back to top |
|
|
|
| Frank Swarbrick... |
| Posted: Fri Oct 16, 2009 4:21 am |
|
|
|
Guest
|
Very cool! I had to use TIMESTAMP_ISO instead of TIMESTAMP to convert the
dates, but other than that it works great. And is very easy to read to
boot!
No if only some day I will be able to come up with these ideas on my own!
Ah well. Practice makes perfect, I guess.
Frank
--
Frank Swarbrick
Applications Architect - Mainframe Applications Development
FirstBank Data Corporation - Lakewood, CO USA
P: 303-235-1403
n 10/15/2009 at 5:31 PM, in message
<cabf060a-cbf0-4a57-867e-b33a997b9e00 at (no spam) 12g2000pri.googlegroups.com>,
Tonkuma<tonkuma at (no spam) fiberbit.net> wrote:
Quote: Your second query is better than others without considering warning
sqlstate.
Because, it eliminates nest of functions and the query get simple.
Here is (a little tricky?)another query calculating the date
difference at once.
SELECT
MAX(case
when date_diff BETWEEN 0 AND 90 then
'Y'
else 'N'
end ) as r0_90
, MAX(case
when date_diff BETWEEN 91 AND 180 then
'Y'
else 'N'
end ) as r91_180
, MAX(case
when date_diff BETWEEN 181 AND 365 then
'Y'
else 'N'
end ) as r181_365
, MAX(case
when date_diff >= 366 then
'Y'
else 'N'
end ) as r366_730
FROM (SELECT TIMESTAMPDIFF(16, CHAR( TIMESTAMP(current date)
- TIMESTAMP(filing_date )
)
) AS date_diff
FROM sarfiling
WHERE cust_nbr = ?
AND filing_date >= current date - 730 days
) AS s
; |
|
|
| Back to top |
|
|
|
| Tonkuma... |
| Posted: Fri Oct 16, 2009 6:55 am |
|
|
|
Guest
|
On Oct 16, 9:21 am, "Frank Swarbrick" <Frank.Swarbr... at (no spam) efirstbank.com>
wrote:
Quote: ... I had to use TIMESTAMP_ISO instead of TIMESTAMP to convert the
dates, but other than that it works great.
DB2 9.7 for LUW supports TIMESTAMP(<date>). While eralier version
doesn't support TIMESTAMP(<date>).
Here is another tricky but shorter query:
SELECT
MAX( SUBSTR('YNNNNNNNN', date_diff / 91 + 1, 1) ) as
r0_90
, MAX( SUBSTR('NNYNNNNNNN', (date_diff + 179) / 90, 1) ) as
r91_180
, MAX( SUBSTR('NYNNN', (date_diff + 189) / 185, 1) ) as
r181_365
, MAX( SUBSTR('NY', date_diff / 366 + 1, 1) ) as
r366_730
FROM (SELECT TIMESTAMPDIFF(16, CHAR( TIMESTAMP_ISO(current date)
- TIMESTAMP_ISO(filing_date )
)
) AS date_diff
FROM sarfiling
WHERE cust_nbr = ?
AND filing_date >= current date - 730 days
) s
; |
|
|
| Back to top |
|
|
|
| Frank Swarbrick... |
| Posted: Fri Oct 16, 2009 8:11 pm |
|
|
|
Guest
|
------------------------------ Commands Entered
------------------------------
values timestamp(current date);
values timestamp_iso(current date);
values timestamp(current date, '00:00');
----------------------------------------------------------------------------
--
values timestamp(current date)
SQL0440N No authorized routine named "TIMESTAMP" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884
values timestamp_iso(current date)
1
--------------------------
2009-10-16-00.00.00.000000
1 record(s) selected.
values timestamp(current date, '00:00')
1
--------------------------
2009-10-16-00.00.00.000000
1 record(s) selected.
I don't think I will bother with that tricky but shorter query. But
thanks!
Frank
--
Frank Swarbrick
Applications Architect - Mainframe Applications Development
FirstBank Data Corporation - Lakewood, CO USA
P: 303-235-1403
n 10/16/2009 at 12:55 AM, in message
<bc73010b-2f33-44c7-abc9-b72e48c375d6 at (no spam) 12g2000pri.googlegroups.com>,
Tonkuma<tonkuma at (no spam) fiberbit.net> wrote:
Quote: On Oct 16, 9:21 am, "Frank Swarbrick" <Frank.Swarbr... at (no spam) efirstbank.com
wrote:
... I had to use TIMESTAMP_ISO instead of TIMESTAMP to convert the
dates, but other than that it works great.
DB2 9.7 for LUW supports TIMESTAMP(<date>). While eralier version
doesn't support TIMESTAMP(<date>).
Here is another tricky but shorter query:
SELECT
MAX( SUBSTR('YNNNNNNNN', date_diff / 91 + 1, 1) ) as
r0_90
, MAX( SUBSTR('NNYNNNNNNN', (date_diff + 179) / 90, 1) ) as
r91_180
, MAX( SUBSTR('NYNNN', (date_diff + 189) / 185, 1) ) as
r181_365
, MAX( SUBSTR('NY', date_diff / 366 + 1, 1) ) as
r366_730
FROM (SELECT TIMESTAMPDIFF(16, CHAR( TIMESTAMP_ISO(current date)
- TIMESTAMP_ISO(filing_date )
)
) AS date_diff
FROM sarfiling
WHERE cust_nbr = ?
AND filing_date >= current date - 730 days
) s
; |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Sat Dec 12, 2009 7:34 am
|
|