• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

SQl Query

I have a query where I need to return records that are modified today.  I assume I can use the Modified ON column, but not sure how to word the Where clause.  Any help would be appreciated.

Thanks

SELECT     AYR_accountsreceivablesBase.ModifiedOn, AYR_accountsreceivablesExtensionBase.AYR_additionalmsns, AYR_accountsreceivablesExtensionBase.AYR_DueDate, 
                      AYR_accountsreceivablesExtensionBase.AYR_InvoiceType, AYR_accountsreceivablesExtensionBase.AYR_name, 
                      AYR_accountsreceivablesExtensionBase.AYR_Notes, AYR_accountsreceivablesExtensionBase.AYR_OpenClosed, 
                      AYR_accountsreceivablesExtensionBase.AYR_OtherMSN, AYR_accountsreceivablesExtensionBase.AYR_TotalAROS, 
                      AYR_accountsreceivablesExtensionBase.ayr_totalaros_Base, AYR_accountsreceivablesExtensionBase.AYR_TotalSDRcvd, 
                      AYR_accountsreceivablesExtensionBase.ayr_totalsdrcvd_Base, AYR_accountsreceivablesExtensionBase.AYR_MSN, 
                      AYR_accountsreceivablesExtensionBase.AYR_LOIOMName, AYR_accountsreceivablesExtensionBase.AYR_TotalOtherReceived, 
                      AYR_accountsreceivablesExtensionBase.ayr_accountid, AYR_accountsreceivablesExtensionBase.ayr_totalotherreceived_Base
FROM         AYR_accountsreceivablesBase INNER JOIN
                      AYR_accountsreceivablesExtensionBase ON 
                      AYR_accountsreceivablesBase.AYR_accountsreceivablesId = AYR_accountsreceivablesExtensionBase.AYR_accountsreceivablesId

Open in new window

0
bduenges
Asked:
bduenges
1 Solution
 
tigin44Commented:
try this
SELECT     AYR_accountsreceivablesBase.ModifiedOn, AYR_accountsreceivablesExtensionBase.AYR_additionalmsns, AYR_accountsreceivablesExtensionBase.AYR_DueDate, 
                      AYR_accountsreceivablesExtensionBase.AYR_InvoiceType, AYR_accountsreceivablesExtensionBase.AYR_name, 
                      AYR_accountsreceivablesExtensionBase.AYR_Notes, AYR_accountsreceivablesExtensionBase.AYR_OpenClosed, 
                      AYR_accountsreceivablesExtensionBase.AYR_OtherMSN, AYR_accountsreceivablesExtensionBase.AYR_TotalAROS, 
                      AYR_accountsreceivablesExtensionBase.ayr_totalaros_Base, AYR_accountsreceivablesExtensionBase.AYR_TotalSDRcvd, 
                      AYR_accountsreceivablesExtensionBase.ayr_totalsdrcvd_Base, AYR_accountsreceivablesExtensionBase.AYR_MSN, 
                      AYR_accountsreceivablesExtensionBase.AYR_LOIOMName, AYR_accountsreceivablesExtensionBase.AYR_TotalOtherReceived, 
                      AYR_accountsreceivablesExtensionBase.ayr_accountid, AYR_accountsreceivablesExtensionBase.ayr_totalotherreceived_Base
FROM         AYR_accountsreceivablesBase INNER JOIN
                      AYR_accountsreceivablesExtensionBase ON 
                      AYR_accountsreceivablesBase.AYR_accountsreceivablesId = AYR_accountsreceivablesExtensionBase.AYR_accountsreceivablesId
WHERE AYR_accountsreceivablesBase.ModifiedOn > CONVERT(datetime, CONVERT(date, getdate(), 101), 101)

Open in new window

0
 
vastoCommented:
if ModifiedOn  contains just Date:

WHERE AYR_accountsreceivablesBase.ModifiedOn = convert(datetime,floor(convert(decimal(19,9),GETDATE())))

if it contains Date and Time:

WHERE convert(datetime,floor(convert(decimal(19,9),GETDATE()))) <= AYR_accountsreceivablesBase.ModifiedOn
AND AYR_accountsreceivablesBase.ModifiedOn < dateadd(d,1convert(datetime,floor(convert(decimal(19,9),GETDATE()))))
0
 
