Count (distinct ###) for weeks back.

[swarddb]

WE need to backload a data warehouse by week back two years and do a count(distinct station_id) by each week from that starting point forward. How is this done? Is there a week function we are not aware of?

 

[karluk]

If this is Oracle, I would try to construct a query using the yyyy-ww date format. It would look something like

select to_char(some_date,'yyyy-ww'), count(*) from your_table
group by to_char(some_date,'yyyy-ww')
order by 1

 

[karluk]

Sorry, I didn't notice that you need a count of distinct station_id.

select to_char(some_date,'yyyy-ww'),
count(distinct station_id) from your_table
group by to_char(some_date,'yyyy-ww')
order by 1

Also, the yyyy-ww format isn't the only possibility. Another one is yyyy-iw. Hopefully you can find one that meets your needs.

 

[swarddb]

Thanks for the help. Work just fine.

stefan

 

[kuysal]

Alert !
select to_char(to_date('31-dec-01'),'yyyy-iw') from dual
select to_char(to_date('01-jan-01'),'yyyy-iw') from dual
try and burst into tears!

 

[THoey]

Thanks Kuysal... For those that don't want to run and try this:

SQLWKS> select to_char(to_date('31-dec-01'),'yyyy-iw') from dual;
TO_CHAR(TO_DATE('31-DEC-01'),'YYYY-IW')                                    
------------------------------------------------------------

2001-01                                                                    
1 row selected.
SQLWKS> select to_char(to_date('01-jan-01'),'yyyy-iw') from dual;
TO_CHAR(TO_DATE('01-JAN-01'),'YYYY-IW')                                    
------------------------------------------------------------

2001-01                                                                    
1 row selected.

Not a good thing...

Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[karluk]

I'm not convinced that this behavior is really a problem, as long as it's well understood what's happening. It's very inconvenient that the number of days in a year isn't a multiple of seven. It makes it difficult to count weeks. That's why Oracle provides multiple date formats, so you can pick the one that's appropriate.

I must confess, however, that putting the end of December into the next calendar year strikes me as a bit bizarre. I can think of lots of tax problems I would have had, if the IRS did business that way.

 

[karluk]

Sorry, I'm not too alert today. The year is still 2001, so the date is simply wrong.

It's not really a bug, however. I just suggested the wrong format. If you want to use 'iw' for weeks, you have to use 'iyyy' for year. Then you will get the first week of 2002.

1* select to_char(to_date('31-dec-2001'),'iyyy-iw') from dual
SQL> /

TO_CHAR
-------
2002-01

 

[THoey]

Karluk,

Take another look. It is not at the end of the next year, but at the beginning of the same year. Either way, it could be a major problem... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[THoey]

Woops... Simultaneous posts... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.