Select all records in current month

  • I have to change a prior report which included all records in which DateFinished is in the last 2 weeks to all records in the current month and am struggling to find the correct syntax. I have tried many things but I know TSQL and this does not work WHERE YEAR(DateFinished) = YEAR(GetDate()) AND MONTH(DateFinished) = MONTH(GetDate()).

  • Can you post your original query, George? The last two weeks' version? Cheers.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • george.greiner (12/5/2012)


    I have to change a prior report which included all records in which DateFinished is in the last 2 weeks to all records in the current month and am struggling to find the correct syntax. I have tried many things but I know TSQL and this does not work WHERE YEAR(DateFinished) = YEAR(GetDate()) AND MONTH(DateFinished) = MONTH(GetDate()).

    Firstly, when looking for a month of data you're better off with this: -

    WHERE DateFinished <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND DateFinished > DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)

    Which allows the query optimiser to seek on any index that may be on your DateFinished column.

    Secondly, define what "does not work" means. You get a syntax error? You have no results? You have incorrect results?

    Thirdly, have a read through this article --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D, which is all about how best to ask a question and expect to get an answer. If you follow the advise, then someone will be able to give you a fully coded working example for you to adapt for use in your particular environment.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • ChrisM@Work (12/5/2012)


    Can you post your original query, George? The last two weeks' version? Cheers.

    My original query was:

    SELECT PropertyInformation.BRTNumber, PropertyInformation.ClientsKey, PropertyInformation.ProductKey, PropertyInformation.DateFinished,

    PropertyInformation.Finished, PropertyInformation.ReportType, PropertyInformation.Premises, PropertyInformation.OrderDate,

    PropertyInformation.ClientKey, PropertyInformation.Invoiced, Fees.CaseNumberKey, Fees.Total, Fees.Summary, ClientTable.ClientKey AS Expr1

    FROM PropertyInformation INNER JOIN

    Fees ON PropertyInformation.CaseNumberKey = Fees.CaseNumberKey INNER JOIN

    ClientTable ON PropertyInformation.ClientKey = ClientTable.ClientKey

    WHERE (PropertyInformation.Finished = - 1) AND (PropertyInformation.ClientKey = 2) AND (PropertyInformation.DateFinished >= DATEADD(hour, - 336,

    GETDATE())) AND (PropertyInformation.OrderDate > CONVERT(DATETIME, '2011-06-30 00:00:00', 102))

    ORDER BY PropertyInformation.ClientsKey

    Obviously this does not translate though as 1 month is not always a set amount of time.

  • Cadavre (12/5/2012)


    george.greiner (12/5/2012)


    I have to change a prior report which included all records in which DateFinished is in the last 2 weeks to all records in the current month and am struggling to find the correct syntax. I have tried many things but I know TSQL and this does not work WHERE YEAR(DateFinished) = YEAR(GetDate()) AND MONTH(DateFinished) = MONTH(GetDate()).

    Firstly, when looking for a month of data you're better off with this: -

    WHERE DateFinished <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND DateFinished > DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)

    Which allows the query optimiser to seek on any index that may be on your DateFinished column.

    Secondly, define what "does not work" means. You get a syntax error? You have no results? You have incorrect results?

    Thirdly, have a read through this article --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D, which is all about how best to ask a question and expect to get an answer. If you follow the advise, then someone will be able to give you a fully coded working example for you to adapt for use in your particular environment.

    When I use the code I provided I get no results and as of today there should be 7.

    When I use your code I get a syntax error.

  • -- run this and check DateFinished in the output to see how many

    -- rows you should expect to return for December 2012, the current month.

    -- The oldest rows will be from the last half hour or so of November.

    SELECT

    p.BRTNumber,

    p.ClientsKey,

    p.ProductKey,

    p.DateFinished,

    p.Finished,

    p.ReportType,

    p.Premises,

    p.OrderDate,

    p.ClientKey,

    p.Invoiced,

    f.CaseNumberKey,

    f.Total,

    f.Summary,

    c.ClientKey AS Expr1

    FROM PropertyInformation p

    INNER JOIN Fees f

    ON p.CaseNumberKey = f.CaseNumberKey

    INNER JOIN ClientTable c

    ON p.ClientKey = c.ClientKey

    WHERE p.Finished = - 1

    AND p.ClientKey = 2

    AND p.DateFinished >= DATEADD(hour, -135, GETDATE())

    AND p.OrderDate > CONVERT(DATETIME, '2011-06-30 00:00:00', 102)

    ORDER BY p.DateFinished DESC --p.ClientsKey

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • I should have 7 rows. I already have a query that my business partner always asks me to run to check on client volume by whatever he feels like it that day.

    USE newCityCollection

    SELECT CaseNumberKey, DateFinished

    FROM PropertyInformation

    WHERE DateFinished between '12/1/2012' AND '12/06/2012' AND ClientKey = 3

    Results:

    CaseNumberKeyDateFinished

    0024282012-12-03 09:24:45.093

    0024292012-12-03 12:32:29.687

    0024302012-12-04 10:39:20.280

    0024362012-12-04 10:43:55.640

    0024382012-12-04 11:10:59.877

    0024392012-12-04 12:11:24.377

    0024402012-12-04 12:37:57.267

  • george.greiner (12/5/2012)


    Cadavre (12/5/2012)


    george.greiner (12/5/2012)


    I have to change a prior report which included all records in which DateFinished is in the last 2 weeks to all records in the current month and am struggling to find the correct syntax. I have tried many things but I know TSQL and this does not work WHERE YEAR(DateFinished) = YEAR(GetDate()) AND MONTH(DateFinished) = MONTH(GetDate()).

    Firstly, when looking for a month of data you're better off with this: -

    WHERE DateFinished <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND DateFinished > DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)

    Which allows the query optimiser to seek on any index that may be on your DateFinished column.

    Secondly, define what "does not work" means. You get a syntax error? You have no results? You have incorrect results?

    Thirdly, have a read through this article --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D, which is all about how best to ask a question and expect to get an answer. If you follow the advise, then someone will be able to give you a fully coded working example for you to adapt for use in your particular environment.

    When I use the code I provided I get no results and as of today there should be 7.

    When I use your code I get a syntax error.

    Yes, for some reason the forum changed < to & l t ; and > to & g t ;

    It should read: -

    WHERE DateFinished <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND DateFinished > DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)

    This is irrelevant though. Without the sample data showing your issue so that we can have a look, it's pretty impossible to know why you aren't getting the results you require. As in my previous post, please take a look at this article --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D, which explains how to post sample data.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • I think the comparison operators in the example above were the wrong way round. I have used the following against a date column in a test system of mine and get the expected answer.

    where DateFinished >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) and

    DateFinished <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)

    In my system this shows all orders for January 2013.

    Hope this helps.

  • Grasshopper:

    I think your query is wrong

    where DateFinished >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) and

    DateFinished <= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0

    it will display from Jan1 to Feb1 in this logic

    It has to be

    where DateFinished >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) and

    DateFinished < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0

    I guess 🙂

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply