Difficult SQL Problem

[Dagon]

I have a table in the following format e.g.

ID DATE MEASURE
-- ---- -------
1 30-JAN 10
1 02-FEB 15
1 12-FEB 20
1 14-FEB 30
1 15-FEB 35

Each day, this has to be converted into a summary table with columns:

ID
MEASURE
DAYS_AGO_INDICATOR

where DAYS_AGO_INDICATOR = CURRENT, 1_DAY_AGO, 7_DAYS_AGO, 14_DAYS_AGO and possibly loads of others up to a year ago. These are all offsets from the current date.

In the above example (with 15-jan as the current date), the values would be:

ID ME IND
-- -- --
1 35 CUR
1 30 1DAY
1 15 7DAY
1 10 14DAY

The only way I can think of doing this is with a union query, something like:

select * from
(select id, 'CUR',
row_number() over
(partition by ID order by datecol desc) as rown
from table
where datecol <= current date )
where rown = 1
union all
select * from
(select id, '1DAY',
row_number() over
(partition by ID order by datecol desc) as rown
from table
where datecol <= current date - 1 day)
where rown = 1
...

Does anyone have any other ideas ?

 

[Dagon]

Should say &quot;15-feb as the current date&quot;.

 

[blom0344]

Dagon,

I should think that you can Julian_date for this problem:

Get your dates in a proper date format and then apply:

Select
Case when
(Julian_date(Current Date)-Julian_date(Date*)) > 0 then
Char(Julian_date(Current Date)-Julian_date(Date*))||'DAY'
else 'CUR' from ......

You may need to place some formatting around the char function to get rid of leading zero's


T. Blom
Information analyst
tbl@shimano-eu.com

 

[MarcLodge]

Hi Dagon,
Howabout something like:

SELECT ID, SUM(ME), LITERAL FROM
(
SELECT MEASURE AS ME,
CASE
WHEN DATECOL = CURRENT_DATE
THEN 'CUR'
WHEN DATECOL = CURRENT_DATE - 1 DAY
THEN '1DAY'
WHEN DATECOL = CURRENT_DATE - 7 DAY
THEN '7DAY'
END AS LITERAL
FROM TABLE
)
AS TEMP_TAB
GROUP BY ID, LITERAL

You may have to change your date clauses to pick up the right format and also for when dates fall between 2 dates (if applicable)

Marc

 

[MarcLodge]

Dagon,
Sorry in the previous I left out the ID in the inner select, should have read:
SELECT ID, SUM(ME), LITERAL FROM
(
SELECT ID, MEASURE AS ME,
CASE
WHEN DATECOL = CURRENT_DATE
THEN 'CUR'
WHEN DATECOL = CURRENT_DATE - 1 DAY
THEN '1DAY'
WHEN DATECOL = CURRENT_DATE - 7 DAY
THEN '7DAY'
END AS LITERAL
FROM TABLE
)
AS TEMP_TAB
GROUP BY ID, LITERAL

This may still require a bit of tweaking as I haven't tested it.
hth
Marc

 

[Dagon]

I don't think these ideas will work as they assume that there is always a value from 1 day ago, 7 days ago etc. As I tried to show in the example data where the 7 day value was from '02-feb', not '08-feb', this is not always the case.

In fact, if the last value we have was from 10 days ago, it would serve as the 7 day, 1 day and current value since we don't have anything else. Therefore, a CASE statement won't work as the CASE would have to be able to assume multiple values (i.e. a row can be a current, 1 day and 7 day).

I think this would be quite difficult to solve by SQL alone as there are potentially more rows in the target table than there are in the source.

 

[blom0344]

Don't really get what you are aiming at, but you may be right that SQL alone will not cut the cake. Personally I would take my ETL tool to fix this

How about this structure:

CASE WHEN JULIAN_DAY(CURRENT DATE) - JULIAN_DAY(DATE) > 0 THEN
SUBSTR(CHAR(FLOOR((JULIAN_DAY(CURRENT DATE) - JULIAN_DAY(DATE))/7)*7),1,3)||'DAY' ELSE 'CUR' END

T. Blom
Information analyst
tbl@shimano-eu.com