Page 1 of 1

To fetch 3 weeks ago average count from table

PostPosted: Mon Nov 28, 2011 5:21 am
by rekhamf
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?

Re: To fetch 3 weeks ago average count from table

PostPosted: Mon Nov 28, 2011 11:33 am
by dick scherrer
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.

Re: To fetch 3 weeks ago average count from table

PostPosted: Mon Nov 28, 2011 11:34 am
by dick scherrer
Follow on:

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

d

Re: To fetch 3 weeks ago average count from table

PostPosted: Mon Nov 28, 2011 8:21 pm
by rekhamf
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.

Re: To fetch 3 weeks ago average count from table

PostPosted: Mon Nov 28, 2011 8:54 pm
by rekhamf
sorry the output would be count : 2

Re: To fetch 3 weeks ago average count from table

PostPosted: Mon Nov 28, 2011 9:49 pm
by GuyC
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 )

Re: To fetch 3 weeks ago average count from table

PostPosted: Mon Nov 28, 2011 10:03 pm
by rekhamf
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 ?

Re: To fetch 3 weeks ago average count from table

PostPosted: Mon Nov 28, 2011 10:09 pm
by rekhamf
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 ?

Re: To fetch 3 weeks ago average count from table

PostPosted: Mon Nov 28, 2011 11:29 pm
by rekhamf
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..?

Re: To fetch 3 weeks ago average count from table

PostPosted: Tue Nov 29, 2011 1:10 am
by NicC
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?