to fetch current date-2 date records count only



IBM's flagship relational database management system

to fetch current date-2 date records count only

Postby rekhamf » Tue Nov 29, 2011 12:17 am

Hi ,

Can you please let me know : How I can get the count for the particular date (from start timestamp till end timestamp) from the table ?

Rule :
=====
Current date -2 ( if todays date is dec 1st, then i need to get the count from the table for only the date nov 29th)

SELECT Count(Distinct (Emply number) FROM Employ table where
WHERE LOG_TS BETWEEN
(TIMESTAMP(CURRENT DATE- 02 days ,'00:00')-
AND (TIMESTAMP(CURRENT DATE - 02 DAYS,'00:00')- 1 MICROSECOND) WITH UR;

This returns no rows .
Can you please help on this ?
rekhamf
 
Posts: 33
Joined: Mon Nov 28, 2011 5:09 am
Has thanked: 0 time
Been thanked: 0 time

Re: to fetch current date-2 date records count only

Postby Akatsukami » Tue Nov 29, 2011 12:29 am

Please copy and paste the actual query using code tags to preserve formatting.
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: to fetch current date-2 date records count only

Postby rekhamf » Tue Nov 29, 2011 1:00 am

I have this query .but i need someone to help with this condition :

SELECT Count(Distinct (Emply_number) FROM Employ table where
WHERE LOG_TS BETWEEN
(TIMESTAMP(CURRENT DATE- 02 days ,'00:00')-
AND (TIMESTAMP(CURRENT DATE - 02 DAYS,'00:00')- 1 MICROSECOND) WITH UR;
rekhamf
 
Posts: 33
Joined: Mon Nov 28, 2011 5:09 am
Has thanked: 0 time
Been thanked: 0 time

Re: to fetch current date-2 date records count only

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

Well, the condition is perfectly valid as it is - might not do what you want but you have not provided the information asked for in the format asked for - using code tags. (See the full editor.)
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

Re: to fetch current date-2 date records count only

Postby rekhamf » Tue Nov 29, 2011 2:08 am

SELECT Count(Distinct (Emply_number) FROM Employ table where
WHERE LOG_TS BETWEEN
(TIMESTAMP(CURRENT DATE- 02 days ,'00:00')-
AND (TIMESTAMP(CURRENT DATE - 02 DAYS,'00:00')- 1 MICROSECOND) WITH UR;


I have this query .but i need someone to help with this condition : for particular date ( current date-2) .I tried many scenarios for this,but not able to find the exact condition.
rekhamf
 
Posts: 33
Joined: Mon Nov 28, 2011 5:09 am
Has thanked: 0 time
Been thanked: 0 time

Re: to fetch current date-2 date records count only

Postby rekhamf » Tue Nov 29, 2011 2:30 am

WHERE LOG_TS BETWEEN                                   
              TIMESTAMP(CURRENT DATE - 2 DAYS ,'00:00')
       AND      TIMESTAMP(CURRENT DATE - 2 DAYS,'24:00')
       WITH UR;                                         



I have change the where condition in this query
rekhamf
 
Posts: 33
Joined: Mon Nov 28, 2011 5:09 am
Has thanked: 0 time
Been thanked: 0 time

Re: to fetch current date-2 date records count only

Postby Akatsukami » Tue Nov 29, 2011 2:30 am

rekhamf wrote:
SELECT Count(Distinct (Emply_number) FROM Employ table where
WHERE LOG_TS BETWEEN
(TIMESTAMP(CURRENT DATE- 02 days ,'00:00')-
AND (TIMESTAMP(CURRENT DATE - 02 DAYS,'00:00')- 1 MICROSECOND) WITH UR;


I have this query .but i need someone to help with this condition : for particular date ( current date-2) .I tried many scenarios for this,but not able to find the exact condition.

Well, your "query" has two sets of unbalanced parentheses, an improperly repeated keyword, an typographical error, and an apparently missing duration, any of which would prevent it for running; therefore, this isn't the SQL that you're actually using. Since we can't literally hold your feet to the fire to get the truth out of you, we can't help and I won't try.
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: to fetch current date-2 date records count only

Postby GuyC » Tue Nov 29, 2011 7:49 pm

I must say this looks very much like the query I gave you yesterday :
ibm-cobol/topic6695.html
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: to fetch current date-2 date records count only

Postby rekhamf » Tue Nov 29, 2011 10:58 pm

Thanks Guyc.I tried with same model of query for this .its working .you helped me lot.Thank you so much
rekhamf
 
Posts: 33
Joined: Mon Nov 28, 2011 5:09 am
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post