getting Data in between two dates

Question

0.00/5 (No votes)

See more:

Hey ,

i am working on a query , but having a problem setting up my boundaries for the query.

here is what i want to do . records between 10/1/2010 and 12/31/2010 but without a record (activity) in calendar year 2011 to date.

where INV.Date_Imported BETWEEN '10/1/2010'  AND   '12/31/2010'
AND INV.RecID NOT IN ( SELECT  RecID 
                       FROM   [VW_Invoice_All] 
                       WHERE   Date_Imported > '1/1/2011' )

Posted 8-Aug-11 9:13am

anjumnavid

Updated 8-Aug-11 10:50am

Wendelius

v2

Add a Solution

3 solutions

Solution 1

Does INV.Date_Imported also includes a time component? If 'yes', then you should consider changing your query as follows:

SQL

where INV.Date_Imported BETWEEN '10/1/2010' AND '12/31/2010 23:59:59'
AND INV.RecID NOT IN ( SELECT RecID FROM [VW_Invoice_All] WHERE Date_Imported > '1/1/2011' )

I hope this helps.

Posted 8-Aug-11 9:27am

Wagner DosAnjos

Comments

anjumnavid 8-Aug-11 15:31pm

no time component.

my query is almost work but not 100%. i would like data in between ('10/1/2010' AND '12/31/2010) BUT not in 2011.

Kschuler 8-Aug-11 16:00pm

Could you elaborate about how it isn't currently working? Are you getting dates that are in 2011? Or are you missing dates that are in 2010, for example are you missing 12/31/2010 when you are expecting a record with it?

anjumnavid 8-Aug-11 16:02pm

i am getting data in 2010 which is also in 2011. But if they are in 2011 don't , then query should filter those record.

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)

anjumnavid · Accepted Answer · 2011-08-08T11:10:00

This is my Query .

SQL

SELECT  DISTINCT
        EIE.[ID] ,
        EIE.[Lastname] ,
        EIE.[Firstname] ,
        EIE.[Email] ,
        EIE.AddressOne ,
        EIE.City ,
        EIE.State ,
        EIE.Zip ,
        EIE.Phone ,
        EIE.EveningPhone ,
        EIE.Email
        ,INV.Date_Imported
FROM   [tbl_EI_EmpInfo] AS EIE
        JOIN [tbl_EI_SDis] AS EISD ON EIE.ID = EISD.EmpID
        JOIN [tbl_EI_Status] AS EIS ON EIE.ID = EIS.EmpID
        JOIN [tbl_EI_Sdis_Fun] AS ESD ON EISD.EmpID = ESD.EmpID AND ESD.SDID = EISD.SDID
        INNER JOIN [VW_Invoice_All] AS INV ON EIE.ID = INV.T_ID
WHERE   Email <>
        AND EISD.Active = 1
        AND EIS.TDate IS NULL
        AND INV.Date_Imported >= '10/1/2010'
        AND INV.Date_Imported < '1/1/2011'
        AND NOT EXISTS ( SELECT *
                         FROM   [VW_Invoice_All] I2
                         WHERE  I2.RecID = INV.RecID
                                AND I2.Date_Imported >= '1/1/2011' AND INV.RecID = INV.RecID

Wendelius · Accepted Answer · 2011-08-08T11:01:00

Hi,

Few notes to the query. In the inner select use alias for the table name. I take it, you're using the same table (or view) in both queries so try modifying the query to something like:

SQL

where INV.Date_Imported BETWEEN '10/1/2010'  AND   '12/31/2010'
AND INV.RecID NOT IN ( SELECT  inner1.RecID
                       FROM   [VW_Invoice_All] inner1
                       WHERE   inner1.Date_Imported > '1/1/2011' )

Also instead of using literals in the statement try changing them to bind variables.

About the conditions, if the table is the same in the outer query and the inner in-list select, I don't quite understand why you have basically the same condition twice. Could you post the whole query?

getting Data in between two dates

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0