Post reply

Reporting a single record for multiple occurrences spanning 3 days

  • April 7, 2009 at 10:31 am

    #129155

    I need to write a query to emulate the functionality of a SELECT DISTINCT query which reports a single record where the datetime field has a date within 3 days of a particular record. In the attached spreadsheet I would want the sql statement to report two records:

    324329 03/04/2009

    and

    324329 06/04/2009

    as row 17 would be the first record, and the next record outside the 3 day range (in ascending date order) would be row 8.

    Any ideas please?

  • April 7, 2009 at 12:00 pm

    #973697

    Here's the test data taken from your attachment:

    CREATE TABLE #FileData (

    File_ref int,

    tran_datetime datetime

    )

    INSERT #FileData (File_ref, tran_datetime)

    SELECT 324329, '20090406 10:47:00' UNION ALL

    SELECT 324329, '20090406 07:41:00' UNION ALL

    SELECT 324329, '20090406 07:41:00' UNION ALL

    SELECT 324329, '20090406 07:40:00' UNION ALL

    SELECT 324329, '20090406 07:40:00' UNION ALL

    SELECT 324329, '20090406 07:40:00' UNION ALL

    SELECT 324329, '20090406 07:40:00' UNION ALL

    SELECT 324329, '20090404 08:34:00' UNION ALL

    SELECT 324329, '20090403 07:47:00' UNION ALL

    SELECT 324329, '20090403 07:47:00' UNION ALL

    SELECT 324329, '20090403 07:45:00' UNION ALL

    SELECT 324329, '20090403 07:45:00' UNION ALL

    SELECT 324329, '20090403 07:44:00' UNION ALL

    SELECT 324329, '20090403 07:44:00' UNION ALL

    SELECT 324329, '20090403 07:44:00' UNION ALL

    SELECT 324329, '20090403 07:42:00'

    My solution groups the rows by the File_ref column and the difference in days between the tran_datetime column of each row with a particular File_ref value and the minimum value of the tran_datetime column with the same File_ref value divided by 3 (integer division). The grouping therefore breaks the rows for each File_ref value into groups of 3-day intervals, and the query returns the minimum tran_datetime value within each of these groups.

    SELECT F.File_ref, MIN(F.tran_datetime) AS tran_datetime

    FROM #FileData F

    INNER JOIN (

    SELECT File_ref, MIN(tran_datetime) AS Min_tran_datetime

    FROM #FileData

    GROUP BY File_ref

    ) FMIN ON (F.File_ref = FMIN.File_ref)

    GROUP BY F.File_ref, DATEDIFF(day, FMIN.Min_tran_datetime, F.tran_datetime) / 3

    ORDER BY F.File_ref

    The query returns the following when run against the test data above, which seems to match your requirements.

    File_ref tran_datetime

    ----------- ------------------------

    324329 2009-04-03 07:42:00.000

    324329 2009-04-06 07:40:00.000

    EDIT: Removed column from ORDER BY clause that has no effect on output

  • April 8, 2009 at 2:53 am

    #974085

    That's brilliant! Many thanks for that. Genius!!!

  • April 9, 2009 at 8:59 am

    #975127

    As a matter of interest, did you generate the create table and insert statement electronically or did you type it all out? I'm currently trying to log another call and need to provide a similar command to illustrate my issue. Don't want to type it all out manually though.

  • April 9, 2009 at 9:23 am

    #975154

    I did it semi-automatically using a regular expression based find-and-replace function in a text editior called TextPad.

    There are TSQL scripts available that you can use to automatically generate insert statements from your database - have a look at this article by Jeff Moden.

    http://qa.sqlservercentral.com/articles/Best+Practices/61537/

  • April 9, 2009 at 9:35 am

    #975167

    Thanks. Just what I needed! Double genius!!!

Viewing 6 posts - 1 through 5 (of 5 total)

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