Restrict number of rows returned - Sybase IQ

Often when you are trying to figure out a database, you will look at a table just to see what is in it. Some SQL tools return every single row on a "select * from table" COUGH!*TOAD for Data Analysts*!COUGH which is a pain when you have 3 million rows.

In Oracle you can use ROWNUM < 100 to return the first 99 rows and in MySQL 'limit 99' does the same. But for Sybase IQ? Nothing so simple. But today I discovered that there is a way to do it using the ROWID function:
select * from <SCHEMA>.<TABLE_NAME> where rowid("<TABLE_NAME>") < 100

Business Days between 2 dates in Sybase IQ

This is just ridiculously complex in Sybase compared to the others. But it CAN be done:

-- get the work days between the mondays follwoing each date
abs(datediff(day,(dateadd(dd,1,dateceiling(wk,dt1)) ), (dateadd(dd,1,dateceiling(wk,dt2)) ))) -  round(abs(datediff(day,(dateadd(dd,1,dateceiling(wk,dt1)) ) , (dateadd(dd,1,dateceiling(wk,dt2)) )))/7,0)*2 +
--add on the working days to the following monday for the first date
case when (5 - case when cast(dateformat(dt1,'d') as numeric) = 1 then 7 else  cast(dateformat(dt1,'d') as numeric)-1 end ) < 0 
        then 0 else (5 - case when cast(dateformat(dt1,'d') as numeric) = 1 then 7 else  cast(dateformat(dt1,'d') as numeric)-1 end ) end -
--take off the working days to the follwoing monday for the second date   
   case when (5 - case when cast(dateformat(dt2,'d') as numeric) = 1 then 7 else  cast(dateformat(dt2,'d') as numeric)-1  end) < 0 
        then 0 else  (5 - case when cast(dateformat(dt2,'d') as numeric) = 1 then 7 else  cast(dateformat(dt2,'d') as numeric)-1  end) end biz_days_between

Replace dt1 and dt2 with your dates. Make dt1 < dt2 otherwise I think it will break. I didn't bother testing because coding this took away my will to live.

MySQL useful dates

You get asked weird things in reporting, but the worst are translating the nice, neat, sensible dates in database tables into the strange ways that people think about dates.

Here is some MySQL SQL that returns some useful relative dates:

• start of the week
• start of last week
• start of the month
• end of previous month
• start of the previous month


select
date(@dt) base_date,
date(@dt) - interval weekday(@dt) day week_start,
date(@dt) - interval weekday(@dt) day - interval 1 week last_week_start,
date(@dt) - interval (dayofmonth(@dt)-1) day month_start,
date(@dt) - interval (dayofmonth(@dt)) day end_prev_mth,
(date(@dt) - interval (dayofmonth(@dt)-1) day) - interval 1 month start_prev_mth
from
(select @dt := now()) x


Beginning of financial year in Oracle

In Australia, the financial year runs from 1 July to 30 June. When doing financial reports, sometimes you need to know the financial year current at the time. Oracle (amazingly) makes it easy...


select
case
when mth < 4 then trunc(add_months(dt,-6),'Q') when mth < 7 then trunc(add_months(dt,-9),'Q') when mth < 10 then trunc(dt,'Q') else trunc(add_months(dt,-3),'Q') end fin_year /*return the beginning of the current financial year*/ from (select dt, to_number(to_char(dt ,'MM')) mth from ( select sysdate dt from dual) xx ) xx1

Business days between 2 dates in Oracle

A request I get occasionally is how many business days between 2 arbitrary dates. Oracle is, of course, not at all helpful here. So here is SQL to do it for you (2 methods). The second method relies on 'CONNECT BY' syntax being available so not less than Oracle 10 I'm afraid. The other one is more complex, but should work on any version.

The 2 different methods give slightly different results when the start dates are on Sat/Sun - differing interpretation of when you start counting business days from. The second method is a bit of a hack and only covers 18 months in the past and 18 months in the future. To expand that, change the 500 to the number of days ago you want to start from and the 1000 to the number of days you need to go forward from there. Ridiculously complex I know. but most of it is handling weekend dates.



select
abs( case when to_char(dt2,'d') > 5 then trunc(dt2,'d')+7 else dt2 end -
case when to_char(dt1,'d') > 5 then trunc(dt1,'d')+7 else dt1 end ) -
abs(trunc((trunc( case when to_char(dt2,'d') > 5 then trunc(dt2,'d')+7 else dt2 end ,'d') -
trunc(case when to_char(dt1,'d') > 5 then trunc(dt1,'d')+7 else dt1 end ,'d'))/7)
)*2
method1


, greatest((select count(*) from (select sysdate - 500 + level - 1 dd from dual connect by level< 1000) w where w.dd between dt1 and dt2 and to_char(w.dd,'d') < 6)-1,0) method2

,abs((trunc(dt1,'d')+7) - (trunc(dt2,'d')+7)) - trunc(abs((trunc(dt1,'d')+7) - (trunc(dt2,'d')+7))/7)*2 +
greatest(5 - to_char(dt1,'d'),0) -
greatest(5 - to_char(dt2,'d'),0)
method3


from
(
select to_date('24/08/2011','dd/mm/yyyy') dt1 , to_date('25/8/2011','dd/mm/yyyy') dt2 from dual
union select to_date('25/11/2011','dd/mm/yyyy') dt1 , to_date('28/11/2011','dd/mm/yyyy') dt2 from dual
union select to_date('13/12/2011','dd/mm/yyyy') dt1 , to_date('26/12/2011','dd/mm/yyyy') dt2 from dual
union select to_date('21/12/2011','dd/mm/yyyy') dt1 , to_date('11/01/2012','dd/mm/yyyy') dt2 from dual
union select to_date('22/12/2011','dd/mm/yyyy') dt1 , to_date('13/01/2012','dd/mm/yyyy') dt2 from dual
union select to_date('28/12/2011','dd/mm/yyyy') dt1 , to_date('25/01/2012','dd/mm/yyyy') dt2 from dual
) c
order by c.dt1



Christmas tree in SQL

If you are using Oracle 10g or better..and are using a fixed width font.

select
case
when lvl=1 then lpad('*',height,' ')
else
    lpad(' ',height+1-lvl,' ') ||  rpad('0',lag(lvl) over (partition by 1 order by lvl) + lvl -2,'0')
 end "Merry Christmas!"
from (
select
    level lvl ,max(level) over (partition by 1) height
    from dual connect by level < 11
)

returns...

*
0
000
00000
0000000
000000000
00000000000
0000000000000
000000000000000
00000000000000000
0000000000000000000


mod_auth_mysql does not set REMOTE_USER environment variable

When you authenticate using the mod_auth_mysql module, you can be authenticated to the server, but still not have the REMOTE_USER environment variable (for use in your CGI scripts) set.

To do this, you need to set the

require group ADMIN ## any group name, not just ADMIN


line.

This caused my a couple of hours mucking about this morning, so maybe it will save you some work. Google didn't return anything anyway...