Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Report a single record for multiple records within a date range

Posted on 2009-04-07
21
Medium Priority
?
292 Views
Last Modified: 2012-05-06
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
Comment
Question by:Grffster
  • 9
  • 7
  • 3
  • +1
21 Comments
 
LVL 6

Expert Comment

by:J_Carter
ID: 24089322
select File_ref, CONVERT(VARCHAR,tran_datetime102)
from TABLE
group by File_ref, CONVERT(VARCHAR,tran_datetime102)
0
 
LVL 6

Expert Comment

by:J_Carter
ID: 24089332
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
 

Author Comment

by:Grffster
ID: 24089350
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 6

Expert Comment

by:J_Carter
ID: 24089445
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
 

Author Comment

by:Grffster
ID: 24094940
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
 
LVL 2

Expert Comment

by:cthulthu
ID: 24099300
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
 

Author Comment

by:Grffster
ID: 24105156
I don't think SSRS is that sophisticated really.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24148718
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
 

Author Comment

by:Grffster
ID: 24149204
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24149404
It looks like you need to have an INSERT INTO, instead of the UNION ALL, in a WHILE loop, with a counter.
0
 

Author Comment

by:Grffster
ID: 24202603
That statement is to populate my test data not the solution.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24202762
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
 

Author Comment

by:Grffster
ID: 24202822
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24203193
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
 

Author Comment

by:Grffster
ID: 24205952
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24205975
Hmmm...I used your test data, had something like 16 input records, and the output results showed only 2 lines...
0
 

Author Comment

by:Grffster
ID: 24206456
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24208901
I wasn't using that query since it didn't seem to work.  Are you still trying to make it work?
0
 

Author Comment

by:Grffster
ID: 24212884
Yes, unfortunately. How did you get two records in the end?
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 2000 total points
ID: 24213166
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question