Post reply

Checking for consecutive records in recordset

  • May 9, 2011 at 11:26 am

    #239324

    Hi all,

    This is a general sql question. I hope im putting this in the right forum.

    I'm using an example below to make it easier to understand.

    Say I have a table with monthly rainfall figures by location, if there's no rainfall, then no entry. How would i filter locations which had rainfall for more than 1 consecutive month over a period of time say a year.

    Cheers

    Gerry in Dublin

  • May 9, 2011 at 11:31 am

    #1321897

    Hi,

    You may want to post the schema involved as well as some sample data to get a better response.

    Regards,

    Welsh

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • May 9, 2011 at 12:39 pm

    #1321945

    I'd say with a combination of ROW_NUMBER(), DATEADD(), DATEDIFF() and possibly a self-referencing join. Maybe even RANK() and DENSE_RANK().

    These links may help:

    SQL Server Ranking Functions[/url]

    Common Date Routines[/u]

    You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

    Things that we'd need to know:

    1. Are you using a datetime data type in that table?

    2. What date are you using to indicate rainfall for a month (first of month? last of month? something else?)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 3 posts - 1 through 2 (of 2 total)

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