restrict the past yeared values to retrive

Question

0.00/5 (No votes)

See more:

hi,,

for my application

i want to check the scheme before the Valid_From_Date also and also to restrict to retrive if the date is after the expiration of Valid_To_Date..

SQL

select (case when Deposit_Type =1 then 'RD' else 'FD' end) as Deposit_Type,
        ID,
        Scheme_Name,
        (case when Mode_Operation=1 then 'General' else 'SC' end)as Mode_Operation,
        Period_Type,
        Period_Value,
        Type_Interest,
        Interest_Rate,
        LockIn_Period,
        PNL_Interest,  
        Is_Active from Scheme WHERE GETDATE() BETWEEN Valid_From_Date AND Valid_To_Date

Posted 5-Aug-12 20:55pm

sk. maqdoom ali

Updated 5-Aug-12 20:59pm

Mehdi Gholam

v2

Add a Solution

Comments

OriginalGriff 6-Aug-12 3:00am

And?
What problem are you having?

sk. maqdoom ali 6-Aug-12 3:18am

I WANT TO GET RETRIVE THE DATA BEFORE THE DAY OF Valid_From_Date ex;Valid_From_Date = 12-08-2012 then for 11-08-2012 also i want to retrive the data
and also
not to retrive data Valid_To_Date which is expired before . for ex:Valid_To_Date = 12-07-2012 if we check it today this Valid_To_Date data must not be retrived

OriginalGriff 6-Aug-12 3:25am

Did I ask you to shout at me?
No.
So I am not answering any further.

sk. maqdoom ali 6-Aug-12 3:30am

no sir, i am not at all shout you , i just explained my requirement clearly thats it..... if i hurted u i am extreamly very very sorry.

OriginalGriff 6-Aug-12 3:42am

Using all capitals is considered shouting on the internet, and rude (using all lower case is considered childish). Use proper capitalisation if you want to be taken seriously.

sk. maqdoom ali 6-Aug-12 4:02am

ok sir, till now i don't know about this ,i will rectify it

AshishChaudha 6-Aug-12 3:01am

Where you are getting stuck??

sk. maqdoom ali 6-Aug-12 3:06am

I WANT TO GET RETRIVE THE DATA BEFORE THE DAY OF Valid_From_Date ex;Valid_From_Date = 12-08-2012 then for 11-08-2012 also i want to retrive the data

AshishChaudha 6-Aug-12 3:09am

you want to retrieve data of one day before Valid_From_Date ???

sk. maqdoom ali 6-Aug-12 3:13am

yes, and also not to retrive data Valid_To_Date which is expired before . for
ex:Valid_To_Date = 12-07-2012 if we check it today this Valid_To_Date data must not be retrived

Philip Stuyck 6-Aug-12 6:00am

You are still totally unclear about what you want.
The query mentioned above is the only thing I can understand, it fetches data based on the current date. If today is between the from and to date then you retrieve it. To me it seems that the to date is always later than the from date. And based on your explanation you want to get data before the from date but not before the to date. Which is nothing. And you are also not clear about what you are comparing with, the query mentions current date but you are not.

sk. maqdoom ali 6-Aug-12 6:28am

i compared like this and got the required solution

GETDATE() < = Valid_From_Date AND GETDATE() !> Valid_To_Date

Philip Stuyck 6-Aug-12 6:39am

actually that is the not between or the negation of your sql in the OP.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PopeDarren · Answer 1 · 2012-08-07T07:44:00

I think I understand what you're after. You want the record(s) previous to the current record?

If your query actually returns something, you could do this:

SQL

select (case when Deposit_Type =1 then 'RD' else 'FD' end) as Deposit_Type,
        ID,
        Scheme_Name,
        (case when Mode_Operation=1 then 'General' else 'SC' end)as Mode_Operation,
        Period_Type,
        Period_Value,
        Type_Interest,
        Interest_Rate,
        LockIn_Period,
        PNL_Interest,  
        Is_Active from [Scheme]
WHERE GETDATE() NOT BETWEEN Valid_From_Date AND Valid_To_Date

And if you want just the most current previous record:

SQL

select TOP 1 
(case when Deposit_Type =1 then 'RD' else 'FD' end) as Deposit_Type,
        ID,
        Scheme_Name,
        (case when Mode_Operation=1 then 'General' else 'SC' end)as Mode_Operation,
        Period_Type,
        Period_Value,
        Type_Interest,
        Interest_Rate,
        LockIn_Period,
        PNL_Interest,  
        Is_Active from [Scheme]
WHERE GETDATE() NOT BETWEEN Valid_From_Date AND Valid_To_Date
ORDER BY Valid_From_Date DESC

Santhosh Kumar Jayaraman · Answer 2 · 2012-08-07T15:40:00

SQL

select (case when Deposit_Type =1 then 'RD' else 'FD' end) as Deposit_Type,
        ID,
        Scheme_Name,
        (case when Mode_Operation=1 then 'General' else 'SC' end)as Mode_Operation,
        Period_Type,
        Period_Value,
        Type_Interest,
        Interest_Rate,
        LockIn_Period,
        PNL_Interest,  
        Is_Active from Scheme WHERE GETDATE() < Valid_To_Date

You want to fetch all records irrespective of validstartdate but the only condition is it should be valid as of current date

restrict the past yeared values to retrive

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0