[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

Report a single record for multiple records within a date range

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?
example.xls
0
Grffster
Asked:
Grffster
  • 9
  • 7
  • 3
  • +1
1 Solution
 
J_CarterCommented:
select File_ref, CONVERT(VARCHAR,tran_datetime102)
from TABLE
group by File_ref, CONVERT(VARCHAR,tran_datetime102)
0
 
J_CarterCommented:
sorry, there was a typo.

select File_ref, CONVERT(VARCHAR,tran_datetime,102)
from TABLE
group by File_ref, CONVERT(VARCHAR,tran_datetime,102)
0
 
GrffsterAuthor Commented:
That won't group together those within 3 days of each other though. In the example file I would want to group those records from the 3rd and 4th of the month you see.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
J_CarterCommented:
try this then
select File_ref, CONVERT(VARCHAR,tran_datetime,102)
from TABLE t1
join TABLE t2 on t1.File_ref = t2.File_ref AND DATEDIFF(day, t1.tran_datetime, t2.tran_datetime) < 3
group by File_ref, CONVERT(VARCHAR,tran_datetime,102)

Open in new window

0
 
GrffsterAuthor Commented:
That doesn't work either unfortunately. It gives me exactly the same results as your previous query i.e. reports 3 records, one for each day. Would need to see one record to cater for those records on the the 3rd and 4th of april, and another one for those on the 6th of april.
CREATE TABLE #TempData1 (
   
File_ref int,
   
tran_datetime datetime
)
 
 
INSERT #TempData1 (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'

Open in new window

0
 
cthulthuCommented:
Why does the SQL statement have to do all the work?  Can't you get most of the way there and then use your reporting tool or some sort of post processing to clean up the records as you need them?
0
 
GrffsterAuthor Commented:
I don't think SSRS is that sophisticated really.
0
 
Bob LearnedCommented:
I would love to help, but I don't completely understand your requirement.  Are you displaying a single-record report, or is there a UNION involved?
0
 
GrffsterAuthor Commented:
I'm trying to obtain a table with all the records that will be created using the interval data above. I've attached a copy of what the results would look like if I only used the first three records of my test file above (changing the interval type from M (months) to D (days) to illustrate the point).  Ignore the formatting in this spreadsheet. I manually typed most of it.
test-data-for-promise-to-pays.xls
0
 
Bob LearnedCommented:
It looks like you need to have an INSERT INTO, instead of the UNION ALL, in a WHILE loop, with a counter.
0
 
GrffsterAuthor Commented:
That statement is to populate my test data not the solution.
0
 
Bob LearnedCommented:
I have to admit that I don't understand what kind of help that you need, and all the comments aren't helping either.
0
 
GrffsterAuthor Commented:
J Carter's solutions weren't working as they were still grouping by date. I need to group records that occur within 3 days of each other as one record. Apologies for the test file called test-data-for-promise-to-pays.xls. This was meant for another open question I had. This may be confusing the issue.  The test data you need to view is the one with ID 24094940. I was getting three records here using J Carter's solution, one for the 3rd of april, one for the 4th and one for the 6th. There should only be 2 records: one for the 3rd of april (which would include those from the 4th as it's within 3 days of the 3rd) and one for the 6th of april.
0
 
Bob LearnedCommented:
So, you are talking about this query:

SELECT File_ref, CONVERT(VARCHAR, tran_datetime, 102) AS tran_datetime
   FROM TABLE t1
   INNER JOIN TABLE t2
        ON t1.File_ref = t2.File_ref AND
             DATEDIFF(day, t1.tran_datetime, t2.tran_datetime) < 3
   GROUP BY File_ref, CONVERT(VARCHAR, tran_datetime, 102)

I have attached a document of my experimentation.
SQL-Server-Query.doc
0
 
GrffsterAuthor Commented:
Tried your code thanks but it's still reporting three records, one for 3rd of april, one for 4th, and one for the 6th.
0
 
Bob LearnedCommented:
Hmmm...I used your test data, had something like 16 input records, and the output results showed only 2 lines...
0
 
GrffsterAuthor Commented:
This is how I called it from your function:

SELECT t1.File_ref, CONVERT(VARCHAR, t1.tran_datetime, 102) AS tran_datetime
   FROM dbo.TestData() t1
   INNER JOIN dbo.TestData() t2
        ON t1.File_ref = t2.File_ref AND
             DATEDIFF(day, t1.tran_datetime, t2.tran_datetime) < 3
   GROUP BY t1.File_ref, CONVERT(VARCHAR, t1.tran_datetime, 102)
0
 
Bob LearnedCommented:
I wasn't using that query since it didn't seem to work.  Are you still trying to make it work?
0
 
GrffsterAuthor Commented:
Yes, unfortunately. How did you get two records in the end?
0
 
Bob LearnedCommented:
I got those 2 records from the stored procedure.  I saw an anomaly from the query and inner join approach, since it results in too many records (cartesian product), and appears to be a flawed approach.  

I used the approach of selecting just a unique set of records as the basis,

      SELECT file_ref, CONVERT(varchar, tran_datetime, 101) AS tran_datetime
          FROM TestData()
          GROUP BY file_ref, CONVERT(varchar, tran_datetime, 101)

and, then scanning the records, comparing the difference between a previously stored date and the current date, looking for >= 3 day difference.
DECLARE Data_Cursor CURSOR FOR
SELECT file_ref, CONVERT(varchar, tran_datetime, 101) AS tran_datetime
  FROM TestData()
  GROUP BY file_ref, CONVERT(varchar, tran_datetime, 101)
  
DECLARE @ResultTable TABLE (File_ref int, tran_datetime smalldatetime, date_diff smallint)
DECLARE @File_ref int, @tran_datetime smalldatetime
DECLARE @start_file_ref int, @start_tran_datetime smalldatetime
DECLARE @date_diff smallint
DECLARE @Fetch_Status smallint
 
OPEN Data_Cursor;
FETCH NEXT FROM Data_Cursor INTO @start_file_ref, @start_tran_datetime;
SET @Fetch_Status = @@FETCH_STATUS
         INSERT INTO @ResultTable (file_ref, tran_datetime, date_diff) 
            VALUES (@start_file_ref, @start_tran_datetime, 0)
WHILE @Fetch_Status = 0
   BEGIN
      FETCH NEXT FROM Data_Cursor INTO @File_ref, @tran_datetime
      SET @Fetch_Status = @@FETCH_STATUS
      SET @date_diff = DATEDIFF(DAY, @start_tran_datetime, @tran_datetime)
      IF @date_diff >= 3
      BEGIN
         INSERT INTO @ResultTable (file_ref, tran_datetime, date_diff) 
            VALUES (@file_ref, @tran_datetime, @date_diff)
         SET @start_file_ref = @File_ref
         SET @start_tran_datetime = @tran_datetime        
      END
   END;
CLOSE Data_Cursor;
DEALLOCATE Data_Cursor;
 
SELECT * 
   FROM @ResultTable

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 7
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now