在两个日期之间获取数据 [英] getting Data in between two dates

问题描述

嘿，

我正在处理查询，但在设置查询边界时遇到问题.

这是我想做的.在2010年10月1日至2010年12月31日之间的记录，但至今没有2011日历年的记录(活动).

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'' )

推荐答案

这是我的查询.

This is my Query .

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

INV.Date_Imported 还包括时间部分?如果是"，则应考虑按以下方式更改查询:

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

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.

您好，

有关查询的注释很少.在内部，选择使用别名作为表名.我接受了，您在两个查询中都使用了相同的表(或视图)，因此请尝试将查询修改为以下内容:

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:

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?

这篇关于在两个日期之间获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！