To fetch 3 weeks ago average count from table



Support for OS/VS COBOL, VS COBOL II, COBOL for OS/390 & VM and Enterprise COBOL for z/OS

To fetch 3 weeks ago average count from table

Postby rekhamf » Mon Nov 28, 2011 5:21 am

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?
rekhamf
 
Posts: 33
Joined: Mon Nov 28, 2011 5:09 am
Has thanked: 0 time
Been thanked: 0 time

Re: To fetch 3 weeks ago average count from table

Postby dick scherrer » Mon Nov 28, 2011 11:33 am

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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: To fetch 3 weeks ago average count from table

Postby dick scherrer » Mon Nov 28, 2011 11:34 am

Follow on:

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

d
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: To fetch 3 weeks ago average count from table

Postby rekhamf » Mon Nov 28, 2011 8:21 pm

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.
rekhamf
 
Posts: 33
Joined: Mon Nov 28, 2011 5:09 am
Has thanked: 0 time
Been thanked: 0 time

Re: To fetch 3 weeks ago average count from table

Postby rekhamf » Mon Nov 28, 2011 8:54 pm

sorry the output would be count : 2
rekhamf
 
Posts: 33
Joined: Mon Nov 28, 2011 5:09 am
Has thanked: 0 time
Been thanked: 0 time

Re: To fetch 3 weeks ago average count from table

Postby GuyC » Mon Nov 28, 2011 9:49 pm

some pretty basic sql-features : count( distinct col)and some basic date-timetamp manipulations could lead to this following statement :
select count( distinct employ_number) from employ_table where log_timestamp between
timestamp(current date - 24 days,'00:00')  and (timestamp(current date - 17 days,'00:00') - 1 microsecond )
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: To fetch 3 weeks ago average count from table

Postby rekhamf » Mon Nov 28, 2011 10:03 pm

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 ?
rekhamf
 
Posts: 33
Joined: Mon Nov 28, 2011 5:09 am
Has thanked: 0 time
Been thanked: 0 time

Re: To fetch 3 weeks ago average count from table

Postby rekhamf » Mon Nov 28, 2011 10:09 pm

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 ?
rekhamf
 
Posts: 33
Joined: Mon Nov 28, 2011 5:09 am
Has thanked: 0 time
Been thanked: 0 time

Re: To fetch 3 weeks ago average count from table

Postby rekhamf » Mon Nov 28, 2011 11:29 pm

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..?
rekhamf
 
Posts: 33
Joined: Mon Nov 28, 2011 5:09 am
Has thanked: 0 time
Been thanked: 0 time

Re: To fetch 3 weeks ago average count from table

Postby NicC » Tue Nov 29, 2011 1:10 am

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?
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times


Return to IBM Cobol

 


  • Related topics
    Replies
    Views
    Last post