Anthony PerkinsCommented:
Something like this:
SELECT  r.ModifiedOn,
        r.AYR_additionalmsns,
        r.AYR_DueDate,
        r.AYR_InvoiceType,
        r.AYR_name,
        r.AYR_Notes,
        r.AYR_OpenClosed,
        r.AYR_OtherMSN,
        r.AYR_TotalAROS,
        r.ayr_totalaros_Base,
        r.AYR_TotalSDRcvd,
        r.ayr_totalsdrcvd_Base,
        r.AYR_MSN,
        r.AYR_LOIOMName,
        r.AYR_TotalOtherReceived,
        r.ayr_accountid,
        r.ayr_totalotherreceived_Base
FROM    AYR_accountsreceivablesBase r
        INNER JOIN AYR_accountsreceivablesExtensionBase re ON r.AYR_accountsreceivablesId = r.AYR_accountsreceivablesId
WHERE   r.ModifiedOn >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))

Open in new window

0
 
Anthony PerkinsCommented:
Let's try that again:
SELECT  r.ModifiedOn,
        re.AYR_additionalmsns,
        re.AYR_DueDate,
        re.AYR_InvoiceType,
        re.AYR_name,
        re.AYR_Notes,
        re.AYR_OpenClosed,
        re.AYR_OtherMSN,
        re.AYR_TotalAROS,
        re.ayr_totalaros_Base,
        re.AYR_TotalSDRcvd,
        re.ayr_totalsdrcvd_Base,
        re.AYR_MSN,
        re.AYR_LOIOMName,
        re.AYR_TotalOtherReceived,
        re.ayr_accountid,
        re.ayr_totalotherreceived_Base
FROM    AYR_accountsreceivablesBase r
        INNER JOIN AYR_accountsreceivablesExtensionBase re ON r.AYR_accountsreceivablesId = re.AYR_accountsreceivablesId
WHERE   r.ModifiedOn >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))

Open in new window

0
 
bduengesAuthor Commented:

acperkins,

Your code worked, but the query I should be using is this and I tried adding the line you indicated but it gave me more than today's modified records.
SELECT     AYR_accountsreceivablesExtensionBase.AYR_Notes, AYR_accountsreceivablesExtensionBase.AYR_OpenClosed, 
                      AYR_accountsreceivablesExtensionBase.AYR_InvoiceType, 
                      CASE AYR_accountsreceivablesExtensionBase.AYR_InvoiceType WHEN 1 THEN 'Rental' WHEN 2 THEN 'Maintenance' WHEN 3 THEN 'Rental/Maintenance' WHEN 4 THEN
                       'LOI' WHEN 5 THEN 'Other' WHEN 6 THEN 'Loan' WHEN 7 THEN 'Taxes' WHEN 8 THEN 'Fees' ELSE 'Unknown' END AS INVTYPE, 
                      AYR_accountsreceivablesExtensionBase.ayr_totalaros_Base, AYR_accountsreceivablesExtensionBase.AYR_TotalAROS, 
                      AYR_accountsreceivablesExtensionBase.AYR_DueDate, AYR_accountsreceivablesExtensionBase.ayr_accountid, 
                      AYR_accountsreceivablesExtensionBase.AYR_MSN, AccountBase.Name, AYR_accountsreceivablesBase.ModifiedOn
FROM         AYR_accountsreceivablesExtensionBase INNER JOIN
                      AccountBase ON AYR_accountsreceivablesExtensionBase.ayr_accountid = AccountBase.AccountId INNER JOIN
                      AYR_accountsreceivablesBase ON 
                      AYR_accountsreceivablesExtensionBase.AYR_accountsreceivablesId = AYR_accountsreceivablesBase.AYR_accountsreceivablesId
WHERE     (AYR_accountsreceivablesExtensionBase.AYR_InvoiceType = 1) AND (AYR_accountsreceivablesExtensionBase.AYR_OpenClosed = 1) OR
                      (AYR_accountsreceivablesExtensionBase.AYR_InvoiceType = 2) AND (AYR_accountsreceivablesExtensionBase.AYR_OpenClosed = 1) OR
                      (AYR_accountsreceivablesExtensionBase.AYR_InvoiceType = 3) AND (AYR_accountsreceivablesExtensionBase.AYR_OpenClosed = 1) OR
                      (AYR_accountsreceivablesExtensionBase.AYR_InvoiceType = 6) AND (AYR_accountsreceivablesExtensionBase.AYR_OpenClosed = 1)
ORDER BY AYR_accountsreceivablesExtensionBase.AYR_DueDate

Open in new window

0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now