IBM Mainframe Forum

Hi ,

I have a Employment table with has employ number as unique key and log timestamp with dd.mm.yyyy.hh.mm.ss ..


I need to fetch the count of employ based on employ number(it can have duplicate, i need to avoid duplicate too ) for 3 weeks ago average from current date .Can you please let me know how i can do that?

Hello,

Post some sample data that has data that is to be "fetched" and data that should be skipped. Clearly explain the rules and apply these to the data. Show the exact desired output when the sample input data is processed.

Follow on:

Your duplicate topic has been removed - do not post the same question as multiple topics

d

Hi

I have below table :

Employ table :

Employ number Log_timestamp
----------------- --------------------
1 10.10.2011.01.00.00
2 11.10.2011.01.00.00
3 12.10.2011.01.00.00
4 01.10.2011.01.00.00
5 14.10.2011.01.00.00
5 15.10.2011.01.00.00
6 04.10.2011.01.00.00
7 18.10.2011.01.00.00

Output is the count of total employ : 6
Rule :

I need to take three weeks average will contain the figures for current date-18 to current date-24 (if today date is 28 then it should pick record from date oct 04 to oct 10th) and to remove duplicate row with same employ number .

Please let me know if you need more information.

sorry the output would be count : 2

some pretty basic sql-features : count( distinct col)and some basic date-timetamp manipulations could lead to this following statement :

Hi When i Tried this command :
I got this error :
DSNT408I SQLCODE = -440, ERROR: NO FUNCTION BY THE NAME AND HAVING COMPATIBLE
ARGUMENTS WAS FOUND IN THE CURRENT PATH
DSNT418I SQLSTATE = 42884 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXORFN SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -100 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF9C' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

Do i need to make any calculation on current date - 24 days ? similarly for current date - 17 days ?

IS that possible for me to fetch with employ number with recent timestamp ?
Eg

Employ number log timestamp
5 14.10.2011.01.00.00
5 15.10.2011.01.00.00

I need to count the on only the emply number with date of oct 15th ?

Hi,

Thank you this works .But it the start date goes to pervious month ,will this function will able to handle the month also ... for eg.. current date-18 , if current date is dec 5th and current date-18 should be in Nov month ,, do i need to have any special condition for this ? ,similiary for year..?

Why not try using your current data but subtracting a larger number of days to see if it calculates the date properly - or read the manual?