Below qry can be executed in sql server .. how to write in DB2 ?? does it need dummy tables??? without that anyway to write ????

SELECT COUNT(*)
FROM
(
SELECT 1 AS d
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
) weekdays
=======================

Acutal qry need to be converted in db2 ... for business days
------------------------------------------------------------


select (DATEDIFF(DAY, '2008/11/01', '2009/12/30')+1) / 7 * 5

+

( (DATEDIFF(DAY, '2008/11/01', '2008/12/30')+1) % 7 )

-

(
SELECT COUNT(*)
FROM
(
SELECT 1 AS d
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
) weekdays
WHERE d <= ( (DATEDIFF(DAY, '2008/11/01', '2008/12/30')+1) % 7 ) % 7
AND DATENAME(WEEKDAY, day('2008/12/30') - d + 1)
IN
(
'Saturday',
'Sunday'
)
)

SELECT needs a FROM clause, always. You can either use the VALUES statement instead or select from a 1-row table SYSIBM.SYSDUMMY1.

Did I understand you right ?

You have a start date (eg. 01.11.2008) and an end date (eg. 31.12.2009) and you want to know, how many mondays+tuesdays+wednesdays+thursdays+fridays are within that date range ?

This query will do it:

WITH ABCD ( DATUM ) AS
( SELECT DATE('01.11.2008') AS DATUM FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT ABCD.DATUM + 1 DAYS FROM ABCD X
WHERE ABCD.DATUM < DATE('31.12.2009')
)
SELECT COUNT(*) FROM ABCD
WHERE DAYOFWEEK(DATUM) IN (2,3,4,5,6)


the dates may be specified in one of these formats:
dd.mm.yyyy
mm/dd/yyyy
yyyy-mm-dd