Post reply

find dates where over x # of rows were created?

  • May 30, 2013 at 1:18 pm

    #273341

    Hello - I need to query a table to find out dates on which more than a certain number of records were created. The CreatedOn/ModifiedOn columns have a datetime data type. This is just an ad hoc query so performance is not important for this query. Thanks!

  • May 30, 2013 at 1:28 pm

    #1619857

    To help those who will attempt to help you ... can / will you provide the table definition (or those fields pertinent to your question), some sample data and the desired answer using the sample data.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • May 30, 2013 at 1:30 pm

    #1619859

    my wild guess to provide a rough example: note i'm truncating the time portion of hte datetimes to the day.

    With YourTable

    As(

    SELECT 1 AS SomeColumnForGrouping,CONVERT(datetime,'2013-05-30 07:26:30.157') AS CreatedOn UNION ALL

    SELECT 1 ,CONVERT(datetime,'2013-05-30 15:26:30.157') UNION ALL

    SELECT 2 ,CONVERT(datetime,'2013-05-30 15:36:30.157')

    )

    SELECT

    SomeColumnForGrouping,

    COUNT(DATEADD(dd, DATEDIFF(dd,0,CreatedOn), 0)) As TheCount,

    DATEADD(dd, DATEDIFF(dd,0,CreatedOn), 0) As DateWithZeroedTime

    From YourTable

    GROUP BY

    SomeColumnForGrouping,DATEADD(dd, DATEDIFF(dd,0,CreatedOn), 0)

    HAVING COUNT(DATEADD(dd, DATEDIFF(dd,0,CreatedOn), 0)

    ) > 1

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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